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

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.....

On Jan 5, 11:31*am, mrdjmag... (AT) aol (DOT) com wrote:
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 --

On Jan 5, 11:43*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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/t...clause-2053722

On Jan 5, 9:58*am, mrdjmag... (AT) aol (DOT) com wrote:
It depends on the proportion of unique v_table. If most every one is
"EMPLOYEES" then you'll get a lot of reuse. If everyone is different
(or it is rarely used) you will get a lot of hard parsing. Isn't it
really that simple?

jg
--
@home.com is bogus.
http://chris.pirillo.com/2009/01/03/...ng-on-twitter/

On Jan 5, 11:58*am, mrdjmag... (AT) aol (DOT) com wrote:
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

On Jan 6, 9:27*am, ddf <orat... (AT) msn (DOT) com> wrote:
I think some readers are confused by my wording. The using clause
only applies to where clause bind variables. I thought that was
understoon based on the nature of the using clause itself. My
mistake.

Syntax diagram
file:///D:/oracle/Doc%2010gR2/B19306_01/appdev.102/b14261/executeimmediate_statement.htm#sthref2687

Dynamic SQL examples
file:///D:/oracle/Doc%2010gR2/B19306_01/appdev.102/b14261/dynamic.htm#sthref1562

HTH -- Mark D Powell --