Re: passing in list of values in stored procedure

From: z1hou1 <z1hou1_at_gmail.com>
Date: Tue, 02 Oct 2007 00:01:57 -0700
Message-ID: <1191308517.108264.223300@r29g2000hsg.googlegroups.com>

Hi tiffanythang,
It would be helpful if you can specify the database version. I have modified the procedure here to use REF CURSOR - applicable in Oracle 8i and above.
This procedure is valid if you may call it as follows:

EXEC myproc1('1') or EXEC myproc1('1,2'). Both of these will retrieve the rows corresponding to id=1 or id=1 or 2 from mytab as mentioned in your very first post. The type REF CURSOR also allows you to pass the CURSOR to other procedures/functions as parameters.

/** BEGIN of example **/

CREATE OR REPLACE PROCEDURE myproc1(p_idlist IN VARCHAR2) AS

   v_id NUMBER;
   v_name VARCHAR2(30);
   v_sql_text VARCHAR2(1000);

   TYPE c_ref_cursor IS REF CURSOR;
   c_mytab_cur c_ref_cursor;
BEGIN
   v_sql_text := 'SELECT id,name FROM mytab WHERE id IN (' || p_idlist || ')';

   OPEN c_mytab_cur FOR v_sql_text;
   LOOP

      FETCH c_mytab_cur  INTO v_id, v_name;
      EXIT WHEN c_mytab_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('id = ' || v_id);
      DBMS_OUTPUT.PUT_LINE('name = ' || v_name);

   END LOOP;
   CLOSE c_mytab_cur;
END;
/** END of example **/

While the example will work as long as the list is a numeric list, slight modifications are neccessary if the parameters passed are going to types other than numeric. If the parameters are say - alphanumeric, then you will have to build in the single quotes as well into v_sql_text - just as you had called the proc originally. Using REF CURSOR you may construct complex SQLs with parameters representing different datatypes, not just lists as is evident from this example.

Look up REF CURSOR against Oracle documentation all the way from Oracle 8i to 10g. It has evolved and is very powerful. There is a variation in SYS_REFCURSOR and OracleRefCursor as you move thru the versions. It is extremely flexible and with 9i and 10g can be passed back to .net programs and jdbc programs.

Hope this helps.

Regards,
z1hou1 Received on Tue Oct 02 2007 - 02:01:57 CDT