well, not doing .nit stuff currently, can't tell you how it stuffs arrays
into oracle procs -- but i'd really, really be surprised if it can't be
done, as it was such a major issue with VB/RDO/ADO and solutions were
available from MS and Oracle and a third party vendor -- which may have been
DataDirect under a prior name; check out 'Arrays of parameters (bulk
inserts/updates)' on
http://www.datadirect-technologies.com/products/dotnet/docs/dotnet-interop.asp
it looks like it might be related to your needs
meanwhile, on the generalization side -- you just need to apply a little
more design, i.e:
have the 'array' parameter start (and optionally end) with the separator
character, and create a reusable oracle procedure that translates any
'array' parameter into the PL/SQL table needed by the proc doing the actual
work
ie:
CREATE OR REPLACE procedure populate_tbl(
ip_tbl in out vc4000tbl
, ip_arr in varchar2 )
is
offset number default 2;
dlen number default length(ip_arr);
slen number;
sep varchar2(1) := substr(ip_arr,1,1);
begin
ip_tbl.delete;
while offset < dlen
loop
slen := instr(ip_arr,sep,offset);
if slen = 0
then
slen := dlen+1;
end if;
ip_tbl.extend;
ip_tbl(ip_tbl.count) := substr(ip_arr,offset,slen-offset);
offset := slen+1;
end loop;
end populate_tbl;
/
which then gets reused as needed:
procedure list_emps ( ip_depts in varchar2 )
is
- declare and initialize PL/SQL table based on pre-created type
tbl_deptno vc4000tbl := vc4000tbl();
begin
- load deptno table from comma separated list
populate_tbl(tbl_deptno,ip_depts);
- use the deptno table in subquery with TABLE and CAST syntax
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;
SQL> exec list_emps('|10|20|30')
is it the best tool in all cases? nothing ever is. but it's a good tool to
have in the drawer.
- mcs
"roger" <rsr_at_rogerware.com> wrote in message
news:Xns947DAA4C1306rsrrogerwarecom_at_63.240.76.16...
| "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.
| >| | >
| >| |
| >|
| >|
| >
| >
|
Received on Tue Jan 27 2004 - 19:48:46 CST
