Dynamic SQL

From: <mrdjmagnet_at_aol.com>
Date: Mon, 5 Jan 2009 08:31:45 -0800 (PST)
Message-ID: <2685a1fc-1d61-45df-85dd-b4eb5b9d48ff_at_s1g2000prg.googlegroups.com>

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..... Received on Mon Jan 05 2009 - 10:31:45 CST