Re: Utterly simple but eluding solution: Passing a string of type IN into a procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 12 Jun 1999 00:18:05 GMT
Message-ID: <3761a5a6.3686120@newshost.us.oracle.com>

A copy of this was sent to shuchi_at_my-deja.com (if that email address didn't require changing) On Fri, 11 Jun 1999 22:50:10 GMT, you wrote:

>I have written a explicit cursor in a procedure having a select
>statement of the type
>
>Select xyz from A_TABLE
>where xyz IN (string_input)
>
>The Procedure therefore looks like as follows:-
>PROCEDURE SOMETHING(string_input IN VARCHAR2(30)
>IS
>CURSOR IS
>Select xyz from A_TABLE
>where xyz IN (string_input);
>....
>END SOMETHING;
>
>Now how do I pass in correctly into the procedure a value for say
>string_input = 'A1','A2','A3' .
>Do I need Additional quotes. I have tried passing
>string_input = '''A1'',''A2'',''A3'''. Though the procedure accepts
>the string it does not return any correct answer.
>How do I go about it?

The query returns the correct answer -- you are asking the wrong question :)

You need to use dynamic sql to do this particular feat. the problem is, your query is equivalent to:

select * from t where x in ( '''x'', ''y''' );

or more simply:

select * from t where x in ( '<constant>' )

where constant just happens to be 'x', 'y'

Here is a quick example using dbms_sql (versions 8.0 and less of oracle) to do something *similar* (doesn't do an in but you get the picture)

create or replace procedure dynquery( p_tname in varchar2, p_time in date) is

        l_theCursor             integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(2000);
        l_status                integer;
        l_query                 varchar2(1000) default 'select ename from ' || 
                                              p_tname || ' where hiredate = :x';

begin

    dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
    dbms_sql.bind_variable( l_theCursor, ':x', p_time );
    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop

       exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
       dbms_sql.column_value( l_theCursor, 1, l_columnValue );
       dbms_output.put_line(  l_columnValue );

    end loop;
    dbms_sql.close_cursor(l_theCursor);

end ;
/

exec dynquery( 'emp', '23-jan-82' );

In Oracle8i, release 8.1, this will simplify to:

...

   type myCur is ref cursor;
   l_cursor myCur;
begin

   open l_cursor for 'select * from T where x in ( ' || l_string || ' )';

   loop

      fetch l_cursor into some_fields....;
      exit when l_cursor%notfound;
       ....

   end loop;
end;
/

>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jun 11 1999 - 19:18:05 CDT