Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Native Dynamic SQL in Oracle8i

Re: Native Dynamic SQL in Oracle8i

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 22 Aug 2002 12:36:17 +0400
Message-ID: <ak27qc$mc5$1@babylon.agtel.net>


Use another execute immediate call or dbms_sql to execute it as an anonymous PL/SQL block seems to be the only option. But this will effectively counter the intended use of dynamic sql with bind variables as your call will result in a statement with literals that are bound to another dynamic statement. I think you should resort to dbms_sql initially since you can use bind_variable() calls to bind variables progressively as you need. Native dynamic SQL is only good when number and types on bind variables are known and your statement is not going to be called excessively (execute immediate parses the statement each time it is invoked even though the executed statement was not altered - Oracle can't tell if it was or not so it has to parse on every call).

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Ryan Docena" <ryandocena_at_yahoo.com> wrote in message
news:829a27e7.0208211631.37dc14ca_at_posting.google.com...

> Since Oracle8i's native dynamic sql is in this form:
>
> EXECUTE IMMEDIATE <sql-stmt>
> USING <var-to-bind-1> [...,<var-to-bind-n>]
>
> I've made even the 'USING' clause of the above statement dynamic
> because it isn't known till runtime.
>
> Now that all my dynamic sql (whole EXECUTE IMMEDIATE <sql-stmt>...
> USING <>) is in a variable, how can I invoke it to execute?
>
> Thanks.
Received on Thu Aug 22 2002 - 03:36:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US