Home » SQL & PL/SQL » SQL & PL/SQL » print a message
print a message [message #582346] Wed, 17 April 2013 05:54 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello friends,

i want to print a message just before asking for input to a bind variable

SQL> SET serveroutput on;
SQL> DECLARE
  2  pname varchar2(20);
  3  BEGIN
  4     DBMS_OUTPUT.put_line ('hello world');
  5
  6     SELECT product_name
  7       INTO pname
  8       FROM product
  9      WHERE ID = #
 10
 11     DBMS_OUTPUT.put_line ('Product is ' || pname);
 12  EXCEPTION
 13     WHEN OTHERS
 14     THEN
 15        ROLLBACK;
 16        DBMS_OUTPUT.put_line ('Exception');
 17        DBMS_OUTPUT.put_line ('EXCEPTION MESSAGE =' || SQLERRM);
 18        DBMS_OUTPUT.put_line ('EXCEPTION CODE =' || SQLCODE);
 19  END;
 20  /

Enter value for num: 1
old   9:     WHERE ID = #
new   9:     WHERE ID = 1;
hello world
Product is PC

PL/SQL procedure successfully completed.

SQL>






but i want to print 'hello world' before asking value for bind variable like:


hello world
Enter value for num: 1
old 9: WHERE ID = #
new 9: WHERE ID = 1;


then how to achive it?

thanks in advance.............
Re: print a message [message #582349 is a reply to message #582346] Wed, 17 April 2013 06:42 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
Move the hello word to a seperate script.

For any given block of code sqlplus fills in the substitution variables before running any code.
Re: print a message [message #582355 is a reply to message #582349] Wed, 17 April 2013 07:17 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,

is there no way in same pl/sql block?



thanks.......
Re: print a message [message #582356 is a reply to message #582355] Wed, 17 April 2013 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59747
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

You have to understand how it works.
"&something" is a SQL*Plus variable, this SQL*Plus variable must have a value to be executed in PL/SQL engine, so SQL*Plus has to ask you for the value BEFORE it sends the block to PL/SQL.

Regards
Michel
Re: print a message [message #582365 is a reply to message #582356] Wed, 17 April 2013 09:04 Go to previous message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
ok. i have understand its working.



Quote:

"&something" is a SQL*Plus variable, this SQL*Plus variable must have a value to be executed in PL/SQL engine,



thanks alot for the valuable info sir.


thanks again.......
Previous Topic: Formatting Output into multiple columns
Next Topic: Query help
Goto Forum:
  


Current Time: Sat Nov 22 09:56:12 CST 2014

Total time taken to generate the page: 0.04794 seconds