Re: Input array string

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in news:4vidnahR-Iu-YovdRVn-jg_at_comcast.com:

OK.
I wasn't questioning that it could be done that way - just that I wouldn't consider this to be an attractive approach to the problem in general.

You realize it get's a whole lot uglier if you attempt to handle things like embedded commas, quotes or who knows what all...

Not that it can't be done of course, just not what I would like to see as a general solution to this problem.

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