Re: Input array string

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 27 Jan 2004 19:15:03 -0500
Message-ID: <4vidnahR-Iu-YovdRVn-jg@comcast.com>

rog,

here's a complete example (albeit using dbms_output instead of a ref cursor) based on dan's reference, adding population of the PL/SQL table from a comma-separated list passed within a single variable

mcs
create or replace type vc4000tbl is table of varchar2(4000); /

create or replace procedure list_emps ( ip_depts in varchar2 ) is

declare and initialize PL/SQL table based on pre-created type

tbl_deptno vc4000tbl := vc4000tbl();

variables needed for parsing offset number default 1; dlen number default length(ip_depts); slen number; c_sep constant varchar2(1) := ',';

begin

load deptno table from comma separated list
this could be wrapped in a procedure for generalization and reuse

while offset < dlen
loop

     slen := instr(ip_depts,c_sep,offset);
     if slen = 0
     then
        slen := dlen+1;
     end if;

tbl_deptno.extend;
tbl_deptno(tbl_deptno.count) := substr(ip_depts,offset,slen-offset);

offset := slen+1;
end loop;

use the deptno table in subquery with TABLE and CAST syntax
(for illustration -- this would likely be used with a ref cursor)

for r1 in (

     select empno, ename, deptno
   from emp
   where deptno in (

      select column_value
    from table(cast(tbl_deptno as vc4000tbl))     )
   order by deptno, empno
   )
  loop

     dbms_output.put_line(
     r1.deptno
   || ': '
   || r1.empno

   || ' -- '
   || r1.ename);
  end loop;
end list_emps;

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:...

| roger,
|
| in response to your request: 'you're wrong'
|
| i'm not sure how you do it in .NET, but i seem to remember being able to
| pass an array via ADO as a PL/SQL indexed table
|
| the sample referenced by dan morgan just needs to be modified to populate
| the type InStrTab variable from either a passed PL/SQL indexed table, or
| parse out the individual elements from a single parameter that contains a
| comma-separated list of values
|
| bottom line is, the sample nicely solves the problem of having a varying
| number of parameters for the IN clause
|
| -- mcs
|
| "roger" <rsr_at_rogerware.com> wrote in message
| news:Xns947D96AF35770rsrrogerwarecom_at_63.240.76.16...
| |
| | That's a handy reference page with some interesting info.
| | Unfortunately, it doesn't address the question the poster is asking.
| |
| | He is saying that he's got some application with an array of
| | values that he wishes to pass to a stored procedure, for use
| | in an SQL statement IN clause.
| |
| | Nothing about using the InStrTab table type is going to help
| | him to pass those values to the stored procedure.
| |
| | Unfortunately, I don't have a good answer for him, because as
| | far as I can see, there is no good answer to this problem, which
| | fundamentally is - how do you pass an array to a stored procedure.
| |
| | Answer is - you can't.  If you could, then you could construct
| | a variety of solutions for building up the IN clause, including
| | initializing a pl/sql table variable and querying it as shown.
| |
| | But, since you can't pass the array of values to the stored procedure,
| | it's all moot.
| |
| | Please, tell me I'm wrong!:)
| |
| |
| | Daniel Morgan <damorgan_at_x.washington.edu> wrote in
| | news:1074892854.75017_at_yasure:
| |
| | > Cowboy wrote:
| | >
| | >> I have a .NET app that pulls a group of values from a listbox. I need
| | >> to have that list in a WHERE IN clause:
| | >>
| | >> SELECT * FROM TABLE1
| | >> WHERE Column1 IN {PASSED IN VALUES}
| | >>
| | >> Not sure how to do this in Oracle. I do know the following.
| | >>
| | >> 1. I cannot pass in an array or a table type as a parameter, as I am
| | >> stuck with the Microsoft Oracle Client provider and not the Oracle
| | >> downloadable provider. Input will have to be something like a
| | >> comma-separated string as VARCHAR.
| | >>
| | >> 2. I know how to create a TABLE TYPE and fill it with a loop, so I
| | >> can create the TABLE TYPE in the sproc and utilize it, if that is the
| | >> answer.
| | >>
| | >> 3. I had to prove to the other developer that this does not work:
| | >>
| | >> CREATE PROCEDURE MyProc
| | >> (
| | >>     IN_STRING VARCHAR2,
| | >>     OUT_CUR Types.cursor_type
| | >> )
| | >> AS
| | >>
| | >> BEGIN
| | >>   SELECT * FROM TABLE1
| | >>   WHERE Column1 IN IN_STRING ;  -- Invalid numeric error
| | >> END;
| | >>
| | >>
| | >> NOTE: REF_CURSOR is fairly normal for output to .NET in our environ.
| | >>
| | >> I also have tried
| | >>
| | >> WHERE IN TableTypeNameHere
| | >>
| | >> WHERE IN (SELECT * FROM TableTypeNameHere)
| | >>
| | >> I know how I would solve this in SQL Server, but I am lost with the
| | >> proper Oracle implementation, other than to embed a dynamic SQL query
| | >> in the app (not my choice) or create a temp table (an option, but
| | >> would prefer something that can use an array or TABLE TYPE in the
| | >> WHERE IN clause).
| | >>
| | >> TIA,
| | >>
| | >> Gregory A. Beamer
| | >
| | > Go to: http://www.psoug.org/reference/conditions.html
| | >
| | > and scroll down to the demo titled: Complex IN Demo
| | >
| | > And thank you Connor for the brilliant inspiration for this piece of
| | > work.
| | >
| |
|
|

Received on Tue Jan 27 2004 - 18:15:03 CST