topic Re: Oracle Syntax Problem in SAS Procedures

Oracle Syntax Problem

OS2Rules — Mon, 01 Dec 2014 15:48:59 GMT

Hi All:

I am using SAS/ACCESS ODBC (v9.3) to access an Oracle table. I would like to execute a stored process but I keep getting an error:

    proc sql;
    connect TO ODBC (datasrc=odbc_name user=myuserid password=XXXX1234 );
    execute (execute AAA_BBB_CCCC.MY.STORED.PROCESS.NAME ) by ODBC;

ERROR: CLI execute error: [Oracle][ODBC]Syntax error or access violation.

disconnect from ODBC;
quit;

NOTE: The SAS System stopped processing this step because of errors.

I have used this same syntax for a SQL Server store process and it works there.

Any help would be appreciated.

Re: Oracle Syntax Problem

DBailey — Mon, 01 Dec 2014 17:55:37 GMT

Are you sure of the package name? You're probably obfuscating, but I don't think it can have all of the periods.

Re: Oracle Syntax Problem

OS2Rules — Mon, 01 Dec 2014 18:11:00 GMT

Hi:

Just trying to be obtuse.... I messed up the periods - should be AAA_BBB_CCCC.MY_STORED_PROCESS_NAME

But I can run the stored process from SQL + so that shouldn't be an issue.

Re: Oracle Syntax Problem

SASKiwi — Mon, 01 Dec 2014 18:36:41 GMT

Have you tried a semicolon on your EXECUTE statement?

execute (execute AAA_BBB_CCCC.MY_STORED_PROCESS_NAME; ) by ODBC;

Re: Oracle Syntax Problem

OS2Rules — Mon, 01 Dec 2014 18:39:29 GMT

SASKiwi:

That give me a syntax error.....

Re: Oracle Syntax Problem

SASKiwi — Mon, 01 Dec 2014 19:08:24 GMT

I think you are right, semicolons are only required on SELECT statements. Unfortunately I don't have Oracle to test this out on, although I too have a stored procedure running under SQL Server and it works fine.

Have a look at this: 18350 - Calling stored procedures using SAS/ACCESS® Interface to Oracle

And maybe try: execute (execute AAA_BBB_CCCC.MY_STORED_PROCESS_NAME() ) by ODBC; Does your stored proc have any parameters?