Set Serveroutput on in Oracle Procedure
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...
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;
6 Answers
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
And to answer your question "One should use SET SERVER OUTPUT in the beginning of the session. "
"SET serveroutput ON" is a SQL*Plus command and is not valid PL/SQL.
First add next code in your sp:
BEGIN
dbms_output.enable();
dbms_output.put_line ('TEST LINE');
END;
Compile your code in your Oracle SQL developer. So go to Menu View--> dbms output. Click on Icon Green Plus and select your schema. Run your sp now.
Actually, you need to call SET SERVEROUTPUT ON; before the BEGIN call.
Everyone suggested this but offers no advice where to actually place the line:
SET SERVEROUTPUT ON;
BEGIN
FOR rec in (SELECT * FROM EMPLOYEES) LOOP
DBMS_OUTPUT.PUT_LINE(rec.EmployeeName);
ENDLOOP;
END;
Otherwise, you won't see any output.
If you want to execute any procedure then firstly you have to set serveroutput on in the sqldeveloper work environment like.
-> SET SERVEROUTPUT ON;
-> BEGIN
dbms_output.put_line ('Hello World..');
dbms_output.put_line('Its displaying the values only for the Testing purpose');
END;
/
Procedure successful but any outpout
Error line1: Unexpected identifier
Here is the code:
SET SERVEROUTPUT ON
DECLARE
-- Curseurs
CURSOR c1 IS
SELECT RWID FROM J_EVT
WHERE DT_SYST < TO_DATE(TO_CHAR(SYSDATE,'DD/MM') || '/' || TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 3));
-- Collections
TYPE tc1 IS TABLE OF c1%RWTYPE;
-- Variables de type record
rtc1 tc1;
vCpt NUMBER:=0;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO rtc1 LIMIT 5000;
FORALL i IN 1..rtc1.COUNT
DELETE FROM J_EVT
WHERE RWID = rtc1(i).RWID;
COMMIT;
-- Nombres lus : 5025651
FOR i IN 1..rtc1.COUNT LOOP
vCpt := vCpt + SQL%BULK_RWCOUNT(i);
END LOOP;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
COMMIT;
DBMS_OUTPUT.PUT_LINE ('Nombres supprimes : ' || TO_CHAR(vCpt));
END;
/
exit
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
