Re: Dynamic SQL
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 6 Jan 2009 13:42:51 -0800 (PST)
Message-ID: <ea074ada-8a62-4b73-94a3-82f76d16d34b_at_35g2000pry.googlegroups.com>
On Jan 6, 9:27 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 6 Jan 2009 13:42:51 -0800 (PST)
Message-ID: <ea074ada-8a62-4b73-94a3-82f76d16d34b_at_35g2000pry.googlegroups.com>
On Jan 6, 9:27 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
>> you can add a using clause to get some reuse when the same table is used <<
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 -- Received on Tue Jan 06 2009 - 15:42:51 CST