Thanks for the question.

Asked: February 09, 2017 - 11:57 am UTC

Last updated: March 31, 2017 - 4:29 am UTC

Version: latest

Viewed 1000+ times

You Asked

simply retrieving a table using table name dynamically

DECLARE
TABL_NAME VARCHAR2(255):='MY_DETAILS';
STRNG VARCHAR2(255);
begin
STRNG :='SELECT * FROM' || TABL_NAME;
DBMS_SQL.PARSE (STRNG);
END;

im new to sql please help why this simple dynamic query is not running.
i also used this via immediate execute but its showing from keyword error

and Connor said...

Here's an example of taking in *any* query, parsing it, fetching the data and printing it out.


SQL> set serverout on size 999999
SQL> set verify off
SQL> declare
  2    p_query varchar2(32767) := 'select * from scott.emp';
  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  /
EMPNO                         : 7369
ENAME                         : SMITH
JOB                           : CLERK
MGR                           : 7902
HIREDATE                      : 17-dec-1980 00:00:00
SAL                           : 800
COMM                          :
DEPTNO                        : 20
-----------------
EMPNO                         : 7499
ENAME                         : ALLEN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 20-feb-1981 00:00:00
SAL                           : 1600
COMM                          : 300
DEPTNO                        : 30
-----------------
EMPNO                         : 7521
ENAME                         : WARD
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 22-feb-1981 00:00:00
SAL                           : 1250
COMM                          : 500
DEPTNO                        : 30
-----------------
EMPNO                         : 7566
ENAME                         : JONES
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 02-apr-1981 00:00:00
SAL                           : 2975
COMM                          :
DEPTNO                        : 20
-----------------
EMPNO                         : 7654
ENAME                         : MARTIN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 28-sep-1981 00:00:00
SAL                           : 1250
COMM                          : 1400
DEPTNO                        : 30
-----------------
EMPNO                         : 7698
ENAME                         : BLAKE
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 01-may-1981 00:00:00
SAL                           : 2850
COMM                          :
DEPTNO                        : 30
-----------------
EMPNO                         : 7782
ENAME                         : CLARK
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 09-jun-1981 00:00:00
SAL                           : 2450
COMM                          :
DEPTNO                        : 10
-----------------
EMPNO                         : 7788
ENAME                         : SCOTT
JOB                           : ANALYST
MGR                           : 7566
HIREDATE                      : 19-apr-1987 00:00:00
SAL                           : 3000
COMM                          :
DEPTNO                        : 20
-----------------
EMPNO                         : 7839
ENAME                         : KING
JOB                           : PRESIDENT
MGR                           :
HIREDATE                      : 17-nov-1981 00:00:00
SAL                           : 5000
COMM                          :
DEPTNO                        : 10
-----------------
EMPNO                         : 7844
ENAME                         : TURNER
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 08-sep-1981 00:00:00
SAL                           : 1500
COMM                          : 0
DEPTNO                        : 30
-----------------
EMPNO                         : 7876
ENAME                         : ADAMS
JOB                           : CLERK
MGR                           : 7788
HIREDATE                      : 23-may-1987 00:00:00
SAL                           : 1100
COMM                          :
DEPTNO                        : 20
-----------------
EMPNO                         : 7900
ENAME                         : JAMES
JOB                           : CLERK
MGR                           : 7698
HIREDATE                      : 03-dec-1981 00:00:00
SAL                           : 950
COMM                          :
DEPTNO                        : 30
-----------------
EMPNO                         : 7902
ENAME                         : FORD
JOB                           : ANALYST
MGR                           : 7566
HIREDATE                      : 03-dec-1981 00:00:00
SAL                           : 3000
COMM                          :
DEPTNO                        : 20
-----------------
EMPNO                         : 7934
ENAME                         : MILLER
JOB                           : CLERK
MGR                           : 7782
HIREDATE                      : 23-jan-1982 00:00:00
SAL                           : 1300
COMM                          :
DEPTNO                        : 10
-----------------

PL/SQL procedure successfully completed.

SQL>
SQL>

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

OVERWHELMED

sid, February 10, 2017 - 8:17 am UTC

thanks Connor but as i said earlier i am new to SQL, PL/SQL so although i somewhat got whats happening in your code but still not able to understand properly how package dbms_sql is used and where and when can we use things like open cursor or row fetch so can you please demonstrate the example with a little easy code using dbms_sql

February 10, 2017 - 5:35 pm UTC

Search this site for "dbms_sql" and you'll find lots of examples, some trivial, some not so :-)

And nice examples in the docs too

http://docs.oracle.com/database/122/ARPLS/DBMS_SQL.htm#ARPLS68184

on 12c

Rajeshwaran, Jeyabal, February 10, 2017 - 1:55 pm UTC

Since the version is mentioned as "latest", Hope that you will be on 12c

you can take the advantage of Implicit Result sets feature. (so the above code, will become like this)

demo@ORA12C> declare
  2     l_sql long;
  3     l_cursor int;
  4     l_rows int;
  5  begin
  6     l_sql :=' select * from dept';
  7     l_cursor := dbms_sql.open_cursor;
  8     dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
  9     l_rows := dbms_sql.execute(l_cursor);
 10     dbms_sql.return_result(l_cursor);
 11  end;
 12  /

PL/SQL procedure successfully completed.

ResultSet #1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

demo@ORA12C>

if you would like to understand more on DBMS_SQL API, you could refer to the book "Expert One on One Oracle" book, Chapter#16 - Dynamic SQL - Tom Kyte goes about DBMS_SQL API in detail.

hmm

GH.., February 10, 2017 - 4:56 pm UTC

select * from v$version;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

declare
l_sql long;
l_cursor int;
l_rows int;
begin
l_sql :=' select * from t';
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
l_rows := dbms_sql.execute(l_cursor);
dbms_sql.return_result(l_cursor);
end;
/

ORA-29481: Implicit results cannot be returned to client.
ORA-06512: on "SYS.DBMS_SQL", line 2785
ORA-06512: on ligne 10
29481. 00000 - "Implicit results cannot be returned to client."
*Cause: An attempt was made to return implicit results to an older version
client that is not capable of receiving these implicit results.
*Action: Upgrade the Oracle Database Client software.

February 10, 2017 - 5:17 pm UTC

Are you using the 12c client as well ? This from my 12c sqlplus

SQL> declare
  2  l_sql long;
  3  l_cursor int;
  4  l_rows int;
  5  begin
  6  l_sql :=' select * from scott.emp';
  7  l_cursor := dbms_sql.open_cursor;
  8  dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
  9  l_rows := dbms_sql.execute(l_cursor);
 10  dbms_sql.return_result(l_cursor);
 11  end;
 12  /

PL/SQL procedure successfully completed.

ResultSet #1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Always look at what you're trying to execute

Andrew Sayer, February 11, 2017 - 1:22 am UTC

The second rule of dynamic SQL is to always make sure you know what you're executing. A quick and dirty dbms_output.put_line(strng); before the dbms_sql call will do the trick here.

In this case the SQL evaluates to
SELECT * FROMMY_DETAILS
Which doesn't parse (because there's no FROM keyword - like your error stated) , and that's because there's no space between the FROM and TABL_NAME that's getting concatenated to the string. So the fix is to add the space:
STRNG :='SELECT * FROM ' || TABL_NAME;

(If you're wondering what the first rule of dynamic SQL is - only use it when absolutely necessary and you've made sure it's safe from SQL injection)

February 11, 2017 - 1:56 am UTC

Agreed.

And the thing that precedes that is:

"Do I *really* need dynamic SQL for this"

Need the Executed Query to log

Senthil, March 29, 2017 - 11:30 pm UTC

Hi,

Thanks for the detailed how-to. It helped me to build my complex dynamic query.

I have one other related requirement.

I am using DBMS_SQL to execute a very large query with multiple bind variables. I need the query (with values) to log for support team in their debugging exercise. I am able to get the query with bind variables but not the query with actual values used in place of bind variables during execution.

eg.,

SELECT emp_id, emp_name from employee where emp_id = :b_id

In the log I need to store the query with value as below:

SELECT emp_id, emp_name from employee where emp_id = 1001
(when I pass :b_id = 1001)

I am able to achieve this with EXECUTE IMMEDIATE. But I need to do the same with DBMS_SQL.

Can you please help me in this?

Thanks!

March 31, 2017 - 4:29 am UTC

I wouldn't do it that way, because

select * from EMP where empno = :b1

and

select * from EMP where empno = 100

are *different* queries. They'll run differently.

Easy would be just log your binds as a structure, json.

So you'd end up with an audit trail like:

SQL: select * from emp where sal > :b1 and job = :b2
BINDS:
{ binds:
    {  "b1" : "10"
       "b2" : "SALES"
    }
}

Thank you!!!

Senthil, April 03, 2017 - 6:16 am UTC

Will log the information as per your suggestion

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer