Calling Stored Procedure in Datastage
Posted: Thu Sep 21, 2017 10:37 am
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
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