69186

How do you specify IN clause in a dynamic query using a variable?

Question:

In PL/SQL, you can specify the values for the IN operator using concatenation:

v_sql := 'select field1 from table1 where field2 in (' || v_list || ')';

Is it possible to do the same using a variable?

v_sql := 'select field1 from table1 where field2 in (:v_list)';

If so, how?

EDIT: With reference to Marcin's answer, how do I select from the resultant table?

declare cursor c_get_csv_as_tables is select in_list(food_list) food_list from emp_food where emp_type = 'PERM'; cursor c_get_food_list (v_food_table varchar2Table)is select * from v_food_table; begin for i in c_get_csv_as_tables loop for j in c_get_food_list(i.food_list) loop dbms_output.put_line(j.element); end loop; end loop; end;

I get the following error:

ORA-06550: line 10, column 6: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 9, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 15, column 34: PLS-00364: loop index variable 'J' use is invalid ORA-06550: line 15, column 13: PL/SQL: Statement ignored

Answer1:

Like in @Sathya link, you can bind the varray (I took @Codo example):

CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200); / DECLARE l_str_tab str_tab_type; l_count NUMBER; v_sql varchar2(3000); BEGIN l_str_tab := str_tab_type(); l_str_tab.extend(2); l_str_tab(1) := 'TABLE'; l_str_tab(2) := 'INDEX'; v_sql := 'SELECT COUNT(*) FROM all_objects WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(:v_list))'; execute immediate v_sql into l_count using l_str_tab; dbms_output.put_line(l_count); END; /

<strong>UPDATE:</strong> the first command can be replaced with:

CREATE OR REPLACE TYPE str_tab_type IS TABLE OF VARCHAR2(200); /

then call:

l_str_tab.extend(1);

when ever you add a value

Answer2:

Unfortunately you cannot bind a list like this, however you can use a table function. Read <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:210612357425" rel="nofollow">this</a>

Here's an example of usage based on your code:

declare cursor c_get_csv_as_tables is select in_list(food_list) food_list from emp_food where emp_type = 'PERM'; cursor c_get_food_list (v_food_table varchar2Table)is select column_value food from TABLE(v_food_table); begin for i in c_get_csv_as_tables loop for j in c_get_food_list(i.food_list) loop dbms_output.put_line(j.food); end loop; end loop; end;

I used here a <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns004.htm" rel="nofollow">column_value</a> pseudocolumn

Answer3:

Bind variable can be used in Oracle SQL query with "in" clause.

Works in 10g; I don't know about other versions.

Bind variable is varchar up to 4000 characters.

Example: Bind variable containing comma-separated list of values, e.g.

:bindvar = 1,2,3,4,5

select * from mytable where myfield in ( SELECT regexp_substr(:bindvar,'[^,]+', 1, level) items FROM dual CONNECT BY regexp_substr(:bindvar, '[^,]+', 1, level) is not null );

Answer4:

As per @Marcin's answer you can't do this, however, there's a fair bit to add to that, as your query should actually work, i.e. run.

Simply put, you cannot use a bind variable for a table or column. Not only that, bind variables they are assumed to be a character, so if you want a number you have to use to_number(:b1) etc.

This is where your query falls down. As you're passing in a string Oracle assumes that your entire list is a single string. Thus you are effectively running:

select field1 from table1 where field2 = v_list

There is no reason why you can't do this a different way though. I'm going to assume you're dynamically creating v_list, which means that all you need to do is create this list differently. A series of or conditions is, purportedly :-), no different to using an in.

By purportedly, I mean never rely on something that's untested. Although Tom does say in the link that there may be performance constraints there's no guarantee that it wasn't quicker than using in to begin with. The best thing to do is to run the trace on your query and his and see what difference there is, if any.

SQL> set serveroutput on SQL> SQL> declare 2 3 l_string varchar2(32767); 4 l_count number; 5 6 begin 7 8 for xx in ( select rownum as rnum, a.* 9 from user_tables a 10 where rownum < 20 ) loop 11 12 if xx.rnum = 1 then 13 l_string := 'table_name = ''' || xx.table_name || ''''; 14 else 15 l_string := l_string || ' or table_name = ''' || xx.table_name || ' '''; 16 end if; 17 18 end loop; 19 20 execute immediate 'select count(*) 21 from user_tables 22 where ' || l_string 23 into l_count 24 ; 25 26 dbms_output.put_line('count is ' || l_count); 27 28 end; 29 / count is 19 PL/SQL procedure successfully completed.

Recommend

  • How to populate an array in an oracle stored procedure?
  • SQL Retrieving an object from VARRAY in Oracle 11g Database
  • python list vote(['G', 'G', 'N', 'G', 'C']) [dupli
  • Oracle: Trying to loop thru insert statement using dynamic list of table names
  • ORA-29531: no method in class error
  • ORA-00984: column not allowed here - Oracle database
  • Return one variable from a SELECT inside a function
  • “Missing right parenthesis”: On Delete Set Null On Update Cascade (SQL/Oracle) [closed]
  • Format SQL Table data as Text Table
  • Jdbc array binding: character set encoding
  • How to design a data model that deals with the current employees and forecasted employees?
  • INSERT Statement in PL/SQL fails in Oracle database
  • Java Regex find/replace pattern in SQL comments
  • multiset union distinct gives “wrong number of types or arguments passed” error
  • confusion about using types instead of gtts in oracle
  • Create a dynamic name table with Prepared Statement in java
  • Is there a way to create a table and some initial partitions dynamically?
  • Create a pl/sql function & find leap years [closed]
  • Dapper Oracle Number(10,0) is returned as Decimal Parser error
  • DES3 encryption: ruby openssl::cipher vs. oracle dbms_obfuscation_toolkit
  • Memory Leak with OracleCommand
  • Accessing types defined inside package from java
  • PL/SQL: re-write SELECT statement using IN parameter in stored procedure
  • SQL Procedures - Comparing values
  • DML and Exception Handling - Oracle
  • SAXReader not re-ecape characters
  • Checking free space on FTP server
  • Is there a javascript serializer for JSON.Net?
  • Where to put my custom functions in Wordpress?
  • R: gsub and capture
  • AT Commands to Send SMS not working in Windows 8.1
  • Comma separated Values
  • Buffer size for converting unsigned long to string
  • Error creating VM instance in Google Compute Engine
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Hits per day in Google Big Query
  • how does django model after text[] in postgresql [duplicate]
  • How do I configure my settings file to work with unit tests?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Binding checkboxes to object values in AngularJs