Solved

plsql - how to by pass the need for dynamic sql

Posted on 2012-03-19
2
406 Views
Last Modified: 2012-03-19
On Oracle 10.2
I am looking for a way to write a plsql procedure without the use of dynamic SQL.
Inside I need to add a where clause with a parameter that is actually a concatinated list of values:

proedure foo (p_list in varchar2) is
  v_result1     number;
   .
   .
   .
begin
  select field1,field2, field3...
  into    v_result...
  from  my_table
  where   id in (p_list) ;    -- This is the dilema!!!
end;
/

The procedure is being called like this:
foo ('3,5,5656,77');

I know how to do it with dynamic SQL, looking for a way to avoid it.
any ideas?
0
Comment
Question by:peledc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 37737591
first create this type and this function...

CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);

            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;

        RETURN;
            WHEN NO_DATA_NEEDED THEN
            null;
    END str2tbl;



then you can use the function in your query like this...


select field1,field2, field3...
  into    v_result...
  from  my_table
  where   id in (select * from table(str2tbl(p_list))) ;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37737594
alternate version, a little more complicated  but doesn't require creating new type or function


select field1,field2, field3...
  into    v_result...
  from  my_table
  where   id in (
SELECT     REGEXP_SUBSTR(p_list, '[^,]+', 1, LEVEL)
      FROM dual
CONNECT BY LEVEL <= LENGTH(p_list) - LENGTH(REPLACE(p_list, ',')) + 1
           AND REGEXP_SUBSTR(p_list, '[^,]+', 1, LEVEL) IS NOT NULL)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question