Oracle Store Procedure Execution with OUTPUT in SAS

[higorcc]

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.

[Tom]

---

@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?

[higorcc]

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.

[Tom]

---

@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?

[SASKiwi]

Please post your SAS log including the complete code and any notes and errors.

[higorcc · Accepted solution]

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.