1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Querying elements in an array

Discussion in 'SQL PL/SQL' started by athomas, Aug 12, 2016.

  1. athomas

    athomas Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    St. Louis
    Hi All,

    My developer is passing me a pipe delimited string through a procedure like 'abc|xyz|123' and I want to return a ref cursor back of values from a table that match elements in that string like Select * from table_name where value in ('abc', 'xyz', '123');

    How do i go about doing this? Here is what I have so far: (please let me know if there is better way to return a ref cursor out with the result)

    CREATE TYPE test_array AS VARRAY(10) OF VARCHAR2(100 BYTE);

    CREATE OR REPLACE PROCEDURE test_proc(
    test_string IN VARCHAR2,
    ref_cursor OUT ref_cursor_type
    ) AS

    v_pos INTEGER := 0;
    v_count INTEGER := 0;
    i INTEGER := 0;
    x_string VARCHAR2(500);
    v_string VARCHAR2(500);
    elements test_array;

    BEGIN
    elements test_array : = test_array();
    v_count := 1;
    x_string := test_string;
    i := 1;

    WHILE v_count > 0
    LOOP
    IF x_string IS NOT NULL THEN
    v_pos := LENGTH(x_string);
    v_string := x_string;
    v_count := INSTR(v_string, '|', 1, 1);
    IF v_count = 0 THEN
    elements.extend();
    elements(i) := v_string;
    i++;
    ELSE
    x_string := SUBSTR(v_string, v_count + 1);
    v_string := SUBSTR(v_string, -v_pos, v_count - 1);

    elements.extend();
    elements(i) := v_string;
    i++;
    END IF;
    ELSE
    v_count := 0;
    END IF;
    END LOOP;

    OPEN p_ref_cursor FOR
    SELECT value FROM test_table
    WHERE test_value IN elements;

    end;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. dencypriya

    dencypriya Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    chennai
    Hiiii...
    {"_id":ObjectId("562e7c594c12942f08fe4192"),"shapes":[{"shape":"square","color":"blue"},{"shape":"circle","color":"red"}]},{"_id":ObjectId("562e7c594c12942f08fe4193"),"shapes":[{"shape":"square","color":"black"},{"shape":"circle","color":"green"}]}

    Oracle SCM Online Training
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    I believe that use of the procedure is excessive here.
    Of course, if you don't study oracle ))

    Code (SQL):
    DECLARE
        l_rc sys_refcursor;
        l_tab ora_mining_varchar2_nt;
        --prc
        PROCEDURE prc_test
        (
            p_array varchar2,
            p_delim varchar2,
            p_sys_rc  OUT sys_refcursor
        )
        AS
            l_delim varchar2(7 CHAR) := '[^'||nvl(p_delim,',')||']+';
            l_tab ora_mining_varchar2_nt := ora_mining_varchar2_nt(); -- oracle type
            l_idx pls_integer :=1;
            l_elem varchar2(4000);
        BEGIN  
            loop
                l_elem := regexp_substr(p_array,l_delim,1,l_idx);
                exit WHEN l_elem IS NULL;
                l_tab.extend;
                l_tab(l_tab.LAST) := l_elem;
                l_idx := l_idx + 1;
            END loop;
            OPEN p_sys_rc FOR SELECT * FROM TABLE(l_tab);
        END;

    -- main block
    BEGIN
      -- Call the procedure
      prc_test(p_array => '1,2,2,3dsdsd,rff,asd,f,f,sd,fsd,fsd',
               p_delim => ',',
               p_sys_rc => l_rc);
      dbms_output.put_line('your data...');      
      fetch l_rc bulk collect INTO l_tab;
      close l_rc;          
      FOR z IN 1 .. l_tab.COUNT
      loop
        dbms_output.put_line(rpad(z,8,'.')||l_tab(z));
      END loop;      

    END;
     
    it is possible to use a hierarchical query for parse of string:
    Code (SQL):
                       
    SELECT * FROM your_table t,
      (
      SELECT
      regexp_substr(&your_data,'[^,]+',1,level) str
      FROM
      dual
      CONNECT BY
      regexp_substr(&your_data,'[^,]+',1,level) IS NOT NULL
      ) f
    WHERE
      f.str = t.your_column;
     
     
    Last edited: Sep 13, 2016