Using string in Oracle stored procedure

Question

When I simply write a query in which it has code like

Select * from ..
where ...
AND    gfcid in ( select regexp_substr('1005771621,1001035181'||',','\d+',1,level)
        from dual
      connect by level <= (select max(length('1005771621,1001035181')-length(replace('1005771621,1001035181',',')))+1
                             from dual) )

It works.

But I want to make it dynamic query in oracle stored procedure. I did like this:

GDFCID_STRING := ' select regexp_substr('
           || '1005771621,1001035181'
           || ','
           || ','
           || '\d+'
           || ',1,level) from dual connect by level <= (select max(length('
           || '1005771621,1001035181'
           || ')-length(replace('
           || '1005771621,1001035181'
           || ','
           || ','
           || ')))+1 from dual)';

Select * from ..  
where ...
AND    gfcid in (GDFCID_STRING)

But it does now work.

I think a stored procedure is what you require with input variables. — Stanley Mungai
– Stanley Mungai, Commented Nov 10, 2014 at 6:17
So you want to break the string GDFCID_STRING into a list of numeric values and then use it as an in parameter` to be compared with gfcid, am I right? — Rachcha
– Rachcha, Commented Nov 10, 2014 at 6:51

Rachcha · Accepted Answer · 2014-11-10 07:13:26Z

2

As far as I understand your problem, you need a method to accept a comma-delimited string as an input, break it into a collection of integers and then compare a number (read: integer) with the values in this collection.

Oracle offers mainly three types of collections- varrays, nested tables and associative arrays. I would explain how to convert a comma-delimited string into a nested table and use it to query or compare.

First, you need to define an object type in the schema. You can write queries using this type only if you define it at schema level.

CREATE OR REPLACE TYPE entity_id AS OBJECT (id_val NUMBER(28));
/

CREATE OR REPLACE TYPE entity_id_set IS TABLE OF entity_id;
/

Next, define a function like this:

FUNCTION comma_to_nt_integer (p_comma_delimited_str IN VARCHAR)
    RETURN entity_id_set IS
    v_table     entity_id_set;
BEGIN
    WITH temp AS (SELECT TRIM(BOTH ',' FROM p_comma_delimited_str) AS str FROM DUAL)
        SELECT ENTITY_ID(TRIM (REGEXP_SUBSTR (t.str,
                                    '[^,]+',
                                    1,
                                    LEVEL)))
                   str
          BULK COLLECT INTO v_table
          FROM temp t
    CONNECT BY INSTR (str,
                      ',',
                      1,
                      LEVEL - 1) > 0;

    RETURN v_table;
END comma_to_nt_integer;

You are done with the DDL required for this task. Now, you can simply write your query as:

SELECT *
  FROM ..  
 WHERE ...
       AND gfcid in (table(comma_to_nt_integer(GDFCID_STRING)));

answered Nov 10, 2014 at 7:13

Rachcha

8,8969 gold badges52 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Anil Gavate Over a year ago

Here in -> AND gfcid in (table(comma_to_nt_integer(GDFCID_STRING))) in clause the sql might be different. If user is passing blank gfcId I need to put a join with another table, so in IN clause there is a dynamic query.. :( . Thank you very much for your reply. I tried-> AND gfcid in (EXECUTE IMMEDIATE GDFCID_STRING2) -> but it dint work as well.

Maheswaran Ravisankar Over a year ago

@AnilGavate the query can be either fully dynamic or fully static, but not both.

Anil Gavate Over a year ago

I want to create a dynamic query

Rachcha Over a year ago

@MaheswaranRavisankar: Exactly.

Rachcha Over a year ago

@AnilGavate: Please explain your scenario further. I would suggest you post a reproducible example (as small as possible) and the precise output behavior that you expect. A set of test cases would work.

|

evilive · Accepted Answer · 2014-11-10 08:00:50Z

1

In general you can use

execute immediate v_your_sql_code;

to execute dynamic SQL within PL/SQL, but from your question I'm not really aware what you want to do.

Edit:

y_your_sql_code := 'Select yourColumn from ..  where ...AND  gfcid in ('||GDFCID_STRING||')';

execute immediate v_your_sql_code into v_result;

You'll have to define v_result in the right datatype, you could use more then one result variable if you need more result columns, you'll need i.e. a complex type if you can retrieve more than one row.

edited Nov 10, 2014 at 8:00

answered Nov 10, 2014 at 7:09

evilive

9961 gold badge8 silver badges27 bronze badges

5 Comments

Anil Gavate Over a year ago

I want to add dynamic String in the query Like select x from y where id in (dynamic sql)

Anil Gavate Over a year ago

How can I do that in IN clause?

evilive Over a year ago

You'll have to pass your whole Statement as string to the execute immediate command. And you have define variables for the result. I'll edit above.

Anil Gavate Over a year ago

result would be set of Strings

evilive Over a year ago

Then you could select into an v_table, build i.e. as described in the answer from Rachcha.

Collectives™ on Stack Overflow

Using string in Oracle stored procedure

2 Answers 2

7 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related