Calling a stored procedure in Oracle with IN and OUT parameters

Question

I have this procedure:

CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER) AS BEGIN SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr; END; 

So when I run it like this it returns absolutely nothing:

DECLARE     amount NUMBER; BEGIN     PROC1(1000001, amount);     dbms_output.put_line(amount); END; 

BTW I use DreamCoder for Oracle. Is there a problem with the procedure itself or with the way I call it? There is an entry in the INVOICE table with INVOICE_NR equal to 1000001.

Answer 1

If you set the server output in ON mode before the entire code, it works, otherwise put_line() will not work. Try it!

The code is,

set serveroutput on; CREATE OR REPLACE PROCEDURE PROC1(invoicenr IN NUMBER, amnt OUT NUMBER) AS BEGIN SELECT AMOUNT INTO amnt FROM INVOICE WHERE INVOICE_NR = invoicenr; END; 

And then call the function as it is:

DECLARE amount NUMBER; BEGIN PROC1(1000001, amount); dbms_output.put_line(amount); END;