?
Solved

plsql - how to by pass the need for dynamic sql

Posted on 2012-03-19
2
Medium Priority
?
408 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 2000 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

649 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