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

It occurred to me a couple of days ago that this could be done in Oracle 8i with the object option by having a varray type of varchar2.

If you pass in a varray you could then cast it into a subquery in your procedure.

I hadn't got around to a worked example yet, but I'll try and find some time this week.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Thomas Kyte wrote in message <3761a5a6.3686120_at_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 Sat Jun 12 1999 - 04:35:37 CDT