Solved

plsql - how to by pass the need for dynamic sql

Posted on 2012-03-19
2
402 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
  • 2
2 Comments
 
LVL 73

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 73

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now