Here is a code snippet from one of my functions that get a list of ranges,
i.e '1_10,11-100,' and separate them into a vector.
(needs comma also after last value for easier parsing).
The function operates under a package so I can retain variables between
activation of the functions.
I keep the last 2 different values passed so if there is a repeat call I do
not have to parse the range list again.
<package body>
CREATE OR REPLACE PACKAGE "RANGE"
AS
--
-- Developed by: Yechiel Adar, Mehish Computer services.
--
-- E-mail: [EMAIL PROTECTED]
-- All Rights Reserved.
-- You can use, give away and modify this package freely,
-- just retain the credits.
-- Send any modifications and changes back to me so I can enhance my copy.
--
TYPE R_vec_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
current_ptr NUMBER DEFAULT 0;
R_low R_vec_type;
R_high R_vec_type;
no_of_ranges NUMBER DEFAULT 0;
SAVE_PARAM VARCHAR2(300) DEFAULT NULL;
R_low1 R_vec_type;
R_high1 R_vec_type;
no_of_ranges1 NUMBER DEFAULT 0;
SAVE_PARAM1 VARCHAR2(300) DEFAULT NULL;
R_low2 R_vec_type;
R_high2 R_vec_type;
no_of_ranges2 NUMBER DEFAULT 0;
SAVE_PARAM2 VARCHAR2(300) DEFAULT NULL;
</package body>
The
FUNCTION RANGES(RANGE_LIST IN VARCHAR2 , FORMULA_V IN NUMBER) RETURN
VARCHAR2 PARALLEL_ENABLE;
FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2 , FORMULA_V IN NUMBER) RETURN
VARCHAR2 PARALLEL_ENABLE;
PRAGMA RESTRICT_REFERENCES (RANGES, WNDS, RNDS);
END RANGE;
/
<function>
FUNCTION RANGES(RANGE_LIST IN VARCHAR2 ,FORMULA_V IN NUMBER) RETURN
VARCHAR2
IS
list_p VARCHAR2(300);
obligo_p NUMBER;
len NUMBER;
s1 VARCHAR2(300);
BEGIN
IF SAVE_PARAM = RANGE_LIST THEN
CURRENT_PTR := current_ptr;
ELSIF SAVE_PARAM1 = RANGE_LIST THEN
SAVE_PARAM := SAVE_PARAM1;
FOR I IN 1..NO_OF_RANGES1 LOOP
R_LOW(I) := R_LOW1(I);
R_HIGH(I) := R_HIGH1(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES1;
ELSIF SAVE_PARAM2 = RANGE_LIST THEN
-- dbms_output.put_line('2 ' || current_ptR);
SAVE_PARAM := SAVE_PARAM2;
FOR I IN 1..NO_OF_RANGES2 LOOP
R_LOW(I) := R_LOW2(I);
R_HIGH(I) := R_HIGH2(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES2;
ELSE
-- dbms_output.put_line('1 ' || CUrrent_ptR);
SAVE_PARAM := range_list;
list_p := range_list;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW(I) := NULL;
R_HIGH(I) := NULL;
END LOOP;
NO_OF_RANGES := 0;
FOR i IN 1..LENGTH(list_p) LOOP /* loop on input string with comma
delimited ranges
len := INSTR(list_p,','); /* find next comma
IF len IS NULL THEN
EXIT;
END IF;
no_of_ranges := no_of_ranges + 1;
s1 := SUBSTR(list_p,1,len); /* get the value until next
comma
list_p := SUBSTR(list_p,len+1); /* remove the value from input
string
len := INSTR(s1,'_'); /* put range start and
range end into two vectors.
R_low(i) := SUBSTR(s1,1,len-1);
R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);
END LOOP;
Yechiel Adar
Mehish
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, September 10, 2003 7:34 PM
> Hi all,
>
> How to work with Array in PL/SQL. I have an input
> string like 12,34,56,...... I need to get the values
> out and assign them to an array, and loop through the
> array to call a function. Do you have sample code for
> 1) value assignment to array and 2) loop through
> array. And it's all in pl/sql.
>
> Thank you.
>
> Andrea
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Andrea Oracle
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yechiel Adar
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
