I've created a simple procedure. In this procedure i want to output some data. However where ever i put set serveroutput on
it says
Error(26,5): PLS-00103: Encountered the symbol "SERVEROUTPUT" when expecting one of the following: . ( ) , * @ % & = - + < / > at in is mod remainder not rem => <> or != or ~= >= <= <> and or like like2 like4 likec as between || multiset member submultiset
It doesn't matter where i put it, it keeps saying it.
create or replace PROCEDURE discount
is --- signature
BEGIN --- executable part
update dvd set me_our_price = me_our_price*0.90 WHERE me_release_year = 2011;
update dvd set me_our_price = me_our_price*0.80 WHERE me_release_year = 2010;
update bluray set me_our_price = me_our_price*0.95 WHERE me_release_year = 2011;
update bluray set me_our_price = me_our_price*0.90 WHERE me_release_year = 2010;
DBMS_OUTPUT.PUT_LINE(' Blurays ');
for i in (
SELECT e.mo_title, e.mo_bluray.me_list_price as me_list_price, e.mo_bluray.me_our_price as me_our_price FROM movie e where e.mo_bluray is not null
)
loop
DBMS_OUTPUT.PUT_LINE(i.mo_title|| ' ' || i.me_list_price|| ' ' || i.me_list_price);
end loop;
DBMS_OUTPUT.PUT_LINE(' DVDs ');
for i in (
set serveroutput on
SELECT e.mo_title, e.mo_dvd.me_list_price as me_list_price, e.mo_dvd.me_our_price as me_our_price FROM movie e where e.mo_dvd is not null
)
loop
DBMS_OUTPUT.PUT_LINE(i.mo_title|| ' ' || i.me_list_price|| ' ' || i.me_list_price);
end loop;
END discount;
To understand the use of "SET SERVEROUTPUT ON" I will take an example
DECLARE
a number(10) :=10;
BEGIN
dbms_output.put_line(a) ;
dbms_output.put_line('Hello World ! ') ;
END ;
With an output : PL/SQl procedure successfully completed i.e without the expected output
And the main reason behind is that ,whatever we pass inside dbms_output.put_line(' ARGUMENT '/VALUES) i.e. ARGUMENT/VALUES , is internally stored inside a buffer in SGA(Shared Global Area ) memory area upto 2000 bytes .
*NOTE :***However one should note that this buffer is only created when we use **dbms_output package. And we need to set the environment variable only once for a session !!
And in order to fetch it from that buffer we need to set the environment variable for the session . It makes a lot of confusion to the beginners that we are setting the server output on ( because of its nomenclature ) , but unfortunately its nothing like that . Using SET SERVER OUTPUT ON are just telling the PL/SQL engine that
*Hey please print the ARGUMENT/VALUES that I will be passing inside dbms_output.put_line
and in turn PL/SQl run time engine prints the argument on the main console .
I think I am clear to you all . Wish you all the best . To know more about it with the architectural structure of Oracle Server Engine you can see my answer on Quora http://qr.ae/RojAn8
And to answer your question "One should use SET SERVER OUTPUT in the beginning of the session. "