RE: Bulk bind in Native Dynamic SQL

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