DSXchange

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

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

Hi Craig,

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

Thanks.

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.

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