How to store and populate data returned by Execute Immediate in Oracle?

Question

In my project , i am trying to execute the following query :

DECLARE
  Sid nvarchar2(30) := ''; /*Here the values will come from some other variable>*/
  Bid nvarchar2(30) := ''; /*Here the values will come from some other variable>*/
  ExecuteDSQL varchar2(1000);
  ExecuteDSQLResult varchar2(10000);
BEGIN

  IF Sid IS NULL THEN Sid := '1' ; ELSE Sid := '4' ; END IF;
  IF Bid IS NULL THEN Bid := '1' ; ELSE Bid := '5' ; END IF;

  ExecuteDSQL := '  SELECT * FROM iftlog WHERE serverid='''|| Sid
                 || ''' AND bpid=''' || Bid  || '''  ';

  EXECUTE IMMEDIATE ExecuteDSQL INTO ExecuteDSQLResult;

  DBMS_OUTPUT.PUT_LINE(ExecuteDSQLResult);

END;
/

The query is resulting into the error of inconsistent datatypes, besides this error the question is can we store results into the temp table as like in MS-SQL ? I am new to oracle database. I need temp table to store the records between lets say 1 to 20,i will be using Row_Number() and will modify the query accordingly in my project.

Answer 1

The Oracle eqivalent of a SQL Server temporary table is a PL/SQL collection. We can use the %rowtype syntax to define a local type which matches a table's projection. We can use that type to declare a local variable as a target for a selection.

Alas there is no convenient mechanism for printing out a record: you will need to specify each individual column in the DBMS_OUTPUT.PUT_LINE() call.

Here is your code re-written in idomatic PL/SQL:

DECLARE 

    Sid nvarchar2(30) := '';/*Here the values will come from some other variable>*/
    Bid nvarchar2(30) := '';/*Here the values will come from some other variable>*/

    ExecuteDSQL varchar2(1000) := 'SELECT * FROM iftlog WHERE serverid= :1 AND bpid= :2';
    type iftlog_nt is table of  iftlog%rowtype;

    ExecuteDSQLResult iftlog_nt;

BEGIN

    IF Sid IS NULL THEN Sid := '1' ; ELSE Sid := '4' ; END IF;
    IF Bid IS NULL THEN Bid := '1' ; ELSE Bid := '5' ; END IF;

    EXECUTE IMMEDIATE ExecuteDSQL 
        bulk collect into ExecuteDSQLResult
        using sid and bid;

    -- loop through all records in PL/SQL table
    for idx in 1..ExecuteDSQLResult.count()
    loop
        DBMS_OUTPUT.PUT_LINE(ExecuteDSQLResult(idx).some_col||'::'||ExecuteDSQLResult(idx).some_other_col);
    end loop;

END;
/

Of course, having it re-written the code like this it becomes obvious that there is no need for dynamic SQL at all. So we can replace the EXECUTE IMMEDIATE call with a straightforward select:

SELECT * 
bulk collect into ExecuteDSQLResult 
FROM iftlog 
WHERE serverid= sid 
AND bpid= bid;

Answer 2

In Oracle you can use

1. Object type/table type or
2. Dynamic cursor

Using Object type/table type

CREATE TYPE obj_typ AS OBJECT ( 
   id          number,
   name        VARCHAR2(20)
);
/

CREATE TYPE tab_typ AS table of  obj_typ;
/

declare
    v_type  tab_typ :tab_typ();
    .....
begin
    .....
    execute immediate 'select obj_typ(id, name) from tbl' into v_type;
    .....
end;

now you can use v_type as temp table like,

select id from table(v_type);

One limitation of type is it has to created in the schema before using them.

Using dynamic cursor

sql_stmt := 'SELECT * FROM emp WHERE job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;

limitation you have to loop through the cursor to bet the data not as flexible as temp table

Answer 3

You can also use global temporary table

 create global temporary table tmp  
 (serverid varchar2(10),
  bpid varchar2(10),
  txt varchar2(100)
  )
 ON COMMIT PRESERVE ROWS  
 ;

.

 DECLARE 

 Sid varchar2(30) := '1'; 
 Bid varchar2(30) := '1'; 
 ExecuteDSQL varchar2(1000);

 BEGIN

 ExecuteDSQL := 'insert into  tmp  
 SELECT serverid,bpid,txt FROM iftlog WHERE serverid=:Sid  AND bpid=:Bid';

 EXECUTE IMMEDIATE ExecuteDSQL  using sid, bid;
 commit;
 END;
 /

The inserted data are available for the whole duration of the session (on commit preserve) and can be accessed with SQL

select * from tmp;

see also GTT on SO