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).
