Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SETting SCAN off from inside PL/SQL
A copy of this was sent to Kal Khatib <kkhatib_at_cisco.com>
(if that email address didn't require changing)
On Wed, 01 Jul 1998 13:55:54 -0700, you wrote:
>I have PL/SQL procedures that use dynamic SQL in a package. Many of the
>variables used in dyn SQL might contain the ampersand &. I need to turn
>off variable substituion so that statements take & as a literal. How do
>I do that inside the package/procedure? I'm using an Orable Web
>Application server (OWA), and the procedures are called from a Web
>browser.
>
>
>I need to have SCAN set to off everytime a user points to a procedure in
>the particular package.
>I tried dyn SQL "dbms_sql.parse(c, 'set scan off', ...) etc, but it
>fails.
>
>
>I know I could set scan off in a SQLPLUS session using
>SET SCAN OFF;
>
>what type of command is SET? (it's not DDL, DML, nor DCL) Is it a
>session command? How do I get a procedure pass it to SQL engine
>succesfully?
>
set scan is a sqlplus directive -- not a sql command of any type.
the & and SCAN is a sqlplus thing only.
Once the pl/sql is compiled in the database (using sqlplus with scan off) then you *never* need to set scan off again. Don't worry about & in sqlstmts -- its a macro pre-processing feature of sqlplus ONLY.
once the procedure is in the database and you call if from the web, it'll work.
>Help ever greatly appreciated.
>Kal.
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jul 01 1998 - 20:07:39 CDT