Stored Procedures in Oracle connector stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 11
- Joined: Thu Jun 05, 2008 7:32 am
Stored Procedures in Oracle connector stage
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)
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)
-
- Participant
- Posts: 11
- Joined: Thu Jun 05, 2008 7:32 am
-
- Participant
- Posts: 11
- Joined: Thu Jun 05, 2008 7:32 am
-
- Participant
- Posts: 11
- Joined: Thu Jun 05, 2008 7:32 am
-
- Participant
- Posts: 11
- Joined: Thu Jun 05, 2008 7:32 am
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
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
The below should work for you. Note the semi-colons.
Code: Select all
BEGIN DBMS_STATS.gather_table_stats(XXXXXXX); END;
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
This approach worked fine to me! (the only one)narasimha wrote:The below should work for you. Note the semi-colons.
Code: Select all
BEGIN DBMS_STATS.gather_table_stats(XXXXXXX); END;
Thank you so much.
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil