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.
The error is typical of executing invalid block.
I suggest trying in SQL developer first.
I have tried executing in Oracle and it works fine, just calling it from SAS is giving an error.
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;
I think the add_months was the issue. It worked with to_date(CCYYMM, 'YYYYMM')>= trunc(sysdate)-60.
Thanks for the help.
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.