Oracle - Insert into tables using dynamic queries

Question

I'm trying to create a dynamic query to safely select values from one table and insert them into another table using this_date as a parameter.

Because this will be called from outside the app, I should be using bind variables.

table1 is owned by Foo.
table2 is owned by Bar.

What I have so far is:

create or replace package body Foo.this_thing
AS

procedure load_this(this_date IN date)
AS
  v_select_sql VARCHAR2(255);

  type temp_table_type IS TABLE OF Bar.table2$ROWTYPE;
  temp_table temp_table_type;

BEGIN
  -- Get data from Foo.table1
  v_select_sql := 'select :1, field1, field2 from Foo.table1 where field5 = :1';
  execute immediate v_select_sql into temp_table using this_date;
  
  -- Load from temp_table into Bar.table2
  insert into Bar.table2(attr1, attr2, attr3) select attr1, attr2, attr3 from temp_table;

  commit;
END load_this;

END Foo.this_thing;

When I tried to compile it, this error showed up:

Error(101,41): PLS-00597: expression 'TEMP_TABLE' in the INTO list is of wrong type

I then tried this:

create or replace package body Foo.this_thing
AS

procedure load_this(this_date IN date)
AS
  v_sql VARCHAR2(255);

  type temp_table_type IS TABLE OF Bar.table2$ROWTYPE;
  temp_table temp_table_type;

BEGIN

  DBMS_OUTPUT.PUT_LINE('LOAD_THIS:: this_date: ' || to_char(this_date));
  
  v_sql := 'insert into Bar.table2(attr1, attr2, attr3) select :1, field1, field2 from Foo.table1 where field5 = :1';

  DBMS_OUTPUT.PUT_LINE('LOAD_THIS:: v_sql set.');
  
  execute immediate v_sql using this_date;

  DBMS_OUTPUT.PUT_LINE('LOAD_THIS:: v_sql executed.');

  commit;
END load_this;

END Foo.this_thing;

When I execeuted Foo.this_thing.load_this(TO_DATE('20200629', 'YYYYMMDD'));, I got this in my error message:

Error report -
SQL Error: ORA-00933: SQL command not properly ended
ORA-06512: at "Foo.THIS_THING", line 102
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
LOAD_THIS:: this_date: 29-JUN-20
LOAD_THIS:: v_sql set.

The error message is very ambiguous and I have a feeling it's about the execeute immediate command like I may not be using it correctly.

Does anyone know what I am missing?

Answer 1

The code you posted works, at least as long as you supply the bind value twice:

execute immediate v_sql using this_date, this_date;

But you don't need dynamic SQL:

procedure load_this(this_date IN date)
AS
  v_sql VARCHAR2(255);

BEGIN

  insert into table2(attr1, attr2, attr3)
  select this_date, field1, field2
  from table1 where field5 = this_date;

  commit;
END load_this;

db<>fiddle with the procedure in an anonymous block instead of a package for simplicity.

Foo does not have the privileges to insert into the table even though the role it has allows it to

If you don't want to grant the privilege directly to FOO then you will need to use invoker's rights for the entire package:

create or replace package Foo.this_thing
AUTHID CURRENT_USER
AS

procedure load_this(this_date IN date);

END Foo.this_thing;
/

create or replace package body Foo.this_thing
AS

procedure load_this(this_date IN date)
AS
...
END load_this;

END Foo.this_thing;
/

Answer 2

You do not need dynamic SQL for this. Your concern to "safely select values" while laudable is unnecessary in this case. You are creating a procedure where the compiler automatically converts parameters to bound variables.

Bind Variables

When you embed a SQL INSERT, UPDATE, DELETE, MERGE, or SELECT statement directly in your PL/SQL code, the PL/SQL compiler turns the variables in the WHERE and VALUES clauses into bind variables (for details, see "Resolution of Names in Static SQL Statements"). Oracle Database can reuse these SQL statements each time the same code runs, which improves performance.

PL/SQL does not create bind variables automatically when you use dynamic SQL, but you can use them with dynamic SQL by specifying them explicitly (for details, see "EXECUTE IMMEDIATE Statement").

Answer 3

I'm lazy so I started by reviewing your second example. You did away with the temp table so it seemed simpler overall than your first example.

I noticed a missing "AS" keyword. So:

create or replace package body Foo.this_thing
procedure load_this(this_date IN date)
AS
...

becomes

create or replace package body Foo.this_thing
AS
procedure load_this(this_date IN date)
AS
...

I think the inner SELECT clause can be changed from

insert into Bar.table2(attr1, attr2, attr3) select :1, field1, field2 from Foo.table1 where field5 = :1

to

insert into table2(attr1, attr2, attr3) select field5, field1, field2 from table1 where field5 = this_date

Remove the leftover variables from the first example that aren't used anymore in your second example.

  type temp_table_type IS TABLE OF Bar.table2$ROWTYPE;
  temp_table temp_table_type;

That resulted in a package that was at least syntactically valid in my tests. But I can't speak to the validity of the semantics. You'd have to provide more context or sample data for that. You might still run into basic issues like schema foo does not have permission to insert into Table2 in schema bar. Stuff like that.

Good luck.