Re: PL/SQL-Variables in SQL string

In article <37B11CC8.2D789382_at_gmx.de>, Ralf Steppacher <junior_at_fh-konstanz.de> wrote:
>I tried to implement a package that takes table field names and search
>criteria as parameters. Depending on the field names and the given
>content pattern it should delete records from the table.
>But it seems that PL/SQL can't handle variables in its SQL statements.
>At least not for a field name in the WHERE clause.
>I tried to substitute the where clause of a parameterized cursor, but I
>was always told that my parameter has the wrong type (I tried both CHAR
>and VARCHAR2).
>Simply writing the field name variables into the SQL statement did
>compile but would not delete anything. It seems to me that the variables
>were not interpreted.
>
>My question is: How do I pass field names to a stored procedure and use
>them in a SELECT PK_FIELD FROM TABLE WHERE field_varable =
>criteria_variable statement?

You have to use dynamic SQL to do that. Get a copy of Thomas Kyte's execute_immediate procedure, and go from there. Look it up on Deja.com. Here's one of his articles:

    http://x44.deja.com/[ST_rn=ps]/getdoc.xp?AN=492085611

Or just do a power search on Deja.com, using tkyte* as the author and execute_immediate as the search string.

Steve Cosner Received on Wed Aug 11 1999 - 17:25:38 CEST