Oracle - how do I pass in a param for an IN statement to proc?

Your code has a literal string '|| in_list ||'
That is different from concatenation of || in_list ||

But anyway, you can't use the IN() clause like that. There is no EASY way to do that, but there are ways. You can create dynamic tables with the list of values, or there are other solutions for this, you might read these articles:

http://fdegrelle.over-blog.com/article-1694534.html

http://www.techonthenet.com/oracle/questions/cursor3.php

there has to be a better way than having to pass individuals vars as they will always be not known until coming in and also inserting to another table is a bad idea I think as well.  What about the execute immediate?  

Did you read the 1st URL I provided?

EXECUTE IMMEDIATE would not work for returning multiple rows, which it appears your query does.
So I would just use a dynamic query.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#i1006172

okay I am new to oracle more a SQL guy.  Is there a way to place the value into a temp table somehow and then use the IN off that table?  I will need to find a real good example as I will mess up the syntax.

You have to dynamically build the string, see the example below:
 

Create or replace procedure test_proc(in_list in varchar2, refcur out sys_refcursor) is
begin
open refcur for
 'select 1 col1
 from dual where dummy IN (''' || in_list || ''')';
End;
/
variable refcur refcursor
exec test_proc('X'',''Y',:refcur);
print refcur

anonymous block completed
refcur
------------------
COL1
----
1    


/
variable refcur refcursor
exec test_proc('X',:refcur);
print refcur
anonymous block completed
refcur
------------------
COL1
----
1    

>>okay I am new to oracle more a SQL guy.  Is there a way to place the value into a temp table somehow and then use the IN off that table?  I will need to find a real good example as I will mess up the syntax.

It seems like you are not reading the URLs I provided. I've provided 3 alternatives. I think in your case building the dynamic SQL might be the best. Please read example 8-2 in the Oracle Application Developer's Guide - Fundamentals:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#i1006172

Here is a code sample that shows dynamic SQL with list of values in the IN clause. You could change this to a procedure IN parameter.

DECLARE
  TYPE cur_type IS REF CURSOR;
  my_cursor cur_type;
  my_list_of_values VARCHAR2(100);
  first_name VARCHAR2(50);
  query VARCHAR2(1000);
BEGIN
  my_list_of_values := '''I'', ''A'', ''Z''';
  query := 'SELECT FIRST_NAME FROM PROVIDER WHERE STATUS IN(' || my_list_of_values || ')';
  OPEN my_cursor FOR query;
  LOOP
     FETCH my_cursor INTO first_name;
     dbms_output.put_line(first_name);
     EXIT WHEN my_cursor%NOTFOUND;
  END LOOP;
  CLOSE my_cursor;
END;
/

Select allOpen in new window

I tried this:

DECLARE
  TYPE cur_type IS REF CURSOR;
  my_cursor cur_type;
  my_list_of_values VARCHAR2(100);
  billrun VARCHAR2(50);
  query VARCHAR2(1000);
BEGIN
  my_list_of_values := '''C14'', ''C15'', ''C16''';
  query := 'SELECT BillRun FROM BillRun.RPT_CUSTOM_XLS Where BillRun IN(' || my_list_of_values || ')';
  OPEN my_cursor FOR query;
  LOOP
     FETCH my_cursor INTO billrun;
     dbms_output.put_line(billrun);
     EXIT WHEN my_cursor%NOTFOUND;
  END LOOP;
  CLOSE my_cursor;
END;
/

It compiled and runs but there should be data and none is coming back.

You need to enable server output for dbms_output to work

From sqlplus?

set serveroutput on

i am running in toad

In toad, look at the bottom, where your data grid / results are. There is a tab named "DBMS Output"
go there, then click the red dot button. it goes green, then reexecute the query.

Also, be aware, the output might take a few seconds, due to Toad having to poll the database for the output. When you are developing actively, you can turn down the polling frequency to less than 5 seconds, which is the default.

okay that is green but it actually on the left hand side is showing a red X and next to it DECLARE in the navigator pane.  I wonder if the declare is messed up

wait it is working sweet.  Let me try this now with my proc.  Sorry now I see the results

I have this now but I am getting some error in my application saying the ilistsource does not exist or something.  Do I need the dbms output for this as well in the proc?

 procedure GET_EDIPLATINUM_ACCOUNTS(in_list in varchar2, p_rc out sys_refcursor) is
   cus_parent NVARCHAR2(10);
   parent_name NVARCHAR2(100);
   billrun NVARCHAR2(10);
   folder NVARCHAR2(300);
   map_version CHAR(1);
   query VARCHAR2(6000);
   begin
      query := 'SELECT Cus_Parent, Parent_Name, BillRun, Folder, Map_Version FROM BillRun.RPT_CUSTOM_XLS Where (Delivery_Method like ''%EDI%'' Or Delivery_Method like ''%GSI%'') AND BillRun IN(' || in_list || ') Order By BillRun, Cus_Parent';
      open p_rc for query;
      LOOP
          FETCH p_rc INTO cus_parent, parent_name, billrun, folder, map_version;
          EXIT WHEN p_rc%NOTFOUND;
    END LOOP;
    CLOSE p_rc;
  end;

No, dbms_output is just for printing / debugging. You typically don't use it in production stored procs.

So, you should check that in_list is in the proper format for SQL, ie. escaping, etc. for embedded quotes.

okay cool, it is outputting in my DBMS test let me check what is coming in somehow.

is it possible it is taking it as seperate params or something.  here is what my param is coming in:

"'''C14'',''C15'''"

three single quotes front and end then the two single in between

I need to somehow get this

my_list_of_values := '''C14'', ''C15'', ''C16''';

somehow as this works in my editor test and dbmn outputs values

 DECLARE TYPE cur_type IS REF CURSOR;
  my_cursor cur_type;
  cus_parent NVARCHAR2(10);
  parent_name NVARCHAR2(100);
  billrun NVARCHAR2(10);
  folder NVARCHAR2(300);
  map_version CHAR(1);
  my_list_of_values VARCHAR2(100);
  query VARCHAR2(6000);
  BEGIN
  my_list_of_values := '''C14'', ''C15'', ''C16''';
	query := 'SELECT Cus_Parent, Parent_Name, BillRun, Folder, Map_Version FROM BillRun.RPT_CUSTOM_XLS Where (Delivery_Method like ''%EDI%'' Or Delivery_Method like ''%GSI%'') AND BillRun IN(' || my_list_of_values || ') Order By BillRun, Cus_Parent';
	open my_cursor for query;
	LOOP
	    FETCH my_cursor INTO cus_parent, parent_name, billrun, folder, map_version;
		dbms_output.put_line(cus_parent ||'--'||parent_name);
	    EXIT WHEN my_cursor%NOTFOUND;
    END LOOP;
  CLOSE my_cursor;
  END;
/

Select allOpen in new window

I changed it to a stored proc and called it like this:

execute test_list('''I'', ''Z''');

man this one is a struggle lol.  I hate oracle I really do lol SQL is so much easier.  Can you post the proc you did up?  I am trying everything and cant get it to run it keeps returning 0 tables I think its the way its getting passed through my application.  I have been even doing replace strings in the proc and passing it as '''C10, C11, C12'''

then in my proc

my_list_of_values := replace(in_list,',',chr(39) || chr(39) ||','||chr(39) || chr(39));

which will make the commas a '',''

 procedure GET_EDIPLATINUM_ACCOUNTS(in_list in varchar2, p_rc out sys_refcursor) is
   cus_parent NVARCHAR2(10);
   parent_name NVARCHAR2(100);
   billrun NVARCHAR2(10);
   folder NVARCHAR2(300);
   map_version CHAR(1);
   my_list_of_values VARCHAR2(4000);
   query VARCHAR2(6000);
   begin
    my_list_of_values := replace(in_list,',',chr(39) || chr(39) ||','||chr(39) || chr(39));
      query := 'SELECT Cus_Parent, Parent_Name, BillRun, Folder, Map_Version FROM BillRun.RPT_CUSTOM_XLS Where (Delivery_Method like ''%EDI%'' Or Delivery_Method like ''%GSI%'') AND BillRun IN(' || in_list || ') Order By BillRun, Cus_Parent';
      open p_rc for query;
      LOOP
          FETCH p_rc INTO cus_parent, parent_name, billrun, folder, map_version;
          EXIT WHEN p_rc%NOTFOUND;
    END LOOP;
    CLOSE p_rc;
  end;

thats the proc:

I pass a string into the IN parameter as '''C10,C11,C12'''

then the replace here will make the commas '','' which will make the my_list_of_values equal to '''C10'',''C11'',''C12''' which looks like the declare piece that is working.

I can also pass it exact '''C10'',''C11'',''C12'''  but I think its not recognizing that as a varchar param coming in possibly.

the query I just changed that to AND BillRun IN(' || my_list_of_values || ') Order By BillRun, Cus_Parent';

still no dice

I also just tried this passing '''C14'',''C15'',''C16''' to the following:

procedure GET_EDIPLATINUM_ACCOUNTS(in_list in varchar2, p_rc out sys_refcursor) is
   cus_parent NVARCHAR2(10);
   parent_name NVARCHAR2(100);
   billrun NVARCHAR2(10);
   folder NVARCHAR2(300);
   map_version CHAR(1);
   query VARCHAR2(6000);
   begin
      query := 'SELECT Cus_Parent, Parent_Name, BillRun, Folder, Map_Version FROM BillRun.RPT_CUSTOM_XLS Where (Delivery_Method like ''%EDI%'' Or Delivery_Method like ''%GSI%'') AND BillRun IN(' || in_list || ') Order By BillRun, Cus_Parent';
      open p_rc for query;
      LOOP
          FETCH p_rc INTO cus_parent, parent_name, billrun, folder, map_version;
          EXIT WHEN p_rc%NOTFOUND;
    END LOOP;
    CLOSE p_rc;
  end;

My code in my application C# passes now exactly what is used in the test code here is that code that builds the string to pass to an input param of varchar2.

        protected void LoadAccounts_Click(object sender, EventArgs e)
        {
            string codes = "'";

            for (int index = 0; index <= EDIJobData.Rows.Count - 1; index++)
            {
                CheckBox cb = (CheckBox)EDIJobData.Rows[index].FindControl("JobCheckBox");

                if (cb.Checked)
                {
                    codes += "''";
                    codes += EDIJobData.Rows[index].Cells[1].Text.Trim();
                    codes += "'',";
                }
            }

            if (codes != string.Empty)
            {
                //strip off last comma
                codes = codes.Substring(0, codes.Length - 1);
            }

            codes += "'";

            //Now load accounts from billrun table
            LoadAccountData(codes);
        }

that loops build the string for me '''C10'',''C11'','"C12'''

using replace and putting in chr(39)||chr(39) is not the same thing.

The use of the '' escaping causes Oracle to store only one quote, not two. The 1st quote in the escape sequence is not stored, its only there to allow Oracle to parse the string properly and to tell whether you are trying to put the apostrophe / quote character inside the string, or you are terminating the string.

okay so an idea then if I pass in into the param exactly the '''C10'',''C11'',''C12''' it does not work.  I have the same issues in SQL your right its all the single quote syntax its a pain.  In this case then do I just pass it in as just single quote then such as 'C10','C11','C12' ?  The only this is someone told me that doing it this way the proc thinks it is seperate parameters.

i actually tried just the single quotes and it did not return me any data though.

You are mixing C# and Oracle PL/SQL syntax. When passing from C#, its using a bind variable, you don't need to escape it at all.

Just pass this: Note, I start the C# string with double quote, then I put a few spaces for clarity:
I think this will do the job.

string codes = "   'C10', 'C11', 'C12'   ";

Select allOpen in new window

the double quotes I think just goes around it to show a string I am pretty sure it does not take those in.  But again I think it is thinking it is an end command when I pass it like that.  Hey one other thing since I have this split string now.  Can I create a temp table and loop through like I have a sample proc for create a temp table and then use my IN statement using a nested select?  How do I create a temp table and then insert to that temp table the one value?  This may work.

thanks

thanks for all your help yesterday