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
Calling Stored Procedure in Datastage
Moderators: chulett, rschirm, roy
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.
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
"You can never have too many knives" -- Logan Nine Fingers