DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
happyramana1
Participant



Joined: 05 Jun 2008
Posts: 11

Points: 96

Post Posted: Wed Nov 17, 2010 6:31 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
Additional info: 8.1
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Wed Nov 17, 2010 6:58 am Reply with quote    Back to top    

Did you add a semicolon at the end of the command?

_________________

Image
Rate this response:  
Not yet rated
happyramana1
Participant



Joined: 05 Jun 2008
Posts: 11

Points: 96

Post Posted: Wed Nov 17, 2010 7:02 am Reply with quote    Back to top    

ArndW wrote:
Did you add a semicolon at the end of the command? ...

Yes, semicolon is included. The sql works fine in toad
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Wed Nov 17, 2010 7:24 am Reply with quote    Back to top    

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?

_________________

Image
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42930
Location: Denver, CO
Points: 221405

Post Posted: Wed Nov 17, 2010 8:18 am Reply with quote    Back to top    

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

The Old Ones were, the Old Ones are, and the Old Ones shall be. Not in the spaces we know, but between them. They walk serene and primal, undimensioned and to us unseen.
Rate this response:  
Not yet rated
happyramana1
Participant



Joined: 05 Jun 2008
Posts: 11

Points: 96

Post Posted: Wed Nov 17, 2010 8:37 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1694
Location: Colleyville, Texas
Points: 23058

Post Posted: Wed Nov 17, 2010 8:46 am Reply with quote    Back to top    

Any particular reason you aren't using the stored procedure stage in a separate job?

_________________
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2017
Rate this response:  
Not yet rated
happyramana1
Participant



Joined: 05 Jun 2008
Posts: 11

Points: 96

Post Posted: Wed Nov 17, 2010 12:00 pm Reply with quote    Back to top    

No specific reason, i just wanted to do it in a job after load is done.
Rate this response:  
Not yet rated
narasimha


since February 2010

Group memberships:
Premium Members, Heartland Usergroup

Joined: 22 Oct 2004
Posts: 1236
Location: Staten Island, NY
Points: 6560

Post Posted: Wed Nov 17, 2010 2:03 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
happyramana1
Participant



Joined: 05 Jun 2008
Posts: 11

Points: 96

Post Posted: Thu Nov 18, 2010 4:49 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42930
Location: Denver, CO
Points: 221405

Post Posted: Thu Nov 18, 2010 7:40 am Reply with quote    Back to top    

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. Confused

_________________
-craig

The Old Ones were, the Old Ones are, and the Old Ones shall be. Not in the spaces we know, but between them. They walk serene and primal, undimensioned and to us unseen.
Rate this response:  
Not yet rated
narasimha


since February 2010

Group memberships:
Premium Members, Heartland Usergroup

Joined: 22 Oct 2004
Posts: 1236
Location: Staten Island, NY
Points: 6560

Post Posted: Thu Nov 18, 2010 9:36 pm Reply with quote    Back to top    

The below should work for you. Note the semi-colons.

Code:
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.
Rate this response:  
Not yet rated
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 290

Points: 5351

Post Posted: Mon Feb 11, 2019 11:56 pm Reply with quote    Back to top    

narasimha wrote:
The below should work for you. Note the semi-colons.

Code:
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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42930
Location: Denver, CO
Points: 221405

Post Posted: Tue Feb 12, 2019 2:40 am Reply with quote    Back to top    

In other words, the dreaded bare minimum anonymous block. Wink

_________________
-craig

The Old Ones were, the Old Ones are, and the Old Ones shall be. Not in the spaces we know, but between them. They walk serene and primal, undimensioned and to us unseen.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours