Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2014
    Posts
    5

    Question Unanswered: Returning a cursor from a function to loop through and insert

    Hi!

    I'm working on a batch of queries and there is a requirement where I have to convert this SQL Server queries to similar-function Oracle.

    SQL Server:
    Code:
    WITH Cte
         AS (SELECT cast('<S>' + replace(replace(N'$(AppServers)', ';', ','), ',', '</S><S>') + '</S>' AS XML) AS Servers)
    INSERT INTO INSTANCE
                (INSTANCE_ID,
                 SERVER_NAME,
                 INSTANCE_IDENTIFIER,
                 IDENTIFIER_PREFIX)
    SELECT ROW_NUMBER() OVER (ORDER BY SERVER_NAME) - 1,
           SERVER_NAME,
           NULL,
           0
    FROM   (SELECT DISTINCT upper(Split.Server.value('.', 'VARCHAR(100)')) AS SERVER_NAME
            FROM   Cte
                   CROSS apply Servers.nodes('/S') Split(Server)) Servers
    ORDER  BY SERVER_NAME;
    Oracle:
    Code:
    DECLARE
      L_INPUT VARCHAR2(4000) := 'foo,bar,baz,wibble';
      L_COUNT BINARY_INTEGER;
      L_ARRAY DBMS_UTILITY.LNAME_ARRAY;
    BEGIN
      DBMS_UTILITY.COMMA_TO_TABLE(LIST => REGEXP_REPLACE(L_INPUT, '(^|,)', '\1x'), TABLEN => L_COUNT, TAB => L_ARRAY);
      DBMS_OUTPUT.PUT_LINE(L_COUNT);
      FOR I IN 1 .. L_COUNT
      LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || TO_CHAR(I) || ' of array contains: ' || SUBSTR(L_ARRAY(I), 2));
        INSERT INTO INSTANCE VALUES (I, SUBSTR(L_ARRAY(I), 2), NULL, 0);
        COMMIT;
      END LOOP;
    END;
    Now, the requirement changed that we should have a function that will accept two values: CSV for columns, and CSV of CSV for values (multiple rows). Example:

    SomeFunction(
    "Directory,ID,Location,UserName,Password,Selector",
    "(Directory=Voice Active Directory A,ID=VT-AD1,Location=Canada,UserName=admin,Password=passw0rd,Selector=AD1),(Directory=Voice Active Directory B,ID=VT-AD2,Location=https://beta-proxy.voice.com/VTadp/Proxy/[/url],UserName=admin,Password=passw0rd,Selector=AD2),(Directory=Voice Active Directory C,ID=VT-AD3,Location=https://final-proxy.voice.com/VTadp/Proxy/,UserName=admin,Password=passw0rd)"

    The second parameter is a big one as it has all the row data. Also, it has the column names as well because the third row data has a missing selector column value and should be inserted as NULL. The main reason I want to return a resultset indicated by a pointer is that the target table to insert into can be any. It will be the caller's responsibility to send the correct data to the function and handle the returned collection in a loop for the desired table making this function very general. I'll manage that after getting the generic result set. I'll worry about how to navigate that result set once I can actually get it. Can anyone suggest a basic example? Thank you.
    Last edited by DoomerDGR8; 11-11-14 at 11:49. Reason: Added more details

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    For starters, you can begin with this:
    Code:
    DECLARE
       L_Hdr          VARCHAR2 ( 4000 ) := 'Directory,ID,Location,UserName,Password,Selector';
       L_Dat          CLOB  := '(Directory=Voice Active Directory A,ID=VT-AD1,Location=Canada,UserName=admin,Password=passw0rd,Selector=AD1),(Directory=Voice Active Directory B,ID=VT-AD2,Location=https://beta-proxy.voice.com/VTadp/Proxy/[/url],UserName=admin,Password=passw0rd,Selector=AD2),(Directory=Voice Active Directory C,ID=VT-AD3,Location=https://final-proxy.voice.com/VTadp/Proxy/,UserName=admin,Password=passw0rd)';
       L_Wrk          CLOB;
       L_Recsep       CHAR ( 1 ) := CHR ( 9 );
    
       TYPE Text_Array IS TABLE OF VARCHAR2 ( 4000 )
          INDEX BY PLS_INTEGER;
    
       L_Hdr_Array    Text_Array;
       L_Dat_Array    Text_Array;
       L_Fld_Array    Text_Array;
       L_Elm_Array    Text_Array;
       L_Val_Array    Text_Array;
       L_Hdr_Count    PLS_INTEGER;
       L_Dat_Count    PLS_INTEGER;
       L_Fld_Count    PLS_INTEGER;
       I              PLS_INTEGER;
       J              PLS_INTEGER;
       K              PLS_INTEGER;
       L              PLS_INTEGER;
       N              PLS_INTEGER;
    
       FUNCTION Parse_Csv ( P_Text CLOB, P_Delim VARCHAR2 DEFAULT ',' )
          RETURN Text_Array
       IS
          Wk_Array       Text_Array;
       BEGIN
          N           := 1;
          L           := 1;
          K           := 1;
    
          WHILE 1 = 1
          LOOP
             L           := INSTR ( SUBSTR ( P_Text || P_Delim, K + 1 ), P_Delim );
             EXIT WHEN K >= LENGTH ( P_Text );
             Wk_Array ( N ) := SUBSTR ( P_Text, K, L );
             N           := N + 1;
             K           := K + L + 1;
          END LOOP;
    
          RETURN Wk_Array;
       END;
    BEGIN
       L_Hdr_Array := Parse_Csv ( L_Hdr );
       L_Hdr_Count := L_Hdr_Array.COUNT;
       DBMS_OUTPUT.Put_Line ( 'Hdr#' || L_Hdr_Count );
    
       FOR I IN 1 .. L_Hdr_Count
       LOOP
          DBMS_OUTPUT.Put_Line ( 'Elm#' || I || ': ' || L_Hdr_Array ( I ) );
       END LOOP;
    
       L_Wrk       := SUBSTR ( L_Dat, 2, LENGTH ( L_Dat ) - 2 );
    
       SELECT REPLACE ( L_Wrk, '),(', L_Recsep ) || L_Recsep INTO L_Wrk FROM DUAL;
    
       DBMS_OUTPUT.Put_Line ( 'Dat: ' || L_Wrk );
    
       L_Dat_Array := Parse_Csv ( L_Wrk, L_Recsep );
       L_Dat_Count := L_Dat_Array.COUNT;
    
       FOR I IN 1 .. L_Dat_Count
       LOOP
          L_Fld_Array := Parse_Csv ( L_Dat_Array ( I ) );
          L_Fld_Count := L_Fld_Array.COUNT;
          DBMS_OUTPUT.Put_Line ( 'Rec#' || I || ': ' || L_Dat_Array ( I ) || ' Flds:' || L_Fld_Count );
    
          FOR J IN 1 .. L_Hdr_Count
          LOOP
             K           := 0;
    
             FOR N IN 1 .. L_Fld_Count
             LOOP
                IF L_Hdr_Array ( J ) =
                      SUBSTR ( L_Fld_Array ( N )
                             , 1, LENGTH ( L_Hdr_Array ( J ) ) )
                THEN
                   K           := N;
                   CONTINUE;
                END IF;
             END LOOP;
    
             IF K > 0
             THEN
                L                 := INSTR  ( L_Fld_Array ( K ), '=' );
                L_Elm_Array ( J ) := SUBSTR ( L_Fld_Array ( K ), 1, L - 1 );
                L_Val_Array ( J ) := SUBSTR ( L_Fld_Array ( K ), L + 1 );
             ELSE
                L_Elm_Array ( J ) := '#N/A';
                L_Val_Array ( J ) := '#N/A';
             END IF;
    
             DBMS_OUTPUT.Put_Line ( 'Element ' || TO_CHAR ( J ) || '.' || L_Hdr_Array ( J ) || '=' || L_Val_Array ( J ) );
          --INSERT INTO INSTANCE VALUES (I, SUBSTR(L_ARRAY(I), 2), NULL, 0);
          --COMMIT;
          END LOOP;
       END LOOP;
    END;
    /
    Last edited by LKBrwn_DBA; 11-12-14 at 02:18.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2014
    Posts
    5

    Red face

    Quote Originally Posted by LKBrwn_DBA View Post
    For starters, you can begin with this:
    DECLAREd he is Awesome
    OMG. Thanks a million! Works better than what I was hoping! It returns a text array. I can loop through it? I was expecting a cursor to be a datatype. I am coming from a SQL Server background and am struggling with Oracle and PL/SQL.

    After studying your code, I see that the function Parse_Csv is called once for headers and then once for each data row. Afterwards, each data row is processed.

    How can I make this into a function that returns me a structure like so:

    Code:
    +-----------------------------------------------------------------------------------------------------------------+
    |                                               The returned thingy                                               |
    +-----------------------------------------------------------------------------------------------------------------+
    | Directory                | ID     | Location                                   | UserName | Password | Selector |
    +--------------------------+--------+--------------------------------------------+----------+----------+----------+
    | Voice Active Directory A | VT-AD1 | Canada                                     | admin    | passw0rd | AD1      |
    +--------------------------+--------+--------------------------------------------+----------+----------+----------+
    | Voice Active Directory B | VT-AD2 | https://beta-proxy.voice.com/VTadp/Proxy/  | admin    | passw0rd | AD2      |
    +--------------------------+--------+--------------------------------------------+----------+----------+----------+
    | Voice Active Directory C | VT-AD3 | https://final-proxy.voice.com/VTadp/Proxy/ | admin    | passw0rd | NULL     |
    +--------------------------+--------+--------------------------------------------+----------+----------+----------+
    I call to this as a function. Here is what I have so far through you above code:

    Code:
    create or replace TYPE CSVPARSERRETURN 
    AS TABLE OF  VARCHAR2 ( 4000 );
    
    CREATE OR REPLACE FUNCTION CSVPARSER 
    (
      L_Dat IN CLOB 
    , L_Hdr IN VARCHAR2
    ) RETURN CSVPARSERRETURN AS 
       --L_Hdr          VARCHAR2 ( 4000 ) := 'Directory,ID,Location,UserName,Password,Selector';
       --L_Dat          CLOB  := '(Directory=Voice Active Directory A,ID=VT-AD1,Location=Canada,UserName=admin,Password=passw0rd,Selector=AD1),(Directory=Voice Active Directory B,ID=VT-AD2,Location=https://beta-proxy.voice.com/VTadp/Proxy/[/url],UserName=admin,Password=passw0rd,Selector=AD2),(Directory=Voice Active Directory C,ID=VT-AD3,Location=https://final-proxy.voice.com/VTadp/Proxy/,UserName=admin,Password=passw0rd)';
       L_Wrk          CLOB;
       L_Recsep       CHAR ( 1 ) := CHR ( 9 );
    
       TYPE Text_Array IS TABLE OF VARCHAR2 ( 4000 )
          INDEX BY PLS_INTEGER;
    
       L_Hdr_Array    Text_Array;
       L_Dat_Array    Text_Array;
       L_Fld_Array    Text_Array;
       L_Elm_Array    Text_Array;
       L_Val_Array    Text_Array;
       L_Hdr_Count    PLS_INTEGER;
       L_Dat_Count    PLS_INTEGER;
       L_Fld_Count    PLS_INTEGER;
       I              PLS_INTEGER;
       J              PLS_INTEGER;
       K              PLS_INTEGER;
       L              PLS_INTEGER;
       N              PLS_INTEGER;
    
       FUNCTION Parse_Csv ( P_Text CLOB, P_Delim VARCHAR2 DEFAULT ',' )
          RETURN Text_Array
       IS
          Wk_Array       Text_Array;
       BEGIN
          N           := 1;
          L           := 1;
          K           := 1;
    
          WHILE 1 = 1
          LOOP
             L           := INSTR ( SUBSTR ( P_Text || P_Delim, K + 1 ), P_Delim );
             EXIT WHEN K >= LENGTH ( P_Text );
             Wk_Array ( N ) := SUBSTR ( P_Text, K, L );
             N           := N + 1;
             K           := K + L + 1;
          END LOOP;
    
          RETURN Wk_Array;
       END;
    BEGIN
       L_Hdr_Array := Parse_Csv ( L_Hdr );
       L_Hdr_Count := L_Hdr_Array.COUNT;
       DBMS_OUTPUT.Put_Line ( 'Hdr#' || L_Hdr_Count );
    
       FOR I IN 1 .. L_Hdr_Count
       LOOP
          DBMS_OUTPUT.Put_Line ( 'Elm#' || I || ': ' || L_Hdr_Array ( I ) );
       END LOOP;
    
       L_Wrk       := SUBSTR ( L_Dat, 2, LENGTH ( L_Dat ) - 2 );
    
       SELECT REPLACE ( L_Wrk, '),(', L_Recsep ) || L_Recsep INTO L_Wrk FROM DUAL;
    
       DBMS_OUTPUT.Put_Line ( 'Dat: ' || L_Wrk );
    
       L_Dat_Array := Parse_Csv ( L_Wrk, L_Recsep );
       L_Dat_Count := L_Dat_Array.COUNT;
    
       FOR I IN 1 .. L_Dat_Count
       LOOP
          L_Fld_Array := Parse_Csv ( L_Dat_Array ( I ) );
          L_Fld_Count := L_Fld_Array.COUNT;
          DBMS_OUTPUT.Put_Line ( 'Rec#' || I || ': ' || L_Dat_Array ( I ) || ' Flds:' || L_Fld_Count );
    
          FOR J IN 1 .. L_Hdr_Count
          LOOP
             K           := 0;
    
             FOR N IN 1 .. L_Fld_Count
             LOOP
                IF L_Hdr_Array ( J ) =
                      SUBSTR ( L_Fld_Array ( N )
                             , 1, LENGTH ( L_Hdr_Array ( J ) ) )
                THEN
                   K           := N;
                   CONTINUE;
                END IF;
             END LOOP;
    
             IF K > 0
             THEN
                L                 := INSTR  ( L_Fld_Array ( K ), '=' );
                L_Elm_Array ( J ) := SUBSTR ( L_Fld_Array ( K ), 1, L - 1 );
                L_Val_Array ( J ) := SUBSTR ( L_Fld_Array ( K ), L + 1 );
             ELSE
                L_Elm_Array ( J ) := '#N/A';
                L_Val_Array ( J ) := '#N/A';
             END IF;
    
             DBMS_OUTPUT.Put_Line ( 'Element ' || TO_CHAR ( J ) || '.' || L_Hdr_Array ( J ) || '=' || L_Val_Array ( J ) );
          END LOOP;
       END LOOP;
       
       RETURN NULL;
    END CSVPARSER;
    It doesn't return anything so far as I need to build up a table like the example above and have that returned to process elsewhere.
    Last edited by DoomerDGR8; 11-12-14 at 06:23.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    OK here:
    Code:
    CREATE OR REPLACE TYPE Csvparserreturn AS TABLE OF VARCHAR2 ( 4000 );
    /
    
    CREATE OR REPLACE FUNCTION Csvparser ( P_Dat IN CLOB, P_Hdr IN VARCHAR2 )
       RETURN Csvparserreturn
    AS
       L_Debug        CHAR ( 1 ) := 'F';
       L_Hdr          VARCHAR2 ( 4000 );
       L_Dat          CLOB;
       L_Wrk          CLOB;
       L_Recsep       CHAR ( 1 ) := CHR ( 9 );
    
       O_Hdr          VARCHAR2 ( 4000 );
       O_Dat          CLOB;
       O_Data_Tab     Csvparserreturn := Csvparserreturn ( );
    
       TYPE Text_Array IS TABLE OF VARCHAR2 ( 4000 )
          INDEX BY PLS_INTEGER;
    
       L_Hdr_Array    Text_Array;
       L_Dat_Array    Text_Array;
       L_Fld_Array    Text_Array;
       L_Elm_Array    Text_Array;
       L_Val_Array    Text_Array;
       L_Hdr_Count    PLS_INTEGER;
       L_Dat_Count    PLS_INTEGER;
       L_Fld_Count    PLS_INTEGER;
       I              PLS_INTEGER;
       J              PLS_INTEGER;
       K              PLS_INTEGER;
       L              PLS_INTEGER;
       N              PLS_INTEGER;
    
       PROCEDURE Print_Line ( P_Text VARCHAR2 )
       IS
       BEGIN
          IF L_Debug = 'T'
          THEN
             DBMS_OUTPUT.Put_Line ( P_Text );
          END IF;
       END;
    
       FUNCTION Parse_Csv ( P_Text CLOB, P_Delim VARCHAR2 DEFAULT ',' )
          RETURN Text_Array
       IS
          Wk_Array       Text_Array;
       BEGIN
          N           := 1;
          L           := 1;
          K           := 1;
    
          WHILE 1 = 1
          LOOP
             L           := INSTR ( SUBSTR ( P_Text || P_Delim, K + 1 )
                                  , P_Delim );
             EXIT WHEN K >= LENGTH ( P_Text );
             Wk_Array ( N ) := SUBSTR ( P_Text, K, L );
             N           := N + 1;
             K           := K + L + 1;
          END LOOP;
    
          RETURN Wk_Array;
       END;
    BEGIN
       L_Hdr       := P_Hdr;
       L_Dat       := P_Dat;
       L_Hdr_Array := Parse_Csv ( L_Hdr );
       L_Hdr_Count := L_Hdr_Array.COUNT;
       Print_Line ( 'Hdr#' || L_Hdr_Count );
       O_Hdr       := '';
    
       FOR I IN 1 .. L_Hdr_Count
       LOOP
          O_Hdr       := O_Hdr || L_Hdr_Array ( I ) || L_Recsep;
          Print_Line ( 'Elm#' || I || ': ' || L_Hdr_Array ( I ) );
       END LOOP;
    
       L_Wrk       := SUBSTR ( L_Dat, 2, LENGTH ( L_Dat ) - 2 );
    
       SELECT REPLACE ( L_Wrk, '),(', L_Recsep ) || L_Recsep INTO L_Wrk FROM DUAL;
    
       Print_Line ( 'Dat: ' || L_Wrk );
    
       L_Dat_Array := Parse_Csv ( L_Wrk, L_Recsep );
       L_Dat_Count := L_Dat_Array.COUNT;
    
    
       O_Data_Tab.EXTEND;
       O_Data_Tab ( 1 ) := O_Hdr;
    
       FOR I IN 1 .. L_Dat_Count
       LOOP
          L_Fld_Array := Parse_Csv ( L_Dat_Array ( I ) );
          L_Fld_Count := L_Fld_Array.COUNT;
          Print_Line ( 'Rec#' || I || ': ' || L_Dat_Array ( I ) || ' Flds:' || L_Fld_Count );
    
          O_Dat       := '';
    
          FOR J IN 1 .. L_Hdr_Count
          LOOP
             K           := 0;
    
             FOR N IN 1 .. L_Fld_Count
             LOOP
                IF L_Hdr_Array ( J ) =
                      SUBSTR ( L_Fld_Array ( N )
                             , 1, LENGTH ( L_Hdr_Array ( J ) ) )
                THEN
                   K           := N;
                   CONTINUE;
                END IF;
             END LOOP;
    
             IF K > 0
             THEN
                L           := INSTR ( L_Fld_Array ( K ), '=' );
                L_Elm_Array ( J ) := SUBSTR ( L_Fld_Array ( K ), 1, L - 1 );
                L_Val_Array ( J ) := SUBSTR ( L_Fld_Array ( K ), L + 1 );
             ELSE
                L_Elm_Array ( J ) := '#N/A';
                L_Val_Array ( J ) := '#N/A';
             END IF;
    
             Print_Line ( 'Element ' || TO_CHAR ( J ) || '.' || L_Hdr_Array ( J ) || '=' || L_Val_Array ( J ) );
    
             O_Dat       := O_Dat || L_Val_Array ( J ) || L_Recsep;
          END LOOP;
    
          Print_Line ( 'Out#' || TO_CHAR ( I ) || '=' || O_Dat );
    
          O_Data_Tab.EXTEND;
          O_Data_Tab ( I + 1 ) := O_Dat;
       END LOOP;
    
       RETURN O_Data_Tab;
    END;
    /
    And execute like this:
    Code:
    DECLARE
       Out_Data  Csvparserreturn := Csvparserreturn ( );
       X_Hdr     VARCHAR2 ( 4000 ) := 'Directory,ID,Location,UserName,Password,Selector';
       X_Dat     CLOB := '(Directory=Voice Active Directory A,ID=VT-AD1,Location=Canada,UserName=admin,Password=passw0rd,Selector=AD1),(Directory=Voice Active Directory B,ID=VT-AD2,Location=https://beta-proxy.voice.com/VTadp/Proxy/[/url],UserName=admin,Password=passw0rd,Selector=AD2),(Directory=Voice Active Directory C,ID=VT-AD3,Location=https://final-proxy.voice.com/VTadp/Proxy/,UserName=admin,Password=passw0rd)';
    BEGIN
       Out_Data    := Csvparser ( X_Dat, X_Hdr );
    
       FOR I IN Out_Data.FIRST .. Out_Data.LAST
       LOOP
          DBMS_OUTPUT.Put_Line ( 'Rec# ' || TO_CHAR ( I, 'FM000.' ) || Out_Data ( I ) );
       END LOOP;
    END;
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2014
    Posts
    5

    Thumbs up

    Quote Originally Posted by LKBrwn_DBA View Post
    Thank you very much. Thats worked better than I wanted. You saved me ton of hours.

  6. #6
    Join Date
    Nov 2014
    Posts
    5

    Question 2D Arrays

    One more thing:

    Is there a concept or a datatype in Oracle that can represent something that is actually a table? Like rows and columns? A 2D array? The final result that I get is through Out_Data ( I ) and that returns the value at the index I which is a string made from all columns being concatenated. Is it possible to have Out_Data ( I ) ( J ) in the above function? Even moreover, if a named key can be used as Out_Data ( I ) ( 'Directory' ).
    Last edited by DoomerDGR8; 11-16-14 at 06:45.

  7. #7
    Join Date
    Nov 2014
    Posts
    5

    Question

    I've been working on the different storage models available in Oracle. I see that I may need to have two separate functions to achieve my desired results. The first method will be just what the current function does right now: Get two parameters and return a collection of two rows: Header and data. So if the initial data that is passed has 7 data rows, a collection of total 14 rows will be returned. That is: each data row with it's header. The second function should take this collection of one header and one row and a column name like 'Directory' and return the value at the Directory column in the data row.

    OR

    The main function can return a 2D array. The master array holding 1 array for headers and n arrays for data. Master is indexed by numeric value and the data arrays are indexed by name. and usage would be like:

    Code:
    resultObj := CsvParser('Directory,ID,Location,UserName,Password,Selector', 
                                    '(Directory=Voice Active Directory A,ID=VT-AD1,Location=Canada,UserName=admin,Password=passw0rd,Selector=AD1),(Directory=Voice Active Directory B,ID=VT-AD2,Location=https://beta-proxy.voice.com/VTadp/Proxy/[/url],UserName=admin,Password=passw0rd,Selector=AD2),(Directory=Voice Active Directory C,ID=VT-AD3,Location=https://final-proxy.voice.com/VTadp/Proxy/,UserName=admin,Password=passw0rd)'
                                   )
    And after that, on the resultObj, I can do an index like

    Code:
    myPassword := resultObj (1) ('Password')
    Is this possible in Oracle?

Tags for this Thread

Posting Permissions

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