Oracle Syntax Problem

[OS2Rules]

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.

[DBailey]

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

[OS2Rules]

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.

[SASKiwi]

Have you tried a semicolon on your EXECUTE statement?

execute (execute AAA_BBB_CCCC.MY_STORED_PROCESS_NAME;  ) by ODBC;

[OS2Rules]

SASKiwi:

That give me a syntax error.....

[SASKiwi]

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?