topic Re: Oracle Store Procedure Execution with OUTPUT in SAS in SAS Enterprise Guide

Oracle Store Procedure Execution with OUTPUT in SAS

higorcc — Tue, 01 Aug 2023 14:25:43 GMT

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.

Re: Oracle Store Procedure Execution with OUTPUT in SAS

Tom — Tue, 01 Aug 2023 14:31:40 GMT

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

Re: Oracle Store Procedure Execution with OUTPUT in SAS

higorcc — Tue, 01 Aug 2023 14:42:20 GMT

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.

Re: Oracle Store Procedure Execution with OUTPUT in SAS

Tom — Tue, 01 Aug 2023 15:08:28 GMT

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

Re: Oracle Store Procedure Execution with OUTPUT in SAS

SASKiwi — Tue, 01 Aug 2023 20:27:02 GMT

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

Re: Oracle Store Procedure Execution with OUTPUT in SAS

higorcc — Tue, 08 Aug 2023 16:24:42 GMT

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.