Solved

plsql - how to by pass the need for dynamic sql

Posted on 2012-03-19
2
404 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

790 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