set serveroutput on in oracle procedure

Question

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; 

Reinke

Move set serveroutput on before the create at top – Estradiol

@chrishardin The procedure always needs to output. Not that I first have to put set serveroutput on and then execute the procedure – Reinke

@Estradiol where about do i need to put that? Where ever i put it it says code Error(4,12): PLS-00103: Encountered the symbol "." when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table long double ref char time timestamp interval date binary national character ncharcode – Reinke

orafaq.com/forum/t/14373/2 – Estradiol

Answer 1

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. "

Importunity

Answer 2

"SET serveroutput ON" is a SQL*Plus command and is not valid PL/SQL.

Betweenwhiles

Answer 3

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.

Corroboration

Answer 4

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;
/

Marylnmarylou

Answer 5

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.

Dooley

Answer 6

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

Intercolumniation

please add ; in the end of first line. – Novelist