Dynamic SQL Reality Check

From: Michael42 <melliott42_at_yahoo.com>
Date: 14 Jan 2007 06:59:28 -0800
Message-ID: <1168786768.277235.279640@v45g2000cwv.googlegroups.com>

Hello,

In getting deeper with PL/SQL using Oracle 10g R2 I have a need to execute SQL statements that are not static. In wading through the internet common Oracle docs sites it seems I must use "Oracle Native Dynamic SQL".

Please tell me I am wrong. In comparison to the years of using SQL with other SQL engines this is like a real bad LCD trip ...R-E-A-L B-A-D. :-)

At the crux, I am using a package and have a function I wish to pass a completely dynamic (yet simple) SQL string where the only thing that is a constant is the table name.

A psuedo code example function specs:

FUNCTION qryTable (

   vTable          IN varchar2,
   vFields         IN varchar2,
   vWhere         IN varchar2

) RETURN varchar2 IS

sSQL := 'SELECT ' || vFields || ' FROM ' || vTable || ' WHERE ' || vWhere ;
-- Create a cursor from sSQL next ...

What is the easiest way to do this that will work in the structure of a Package via a function call?

Thanks for your advice,

Michael Received on Sun Jan 14 2007 - 08:59:28 CST