Re: Dynamic SQL
From: <mrdjmagnet_at_aol.com>
Date: Mon, 5 Jan 2009 09:58:01 -0800 (PST)
Message-ID: <6c3dd553-d829-40f9-9825-2416ee5c48f6_at_t39g2000prh.googlegroups.com>
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 --
Date: Mon, 5 Jan 2009 09:58:01 -0800 (PST)
Message-ID: <6c3dd553-d829-40f9-9825-2416ee5c48f6_at_t39g2000prh.googlegroups.com>
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/using-bind-variables-in-from-clause-2053722 Received on Mon Jan 05 2009 - 11:58:01 CST