| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Bulk bind in Native Dynamic SQL
Your assessment is correct. The bulk bind has to be enclosed in an anonymous
block. Listed below is the exact code I'm using to do the bulk bind.
EXECUTE IMMEDIATE 'BEGIN select oid,legacy_key,contact_person_oid bulk collect into :x,:y,:z from '||'&table_name'||' where owner_oid is null and legacy_key is not null; END;' USING OUT tbl_oid, OUT tbl_legacy_key, OUT tbl_cntct_pers_oid;
Thanks
Mark
-----Original Message-----
Sent: Friday, April 18, 2003 10:12 AM
To: Multiple recipients of list ORACLE-L
I see. But what confused me was the bulk collect that you included in the string holding the dynamic select.
I believe it will run only if it is part of anonymous block because it's PL/SQL syntax.
Also the "INTO" will be inside the dynamic sql string which will be bound using the "USING" clause of the EXECUTE IMMEDIATE.
Is this correct, or you meant some other way of doing it?
Thanks
Waleed
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 4/18/03 9:48 AM
Waleed,
"EXECUTE IMMEDIATE" example I suggested instead of ref cursor.
As for "bulk collect" in "EXECUTE IMMEDIATE" with "INTO" clause - it
definitely works in my application (and has been working for coupe
years,
since 8.1.5 came out).
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
> The start of the discussion had to do with bulk reading from a ref
cursor
> that opens a dynamic sql.
>
> This is not available in 8i.
>
> Also regarding the INTO clause of EXECUTE IMMEDIATE:
>
> INTO ...
> Used only for single-row queries, this clause specifies the variables
or
> record into which column values are retrieved. For each value
retrieved by
> the query, there must be a corresponding, type-compatible variable or
field
> in the INTO clause.
>
> Regards,
>
> Waleed
>
>
> -----Original Message-----
> Sent: Thursday, April 17, 2003 5:27 PM
> To: Multiple recipients of list ORACLE-L
>
>
> EXECUTE IMMEDIATE 'select oid BULK COLLECT from ' || TABLE_NAME || '
where
> ...'
> INTO l_oid
> USING ...
>
>
> Igor Neyman, OCP DBA
> ineyman_at_perceptron.com
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, April 17, 2003 4:07 PM
>
>
> > The Execute Immediate does work as long as you put the code inside
an
> > anonymous block.
> >
> > EXECUTE IMMEDIATE 'begin select oid BULK COLLECT from TABLE_NAME
where
> > ... end;'
> > INTO l_oid
> > USING ...
> >
> > What if the table name needs to be dynamic? The execute immediate
would
> not
> > be a viable solution. Hence the need for a ref cursor in the Open
For
> > statement. Currently, I'm working in 8.1.7.4.
> >
> >
> > -----Original Message-----
> > Sent: Thursday, April 17, 2003 11:57 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > This should work:
> >
> > EXECUTE IMMEDIATE 'select oid BULK COLLECT from TABLE_NAME where
...'
> > INTO l_oid
> > USING ...
> >
> >
> > loid is of dbms_sql.number_table, or dbms_sql.varchar2_table, or
> > dbms_sql.date_table type (depends on the type of oid).
> >
> > Igor Neyman, OCP DBA
> > ineyman_at_perceptron.com
> >
> >
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Thursday, April 17, 2003 1:57 PM
> >
> >
> > > Does anyone have a solution to bulk binding in Native Dynamic sql?
> > >
> > > I'm currently using an Open For Using statement which seems to
nullify
> any
> > > possibility of using bulk collect in the fetch statement. Listed
below
> is
> > a
> > > valid and invalid fetch statement. Is there a way of using bulk
binding
> in
> > > the 2nd scenario listed below?
> > >
> > > Valid - Non Bulk collection fetch
> > > open cur for <dynamic statement>
> > > loop
> > > fetch oid into l_oid;
> > > exit when sql%notfound;
> > > <process other code>
> > > end loop;
> > >
> > >
> > > Invalid - Bulk collection fetch
> > > open cur for <dynamic statement>
> > > loop
> > > fetch oid bulk collect into <IOT>;
> > > exit when sql%notfound;
> > > <process other code>
> > > end loop;
> > >
> > >
> > >
> > > Thank you,
> > >
> > > Mark
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Mark Moynahan
> > > INET: Mark.Moynahan_at_apollogrp.edu
> > >
> > > Fat City Network Services -- 858-538-5051
http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting
services
> > >
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: ineyman_at_perceptron.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: Waleed.Khedr_at_FMR.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Moynahan INET: Mark.Moynahan_at_apollogrp.edu Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Apr 18 2003 - 12:47:13 CDT
 |
 |