CODEWITHSUNDEEP
sqloracle11g
RegedUser00x
RegedUser00x

Reputation: 2383

Calling a stored procedure in Oracle with IN and OUT parameters

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.

Upvotes: 63

Views: 562255

Answers (4)

Saranya B
Saranya B

Reputation: 1

As per my knowledge, a colon (":") should precede any output variable while executing stored procedures, so invocation should look like this: PROC1(1000001, :amount);

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

hope this helps

Upvotes: 0

Elneny
Elneny

Reputation: 41

I had the same problem. I used a trigger and in that trigger I called a procedure which computed some values into 2 OUT variables. When I tried to print the result in the trigger body, nothing showed on screen. But then I solved this problem by making 2 local variables in a function, computed what I need with them and finally, copied those variables in your OUT procedure variables. I hope it'll be useful and successful!

Upvotes: 3

Keerthi
Keerthi

Reputation: 909

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;

Upvotes: 82

Alvin
Alvin

Reputation: 37

Go to Menu Tool -> SQL Output, Run the PL/SQL statement, the output will show on SQL Output panel.

Upvotes: 1

Related Questions