Oracle Query
-
Hi all, I have a problem with executing function in Oracle 10g. Below mentioned is the function :- FUNCTION UnpackArray ( Source IN VARCHAR2 DEFAULT NULL, Delimiter IN CHAR DEFAULT ',' ) RETURN reSourceArray0 PIPELINED IS SourceArray00 SourceArray0:=SourceArray0(NULL); TYPE REFCURSOR IS REF CURSOR; CURSOR0 REFCURSOR; DelLen int; Pos int; Cnt int; str int; LEN int; Holder VARCHAR2(255); BEGIN IF Source is null or Delimiter is null THEN Return; END IF; IF RTRIM(LTRIM(Source)) = ' ' THEN Return; END IF; SELECT LENGTH(RTRIM(Delimiter)) INTO DelLen FROM DUAL; SELECT INSTR(UPPER(Source), UPPER(Delimiter)) INTO Pos FROM DUAL; IF Pos = 0 THEN BEGIN INSERT INTO UnpackArray_TBL ( Data ) VALUES ( Source ); return; OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL; END; END IF; SELECT 1 INTO str FROM DUAL; << LABEL4 >> WHILE Pos > 0 LOOP BEGIN SELECT Pos - str INTO len FROM DUAL; SELECT SUBSTR(Source, str, len) INTO Holder FROM DUAL; INSERT INTO UnpackArray_TBL VALUES ( Holder ); SELECT Pos + DelLen INTO str FROM DUAL; SELECT INSTR(UPPER(Source), UPPER(Delimiter), str) INTO Pos FROM DUAL; OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL; END; END LOOP; SELECT SUBSTR(Source, str, length(RTRIM(Source))) INTO Holder FROM DUAL; IF length(RTRIM(Holder)) > 0 THEN INSERT INTO UnpackArray_TBL VALUES ( Holder ); OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL; END IF; Return; LOOP FETCH CURSOR0 INTO SourceArray00.Data; EXIT WHEN CURSOR0%NOTFOUND; PIPE ROW(SourceArray00); END LOOP; CLOSE CURSOR0; RETURN; END; While executing this am getting the error like: Running "EDOCS"."UNPACKARRAY" ( SOURCE = admin, DELIMITER = ). Parameter 'RETURN_VALUE': No size set for variable length data type: String. can anybody help me to solve this...? Thanks in advance.
-
Hi all, I have a problem with executing function in Oracle 10g. Below mentioned is the function :- FUNCTION UnpackArray ( Source IN VARCHAR2 DEFAULT NULL, Delimiter IN CHAR DEFAULT ',' ) RETURN reSourceArray0 PIPELINED IS SourceArray00 SourceArray0:=SourceArray0(NULL); TYPE REFCURSOR IS REF CURSOR; CURSOR0 REFCURSOR; DelLen int; Pos int; Cnt int; str int; LEN int; Holder VARCHAR2(255); BEGIN IF Source is null or Delimiter is null THEN Return; END IF; IF RTRIM(LTRIM(Source)) = ' ' THEN Return; END IF; SELECT LENGTH(RTRIM(Delimiter)) INTO DelLen FROM DUAL; SELECT INSTR(UPPER(Source), UPPER(Delimiter)) INTO Pos FROM DUAL; IF Pos = 0 THEN BEGIN INSERT INTO UnpackArray_TBL ( Data ) VALUES ( Source ); return; OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL; END; END IF; SELECT 1 INTO str FROM DUAL; << LABEL4 >> WHILE Pos > 0 LOOP BEGIN SELECT Pos - str INTO len FROM DUAL; SELECT SUBSTR(Source, str, len) INTO Holder FROM DUAL; INSERT INTO UnpackArray_TBL VALUES ( Holder ); SELECT Pos + DelLen INTO str FROM DUAL; SELECT INSTR(UPPER(Source), UPPER(Delimiter), str) INTO Pos FROM DUAL; OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL; END; END LOOP; SELECT SUBSTR(Source, str, length(RTRIM(Source))) INTO Holder FROM DUAL; IF length(RTRIM(Holder)) > 0 THEN INSERT INTO UnpackArray_TBL VALUES ( Holder ); OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL; END IF; Return; LOOP FETCH CURSOR0 INTO SourceArray00.Data; EXIT WHEN CURSOR0%NOTFOUND; PIPE ROW(SourceArray00); END LOOP; CLOSE CURSOR0; RETURN; END; While executing this am getting the error like: Running "EDOCS"."UNPACKARRAY" ( SOURCE = admin, DELIMITER = ). Parameter 'RETURN_VALUE': No size set for variable length data type: String. can anybody help me to solve this...? Thanks in advance.
step 1 : create a Table types named "DIVISION_ARRAY"
CREATE TYPE "DIVISION_ARRAY" AS TABLE OF VARCHAR2(12)step 2 : execute the below proceduresCREATE OR REPLACE FUNCTION "UNPACKDIVISIONARRAY" (Source IN VARCHAR2 DEFAULT NULL, Delimiter IN CHAR DEFAULT ',') RETURN division_array PIPELINED IS l_list varchar2(32767) := Source; l_idx pls_integer; l_value varchar2(32767); BEGIN loop l_idx := instr(l_list,Delimiter); if l_idx > 0 then pipe row(substr(l_list,1,l_idx-1)); l_list := substr(l_list,l_idx+length(Delimiter)); else pipe row(l_list); exit; end if; end loop; return; end UNPACKDIVISIONARRAY;step 3 : execute the below sql to see the resultSelect column_value from TABLE(UNPACKDIVISIONARRAY('111,113'))hope that this will help you. regards, Sumith Koshy Thankan, OASYS Information Technology. Dubai