Calling a stored procedure in Oracle with IN and OUT parameters

oracle11g, sql

Solution

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;

Problem

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.

Original source