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 16:04:52 GMT
Message-ID: <376b8504.5743138@newshost.us.oracle.com>

A copy of this was sent to "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> (if that email address didn't require changing) On Sat, 12 Jun 1999 10:35:37 +0100, you wrote:

>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.
>

sure, if you take the string with 'x', 'y' and parse it into a nested table type or varray type then you can code:

SQL> create or replace type myTableType as table of varchar2(25);   2 /

Type created.

SQL>
SQL> declare
  2 l_x myTableType := myTableType( 'TKYTE', 'SCOTT' );   3
  3 begin

  4      for x in ( select *
  5                   from all_users
  6                  where username in ( select *
  7                   from THE ( select cast( l_x as mytableType ) from dual ) a
  8                   ) )
  9      loop
 10          dbms_output.put_line( x.user_id );
 11      end loop;

 12 end;
 13 /
4932
9306

PL/SQL procedure successfully completed.

and use it directly....

>--
>
>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
>:)
>>

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 - 11:04:52 CDT