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



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Thu Sep 21, 2017 10:37 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
I need to call the stored procedure in datastage , get the data and populate in to another table

Stored procedure is below
CREATE OR REPLACE PROCEDURE GET_JOB_CONTROL_DAILY_DATES(
varPROCESS_GROUP_ID VARCHAR2, varJOB_NAME VARCHAR2, dtRUN_DATE DATE, dtDATA_START_DATE OUT DATE, dtDATA_END_DATE OUT DATE)
AS
nREC_COUNT NUMBER;
BEGIN

dtDATA_START_DATE := NULL;
dtDATA_END_DATE := NULL;

SELECT COUNT(*) INTO nREC_COUNT
FROM DARTDW.D_ETL_JOB_CONTROL_DAILY
WHERE PROCESS_GROUP_ID = VARPROCESS_GROUP_ID AND PROCESSED = 0 AND DATA_END_DATE <= DTRUN_DATE;
IF nREC_COUNT > 0 THEN
SELECT MIN(DATA_START_DATE), MAX(DATA_END_DATE) INTO dtDATA_START_DATE, dtDATA_END_DATE
FROM DARTDW.D_ETL_JOB_CONTROL_DAILY
WHERE PROCESS_GROUP_ID = VARPROCESS_GROUP_ID AND PROCESSED = 0 AND DATA_END_DATE <= DTRUN_DATE;
END IF;

END;
---------------------------------------------------------------------------------
So I used the Stored procedure stage and peek stage.
In SP stage, I gave like below.

In General tab--> Database Vendor--> Oracle

In Syntax tab --> Procedure name - GET_JOB_CONTROL_DAILY_DATES
Procedure type--> Transform
Procedure Call Syntax-->BEGIN DARTDW. GET_JOB_CONTROL_DAILY_DATES(); END;

When I run the job, I am getting the below error.

Stored_Procedure_6,0: Error: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_JOB_CONTROL_DAILY_DATES' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

What is the mistake I am doing? please let me know

_________________
Suja
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42164
Location: Denver, CO
Points: 216472

Post Posted: Thu Sep 21, 2017 9:02 pm Reply with quote    Back to top    

Basically you have a stored procedure with four input and one output parameters and your anonymous block includes none of them.

_________________
-craig

Dr. Frankenstein entered a bodybuilding competition and discovered he had seriously misunderstood the objective.
Rate this response:  
Not yet rated
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Fri Sep 22, 2017 6:36 am Reply with quote    Back to top    

Hi Craig,

It has 3 inputs and 2 outputs. Please let me know what I need to do.

Thanks.

_________________
Suja
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: 42164
Location: Denver, CO
Points: 216472

Post Posted: Fri Sep 22, 2017 6:53 am Reply with quote    Back to top    

Sorry, missed the first "OUT".

I'd suggest you start with the documentation for the stage. Make sure you scroll down to see the other sections like how the Parameters tab works.

_________________
-craig

Dr. Frankenstein entered a bodybuilding competition and discovered he had seriously misunderstood the objective.
Rate this response:  
Not yet rated
Vrisha



Group memberships:
Premium Members

Joined: 15 Jul 2017
Posts: 55
Location: Texas,USA
Points: 650

Post Posted: Fri Sep 22, 2017 9:24 am Reply with quote    Back to top    

Thanks Craig. I went through the documentation . But unable to find out. I will do testing based on trial and error methods.

_________________
Suja
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