Well in Oracle, EXECUTE IMMEDIATE is simpler than resorting to the dbms_sql package with the exception of cross tenant queries as dbms_sql as of 12c allows a container argument.
However, in a small test, I'm getting nothing. i.e.,
set serveroutput on
declare
ret pls_integer;
cnt pls_integer := 0;
cols number := 0;
ctx varchar2(128) := NULL;
cur number;
stmt varchar2(100);
begin
stmt := 'select count(*) from scott.emp';
cur := dbms_sql.open_cursor;
dbms_sql.parse(c => cur,
statement => stmt,
language_flag => dbms_sql.native,
container => ctx);
dbms_sql.define_column(cur, 1, cnt);
ret := dbms_sql.execute(cur);
dbms_output.put_line('execute = '||ret);
ret := dbms_sql.fetch_rows(cur);
dbms_output.put_line(' fetch = '||ret);
dbms_output.put_line(stmt||' = '||cnt);
dbms_sql.close_cursor(cur);
end;
/
The container when null - meaning current or ignored would allow the query to sent to the target container. I however suspect I'm missing something trivial, hence my question; thanks in advance. Sample output:
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> set echo on
SQL> @f
SQL> set serveroutput on
SQL> declare
2 ret pls_integer;
3 cnt pls_integer := 0;
4 cols number := 0;
5 ctx varchar2(128) := NULL;
6 cur number;
7 stmt varchar2(100);
8 begin
9 stmt := 'select count(*) from scott.emp';
10 cur := dbms_sql.open_cursor;
11 dbms_sql.parse(c => cur,
12 statement => stmt,
13 language_flag => dbms_sql.native,
14 container => ctx);
15 dbms_sql.define_column(cur, 1, cnt);
16 ret := dbms_sql.execute(cur);
17 dbms_output.put_line('execute = '||ret);
18 ret := dbms_sql.fetch_rows(cur);
19 dbms_output.put_line('fetch = '||ret);
20 dbms_output.put_line(stmt||' = '||cnt);
21 dbms_sql.close_cursor(cur);
22 end;
23 /
execute = 0
fetch = 1
select count(*) from scott.emp = 0
PL/SQL procedure successfully completed.
dbms_outputcall, the container name (from ctx), or the count result?