Stored Procedures in Oracle connector stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
happyramana1
Participant
Posts: 11
Joined: Thu Jun 05, 2008 7:32 am

Stored Procedures in Oracle connector stage

Post 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)
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Did you add a semicolon at the end of the command?
happyramana1
Participant
Posts: 11
Joined: Thu Jun 05, 2008 7:32 am

Post 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
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post 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?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post 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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
happyramana1
Participant
Posts: 11
Joined: Thu Jun 05, 2008 7:32 am

Post 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.
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

Any particular reason you aren't using the stored procedure stage in a separate job?
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
happyramana1
Participant
Posts: 11
Joined: Thu Jun 05, 2008 7:32 am

Post by happyramana1 »

No specific reason, i just wanted to do it in a job after load is done.
narasimha
Charter Member
Charter Member
Posts: 1236
Joined: Fri Oct 22, 2004 8:59 am
Location: Staten Island, NY

Post by narasimha »

Try using CALL instead of EXEC as below (without the semi-colon in the end)

CALL DBMS_STATS.gather_table_stats(XXXXXXX)
Narasimha Kade

Finding answers is simple, all you need to do is come up with the correct questions.
happyramana1
Participant
Posts: 11
Joined: Thu Jun 05, 2008 7:32 am

Post 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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post 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. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
narasimha
Charter Member
Charter Member
Posts: 1236
Joined: Fri Oct 22, 2004 8:59 am
Location: Staten Island, NY

Post by narasimha »

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.
joycerecacho
Participant
Posts: 298
Joined: Tue Aug 26, 2008 12:17 pm

Post 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.
Joyce A. Recacho
São Paulo/SP
Brazil
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

In other words, the dreaded bare minimum anonymous block. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply