topic Re: Calling Oracle stored procedure from SAS in SAS Programming

Calling Oracle stored procedure from SAS

adisal — Thu, 18 Mar 2021 05:18:31 GMT

I am trying to call a stored procedure created in Oracle through SAS using pass-through but getting an error. The procedure is successfully getting executed in oracle. Below is my code and error

Code -

proc sql;
CONNECT TO oracle (user=abc password='xyz' path=abcd);

EXECUTE (execute del_stored_proc) BY oracle;
EXECUTE (COMMIT) BY oracle;

disconnect from oracle;
QUIT;

Error -
ERROR: ORACLE execute error: ORA-06550: line 1, column 8:PLS-00905: object del_stored_proc is invalidORA-06550:
line 1, column 8:PL/SQL: Statement ignored.

Stored proc - It is no parameter proc with just a delete statement in the body

CREATE OR REPLACE PROCEDURE del_stored_proc ()
IS
BEGIN
DELETE FROM test
WHERE date >= add_months(trunc(sysdate), -4);

END del_stored_proc;

I have tried the pass-through for create/insert statements and they work fine. Not sure what I am doing wrong here.

Thanks for helping out.

Re: Calling Oracle stored procedure from SAS

Sajid01 — Thu, 18 Mar 2021 13:07:50 GMT

The error is typical of executing invalid block.

I suggest trying in SQL developer first.

Re: Calling Oracle stored procedure from SAS

adisal — Thu, 18 Mar 2021 14:25:16 GMT

I have tried executing in Oracle and it works fine, just calling it from SAS is giving an error.

Re: Calling Oracle stored procedure from SAS

Sajid01 — Thu, 18 Mar 2021 14:55:04 GMT

Hello
You are not passing any parameter to stored procedure.
I suggest create stored procedure like this and try.

CREATE OR REPLACE PROCEDURE del_stored_proc
IS
BEGIN
DELETE FROM test
WHERE date >= add_months(trunc(sysdate), -4);

END del_stored_proc;

Re: Calling Oracle stored procedure from SAS

adisal — Thu, 18 Mar 2021 16:40:02 GMT

I think the add_months was the issue. It worked with to_date(CCYYMM, 'YYYYMM')>= trunc(sysdate)-60.

Thanks for the help.