Re: SETting SCAN off from inside PL/SQL

The problem is that when you try to compile the stored procedure from SQL*Plus, it will beg you for a substitution value. For example: create or replace procedure junk is
  x varchar2(30) := 'R &D';
begin
  null;
end;
/
Enter value for d: a
old 2: x varchar2(30) := 'R &D';
new 2: x varchar2(30) := 'R a';

Procedure created.

Which is not what you wanted. You can do one of two things: 1. Change the substitution character from & to something else. You can set it in SQL*PLus for the session (see SET under the SQL*Plus doc). 2. Hide the ampersand with chr(38) the ascii value of '&'. Just concatenate it into your string.
--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.

Kal Khatib <kkhatib_at_cisco.com> wrote in article <359AA25A.4D8D3E1C_at_cisco.com>...
> 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?
>
> Help ever greatly appreciated.
> Kal.
>
>
>
>
Received on Wed Jul 01 1998 - 16:15:40 CDT