SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
adisal
Fluorite | Level 6

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.

4 REPLIES 4
Sajid01
Meteorite | Level 14

The error is typical of executing invalid block.

I suggest trying in SQL developer first.

adisal
Fluorite | Level 6

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

Sajid01
Meteorite | Level 14

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;

adisal
Fluorite | Level 6

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

 

Thanks for the help.

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1608 views
  • 0 likes
  • 2 in conversation