- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
The error is typical of executing invalid block.
I suggest trying in SQL developer first.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I have tried executing in Oracle and it works fine, just calling it from SAS is giving an error.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I think the add_months was the issue. It worked with to_date(CCYYMM, 'YYYYMM')>= trunc(sysdate)-60.
Thanks for the help.