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;
/