12 Replies Latest reply on Jan 8, 2020 8:55 AM by John_K

    Loop through list variable without being able to create a function or table on version 12.0.1 *groans help*

    Pierce_C

      Yo, I am trying to loop through a comma separated list variable on version 12.0.1 and I do not have permissions to create a function or table...

       

      I need a brave soul, that is willing to help.

       

      These are a bunch of solutions I have found that do not work for my particular scenario:

       

      http://www.besserdich-redmann.com/plsql/plsql-function-split-string-into-array-function/

       

       

       

       

      /** Nested Table Type */

      CREATE OR REPLACE TYPE nt_split_result IS TABLE OF VARCHAR2(4000)

      /

       

       

      CREATE OR REPLACE FUNCTION f_split_string_into_array

      (pi_str IN VARCHAR2

      ,pi_delimiter IN VARCHAR2

      ) RETURN nt_split_result

      IS

      /** Function extracts parts of a string which are enclosed with an individual delimiter

      *

      * @author Karsten Besserdich

      * @company Besserdich & Redmann GmbH

      * @email kb@besserdich-redmann.com

      * @URL www.besserdich.com

      * @Date 20.08.2015

      *

      * @param pi_str string to split

      * @param pi_delimiter Delimter

      * @return Nested Table with extracted strings

      *

      * You can use this function in PL/SQL and SQL!

      */

      lv_list nt_split_result := nt_split_result();

      lv_position INTEGER := 1;

      lv_result INTEGER := 1;

       

       

      BEGIN

      -- String must be filled

      IF LENGTH(pi_str) != 0

      THEN

      LOOP

       

       

      - At which point does Delimiter appear next from position X onwards

      lv_result: = INSTR (pi_str, pi_delimiter, lv_position);

      - continue, possibly several delimiters in a row in the string

      IF lv_result = lv_position THEN

      lv_position := lv_position+1;

      CONTINUE;

      END IF;

      -- Found a substring between two delimiters

      IF lv_result > lv_position THEN

      lv_list.extend;

      lv_list(lv_list.last) := SUBSTR(pi_str,lv_position,lv_result-lv_position);

      lv_position := lv_result+1;

      CONTINUE;

      END IF;

      - what was found at the end and then end the loop

      IF lv_result = 0 AND lv_position <= LENGTH(pi_str) THEN

      lv_list.extend;

      lv_list(lv_list.last) := SUBSTR(pi_str,lv_position,LENGTH(pi_str)-lv_position+1);

      EXIT;

      END IF;

      - at the end - end the loop

      IF lv_position > LENGTH(pi_str) THEN

      EXIT;

      END IF;

      END LOOP;

      END IF;

       

       

      RETURN lv_list;

      EXCEPTION

      WHEN OTHERS THEN

      RAISE;

      END f_split_string_into_array;

      /

       

       

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

       

       

       

       

      SET SERVEROUTPUT ON

      DECLARE

        TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

       

       

        l_tab    t_forall_test_tab := t_forall_test_tab();

        l_start  NUMBER;

        l_size   NUMBER            := 1000000;

       

       

        PROCEDURE prepare_table AS

        BEGIN

          EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

       

       

          INSERT /*+ APPEND */ INTO forall_test

          SELECT level, TO_CHAR(level), 'Description: ' || TO_CHAR(level)

          FROM   dual

          CONNECT BY level <= l_size;

          COMMIT;

       

       

          DELETE FROM forall_test WHERE MOD(id, 2) = 0;

          COMMIT;

        END prepare_table;

      BEGIN

        -- Populate collection.

        FOR i IN 1 .. (l_size/2) LOOP

          l_tab.extend;

          l_tab(l_tab.last).id          := i*2;

          l_tab(l_tab.last).code        := TO_CHAR(i*2);

          l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i*2);

        END LOOP;

       

       

        prepare_table;

       

       

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

        -- Test 1: Time bulk inserts. 

        l_start := DBMS_UTILITY.get_time;

       

       

        FORALL i IN l_tab.first .. l_tab.last

          INSERT INTO forall_test VALUES l_tab(i);

       

       

        DBMS_OUTPUT.put_line('Bulk Inserts : ' ||

                             (DBMS_UTILITY.get_time - l_start));

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

       

       

        ROLLBACK;

       

       

        prepare_table;

       

       

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

        -- Test 2: Time bulk inserts using the APPEND_VALUES hint. 

        l_start := DBMS_UTILITY.get_time;

       

       

        FORALL i IN l_tab.first .. l_tab.last

          INSERT /*+ APPEND_VALUES */ INTO forall_test VALUES l_tab(i);

       

       

        DBMS_OUTPUT.put_line('Bulk Inserts /*+ APPEND_VALUES */ : ' ||

                             (DBMS_UTILITY.get_time - l_start));

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

       

       

        ROLLBACK;

       

       

      END;

      /

       

       

       

       

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

       

       

       

       

      CREATE FUNCTION SplitProductID

      (

             -- Add the parameters for the function here

             @List varchar(max)

      )

      RETURNS

      @ParsedList TABLE

      (

             -- Add the column definitions for the TABLE variable here

             ProductID int

      )

      AS

      BEGIN

             -- Fill the table variable with the rows for your result set

       

       

             DECLARE @ProductID  int, @Pos int

       

       

             SET @List = LTRIM(RTRIM(@List))+ ','

             SET @Pos = CHARINDEX(',', @List, 1)

       

       

             IF REPLACE (@List, ',' , '') <> ''

             BEGIN

                    WHILE @Pos > 0

                    BEGIN

                           SET @ProductID = LTRIM(RTRIM(LEFT(@List, @Pos -1)))

                           IF @ProductID <> ''

                           BEGIN

                                 INSERT INTO @ParsedList(ProductID)

                                 VALUES (CAST(@ProductID AS varchar(7)))

                           END

                           SET @List = RIGHT(@List, LEN(@List) - @Pos)    -- Removes what was just inserted

                           SET @Pos = CHARINDEX(',', @List, 1)  -- Find the Next one

       

       

       

       

                    END

             END

            

             RETURN

      END;