plsql select return value using dbms_sql 12c

Question

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.

What are you expecting to see from your last dbms_output call, the container name (from ctx), or the count result? — Alex Poole
– Alex Poole, Commented Oct 20, 2016 at 15:36
Oh, I was trying various things; I updated the post to revert the define_columns to just the 1 column to the cnt variable. I was expecting it to be 14. — slashlos
– slashlos, Commented Oct 20, 2016 at 15:56

Alex Poole · Accepted Answer · 2016-10-20 17:19:37Z

3

You need to get the actual column value from the result set with:

dbms_sql.column_value(cur, 1, cnt);

From the documentation:

COLUMN_VALUE Procedure
This procedure returns the value of the cursor element for a given position in a given cursor. This procedure is used to access the data fetched by calling FETCH_ROWS.

So your code would be:

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);
  -- get the actual value from the column into its variable
  dbms_sql.column_value(cur, 1, cnt);
  dbms_output.put_line(stmt||' = '||cnt);
  dbms_sql.close_cursor(cur);
end;
/

If I do that (admittedly in 11g, so without the container argument - which doesn't seem to really be relevant to your actual issue) and against the hr.employees table instead, I see:

execute = 0
  fetch = 1
select count(*) from employees = 107

PL/SQL procedure successfully completed.

SQL> select count(*) from employees;

  COUNT(*)
----------
       107

edited Oct 20, 2016 at 17:19

answered Oct 20, 2016 at 15:59

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

slashlos Over a year ago

Ah, the code originally posted was wrong; it has since been updated. Your version resembles that. Eventually this would be the genesis of a routine to receive the statement and container context. So, it's 12c I'm interested in getting help thanks.

Alex Poole Over a year ago

@slashlos - but the container has nothing to do with the count issue, as far as I can see; your amended code is still missing the column_value clause?

Alex Poole Over a year ago

Are you also expecting something to update ctx with the actual container it ran against? I can't see anything suggesting that should happen, and it's an IN parameter...

slashlos Over a year ago

ctx is a future; yes the column_value was what I was missing!

Collectives™ on Stack Overflow

plsql select return value using dbms_sql 12c

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related