Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    12

    Unanswered: Problem with Function execution

    Hi All,

    I have a problem executing a function in oracle 10g.

    I am getting an error while executing ....

    Here is a function along with the error i am getting:

    create or replace 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(220);
    BEGIN
    --Check for NULL
    IF Source is null or Delimiter is null THEN
    Return;
    END IF;
    --Check for at leat one entry
    IF RTRIM(LTRIM(Source)) = '' THEN
    Return;
    END IF;
    /*Get the length of the delimeter*/
    SELECT LENGTH(RTRIM(Delimiter)) INTO DelLen FROM DUAL;
    SELECT INSTR(UPPER(Source), UPPER(Delimiter)) INTO Pos FROM DUAL;
    --Only one entry was found
    IF Pos = 0 THEN
    BEGIN
    INSERT INTO UnpackArray_TBL
    ( Data )
    VALUES ( Source );
    return;
    OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
    END;
    END IF;
    /*More than one entry was found - loop to get all of them*/
    SELECT 1 INTO str FROM DUAL;
    << LABEL4 >>
    WHILE Pos > 0
    LOOP
    BEGIN
    /*Set current entry*/
    SELECT Pos - str INTO len FROM DUAL;
    SELECT SUBSTR(Source, str, len) INTO Holder FROM DUAL;
    /* Update array and counter*/
    /* Update array and counter*/
    INSERT INTO UnpackArray_TBL
    VALUES ( Holder );
    /*Set the new strting position*/
    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;
    /*Set last entry*/
    SELECT SUBSTR(Source, str, length(RTRIM(Source))) INTO Holder FROM DUAL;
    -- Update array and counter if necessary
    IF length(RTRIM(Holder)) > 0 THEN
    INSERT INTO UnpackArray_TBL
    VALUES ( Holder );
    OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
    END IF;
    --Return the number of entries found
    Return; LOOP
    FETCH CURSOR0 INTO
    SourceArray00.Data;
    EXIT WHEN CURSOR0%NOTFOUND;
    PIPE ROW(SourceArray00);
    END LOOP;
    CLOSE CURSOR0;
    RETURN;
    END;
    /


    Error is : Compilation failed,line 6 (12:13:25)
    PLS-00201: identifier 'RESOURCEARRAY0' must be declared
    Compilation failed,line 0 (12:13:25)
    PL/SQL: Compilation unit analysis terminated

    Regards,
    Smiley

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ChinnuBlr
    I have a problem executing a function in oracle 10g.

    I am getting an error while executing ....
    Just for preciseness - you showed (and failed in) only creation of that function, not its execution.
    Quote Originally Posted by ChinnuBlr
    Error is : Compilation failed,line 6 (12:13:25)
    PLS-00201: identifier 'RESOURCEARRAY0' must be declared
    Compilation failed,line 0 (12:13:25)
    PL/SQL: Compilation unit analysis terminated
    Is not the error message self-explanative. Line 6 is
    Code:
    RETURN reSourceArray0 PIPELINED
    What is reSourceArray0? According to the code, it should be table type based on SourceArray0. According to the error message, it does not exist in the Oracle database (or at least in the schema you are logged).

  3. #3
    Join Date
    Aug 2009
    Posts
    12
    Hi,

    Thanks for the reply....

    I am not sure about it as i migrated that function from sql to oracle using swiss tool....

  4. #4
    Join Date
    Aug 2009
    Posts
    12
    Hi,

    I have a problem executing this function. Pls help me solve this issue.


    ---------------------------------Function---------------------------------
    BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE reSourceArray0';
    EXECUTE IMMEDIATE 'DROP TYPE SourceArray0';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    /
    CREATE OR REPLACE TYPE SourceArray0 AS OBJECT(Data VARCHAR2(255));
    /
    CREATE OR REPLACE TYPE reSourceArray0 AS TABLE OF SourceArray0;
    /
    BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE UnpackArray_TBL CASCADE CONSTRAINTS';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    /
    CREATE GLOBAL TEMPORARY TABLE UnpackArray_TBL(Data VARCHAR2(255)) ON COMMIT PRESERVE ROWS
    /
    CREATE OR REPLACE 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;
    COUNT_ADV int;
    START_ADV int;
    LENGTH_ADV int;
    Holder VARCHAR2(255);
    BEGIN
    --Check for NULL
    IF Source is null or Delimiter is null THEN
    Return;
    END IF;
    --Check for at leat one entry
    IF RTRIM(LTRIM(Source)) = '' THEN
    Return;
    END IF;
    /*Get the length of the delimeter*/

    SELECT LENGTH(RTRIM(Delimiter)) INTO DelLen FROM DUAL;

    SELECT INSTR(UPPER(Source), UPPER(Delimiter)) INTO Pos FROM DUAL;
    --Only one entry was found
    IF Pos = 0 THEN
    BEGIN

    INSERT INTO UnpackArray_TBL
    ( Data )
    VALUES ( Source );
    return;
    OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;

    END;
    END IF;
    /*More than one entry was found - loop to get all of them*/

    SELECT 1 INTO START_ADV FROM DUAL;
    << LABEL4 >>

    WHILE Pos > 0
    LOOP
    BEGIN
    /*Set current entry*/

    SELECT Pos - START_ADV INTO LENGTH_ADV FROM DUAL;

    SELECT SUBSTR(Source, start_, length) INTO Holder FROM DUAL;
    /* Update array and counter*/

    /* Update array and counter*/
    INSERT INTO UnpackArray_TBL
    VALUES ( Holder );
    /*Set the new starting position*/

    SELECT Pos + DelLen INTO START_ADV FROM DUAL;

    SELECT INSTR(UPPER(Source), UPPER(Delimiter), START_ADV) INTO Pos FROM DUAL;
    OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;

    END;

    END LOOP;
    /*Set last entry*/

    SELECT SUBSTR(Source, start_, LENGTH(RTRIM(Source))) INTO Holder FROM DUAL;
    -- Update array and counter if necessary
    IF LENGTH(RTRIM(Holder)) > 0 THEN

    INSERT INTO UnpackArray_TBL
    VALUES ( Holder );
    OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;

    END IF;
    --Return the number of entries found
    Return; LOOP
    FETCH CURSOR0 INTO
    SourceArray00.Data;
    EXIT WHEN CURSOR0%NOTFOUND;
    PIPE ROW(SourceArray00);
    END LOOP;
    CLOSE CURSOR0;

    RETURN;


    END;
    /


    --------------------------------Error i am getting--------------------------
    "Parameter 'RETURN_VALUE': No size set for variable length data type: String."

    --------------------------------------------------------------------------

    Thanks and Regards,
    Chinnu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •