Re: Dynamic SQL

From: ddf <oratune_at_msn.com>
Date: Tue, 6 Jan 2009 06:27:46 -0800 (PST)
Message-ID: <989f063f-2248-48ea-87cb-b3ddabddc6bd_at_k19g2000yqg.googlegroups.com>

On Jan 5, 11:58 am, mrdjmag..._at_aol.com wrote:
> On Jan 5, 11:43 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 5, 11:31 am, mrdjmag..._at_aol.com wrote:
>
> > > Hi,
>
> > > We're rewriting a lot of code in order to increase our hit count in
> > > the library cache.  One thing we are running into is where dynamic
> > > table names are used.  Here is a quick sample of what someone wrote::
>
> > > LOOKUP TABLE
> > > ------------------------
> > > ID NUM
> > > COLUMN NAME
> > > TABLE NAME
>
> > > PROCEDURE x (p_id_num NUMBER, p_code VARCHAR2) IS
> > > BEGIN
> > >   SELECT column_name, table_name INTO v_column, v_table WHERE id_num =
> > > p_id_num;
>
> > >   v_select := 'BEGIN
> > >                    SELECT count(*) INTO :v_num_row FROM ' || v_table
> > > || ' WHERE ' || v_column || ' = :p_code;
> > >                    END';
>
> > >   EXECUTE IMMEDIATE v_select USING OUT p_id_num, p_code;
> > > .
> > > .
> > > END;
>
> > > Now, that is a piece of junk, but is what it is.  Can that statement
> > > be re-written to be dynamic SQL so it can be reused and kept in the
> > > cache?  The problem being that the FROM table may not be the same.  I
> > > figured with bind variables this would be possible.....
>
> > Depending on what purpose the routine serves it may not be worth
> > worrying about such as if the routine was part of a DBA job to record
> > all tables and the number of rows in each then since it would run once
> > per day or less.  In such a case it can go to the bottom of the list.
>
> > On the hand if the application makes heavy use of code like this then
> > you can add a using clause to get some reuse when the same table is
> > used but you probably need to revisit the design behind requiring such
> > a routine.
>
> > HTH -- Mark D Powell --
>
> I had read this post, but I do not believe him.  I'm sure there is a
> way, a bit of trial and trial and trial with errors will eventually
> give the answer:
>
> http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/...- Hide quoted text -
>
> - Show quoted text -

I'd believe him. Here's an example of what you think will work which won't:

SQL> create table lookup_table(

  2          table_name varchar2(35),
  3          col_name varchar2(35)

  4 );

Table created.

SQL>
SQL> insert into lookup_table
  2 values('EMP','EMPNO');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select table_name, col_name
  2 from lookup_table;

TABLE_NAME                          COL_NAME
-----------------------------------
-----------------------------------
EMP                                 EMPNO

SQL>
SQL> declare

  2          cursor get_tabs is
  3          select table_name, col_name
  4          from lookup_table;
  5
  6          sqltxt varchar2(4000);
  7
  8          v_rec_ct number;

  9
 10 begin
 11 for tabrec in get_tabs loop  12

 13                  dbms_output.put_line(tabrec.table_name||'   '||

tabrec.col_name);
 14

 15                  sqltxt:='select count(*) from :1 where :2 is not

null';
 16

 17                  execute immediate sqltxt into v_rec_ct using

tabrec.table_name, tabrec.col_name;
 18
 19 end loop;
 20
 21 dbms_output.put_line(v_rec_ct);  22
 23 end;
 24 /
EMP EMPNO
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 17

Notice the error generated; bind variables in the FROM clause are not evaluated as such, so the :1 is taken, literally, as a table name and such a table does not exist. Changing the code to embed the table_name variable in the text string causes the dynamic query to work:

SQL> declare

  2          cursor get_tabs is
  3          select table_name, col_name
  4          from lookup_table;
  5
  6          sqltxt varchar2(4000);
  7
  8          v_rec_ct number;

  9
 10 begin
 11 for tabrec in get_tabs loop  12

 13                  dbms_output.put_line(tabrec.table_name||'   '||

tabrec.col_name);
 14

 15                  sqltxt:='select count(*) from '||

tabrec.table_name||' where :1 is not null';  16

 17                  execute immediate sqltxt into v_rec_ct using

tabrec.col_name;
 18
 19 end loop;
 20
 21 dbms_output.put_line(v_rec_ct);  22
 23 end;
 24 /
EMP EMPNO
17

PL/SQL procedure successfully completed.

SQL> You can pass column names into a bind variable in a WHERE clause, but you cannot use a bind variable in the FROM clause.

David Fitzjarrell Received on Tue Jan 06 2009 - 08:27:46 CST