CODEWITHSUNDEEP
plsqloracle11g
Dzman
Dzman

Reputation: 13

Update multiple records using cursor in plsql

I am fairly new to PLSQL so please have some patience with me.

I am trying to dynamically substitute field values into a string and display the values using a loop and cursor. My code is as follows:

DECLARE
  MSG VARCHAR2(500);
  SMS_TYPE VARCHAR(20);
  DDACDATE DATE;
  CUR SMSTYPE%ROWTYPE;

  CURSOR C_SMS IS 
  SELECT SMS_ID,COL_01,COL_02,COL_03,COL_04
  FROM SMS;-- FOR UPDATE OF SMS_CONTENT NOWAIT;
  REC_SMS C_SMS%ROWTYPE;
BEGIN

  SELECT * INTO CUR FROM SMSTYPE WHERE SMSTYPE_NAME = 'RENEWAL';

  MSG := CUR.SMSTYPE_MSG;

  OPEN C_SMS;
  LOOP
    FETCH C_SMS INTO REC_SMS;
    EXIT WHEN C_SMS%NOTFOUND; -- cursor attribute to exit when no rows found to fetch.
    MSG := REPLACE(MSG,'|INSURED|',REC_SMS.COL_01);
    MSG := REPLACE(MSG,'|PRODUCT|',REC_SMS.COL_02);
    MSG := REPLACE(MSG,'|POL_NO|',REC_SMS.COL_03);
    MSG := REPLACE(MSG,'|POL_TO_DT|',REC_SMS.COL_04);
    --UPDATE SMS SET SMS_CONTENT = MSG WHERE CURRENT OF C_SMS; --UPDATE DATABASE
    DBMS_OUTPUT.PUT_LINE(MSG);
  END LOOP;
  CLOSE C_SMS;

END;

MSG value is a string in the form '|INSURED||PRODUCT||POL_NO||POL_TO_DT|'

My problem is, the output is being repeated with the same values for the duration of the loop i.e. the last record in the cursor. I have commented out the update statement because I am getting the same values repeated for all records in the target table.

How can I ensure that each output value is different for the current record in the cursor? i.e. if at first iteration MSG is 'ELLY SOAP 123 12-OCT-2014' at next iteration, MSG should be 'DAVE TOOTHBRUSH 342 13-OCT-2014'.

If i try to output the returned values using 

 DBMS_OUTPUT.PUT_LINE(REC_SMS.COL_01)

instead of

 DBMS_OUTPUT.PUT_LINE(MSG);

I get all my unique values i.e. 

ELLY
DAVE

Thank you in anticipation.

Upvotes: 1

Views: 2346

Answers (1)

Joel
Joel

Reputation: 196

I think the issue is that you are overwritting your tokens (like |INSURED|, etc) in the MSG string during the firt iteration.

When the second loop occurs MSG is no longer '|INSURED||PRODUCT||POL_NO||POL_TO_DT|' but 'ELLY SOAP 123 12-OCT-2014'

You eiter need to reset MSG to CUR.SMSTYPE_MSG for each iteration of the loop or you need to use a temp variable.

Try something like:

LOOP
    FETCH C_SMS INTO REC_SMS;
    EXIT WHEN C_SMS%NOTFOUND; -- cursor attribute to exit when no rows found to fetch.

    MSG := CUR.SMSTYPE_MSG;

    MSG := REPLACE(MSG,'|INSURED|',REC_SMS.COL_01);
    MSG := REPLACE(MSG,'|PRODUCT|',REC_SMS.COL_02);
    MSG := REPLACE(MSG,'|POL_NO|',REC_SMS.COL_03);
    MSG := REPLACE(MSG,'|POL_TO_DT|',REC_SMS.COL_04);
    --UPDATE SMS SET SMS_CONTENT = MSG WHERE CURRENT OF C_SMS; --UPDATE DATABASE
    DBMS_OUTPUT.PUT_LINE(MSG);
END LOOP;

Upvotes: 1

Related Questions