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)
Did you add a semicolon at the end of the command?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 11
- Joined: Thu Jun 05, 2008 7:32 am
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- 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