DanBrother的部落格

** Without prompting user for a parameter value in SQLPlus **

By default, "&" has been defined with  (hex 26)

SQL> show define
define "&" (hex 26)

For this way, when you create a stored procedure which constains "&" character in SQL*Plus environment, you'll always be asked to enter a variable value.

For example"

SQL> create procedure sp_test_prompt is
begin
    htp.p('Test1 Test2');
end;
/
輸入 nbsptest2的值: 

To avoid  this troublesome,  you can simply set define off to bypass the prompting value.
>>
SQL> set define off
SQL> show define
define OFF

Now, the procedure can be created successfully without prompting you to enter any value.

SQL> create procedure sp_test_prompt is
begin
    htp.p('Test1 Test2');
end;
/
已建立程序.


This trick is helpful when you need to a multiple of stored procedures in a text file which constains "&" character.