How to store and populate data returned by Execute Immediate in Oracle?
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 results in an inconsistent data type error, besides this error, the question arises, can we store the results in a temp table like in MS-SQL? I am new to oracle database. I need a temp table to store records between let say 1 to 20, I will use Row_Number () and modify the query accordingly in my project.
source to share
The Oracle equivalent of a SQL Server temporary table is a PL / SQL collection. We can use the syntax %rowtype
to define a local type that matches the table projection. We can use this type to declare a local variable as a target for selection.
Alas, there is no convenient mechanism for printing a record: you will need to specify each individual column in the call to DBMS_OUTPUT.PUT_LINE ().
Here is your code, rewritten 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, by rewriting such code, it becomes obvious that there is no need for dynamic SQL at all. Thus, we can replace the EXECUTE IMMEDIATE call with a simple choice:
SELECT *
bulk collect into ExecuteDSQLResult
FROM iftlog
WHERE serverid= sid
AND bpid= bid;
source to share
In Oracle
you can use
-
Object type/table type
or - 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
like temp table
like,
select id from table(v_type);
One type constraint must be created in the schema before using them.
Using a dynamic cursor
sql_stmt := 'SELECT * FROM emp WHERE job = :j';
OPEN emp_cv FOR sql_stmt USING my_job;
you need to go through cursor
to place your bet not as flexible astemp table
source to share
You can also use a 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;
/
Inserted data is available for the entire duration of the session (while maintaining a commit) and can be accessed using SQL
select * from tmp;
see also GTT on SO
source to share