Re: Dynamic SQL

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 5 Jan 2009 09:43:05 -0800 (PST)
Message-ID: <a73be191-2ce3-46ec-bfa0-feb6ec1ae74c_at_r37g2000prr.googlegroups.com>

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 -- Received on Mon Jan 05 2009 - 11:43:05 CST