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;
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
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;
/
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.
"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.
Source: Stackoverflow.com