Thanks for the question, Rajeshwaran.

Asked: August 01, 2025 - 9:46 am UTC

Last updated: August 04, 2025 - 2:19 pm UTC

Version: 23.8

Viewed 1000+ times

You Asked

Team - this is on 23ai (23.8) can someone help me to understand why do i get this error ?
any schema with DB_Developer_role granted can reproduce this issue.

demo@FREEPDB1> @printtbl ' select * from dual '
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 26

declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 10
ORA-06512: at line 55
ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 26
Help: https://docs.oracle.com/error-help/db/ora-29471/

the script for printtbl is available here - https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:4845523000346615725

Here are the session level privileges and roles i have.

demo@FREEPDB1> select * from session_roles;

ROLE
--------------------------
RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
DB_DEVELOPER_ROLE
SODA_APP
CTXAPP

6 rows selected.

demo@FREEPDB1> select * from session_privs;

PRIVILEGE
-------------------------
CREATE DOMAIN
CREATE MLE
CREATE PROPERTY GRAPH
CREATE ANALYTIC VIEW
CREATE HIERARCHY
CREATE ATTRIBUTE DIMENSION
EXECUTE DYNAMIC MLE
CREATE CUBE BUILD PROCESS
CREATE CUBE
CREATE CUBE DIMENSION
CREATE MINING MODEL
CREATE JOB
DEBUG CONNECT SESSION
ON COMMIT REFRESH
CREATE DIMENSION
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE MATERIALIZED VIEW
CREATE TRIGGER
CREATE PROCEDURE
FORCE TRANSACTION
CREATE SEQUENCE
CREATE VIEW
CREATE SYNONYM
CREATE CLUSTER
CREATE TABLE
CREATE SESSION

28 rows selected.

demo@FREEPDB1>

and Connor said...

I get this on mine

SQL> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04


SQL> show user
USER is "SCOTT"
SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
CONNECT
RESOURCE
SODA_APP

SQL> !cat /tmp/x.sql
set serverout on size 999999
declare
p_query varchar2(32767) := 'select * from dept';

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    n number := 0;
  procedure p(msg varchar2) is
    l varchar2(4000) := msg;
  begin
    while length(l) > 0 loop
      dbms_output.put_line(substr(l,1,80));
      l := substr(l,81);
    end loop;
  end;
begin
    execute immediate
    'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            p( rpad( l_descTbl(i).col_name, 30 )
              || ': ' || 
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
        n := n + 1;
    end loop;
    if n = 0 then
      dbms_output.put_line( chr(10)||'No data found '||chr(10) );
    end if;
end;
/



SQL> @/tmp/x.sql
DEPTNO         : 10
DNAME         : ACCOUNTING
LOC         : NEW YORK
-----------------
DEPTNO         : 20
DNAME         : RESEARCH
LOC         : DALLAS
-----------------
DEPTNO         : 30
DNAME         : SALES
LOC         : CHICAGO
-----------------
DEPTNO         : 40
DNAME         : OPERATIONS
LOC         : BOSTON
-----------------

PL/SQL procedure successfully completed.

Rating

(4 ratings)

Comments

Passing the Query string as Input parameters...

Rajeshwaran Jeyabal, August 04, 2025 - 7:24 am UTC

when passing the query string as input parameter it errors - like this , any reason why ?

demo@FREEPDB1> conn sys/oracle@freepdb1 as sysdba
Connected.
sys@FREEPDB1>
sys@FREEPDB1> grant db_developer_role to demo_user identified by demo_user;

Grant succeeded.

sys@FREEPDB1> conn demo_user/demo_user@freepdb1
Connected.
demo-user@FREEPDB1>
demo-user@FREEPDB1>
demo-user@FREEPDB1> select banner_full from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04


demo-user@FREEPDB1> declare
  2  p_query varchar2(32767) := 'select * from dual';
  3
  4      l_theCursor     integer default dbms_sql.open_cursor;
  5      l_columnValue   varchar2(4000);
  6      l_status        integer;
  7      l_descTbl       dbms_sql.desc_tab;
  8      l_colCnt        number;
  9      n number := 0;
 10    procedure p(msg varchar2) is
 11      l varchar2(4000) := msg;
 12    begin
 13      while length(l) > 0 loop
 14        dbms_output.put_line(substr(l,1,80));
 15        l := substr(l,81);
 16      end loop;
 17    end;
 18  begin
 19      execute immediate
 20      'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
 21
 22      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 23      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 24
 25      for i in 1 .. l_colCnt loop
 26          dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
 27      end loop;
 28
 29      l_status := dbms_sql.execute(l_theCursor);
 30
 31      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 32          for i in 1 .. l_colCnt loop
 33              dbms_sql.column_value( l_theCursor, i, l_columnValue );
 34              p( rpad( l_descTbl(i).col_name, 30 )
 35                || ': ' ||
 36                l_columnValue );
 37          end loop;
 38          dbms_output.put_line( '-----------------' );
 39          n := n + 1;
 40      end loop;
 41      if n = 0 then
 42        dbms_output.put_line( chr(10)||'No data found '||chr(10) );
 43      end if;
 44  end;
 45  /
DUMMY                         : X
-----------------

PL/SQL procedure successfully completed.

demo-user@FREEPDB1> @printtbl ' select * from dual '
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 26

declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 10
ORA-06512: at line 55
ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 26
Help: https://docs.oracle.com/error-help/db/ora-29471/


demo-user@FREEPDB1>

August 04, 2025 - 8:18 am UTC

SQL> !cat /tmp/x1.sql
set serverout on size 999999
declare
p_query varchar2(32767) := '&1';

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    n number := 0;
  procedure p(msg varchar2) is
    l varchar2(4000) := msg;
  begin
    while length(l) > 0 loop
      dbms_output.put_line(substr(l,1,80));
      l := substr(l,81);
    end loop;
  end;
begin
    execute immediate
    'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            p( rpad( l_descTbl(i).col_name, 30 )
              || ': ' || 
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
        n := n + 1;
    end loop;
    if n = 0 then
      dbms_output.put_line( chr(10)||'No data found '||chr(10) );
    end if;
end;
/



[oracle@vbox tmp]$ sqlplus scott/tiger@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Mon Aug 4 16:13:59 2025
Version 23.8.0.25.04

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Mon Aug 04 2025 12:39:29 +08:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04

SQL> @/tmp/x1.sql "select * from dept"
old   2: p_query varchar2(32767) := '&1';
new   2: p_query varchar2(32767) := 'select * from dept';
DEPTNO         : 10
DNAME         : ACCOUNTING
LOC         : NEW YORK
-----------------
DEPTNO         : 20
DNAME         : RESEARCH
LOC         : DALLAS
-----------------
DEPTNO         : 30
DNAME         : SALES
LOC         : CHICAGO
-----------------
DEPTNO         : 40
DNAME         : OPERATIONS
LOC         : BOSTON
-----------------

PL/SQL procedure successfully completed.

SQL> @/tmp/x1.sql 'select * from dept'
old   2: p_query varchar2(32767) := '&1';
new   2: p_query varchar2(32767) := 'select * from dept';
DEPTNO         : 10
DNAME         : ACCOUNTING
LOC         : NEW YORK
-----------------
DEPTNO         : 20
DNAME         : RESEARCH
LOC         : DALLAS
-----------------
DEPTNO         : 30
DNAME         : SALES
LOC         : CHICAGO
-----------------
DEPTNO         : 40
DNAME         : OPERATIONS
LOC         : BOSTON
-----------------

PL/SQL procedure successfully completed.

SQL> @/tmp/x1.sql '  select * from dept  '
old   2: p_query varchar2(32767) := '&1';
new   2: p_query varchar2(32767) := '  select * from dept  ';
DEPTNO         : 10
DNAME         : ACCOUNTING
LOC         : NEW YORK
-----------------
DEPTNO         : 20
DNAME         : RESEARCH
LOC         : DALLAS
-----------------
DEPTNO         : 30
DNAME         : SALES
LOC         : CHICAGO
-----------------
DEPTNO         : 40
DNAME         : OPERATIONS
LOC         : BOSTON
-----------------

PL/SQL procedure successfully completed.

and from scratch

[oracle@vbox tmp]$ sqlplus system/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Mon Aug 4 16:16:54 2025
Version 23.8.0.25.04

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Last Successful login time: Mon Aug 04 2025 12:40:29 +08:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.8.0.25.04

SQL> grant db_developer_role to demo_user identified by demo_user;

Grant succeeded.

SQL> conn demo_user/demo_user@freepdb1
Connected.
SQL> @/tmp/x1 "select * from dual"
old   2: p_query varchar2(32767) := '&1';
new   2: p_query varchar2(32767) := 'select * from dual';
DUMMY         : X
-----------------

PL/SQL procedure successfully completed.

can you try the printtbl from the above link ?

Rajeshwaran Jeyabal, August 04, 2025 - 8:31 am UTC

Can you make use of the Printtbl script from the above link, Here is my code and it errored like this.

C:\Users\Rajeshwaran Jeyabal>sqlplus /nolog

SQL*Plus: Release 23.0.0.0.0 - Production on Mon Aug 4 14:00:25 2025
Version 23.8.0.25.04

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

idle> conn demo/demo@freepdb1
Connected.
demo@FREEPDB1> @printtbl ' select * from dual '
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 26

declare
*
ERROR at line 1:
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 10
ORA-06512: at line 55
ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 26
Help: https://docs.oracle.com/error-help/db/ora-29471/


demo@FREEPDB1> $ type printtbl.sql

set verify off;

declare
        l_cursor number;
        l_status number;
        l_column_value varchar2(4000);
        l_desc_tab dbms_sql.desc_tab;
        l_column_count number;

        procedure execute_immediate(p_sql varchar2) as
        begin
                dbms_sql.parse(l_cursor,p_sql,dbms_sql.native);
                l_status := dbms_sql.execute(l_cursor);
        end;

        procedure p(p_str in varchar2) as
                l_str long := p_str;
        begin
                loop
                        exit when l_str is null;
                        dbms_output.put_line ( substr(l_str,1,250) );
                        l_str := substr(l_str,251);
                end loop;
        end;
begin
        l_cursor := dbms_sql.open_cursor(1);
        execute_immediate(q'|alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'|');
        dbms_sql.parse(l_cursor, replace('&1','"',''''),dbms_sql.native);
        dbms_sql.describe_columns(l_cursor,l_column_count,l_desc_tab);

        for i in 1..l_column_count
        loop
                dbms_sql.define_column(l_cursor,i,l_column_value,4000);
        end loop;

        l_status := dbms_sql.execute(l_cursor);

        while ( dbms_sql.fetch_rows(l_cursor) > 0 )
        loop
                for i in 1..l_column_count
                loop
                        dbms_sql.column_value(l_cursor,i,l_column_value);
                        p ( rpad( l_desc_tab(i).col_name,30)||': "'||l_column_value||'"');
                end loop;
                 dbms_output.put_line( '-----------------' );
        end loop;

        execute_immediate(q'|alter session set nls_date_format='dd-mon-yy'|');
        dbms_sql.close_cursor(l_cursor);

exception
                when others then
                        if dbms_sql.is_open(l_cursor) then
                                dbms_sql.close_cursor(l_cursor);
                        end if;
                        dbms_output.put_line ( dbms_utility.format_error_backtrace);
                        execute_immediate(q'|alter session set nls_date_format='dd-mon-yy'|');
                        raise;
end;
/

set verify on;


demo@FREEPDB1>

August 04, 2025 - 2:19 pm UTC

I would replace "execute_immediate" with "execute immediate" and do away with that proc.

Looks to me like the cursor numbering is getting mixed up in some way

Thanks it helped and Fixed.

Rajeshwaran jeyabal, August 04, 2025 - 3:02 pm UTC

Thanks Connor, that replacement of Execute_immediate with Execute immediate helped.

SQL*Plus: Release 23.0.0.0.0 - Production on Mon Aug 4 20:27:19 2025
Version 23.8.0.25.04

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

idle> conn demo/demo@freepdb1
Connected.
demo@FREEPDB1> show define
define OFF
demo@FREEPDB1> @printtbl " select * from dual "
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 20

declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 50
ORA-06512: at "SYS.DBMS_SQL", line 1251
ORA-06512: at line 20

Also , I got SET DEFINE OFF in my login.sql script, once it set it to ON, it worked.

demo@FREEPDB1> set define on
demo@FREEPDB1> @printtbl " select * from dual "
DUMMY                         : "X"
-----------------

PL/SQL procedure successfully completed.

demo@FREEPDB1>

Possible for you to update this Original link with the replacement of execute_immediate for the latest version of database?

https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:4845523000346615725

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Passing the Query string as Input parameters...

can you try the printtbl from the above link ?

Thanks it helped and Fixed.

More to Explore

PL/SQL demos

PL/SQL docs

ORA-29471: DBMS_SQL access denied while running Printtbl script

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Passing the Query string as Input parameters...

can you try the printtbl from the above link ?

Thanks it helped and Fixed.

More to Explore

PL/SQL demos

PL/SQL docs