Page 1 of 1
Stored Procedures in Oracle connector stage
Posted: Wed Nov 17, 2010 6:31 am
by happyramana1
Can we execute stored procedure from After SQL statement in oracle connector stage?
I get a error when i run the same...The following SQL statement failed: EXEC DBMS_STATS.gather_table_stats('XYZ','TABLE_NAME',method_opt => 'for all columns size auto').
TABLE_NAME: [IIS-CONN-ORA-001003] The OCI function executeDirect returned status -1. Error code: 900, Error message: ORA-00900: invalid SQL statement. (CC_OraUtils::handleBeforeAfterSQL, file CC_OraUtils.cpp, line 3,871)
Posted: Wed Nov 17, 2010 6:58 am
by ArndW
Did you add a semicolon at the end of the command?
Posted: Wed Nov 17, 2010 7:02 am
by happyramana1
ArndW wrote:Did you add a semicolon at the end of the command? ...
Yes, semicolon is included. The sql works fine in toad
Posted: Wed Nov 17, 2010 7:24 am
by ArndW
A quick Google search shows that the ORA-0900 error has more to do with the call than the procedure itself. Have you tried using double-quotes instead of single quotes in the call?
Posted: Wed Nov 17, 2010 8:18 am
by chulett
Try it without the semicolon. While Toad may like/need it, there are places in DataStage where it doesn't. Not sure this is one, but worth a shot.
Posted: Wed Nov 17, 2010 8:37 am
by happyramana1
ArndW wrote:A quick Google search shows that the ORA-0900 error has more to do with the call than the procedure itself. Have you tried using double-quotes instead of single quotes in the call?
Double quotes also gave me the same error.
Posted: Wed Nov 17, 2010 8:46 am
by asorrell
Any particular reason you aren't using the stored procedure stage in a separate job?
Posted: Wed Nov 17, 2010 12:00 pm
by happyramana1
No specific reason, i just wanted to do it in a job after load is done.
Posted: Wed Nov 17, 2010 2:03 pm
by narasimha
Try using CALL instead of EXEC as below (without the semi-colon in the end)
CALL DBMS_STATS.gather_table_stats(XXXXXXX)
Posted: Thu Nov 18, 2010 4:49 am
by happyramana1
It does not help, i tried running this in a standalone job with only stored procedure stage. the error i got is
Stored_Procedure_45: OCIAttrGet - Must be an anonymous pl/sql block being prepared.
Stored_Procedure_45,0: Error occurred in call to ORPHCallActivePluginInitialize().
Stored_Procedure_45,0: The runLocally() of the operator failed.
Stored_Procedure_45,0: Operator terminated abnormally: runLocally() did not return APT_StatusOk
Posted: Thu Nov 18, 2010 7:40 am
by chulett
Did you let the Stored Procedure stage generate the SQL or did you do it manually? It will build the anonymous block that is needed, hence the question.
Posted: Thu Nov 18, 2010 9:36 pm
by narasimha
The below should work for you. Note the semi-colons.
Code: Select all
BEGIN DBMS_STATS.gather_table_stats(XXXXXXX); END;
Posted: Mon Feb 11, 2019 11:56 pm
by joycerecacho
narasimha wrote:The below should work for you. Note the semi-colons.
Code: Select all
BEGIN DBMS_STATS.gather_table_stats(XXXXXXX); END;
This approach worked fine to me! (the only one)
Thank you so much.
Posted: Tue Feb 12, 2019 2:40 am
by chulett
In other words, the dreaded bare minimum anonymous block.
