Hello,
I would like to know, how can I execute Oracle Store Procedure with OUTPUT variable in SAS GUIDE.
In Sql Developer I can execute like you can see below and it works.
Oracle Code:
VAR OUTPUT1 NUMBER;
VAR OUTPUT2 NUMBER;
EXECUTE StoredProcedure(99, :OUTPUT1, :OUTPUT2);
When I declare VAR OUTPUT NUMBER in PROC SQL it doesn't work.
Could you please help me with an alternative way to execute this?
Thank you.
Higor Carvalho.
Hello,
The solution is:
Oracle Code:
DECLARE
OUTPUT1 NUMBER;
OUTPUT2 NUMBER;
BEGIN
StoredProcedure(99, OUTPUT1, OUTPUT2);
END
;
I solved my issue by above code.
You need execute this code in PROC SQL.
Thank you.
Higor Carvalho.
@higorcc wrote:
Hello,
I would like to know, how can I execute Oracle Store Procedure with OUTPUT variable in SAS GUIDE.
In Sql Developer I can execute like you can see below and it works.
Oracle Code:
VAR OUTPUT1 NUMBER;
VAR OUTPUT2 NUMBER;EXECUTE StoredProcedure(99, :OUTPUT1, :OUTPUT2);
When I declare VAR OUTPUT NUMBER in PROC SQL it doesn't work.
Could you please help me with an alternative way to execute this?
Thank you.
Higor Carvalho.
I am not sure what those two "output" thingys have to do with your problem.
It looks like you have a procedure named STOREDPROCEDURE() that takes as input 3 numbers.
So why not just do that.
What numbers you want to pass in as the last two arguments to the procedure?
In this case I have a stored procedure with 1 IN (Input) and 2 OUT (output).
For 2 OUT (output) when I execute I need declare these two "output".
In Sql Developer I declare this code and it works, but when I put the same code in PROC SQL with Oracle Connection it doesn't work.
Thank you.
Higor Carvalho.
@higorcc wrote:
In this case I have a stored procedure with 1 IN (Input) and 2 OUT (output).
For 2 OUT (output) when I execute I need declare these two "output".
In Sql Developer I declare this code and it works, but when I put the same code in PROC SQL with Oracle Connection it doesn't work.
Thank you.
Higor Carvalho.
How can you tell it did not work? It looks to me like you are writing output to symbols and not into an actual table that you can query. Why not add a step to store those temporary symbols into a table so you can then query the table to get the results back to SAS?
Please post your SAS log including the complete code and any notes and errors.
Hello,
The solution is:
Oracle Code:
DECLARE
OUTPUT1 NUMBER;
OUTPUT2 NUMBER;
BEGIN
StoredProcedure(99, OUTPUT1, OUTPUT2);
END
;
I solved my issue by above code.
You need execute this code in PROC SQL.
Thank you.
Higor Carvalho.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: