My procedure is
CREATE OR REPLACE
PROCEDURE PR_SAP_EXCHANGE_RATE
IS
CURSOR CR_SAP_EXCHANGE_RATE
IS
SELECT ( TO_DATE(LTRIM(RTRIM(Exchange_Date)),'MM/DD/YYYY')) AS EXCH_DT, LTRIM(RTRIM(From_Curr_CD)) AS FROM_CURCY_CD, LTRIM(RTRIM(To_Curr_CD)) AS TO_CURCY_CD, EXCHANGE_RATE AS EXCH_RATE
FROM SAP_Exchange_Rate ;
NEXT_ID NUMBER;
BEGIN
FOR VAR_CR_SAP IN CR_SAP_EXCHANGE_RATE
LOOP
NEXT_ID := SEQ_GEN_TEST.NEXTVAL;
-- SELECT SEQ_GEN_TEST.NEXTVAL INTO NEXT_ID FROM DUAL;
INSERT
INTO EIM_EXCH_RATE
(
-- ROW_ID,
EXCH_CD,
EXCH_DT,
FROM_CURCY_CD,
IF_ROW_BATCH_NUM,
IF_ROW_STAT,
TO_CURCY_CD,
EXCH_RATE
)
VALUES
(
-- NEXT_ID,
'DAILY',
CR_SAP_EXCHANGE_RATE.EXCH_DT,
CR_SAP_EXCHANGE_RATE.FROM_CURCY_CD,
100,
'FOR_IMPORT',
CR_SAP_EXCHANGE_RATE.TO_CURCY_CD,
CR_SAP_EXCHANGE_RATE.EXCH_RATE
);
END LOOP;
COMMIT;
END PR_SAP_EXCHANGE_RATE;
and the errors are like
- Error(14,5): PL/SQL: SQL Statement ignored
- Error(35,9): PLS-00225: subprogram or cursor 'CR_SAP_EXCHANGE_RATE' reference is out of scope
- Error(35,30): PL/SQL: ORA-00984: column not allowed here
can anyone help me out???