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)
very simple instruction to have in a pl/sql procedure [message #575955] Tue, 29 January 2013 14:58 Go to next message
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!!!
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 Go to previous messageGo to next message
BlackSwan
Messages: 23162
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
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
joy_division
Messages: 4559
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.
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 Go to previous messageGo to next message
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.
Re: very simple instruction to have in a pl/sql procedure [message #575961 is a reply to message #575959] Tue, 29 January 2013 16:02 Go to previous messageGo to next message
cookiemonster
Messages: 11287
Registered: September 2008
Location: Rainy Manchester
Senior Member
what error?
Re: very simple instruction to have in a pl/sql procedure [message #575963 is a reply to message #575961] Tue, 29 January 2013 16:53 Go to previous message
limner
Messages: 9
Registered: January 2013
Location: Rome
Junior Member
oh, i found my answer: i put the sql instruction before the "create or replace" statment in the file that contain the procedure

thanks Wink
Previous Topic: connect to sql developer
Next Topic: Not extracting all of the data
Goto Forum:
  


Current Time: Mon Dec 22 19:01:14 CST 2014

Total time taken to generate the page: 0.08638 seconds