3 Replies Latest reply: Jan 2, 2013 3:30 PM by 982397 RSS

    execute immediate with sql collection bind

    982397
      Hi, I am working on an execute immediate implementation where I use a bind which is of a SQL array type. The array type is produced by being passed from a Java array.

      So this looks like:

      execute immediate mystatement using myarray;

      'mystatement' contains a forall command. I had thought that this would not work, because of limitations in using bind arrays with dynamic sql, but it does. Also I see from the Oracle documentation that dynamic sql allows binds to SQL collection types. So am I reading the documentation correctly please, that this is a correct use of execute immediate with a SQL collection?

      I've tested this on both 10.2 and 11g

      thanks

      David
        • 1. Re: execute immediate with sql collection bind
          Solomon Yakobson
          You can bind collection as long as it is collection of SQL declared type. You can't bind collection if it is collection of PL/SQL declared type.

          SY.
          • 2. Re: execute immediate with sql collection bind
            Purvesh K
            979394 wrote:
            Hi, I am working on an execute immediate implementation where I use a bind which is of a SQL array type. The array type is produced by being passed from a Java array.

            So this looks like:

            execute immediate mystatement using myarray;

            'mystatement' contains a forall command. I had thought that this would not work, because of limitations in using bind arrays with dynamic sql, but it does. Also I see from the Oracle documentation that dynamic sql allows binds to SQL collection types. So am I reading the documentation correctly please, that this is a correct use of execute immediate with a SQL collection?

            I've tested this on both 10.2 and 11g

            thanks

            David
            Welcome to the Forum!!!
            Below is a small demonstration to what Soloman has said: (Not the best of examples I would say, but should provide an hint)
            drop table depts;
            drop table emps;
            drop type type_depts;
            
            create table depts (dept_no   number, dept_name varchar2(25), loc varchar2(10));
            
            create table emps (empno  number, name varchar2(25), dept_no  number, salary number);
            
            create or replace type type_depts is table of number;
            
            insert into depts values (10, 'ABC', '111');
            insert into depts values (20, 'ABC1', '111');
            insert into depts values (30, 'ABC2', '111');
            insert into depts values (40, 'ABC3', '111');
            insert into depts values (50, 'ABC4', '111');
            
            insert into emps values (1, 'PQR', 10, 100);
            insert into emps values (2, 'PQR1', 20, 100);
            insert into emps values (3, 'PQR2', 30, 100);
            insert into emps values (4, 'PQR3', 10, 100);
            insert into emps values (5, 'PQR4', 30, 100);
            insert into emps values (6, 'PQR5', 10, 100);
            insert into emps values (7, 'PQR6', 40, 100);
            insert into emps values (8, 'PQR7', 80, 100);
            
            commit;
            
            /* Block to find a DEPT_NO in EMP that does not exist in DEPT table */
            set serveroutput on;
            declare
              dept    type_depts;
              
              type type_emp is table of pls_integer index by pls_integer;
              emp     type_emp;
              
              idx     pls_integer;
            begin
              select dept_no
                bulk collect into dept
                from depts;
            
              execute immediate 'select empno from emps where dept_no not in (select column_value from table(:1))' bulk collect into emp using dept;
            
              for i in emp.first..emp.last loop
                dbms_output.put_line('Emp No:- ' || emp(i));
              end loop;
            end;
            
            anonymous block completed
            Emp No:- 8
            As you said, the "mystatement" contains the FORALL statement; Is the Table Name known at run-time or it is passed as an additional parameter? If the Table name is known, then, IMO, you do not need any Dynamic SQL. A simple FORALL statement should suffice.

            Please provide more details. Also, please read {message:id=9360002}.
            • 3. Re: execute immediate with sql collection bind
              982397
              Thanks very much for this reply, also for Solomons' clarification above.

              The table name is not known at runtime, which is why I am working with a dynamic approach. The purpose of the module is to handle inserts into a related but not identical set of tables.

              I've noted the guidelines for posters and I'll particularly be sure to give more example code from here on in. Thanks again.