OraFAQ Forum: Client Tools » very simple instruction to have in a pl/sql procedure

Home » SQL & PL/SQL » Client Tools » very simple instruction to have in a pl/sql procedure (Oracle 11g R2 64bit version - Windows 7 x64 - Intel machine)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

very simple instruction to have in a pl/sql procedure [message #575955]

Tue, 29 January 2013 14:58

limner
Messages: 9
Registered: January 2013
Location: Rome

Junior Member

Hi to all

do anyone know how to insert this instruction in a oracle pl/sql stored procedure?

set define off

i have to use this because i have this character & in an url to call in pl/sql and i prefer to do this in a procedure not to write every time in sql when i call the procedure

thanks!!!

Report message to a moderator

Re: very simple instruction to have in a pl/sql procedure [message #575956 is a reply to message #575955]

Tue, 29 January 2013 15:08

BlackSwan
Messages: 20101
Registered: January 2009

Senior Member

>set define off

Above is a sqlplus command & is NOT valid for PL/SQL.

however it could be placed inside the glogin.sql file; which gets invoked when sqlplus starts

Report message to a moderator

Re: very simple instruction to have in a pl/sql procedure [message #575957 is a reply to message #575956]

Tue, 29 January 2013 15:14

limner
Messages: 9
Registered: January 2013
Location: Rome

Junior Member

yes i know that this is sql and not pl/sql and i also know that i could intert this in the glogin, but i would that this sql instruction would work only for one procedure.

I tried with "execute immediate" but with no result..any other ideas?

Report message to a moderator

Re: very simple instruction to have in a pl/sql procedure [message #575958 is a reply to message #575955]

Tue, 29 January 2013 15:15

joy_division
Messages: 4266
Registered: February 2005
Location: East Coast USA

Senior Member

A stored procedure or an anonymous block?
If you set define off before creating the stored procedure then that is all you have to do.

SQL> set define off
SQL> create or replace procedure foo1 is
  2    foo varchar2(80);
  3  begin
  4    foo:= 'hello & goodbye';
  5    dbms_output.put_line(foo);
  6  end;
  7  /

Procedure created.

SQL> exec foo1
hello & goodbye

PL/SQL procedure successfully completed.

Report message to a moderator

Re: very simple instruction to have in a pl/sql procedure [message #575959 is a reply to message #575958]

Tue, 29 January 2013 15:22

limner
Messages: 9
Registered: January 2013
Location: Rome

Junior Member

i have already created my stored procedure: it is in a file that i loader in the database using the @procedure-filename.sql

but i i try to launch the procedure, after loaded, without setting define off i get an error.

Report message to a moderator