ORACLE: How to insert into temp table, while using dynamic sql

Question

I am using a few temp tables in Oracle to store some data, which is needed later in the function. To determine the data I need to store in the table, I am using a dynamic sql statement (using parameters as column names of other tables). I tried to different approaches.

The first time, I tried something like this:

INSERT INTO temp_tableA
EXECUTE IMMEDIATE dynamic_sql
USING IN OUT temp_tableB

And the other approach was like:

EXECUTE IMMEDIATE INTO temp_tableA
USING IN OUT temp_tableB

I already used some other temp table (temp_tableB) to store some data it seems, that it worked fine with the first approach, but there I did not have to use a dynamic sql statement. If I am trying to do it the same way with dynamic sql (so like the first approach) it tells me, that the keyword VALUES is missing. The second try returns the error message, that temp_tableA can't be used as an INTO-target of a SELECT/FETCH-Statement.

What am I missing? I have to say, that I am quite new to Oracle and it's starting to drive me crazy :D

Can you expand the example sql statements to include an example of your dynamic sql please? On the face of it, I suspect you're just after insert into temp_tableA (...) select ... from temp_tableB ... where ... - i.e. no need for dynamic sql at all. But that's just a guess, hence the need for more info. — Boneist
Also, how are you defining your "temp tables"? Are they created as Global Temporary Tables (i.e. permanent tables which hold data on a per session basis), aka GTTs? Also, is the data you're storing in these temp tables used more than once within the function? Because if not, it's likely you could just combine your various sqls into one statement. — Boneist
@Boneist Yes, the temp tables are created as global temporary tables using 'ON COMMIT DELETE ROWS' at the end. The dynamic sql is quite long to paste it here, but it starts with 'SELECT ' + [A LOT PARAMETERS AS COLUMN NAMES] + ' FROM TableA A, temp_tableB B WHERE...' The Where-Clause also includes a few column names as parameters. So as far as I know, I have to use a dynamic sql... — Echelon
Sounds like you're doing something like v_sql := insert into some_table (col1, col2, col3) select '||p_col1||', '||p_col2||', '||p_col3||' from temp_tableA ta inner join temp_tableB tb on ta.col1 = tb.col1 where '||p_col4||' = '||p_col4_val;? — Boneist

Aleksej Aleksej · Accepted Answer · 2016-12-12T11:33:36

Say you have tables like the following:

create table someTab(colA number, colB number, colC number);
create table someOtherTab(col varchar2(10), val number);
insert into someTab values (1, 10, 100);
insert into someTab values (2, 20, 200);

You could define a procedure like this:

create or replace procedure copyProc(colName IN varchar2) is
    vSQL varchar2(1000);
begin
    vSQL := 'insert into someOtherTab(col, val) select ''' || colName || ''', ' || colName || ' from someTab';
    --
    dbms_output.put_line(vSQL);
    execute immediate vSQL;
end;

It you run this procedure, this is what you get:

SQL> exec copyProc('colA');
insert into someOtherTab(col, val) select 'colA', colA from someTab

PL/SQL procedure successfully completed.

SQL> select * from someOtherTab;

COL               VAL
---------- ----------
colA                1
colA                2

SQL> exec copyProc('colC');
insert into someOtherTab(col, val) select 'colC', colC from someTab

PL/SQL procedure successfully completed.

SQL> select * from someOtherTab;

COL               VAL
---------- ----------
colA                1
colA                2
colC              100
colC              200

ORACLE: How to insert into temp table, while using dynamic sql

1 Answers