CODEWITHSUNDEEP
plsql
Damien
Damien

Reputation: 14057

How to use EXECUTE IMMEDIATE to populate temp table in Oracle

I have a number of possible queries that could execute all of which will return two columns. Which queries actually run isn't known ahead of the procedure being called. I need to act on the data returned in these queries before placing them into another, known, table.

So I have created a table type to store these two records. I fetch the query, use EXECUTE IMMEDIATE, and attempt to use bulk collect into. Like so:

create or replace TYPE TEMP_RECORD IS object (
   Identifier VARCHAR2(5000), 
   Message varchar2(5000)
  );
  /
create or replace TYPE THETEMP_TABLE IS TABLE OF TEMP_RECORD;

and

DECLARE
  ID NUMBER(38);
  runID NUMBER(38);
  sqlToRun varchar(5000);
  tempTable THETEMP_TABLE;
  CURSOR myCursor IS 
  select ID, SQL FROM QueryTables  
  where IsActive = 1; 

BEGIN

    OPEN myCursor;
    LOOP
    FETCH myCursor INTO id,sqlToRun;

        EXIT WHEN myCursor%notfound; 

        INSERT INTO Run(RunID,StartDateTime)
        VALUES (id,(select sysdate from dual))
        RETURNING ID INTO runID;

       -- dbms_output.PUT_LINE(runID);
        EXECUTE IMMEDIATE sqlToRun BULK COLLECT INTO tempTable;

        INSERT INTO RunResults(RunDate,RunID,SourceID,ResultDescription)
        SELECT (select sysdate from dual),runID, Identifier, Message FROM tempTable;


    END LOOP; 
    CLOSE myCursor ;

END;

The problem is here: EXECUTE IMMEDIATE sqlToRun BULK COLLECT INTO tempTable;

I get this error:

ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 23
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

I believe the datatypes are correct. I am guessing this is an error which how I am attempting to do it.

EDIT: An example of one of those dynamic queries. 

SELECT c.CustomerIdentifier as SourceIdentifier,'Invalid Customer Type record' as InvalidFields
FROM Customer c
WHERE 
c.CustomerType <> 'a' and c.CustomerType <> 'b' and c.CustomerType <> 'b'

So: How do I populate temporary table from a dynamic query which returns a known amount of columns of the same datatype?

Upvotes: 1

Views: 2770

Answers (1)

Boneist
Boneist

Reputation: 23588

The issue is because you can't select directly from a collection - you must first cast it into a table. E.g.:

SELECT sysdate ,runID, Identifier, Message FROM table(tempTable as THETEMP_TABLE);

However, rather than running the select statement, fetching the results into a collection, then using the collection to insert rows into the table, why not simply do an insert-as-select? That'll save on memory (you're no longer fetching the results into a variable temporarily) and be faster and more efficient.

E.g. something like:

execute immediate 'insert into runresults (...) select sysdate, :runid, t.* from (' || sqltorun ||') t' using runid;

Upvotes: 2

Related Questions