Calling Stored Procedure in Datastage

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
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Calling Stored Procedure in Datastage

Post by Vrisha »

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

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Hi Craig,

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

Thanks.
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

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