Help using Base SAS procedures

Oracle Syntax Problem

Reply
Super Contributor
Posts: 358

Oracle Syntax Problem

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.

Super Contributor
Posts: 578

Re: Oracle Syntax Problem

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

Super Contributor
Posts: 358

Re: Oracle Syntax Problem

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.

Super User
Posts: 3,261

Re: Oracle Syntax Problem

Have you tried a semicolon on your EXECUTE statement?

execute (execute AAA_BBB_CCCC.MY_STORED_PROCESS_NAME;  ) by ODBC;

Super Contributor
Posts: 358

Re: Oracle Syntax Problem

SASKiwi:

That give me a syntax error.....

Super User
Posts: 3,261

Re: Oracle Syntax Problem

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?


Ask a Question
Discussion stats
  • 5 replies
  • 264 views
  • 1 like
  • 3 in conversation