I'm trying to execute stored procedure, I'm getting stuck in 54 line 'EXECUTE IMMEDIATE' basically. I'm passing SQL statement into for loop.Please let me know I'm doing wrong somewhere?
CREATE OR REPLACE Procedure DE_DUP_PROC (Dy_File_Name in USER_TABLES.table_name%type,
SUPPLIER_CD in varchar2,
EXT_PHARMA_ID in varchar2,
FLAG_VALUE in varchar2,
DE_REC_COUNT out NUMBER) --RETURN NUMBER
AS
SEQ_NO_SHO Number(38); --EEEE
WYYYYNNN VARCHAR2(250) := 'W2015021';
YYYYNNN VARCHAR2(10);
CUR_DATE Date;
--De_Rec_Count Number(38) := 3456;
DE_DUB_OUTPUT_FILE VARCHAR2(100);
/*CURSOR De_DUB_CUR IS */
DE_DUB_SQL_STATMNT VARCHAR2(3000) := 'SELECT S.TRANS_GUID AS OLD_TRANS_GUID,
H.TRANS_GUID AS NEW_TRANS_GUID,
CASE
WHEN H.TRANS_GUID IS NULL
THEN 0
ELSE 1
END as TRN_STAT,P.INTR_PHARMACY_ID as INT_PHARMACY_ID ,S.EXTRNL_PHARMACY_ID as EXT_PHARMACY_ID ,S.PHARMACY_NM as PHARMACY_NAME ,S.PHARMACY_ADDR as PHARMACY_ADDRESS,
S.SUPPLIERS_PSCR_DRUG_CD as SP_PSCR_DRUG_CD, S.PSCR_DRUG_IPU_CD as PS_DRUG_IPU_CD,''IPU'' as IPU_Value, S.PSCR_DRUG_DESC as PS_DRUG_DESC,
S.DSPNSD_DRUG_PACK_SIZE as DS_DRUG_PACK_SIZE, S.RX_ID as R_ID, S.RX_ITEM_SEQ as R_ITEM_SEQ, S.RX_REPEAT_STATUS as R_REPEAT_STATUS, S.RX_TYP as R_TYPE,
S.EXMT_STATUS as EX_STATUS,S.PSCR_QTY as PS_QTY, S.NRSG_HM_IND as NR_HM_IND,S.RX_DSPNSD_DT as R_DSPNSD_DT, S.RX_DSPNSD_TM as R_DSPNSD_TM,
S.SUPPLIERS_DSPNSD_DRUG_CD as SP_DSPNSD_DRUG_CD, S.DSPNSD_DRUG_IPU_CD as DS_DRUG_IPU_CD, ''IPU'' as IPU_Value2,S.DSPNSD_DRUG_DESC as DS_DRUG_DESC,
S.GENERIC_USE_MARKER as GC_USE_MARKER, S.DSPNSD_UNIT_OF_QTY as DS_UNIT_OF_QTY, S.DSPNSD_QTY as DS_QTY, ''EUR'' as EUR_Value1,S.COST_OF_DSPNSD_QTY as CT_OF_DSPNSD_QTY ,
S.VERBOSE_DOSAGE as VER_DOSAGE FROM
(SELECT stg.*, row_number() over (partition BY key_clmns_hash ORDER BY 1) AS RN FROM '|| Dy_File_Name ||' stg ) s
LEFT JOIN ps_pharmacy p
ON s.extrnl_pharmacy_id = p.extrnl_pharmacy_id
LEFT JOIN ps_rx_hist H
ON h.key_clmns_hash = s.key_clmnS_hash
AND h.rx_dspnsd_dt = s.rx_dspnsd_dt
AND s.supplier_pharmacy_cd = h.SUPPLIER_PHARMACY_CD
AND s.detl_clmns_hash <> h.detl_clmns_hash
WHERE s.RN = 1';
BEGIN
EXECUTE IMMEDIATE 'SELECT count(*) into DE_REC_COUNT
FROM (SELECT stg.*, row_number() over ( partition BY key_clmns_hash ORDER BY 1 ) AS RN FROM '|| Dy_File_Name ||' stg ) s
LEFT JOIN ps_pharmacy p ON s.extrnl_pharmacy_id = p.extrnl_pharmacy_id LEFT JOIN ps_rx_hist H ON h.key_clmns_hash = s.key_clmnS_hash
AND h.rx_dspnsd_dt = s.rx_dspnsd_dt AND s.supplier_pharmacy_cd = h.SUPPLIER_PHARMACY_CD AND s.detl_clmns_hash <> h.detl_clmns_hash WHERE S.RN = 1';
IF DE_REC_COUNT > 0 THEN
--select sysdate into CUR_DATE from dual;
--select PROC_PD_CD into WYYYYNNN from PS_ADMIN.PS_PROC_PD where PD_STRT_DT <= CURRENT_DATE and PD_END_DT >= CURRENT_DATE;
--select PROC_PD_CD into WYYYYNNN from PS_ADMIN.PS_PROC_PD where PD_STRT_DT <= CURRENT_DATE and PD_END_DT >= CURRENT_DATE; -- PD_STRT_DT<='16-AUG-15' and PD_STRT_DT >= '16-AUG-15';
select replace(WYYYYNNN,'W','') into YYYYNNN from dual;
SELECT PS_GET_PROC_PD(SUPPLIER_CD,EXT_PHARMA_ID,YYYYNNN) into SEQ_NO_SHO FROM DUAL;
select 'LRXIE'||FLAG_VALUE||'10_'||SUPPLIER_CD||'_'||EXT_PHARMA_ID||'_'||WYYYYNNN||'_'||SEQ_NO_SHO||'_'||DE_REC_COUNT||'.TXT' into DE_DUB_OUTPUT_FILE from dual;
--DBMS_OUTPUT.PUT_LINE( De_Dub_Output_File );
FOR De_Dub_rec IN EXECUTE IMMEDIATE DE_DUB_SQL_STATMNT
LOOP
dbms_output.enable(100000);
DBMS_OUTPUT.PUT_LINE ('"' || De_Dub_rec.OLD_TRANS_GUID || '"|"' || De_Dub_rec.NEW_TRANS_GUID || '"|' || De_Dub_rec.TRN_STAT || '|' || De_Dub_rec.INT_PHARMACY_ID || '|' || De_Dub_rec.EXT_PHARMACY_ID || '|"' ||
De_Dub_rec.PHARMACY_NAME|| '"|"' || De_Dub_rec.PHARMACY_ADDRESS || '"|' || De_Dub_rec.SP_PSCR_DRUG_CD || '|' || De_Dub_rec.PS_DRUG_IPU_CD || '|"' || De_Dub_rec.IPU_Value || '"|"' ||
De_Dub_rec.PS_DRUG_DESC || '"|' || De_Dub_rec.DS_DRUG_PACK_SIZE || '|"' || De_Dub_rec.R_ID || '"|' || De_Dub_rec.R_ITEM_SEQ || '|' || De_Dub_rec.R_REPEAT_STATUS || '|"' ||
De_Dub_rec.R_TYPE || '"|"' || De_Dub_rec.EX_STATUS || '"|' || De_Dub_rec.PS_QTY || '|' || De_Dub_rec.NR_HM_IND || '|"' || De_Dub_rec.R_DSPNSD_DT || '"|' || De_Dub_rec.R_DSPNSD_TM || '|' ||
De_Dub_rec.SP_DSPNSD_DRUG_CD || '|' || De_Dub_rec.DS_DRUG_IPU_CD|| '|"' || De_Dub_rec.IPU_Value2 || '"|"' || De_Dub_rec.DS_DRUG_DESC|| '"|' || De_Dub_rec.GC_USE_MARKER|| '|"' ||
De_Dub_rec.DS_UNIT_OF_QTY|| '"|' || De_Dub_rec.DS_QTY|| '|"' || De_Dub_rec.EUR_Value1|| '"|' || De_Dub_rec.CT_OF_DSPNSD_QTY || '|"' || De_Dub_rec.VER_DOSAGE || '"');
END LOOP;
DE_REC_COUNT :=0;
ELSE
DE_REC_COUNT :=1;
END IF;
END DE_DUP_PROC;
/
I'm getting this error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
58/44 PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one
of the following:
. ( * @ % & - + / at loop mod remainder rem ..
<an exponent (**)> || multiset
The symbol ". was inserted before "IMMEDIATE" to continue.
There are two errors in your code. The first one is the compiler error you get. You can't use an execute immediate in a for loop (which is clearly documented in the manual)
You need to open a cursor and then loop over the cursor. So instead of
FOR De_Dub_rec IN EXECUTE IMMEDIATE DE_DUB_SQL_STATMNT
you need to use something like this:
OPEN De_Dub_cursor FOR DE_DUB_SQL_STATMNT;
LOOP
FETCH De_Dub_cursor INTO de_dub_cursor_record;
EXIT WHEN cv%NOTFOUND;
... do your stuff here
END LOOP;
Of course you will need to declare the cursor De_Dub_cursor and the record variable de_dub_cursor_record. Note that the record variables needs to be defined with all columns that your result returns (which essentially requires a new TYPE to be defined if I'm not mistaken)
The second error you have won't show up until you run the code. You have an INTO variable clause inside your SQL string for the first EXECUTE IMMEDIATE. This will not work. The into clause can not be used like that. You need to remove the into DE_REC_COUNT part from the string literal and use the INTO clause as an option to the execute immediate statement. Something like this:
EXECUTE IMMEDIATE 'SELECT count(*) FROM ....'
INTO DE_REC_COUNT;
Unrelated to your problems, but the select ... from dual can be replaced with a simple assignment.
So instead of
select replace(WYYYYNNN,'W','') into YYYYNNN from dual;
use
YYYYNNN := replace(WYYYYNNN,'W','');
or instead of:
SELECT PS_GET_PROC_PD(SUPPLIER_CD,EXT_PHARMA_ID,YYYYNNN) into SEQ_NO_SHO FROM DUAL;
select 'LRXIE'||FLAG_VALUE||'10_'||SUPPLIER_CD||'_'||EXT_PHARMA_ID||'_'||WYYYYNNN||'_'||SEQ_NO_SHO||'_'||DE_REC_COUNT||'.TXT' into DE_DUB_OUTPUT_FILE from dual;
use
SEQ_NO_SHO := PS_GET_PROC_PD(SUPPLIER_CD,EXT_PHARMA_ID,YYYYNNN);
DE_DUB_OUTPUT_FILE := 'LRXIE'||FLAG_VALUE||'10_'||SUPPLIER_CD||'_'||EXT_PHARMA_ID||'_'||WYYYYNNN||'_'||SEQ_NO_SHO||'_'||DE_REC_COUNT||'.TXT';