SET SERVEROUTPUT ON
--SPOOL &1
declare
type app is table of varchar2(1000) index by pls_integer;
app1 app;
ind1 pls_integer := 1;
app2 app;
ind2 pls_integer := 1;
l_ind3 pls_integer := 1;
l_loop_val integer;
V_COUNT_OFFER NUMBER(10);
V_COUNT_PRODUCT NUMBER(10);
V_OFFER VARCHAR2(10);
v_count_providerid number(10);
V_RESULT varchar2(2000);
app3 app;
ind4 pls_integer := 1;
BEGIN
SELECT COUNT(*) INTO V_COUNT_OFFER FROM XT_ERROR WHERE OFFER NOT IN ('ABO','FFB','LCA');
IF V_COUNT_OFFER = 0 THEN
V_OFFER := null;
END IF;
IF V_OFFER IS NULL THEN
--dbms_output.put_line('1');
for i in( SELECT
(CASE WHEN PROVIDER_ID IN (2,10,11,12,13) THEN 'LCA'
WHEN PROVIDER_ID IN (0,1) OR (PROVIDER_ID >= 20 AND PROVIDER_ID < 40 ) THEN 'FFB'
WHEN PROVIDER_ID IN (40,50,60,70,80,90,110,120,130,140,150) OR (PROVIDER_ID >= 400 AND PROVIDER_ID < 500 ) THEN 'MVNO'
END ) v_offer
FROM XT_ERROR ) LOOP
APP1(IND1) := I.V_OFFER;
--dbms_output.put_line(app1(ind1));
ind1 := ind1+1;
END LOOP;
END IF;
SELECT COUNT(*) INTO V_COUNT_PRODUCT FROM XT_ERROR WHERE PRODUIT is null;
IF V_COUNT_PRODUCT != 0 THEN
FOR I IN (SELECT DECODE(ACCESS_VARIANT,1005,'SMMO','SMMT') V_PRODUCT1 FROM XT_ERROR) LOOP
APP2(IND2) := I.V_PRODUCT1;
dbms_output.put_line(app1(ind2)||';'||app2(ind2));
ind2 := ind2+1;
END LOOP;
END IF;
FOR I IN (SELECT COUNT(1) VAL,
TO_CHAR(SYSDATE,'YYYYMMDDHHMISS') date1,TO_CHAR(TICKET_TIMESTAMP,'YYYYMMDD') date2 ,LOGICAL_NEADDRESS,PROCESSING_ERRCODE_1
FROM XT_ERROR
WHERE TICKET_TIMESTAMP BETWEEN to_date('&4 00:00:00','DD/MM/YYYY HH24:MI:SS') and to_date('&4 23:59:59','DD/MM/YYYY HH24:MI:SS')
GROUP BY
TO_CHAR(SYSDATE,'YYYYMMDDHHMISS'),
TO_CHAR(TICKET_TIMESTAMP,'YYYYMMDD'),
LOGICAL_NEADDRESS, PROCESSING_ERRCODE_1
) LOOP
APP3(IND4) := I.val||';'||'&2'||';'||'&3'||';'||i.date1||';'||i.date2||';'||i.LOGICAL_NEADDRESS||';'|| APP1(IND4)||';'||APP2(IND4)||';'||i.PROCESSING_ERRCODE_1;
dbms_output.put_line(app3(ind4));
ind4 := ind4+1;
null;
END LOOP;
END;
--show errors;
/
--SPOOL OFF;
When I am commenting red lines above, the script works fine else it says :
Error report:
ORA-01403: no data found
ORA-06512: at line 57
01403. 00000 - "no data found"
*Cause:
*Action:
For argument &2 and &3 I am passing "1" and "2"..
Please help what is the issue..?
Regards
Abhinav