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 ignoredAnswer1:
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); /
when ever you add a valueAnswer2:
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> pseudocolumnAnswer3:
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
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
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.