Solved

plsql - how to by pass the need for dynamic sql

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Import and exporting Oracle Data with encrypted columns 4 58
Row_number in SQL 6 45
date show only hh:mm 2 38
Oracle programming for starter 14 32
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 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