converting a stored procedure into a parallel job?

[mctny]

hi Guys/Gals,

I have an Pl/SQL stored procedure and I need to create a job ( not just by calling it in a Stored procedure stage) to do the same thing as the stored procedure. the stored procedure is opening 2 nested cursors in oracle and use the first 5 rows and update a table oracle. my questions are

1. how can I implement nested cursors ( nested loops fetching records from two different tables)

2. the procedure count number of rows processed and exit the loops if the number of rows processed inside the inner loops is equal to 5. how can I implement this in Datastage parallel job.

if it is not clear I can provide more details
thanks in advance
Cetin

[DSguru2B]

Basically in other words, you need to update your target table with only the first five records from the input.
or there is more to it, because that sounds a bit to easy.
Take you input (file, table), constraint it only to the first five records and update your table.
I know there is more to it than this but I thought id give it a shot

[mctny]

yes there is more to it, here is one of the stored procedure I need to convert it to a parallel job ( not by just calling the procedure from a stored procedure stage as a whole)

CREATE OR REPLACE procedure sp_load_top_five_rfs
is

VN_TRANS_AMT NUMBER(14,2) := 0.00;
VN_TRANS_COUNT NUMBER(15) := 0;
VN_AVG_AMT_CFDA NUMBER(14,2) := 0.00;
V_REC_COUNT NUMBER(15) := 0;
VN_RF_CNT NUMBER(4) := 0;
VC_CURR_FY VARCHAR2(4);

cursor C_ALL_POS is select distinct PO_ORGANIZATION_CD from D_CFDA_SUBPROG;

CURSOR c_top_five(V_POC_CD D_CFDA_SUBPROG.PO_ORGANIZATION_CD%TYPE) is select DCS.PO_ORGANIZATION_CD PO_ORGANIZATION_CD, DCS.PO_ORGANIZATION PO_ORGANIZATION, DCS.CFDA_NO CFDA_NO, DCS.SUBPRG_ID SUBPRG_ID, FFT.PR_AWARD_NO PR_AWARD_NO,
FFT.TRANSACTION_DATE TRANSACTION_DATE, FFT.TRANSACTION_ID TRANSACTION_ID, DTT.TRANSACTION_TYPE TRANSACTION_TYPE, FFT.TRANSACTION_AMOUNT TRANSACTION_AMOUNT FROM
D_CFDA_SUBPROG DCS, D_TRANSACTION_TYPE DTT, F_FINANCIAL_TRANSACTION FFT WHERE
FFT.CFDA_SUBPROG_KEY = DCS.CFDA_SUBPROG_KEY AND
FFT.TRANSACTION_TYPE_KEY = DTT.TRANSACTION_TYPE_KEY AND
FFT.TRANSACTION_DATE < '01-DEC-2005' AND
FFT.TRANSACTION_DATE > (to_date('01-DEC-2005', 'dd-mon-yyyy') - 90) AND
DTT.TRANSACTION_TYPE = 'RF' and
DCS.PO_ORGANIZATION_CD = V_POC_CD and
FFT.TRANSACTION_AMOUNT > 0
ORDER BY FFT.TRANSACTION_AMOUNT DESC;

CURSOR C_GET_DD_AMT_CFDA (V_CFDA_NO D_CFDA_SUBPROG.CFDA_NO%TYPE, V_SUBPRG_ID D_CFDA_SUBPROG.SUBPRG_ID%TYPE ) iS SELECT TRANSACTION_AMOUNT FROM F_FINANCIAL_TRANSACTION FFT, D_TRANSACTION_TYPE DTT
WHERE FFT.TRANSACTION_TYPE_KEY = DTT.TRANSACTION_TYPE_KEY AND
FFT.CFDA_NO = V_CFDA_NO AND
FFT.SUBPRG_ID = V_SUBPRG_ID AND
DTT.TRANSACTION_TYPE = 'DD';

BEGIN

EXECUTE IMMEDIATE 'TRUNCATE TABLE top_five_refunds';

IF SUBSTR(TO_CHAR(SYSDATE, 'MMDDYYYY'), 1, 4) < '1001' THEN
VC_CURR_FY := TO_CHAR(TO_NUMBER(SUBSTR(TO_CHAR(SYSDATE, 'MMDDYYYY'), 5, 4) - 1));

ELSE
VC_CURR_FY := SUBSTR(TO_CHAR(SYSDATE, 'MMDDYYYY'), 5, 4);
END IF;

FOR V_POC IN C_ALL_POS LOOP

FOR V_TOP_FIVE IN C_TOP_FIVE(V_POC.PO_ORGANIZATION_CD) LOOP

V_REC_COUNT := V_REC_COUNT + 1;

IF (V_REC_COUNT > 5) THEN
v_rec_count := 0;
exit;
END IF;

SELECT COUNT(TRANSACTION_ID) INTO VN_RF_CNT
FROM F_FINANCIAL_TRANSACTION FFT, D_TRANSACTION_TYPE DTT
WHERE
FFT.TRANSACTION_TYPE_KEY = DTT.TRANSACTION_TYPE_KEY AND
FFT.PR_AWARD_NO = V_TOP_FIVE.PR_AWARD_NO AND
DTT.TRANSACTION_TYPE = 'RF' AND
FFT.TRANSACTION_DATE > to_date('30-SEP-'||VC_CURR_FY);


BEGIN

INSERT into TOP_FIVE_REFUNDS
(PO_ORGANIZATION_CD,
PO_ORGANIZATION,
CFDA_NO,
PR_AWARD_NO,
TRANSACTION_DATE,
TRANSACTION_ID,
TRANSACTION_RF_AMOUNT,
TRANSACTION_COUNT_CFY,
CREATED_DATE,
LAST_UPDATED_DATE,
CREATED_USER,
LAST_UPDATED_USER
)
values (V_TOP_FIVE.PO_ORGANIZATION_CD,
V_TOP_FIVE.PO_ORGANIZATION,
V_TOP_FIVE.CFDA_NO,
V_TOP_FIVE.PR_AWARD_NO,
V_TOP_FIVE.TRANSACTION_DATE,
V_TOP_FIVE.TRANSACTION_ID,
V_TOP_FIVE.TRANSACTION_AMOUNT,
VN_RF_CNT,
sysdate,
sysdate,
user,
user
);

EXCEPTION WHEN OTHERS THEN
raise;
end;
END LOOP;
COMMIT;

END LOOP;
EXCEPTION WHEN OTHERS THEN
raise;
END sp_load_top_five_rfs;
/

[lstsaur]

Hi Cetin,
If you can post your PL/SQL stored procedure, it will be much easier for me to figure out how to code a parallel job to do same thing.

[mctny]

Hi Cetin,
If you can post your PL/SQL stored procedure, it will be much easier for me to figure out how to code a parallel job to do same thing.

I did already in my previous message, thank you for reminding me. at first I thought it is a long procedure but it wasnot that long. it is not complex either however I have never converted anything to a DS job

Thanks
Cetin