CODEWITHSUNDEEP
oracle11gcursor
Mohsin Ali
Mohsin Ali

Reputation: 112

How to use current cursor value in Select query with in procedure

I am trying to use c_msisdn value in cursor for select query but it gives error of No data found, However if I supply value myself like WHERE MSISDN = '315XXX' instead of WHERE MSISDN = c_msisdn result is returned. How can I use cursor value inside select.

DECLARE
c_msisdn SSM_SMSCDATA.MSISDN%type;
c_status SSM_SMSCDATA.STATUS%type;
c_promo_id SSM_SMSCDATA.PROMO_ID%type;
view_all_status char(50) := '';
unsub_all_status char(50) := '';
services_subscribed char(400) := '';
CURSOR c_smscdata is SELECT MSISDN, STATUS, PROMO_ID FROM SSM_SMSCDATA; 

BEGIN 
OPEN c_smscdata; 
LOOP 
FETCH c_smscdata into c_msisdn, c_status, c_promo_id;

SELECT SUBSCRIPTION_ID into services_subscribed FROM (SELECT LISTAGG(SUBSCRIPTION_ID, '-') WITHIN GROUP (ORDER BY MSISDN) AS SUBSCRIPTION_ID
FROM   SINGLESUBSCRIPTION
WHERE MSISDN = c_msisdn
GROUP BY MSISDN);

IF c_promo_id = '2' THEN 
  view_all_status := 'View All';
   ELSE view_all_status := 'Unsub All';
END IF;

IF c_status = 3 THEN 
  unsub_all_status := 'View Successful';
   ELSE unsub_all_status := 'Unsuccessful';
END IF;


INSERT INTO SSM_DAILY_REPORT (msisdn,view_all,unsub_all,SERVICES)
VALUES (c_msisdn,view_all_status,unsub_all_status,services_subscribed);

--dbms_output.put_line(c_msisdn || ' ' || c_status || ' ' || c_promo_id); 

EXIT 
WHEN c_smscdata%notfound; 
END LOOP; 
CLOSE c_smscdata; 

END;

Upvotes: 0

Views: 61

Answers (1)

Boneist
Boneist

Reputation: 23588

The most likely reason why you're getting the NO_DATA_FOUND exception is because you have msisdn values in your ssm_smscdata table that aren't present in your singlesubscription table.

If I were you, I wouldn't bother using a cursor for loop. Instead, I'd do it all in a single INSERT statement, like so:

INSERT INTO ssm_daily_report (msisdn, view_all, unsub_all, services)
SELECT scs.msisdn,
       CASE WHEN scs.promo_id = '2' THEN 'View All';
            ELSE 'Unsub All'
       END view_all_status,
       CASE WHEN scs.status = 3 THEN 'View Successful';
            ELSE 'Unsuccessful'
       END unsub_all_status,
       sss.services_subscribed
FROM   ssmsmscdata scs 
       INNER JOIN (SELECT msisdn,
                          LISTAGG(subscription_id, '-') WITHIN GROUP (ORDER BY msisdn) AS services_subscribed
                   FROM   singlesubscription
                   GROUP BY msisdn) sss ON sss.msisdn = scs.msisdn;

That way, you avoid reinventing the nested loop join (freeing up Oracle to choose the join type it thinks is best to use, which may or may not be nested loops). You also avoid all the context switching and row-by-row processing that your cursor loop involves, and you get Oracle to do all the work in one fell swoop.

Also, by doing the inner join, you avoid the thorny issue of rows that appear in the ssm_smscdata table but not the singlesubscription, as those rows won't be returned. Should you need those rows to be returned as well, you would need to convert the INNER JOIN in the query above into an OUTER JOIN.

Upvotes: 1

Related Questions