You Asked

SQL Version:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

Query:

Can I use a PL sql Type as a bind variable to execute immediate in FORALL? Please have a look at the code below .. Let me know if it is feasible and if not why so?

CREATE OR REPLACE PROCEDURE sp_test
AS

   TYPE typ_abc IS TABLE OF abc%rowtype INDEX BY BINARY_INTEGER;
   v_typ_abc                   typ_abc;
BEGIN
   --Code
   
         FORALL i IN v_typ_abc.FIRST .. v_typ_abc.LAST  SAVE EXCEPTIONS
            EXECUTE IMMEDIATE 'INSERT INTO ' || p_table_owner || '.' || p_table_name || ' /*+ PARALLEL 1 */ PARTITION ( ' || p_partition_name || ' ) '
                                             || ' ( ' || v_column_names_list ||' ) VALUES :1' USING v_typ_abc(i);
                                             
   --Code                                            
END;
/

On compiling the same I get a PLS-00457 error.

and Chris said...

12c lifted many of the restrictions for the PL/SQL data types you can pass to SQL. Provided you declare the type in a package spec.

Sadly you're using the PL/SQL-only %rowtype. And this is still one of the restrictions:

If the PL/SQL-only data type is an associative array, it cannot be used within a non-query DML statement (INSERT, UPDATE, DELETE, MERGE) nor in a subquery.

https://docs.oracle.com/database/121/LNPLS/release_changes.htm#GUID-57E439FB-B196-46CB-857C-0ADAB32D9EA0

You can get around this by binding each attribute separately:

create table t (
  x int
);

create or replace procedure p  as
  type tp is table of t%rowtype index by binary_integer;
  recs tp;
begin
  recs(1).x := 1;
  recs(2).x := 2;
  
  forall i in 1 .. recs.count 
    execute immediate 'insert into t values (:v)' using recs(i).x;
end p;
/

exec p();
select * from t;

X   
  1 
  2

Or, even better, use static SQL!

create or replace procedure p  as
  type tp is table of t%rowtype index by binary_integer;
  recs tp;
begin
  recs(1).x := 1;
  recs(2).x := 2;
  
  forall i in 1 .. recs.count 
    insert into t values recs(i);
end p;
/
sho err

exec p();
select * from t;

X   
  1 
  2 
  1 
  2

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What Trace Pl/Sql Type to user?

Edgar Corona, March 14, 2018 - 1:32 pm UTC

Hi Tom.

I know that sql_trace beabled to do trace and view explain plan about own executed querys. This is greats. But, pl/sql whats tool i can to use for doing check performance and tunning over my packages or code library, look like sql_trace?

March 14, 2018 - 1:43 pm UTC

What's that got to do with this question?

And I answered a similar question recently:

https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-trace-plsql-procedure-for-tuning

Thanks!!

A reader, March 14, 2018 - 5:59 pm UTC

Thanks for confirming this is not possible!!
I am using the individual record variables as of now. Sadly cannot use static SQL .... as all my queries are dynamic
..

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

What Trace Pl/Sql Type to user?

Thanks!!

More to Explore

PL/SQL demos

PL/SQL docs

PL SQL Type as bind variable in Dynamic FORALL

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

What Trace Pl/Sql Type to user?

Thanks!!

More to Explore

PL/SQL demos

PL/SQL docs