Oracle: Cursor within Cursor

Question

I am failry new to PL/SQL and need a quick help with this.The whole cursor deal is unnerving but I am getting used to it. Please focus only on cursors 1) cur_cheese_proprietary and 2)cur_cheese_mms.I am trying to retrieve those rows from cur_cheese_mms which have the same cheeseNumber as the cheeseNumber in current row of cursor cur_cheese_proprietary while iteration. I would like to to have a CSV string that will look like following when opened in excel:

I would also like to list all those rows which exist only in cur_cheese_proprietary or in cur_cheese_mms ( the code for which is entirely mising in following block of pl/sql)

Sample file: Begin

col1 col2 col3 ......(from cur_cheese_proprietary )

'CHOM DATA'

col1 col2 col3 (from cur_cheese_mms)

ROWS ONLY PRESENT IN cur_cheese_proprietary col1 col2 col3

ROWS ONLY PRESENT IN cur_cheese_mms col1 col2 col3

Sample File: End

Without further ado, this is what I have currently.

Question 1) Where and how do I populate v_cheeseNumber from the ROWTYPE variable cur_rec_cheese cur_cheese_proprietary%ROWTYPE;? Pleas see code below.

Question 2) How do I find non-matching rows (based only on column v_cheeseNumber in either table) ?

Please answer only question 1 if you have time. I may be able to figure out question 2 after reading more pl/sql syntax. I guess outer join would do the trick-- means more cursors !

CREATE OR REPLACE PROCEDURE sp_monthly_status
IS
   v_from        VARCHAR2 (80);
   v_subject     VARCHAR2 (80);
   crlf          VARCHAR2 (2);
   v_mail_host   VARCHAR2 (80);
   v_event_csv   VARCHAR2 (32767);
   v_cheese_csv   VARCHAR2 (32767);
   v_user_csv    VARCHAR2 (32767);
   v_recipient   VARCHAR2 (2000);
   v_rcpts       VARCHAR2 (2000);
   v_to          VARCHAR2 (100);
   v_error       VARCHAR2 (2000);
   n_event_count NUMBER;
   n_cheese_count NUMBER;
   n_user_count  NUMBER;
   n_pos         NUMBER;
   v_mail_conn   UTL_SMTP.connection;
   v_cheeseNumber VARCHAR2 (256);

   CURSOR cur_event_log IS
     SELECT REPLACE (l.evt_name, ',', ' ') || ',' ||
            REPLACE (l.evt_params, ',', ' ') || ',' ||
            l.evt_user || ',' || 
            TO_CHAR (l.evt_start_time, 'MM/DD/YYYY HH24:MI:SS') evt_info
       FROM chom_event_log l
      WHERE UPPER (l.evt_type) = 'REPORT'
        AND l.evt_start_time > ADD_MONTHS (SYSDATE, -1);

   CURSOR cur_cheese_proprietary IS     
     SELECT NVL (TO_CHAR (p.data_name), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.external_data_id), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.block_number), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.TikTok), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.TikTok_area), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.cheese_number), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.sbu), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.comments), '<blank>') || ',' ||
            NVL (TO_CHAR (le.company_name), '<blank>') || ',' ||
            NVL (TO_CHAR (le.TikTok_percent), '<blank>')
            cheese_prop_data
       FROM chom_cheese_proprietary ls, chom_lessee_proprietary le, chom_data p
      WHERE ls.cheese_lessee_key = le.cheese_lessee_key
        AND ls.external_data_id = p.external_data_id
        AND ls.sbu = 'chom DW' 
   ORDER BY ls.sbu, p.data_name, block_number;

    CURSOR cur_cheese_mms IS
     SELECT NVL (TO_CHAR (p.data_name), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.external_data_id), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.block_number), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.TikTok), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.TikTok_area), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.cheese_number), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.sbu), '<blank>') || ',' ||
            NVL (TO_CHAR (ls.comments), '<blank>') || ',' ||
            NVL (TO_CHAR (le.company_name), '<blank>') || ',' ||
            NVL (TO_CHAR (gles.TikTok_percent), '<blank>')
            cheese_mms_data
       FROM chom_cheese_proprietary ls, chom_lessee_proprietary le, chom_data p, explore.chom_cheese gl, chom_lessee gles
      WHERE ls.cheese_lessee_key = le.cheese_lessee_key
        AND ls.external_data_id = p.external_data_id
        --AND gl.TikTok(+) = ls.TikTok
        AND gl.TikTok(+) = ls.TikTok
        --AND gl.cheese_NUMBER = gles.cheese_NUMBER
        AND gles.cheese_NUMBER=v_cheeseNumber
        AND ls.sbu = 'chom DW' 
   ORDER BY ls.sbu, p.data_name, ls.block_number;



   CURSOR cur_user_list IS
     SELECT DISTINCT l.evt_user
                FROM chom_event_log l
               WHERE l.evt_start_time > ADD_MONTHS (SYSDATE, -1)
                 AND UPPER (l.evt_name) = 'LOGIN INFORMATION';

   cur_rec       cur_event_log%ROWTYPE;
   cur_rec_cheese cur_cheese_proprietary%ROWTYPE;
   cur_rec_mms cur_cheese_mms%ROWTYPE;
   cur_rec_user  cur_user_list%ROWTYPE;


   -- Adds recipients for each comma separated values
   PROCEDURE sp_chom_add_recipient (p_rcpts VARCHAR2)
   IS
   BEGIN
      v_rcpts := p_rcpts;

      LOOP
         IF v_rcpts IS NULL
         THEN
            EXIT;
         END IF;

         n_pos := INSTR (v_rcpts, ',');

         IF n_pos > 0
         THEN
            v_to := TRIM (SUBSTR (v_rcpts, 1, n_pos - 1));
            v_rcpts := SUBSTR (v_rcpts, n_pos + 1);

            IF LENGTH (v_to) > 0
            THEN
               UTL_SMTP.rcpt (v_mail_conn, v_to);
            END IF;
         ELSE
            v_to := TRIM (v_rcpts);
            EXIT;
         END IF;
      END LOOP;

      IF LENGTH (v_to) > 0
      THEN
         UTL_SMTP.rcpt (v_mail_conn, v_to);
      END IF;
   END;

-- Begin of main procedure   
BEGIN

   -- Initiallize parameters
   v_from := '[email protected]';
   v_subject := 'chom Monthly Report';
   crlf := CHR (13) || CHR (10);
   n_event_count := 0;
   n_cheese_count := 0;
   n_user_count := 0;

   SELECT value INTO v_mail_host
     FROM chom_config_param
    WHERE UPPER (name) = 'SMTP_MAIL_HOST';

    SELECT value INTO v_recipient
     FROM chom_config_param
    WHERE UPPER (name) = 'MONTHLY_REPORT_MAIL_LIST';

   -- Header row for CSV file
   v_event_csv := 'Name,Description,User,Date Time' || crlf;
   v_cheese_csv := 'data Name,data ID,Block Number,Star Block,Protraction Area,cheese Number,SBU,Comments,Company Name,Lessee Percent' || crlf;
   v_user_csv := 'User CAI' || crlf;

   IF cur_event_log%ISOPEN THEN
      CLOSE cur_event_log;
      OPEN cur_event_log;
   ELSE
      OPEN cur_event_log;
   END IF;

   -- Create data in CSV format for chom event
   LOOP
      FETCH cur_event_log INTO cur_rec;
      EXIT WHEN cur_event_log%NOTFOUND;
      n_event_count := n_event_count + 1;
      v_event_csv := v_event_csv || cur_rec.evt_info || crlf;
   END LOOP;
   CLOSE cur_event_log;

   IF cur_cheese_proprietary%ISOPEN THEN
      CLOSE cur_cheese_proprietary;
      OPEN cur_cheese_proprietary;
   ELSE
      OPEN cur_cheese_proprietary;
   END IF;   


   -- Create data in CSV format 
   LOOP
      FETCH cur_cheese_proprietary INTO cur_rec_cheese;
      EXIT WHEN cur_cheese_proprietary%NOTFOUND;     
      n_cheese_count := n_cheese_count + 1;
      v_cheese_csv := v_cheese_csv || cur_rec_cheese.cheese_prop_data || crlf;
      v_cheese_csv := v_cheese_csv || 'CHOM DATA' || crlf;

      IF cur_cheese_mms%ISOPEN THEN
      CLOSE cur_cheese_mms;
      OPEN cur_cheese_mms;
   ELSE
      OPEN cur_cheese_mms;
   END IF;
   LOOP
      FETCH cur_cheese_mms INTO cur_rec_mms;
      EXIT WHEN cur_cheese_mms%NOTFOUND;
      n_cheese_count := n_cheese_count + 1;      
      v_cheese_csv := v_cheese_csv || cur_rec_mms.cheese_mms_data || crlf;
      END LOOP;
   END LOOP;   

   CLOSE cur_cheese_proprietary;   


   IF cur_user_list%ISOPEN THEN
      CLOSE cur_user_list;
      OPEN cur_user_list;
   ELSE
      OPEN cur_user_list;
   END IF;

   -- Create data in CSV format for USER list
   LOOP
      FETCH cur_user_list INTO cur_rec_user;
      EXIT WHEN cur_user_list%NOTFOUND;
      n_user_count := n_user_count + 1;
      v_user_csv := v_user_csv || cur_rec_user.evt_user || crlf;
   END LOOP;
   CLOSE cur_user_list;

   v_mail_conn := UTL_SMTP.open_connection (v_mail_host, 25);
   UTL_SMTP.helo (v_mail_conn, v_mail_host);
   UTL_SMTP.mail (v_mail_conn, v_from);

   -- email recipient
   sp_chom_add_recipient(v_recipient);

   UTL_SMTP.OPEN_DATA(v_mail_conn);
   -- Use MIME mail header
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Date: ' || TO_CHAR (SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'From: ' || v_from || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Subject: ' || v_subject || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'To: ' || v_recipient || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'MIME-Version: 1.0' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Type: multipart/mixed; boundary="chom_SEC_BOUND"' || crlf || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'This is multipart message' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, '--chom_SEC_BOUND' || crlf);

   -- Message body 
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Type: text/plain;' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Transfer_Encoding: 7bit' || crlf || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Hello User,' || crlf);
   IF n_event_count = 0 AND n_cheese_count = 0 AND n_user_count = 0 THEN
       UTL_SMTP.WRITE_DATA(v_mail_conn, '   No information available.' || crlf);
   ELSE
      UTL_SMTP.WRITE_DATA(v_mail_conn, '   Attached pcheese find chom data .' || crlf);
   END IF;
   UTL_SMTP.WRITE_DATA(v_mail_conn, ' ' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Thanks' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, ' Support Team' || crlf);

   -- Attachment section chom Event log
   UTL_SMTP.WRITE_DATA(v_mail_conn, '--chom_SEC_BOUND' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Type: text/plain;' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, ' name="excel.csv"' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Transfer_Encoding: 8bit' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Disposition: attachment;' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, ' filename="chom_Monthly_Report.csv"' || crlf || crlf);
   IF n_event_count > 0 THEN    
      -- Start CSV content of attachment
      UTL_SMTP.WRITE_DATA(v_mail_conn, v_event_csv);
      -- End CSV content of attachment
   ELSE
      UTL_SMTP.WRITE_DATA(v_mail_conn, 'No information available.' || crlf);
   END IF;

   -- Attachment section cheese proprietary
   UTL_SMTP.WRITE_DATA(v_mail_conn, '--chom_SEC_BOUND' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Type: text/plain;' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, ' name="excel.csv"' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Transfer_Encoding: 8bit' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Disposition: attachment;' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, ' filename="cheese_Proprietary.csv"' || crlf || crlf);
   IF n_cheese_count > 0 THEN
      -- Start CSV content of attachment
      UTL_SMTP.WRITE_DATA(v_mail_conn, v_cheese_csv);
      -- End CSV content of attachment
   ELSE
      UTL_SMTP.WRITE_DATA(v_mail_conn, 'No information available.' || crlf);
   END IF;

   -- Attachment section user access list
   UTL_SMTP.WRITE_DATA(v_mail_conn, '--chom_SEC_BOUND' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Type: text/plain;' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, ' name="excel.csv"' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Transfer_Encoding: 8bit' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, 'Content-Disposition: attachment;' || crlf);
   UTL_SMTP.WRITE_DATA(v_mail_conn, ' filename="User_Access_List.csv"' || crlf || crlf);
   IF n_cheese_count > 0 THEN
      -- Start CSV content of attachment
      UTL_SMTP.WRITE_DATA(v_mail_conn, v_user_csv);
      -- End CSV content of attachment
   ELSE
      UTL_SMTP.WRITE_DATA(v_mail_conn, 'No information available.' || crlf);
   END IF;

   -- End MIME mail
   UTL_SMTP.WRITE_DATA(v_mail_conn, crlf || crlf || '--chom_SEC_BOUND--');
   UTL_SMTP.CLOSE_DATA(v_mail_conn);
   UTL_SMTP.QUIT (v_mail_conn);

   sp_chom_email_status('Monthly report sent successfully.');

EXCEPTION
   WHEN OTHERS
   THEN
      v_error := TO_CHAR (SQLCODE) || ': ' || SQLERRM;

      INSERT INTO chom_event_log
                  (evt_id, evt_source, evt_type,
                   evt_name, evt_user, evt_start_time, evt_end_time,
                   evt_params
                  )
           VALUES (chom_seq_evt_id.NEXTVAL, 'SP_chom_EMAIL_STATUS', 'Error',
                   'SP_chom_EMAIL_STATUS', 'JOB', SYSDATE, SYSDATE,
                   v_error
                  );

      sp_chom_email_status('Error in sending monthly report.' ||
            crlf || SQLCODE || ': ' || SQLERRM);
END sp_chom_monthly_status
/

Answer 1

I don't quite understand question 1, but I will try...

The cur_cheese_mns cursor uses v_cheeseNumber. Therefore, v_cheeseNumber must be assigned a value before the cur_cheese_mns cursor is opened.

You claim that this value is taken from cur_rec_cheese which is fetched from the cur_cheese_proprietary cursor. The cur_cheese_proprietary cursor does not have any column that appears to contain your cheeseNumber. You will need to modify the cursor to return this column.

-- Create data in CSV format 
LOOP
   FETCH cur_cheese_proprietary INTO cur_rec_cheese;
   EXIT WHEN cur_cheese_proprietary%NOTFOUND;     
   n_cheese_count := n_cheese_count + 1;
   v_cheese_csv := v_cheese_csv || cur_rec_cheese.cheese_prop_data || crlf;
   v_cheese_csv := v_cheese_csv || 'CHOM DATA' || crlf; 

   /*
    * You will have to modify the cur_rec_cheese cursor to select the 
    * cheese number.
    */
   v_cheeseNumber := cur_rec_cheese.cheese_number;

   IF cur_cheese_mms%ISOPEN THEN
       CLOSE cur_cheese_mms;
       OPEN cur_cheese_mms;
   ELSE
       OPEN cur_cheese_mms;
   END IF;

   LOOP
       FETCH cur_cheese_mms INTO cur_rec_mms;
       EXIT WHEN cur_cheese_mms%NOTFOUND;
       n_cheese_count := n_cheese_count + 1;      
       v_cheese_csv := v_cheese_csv || cur_rec_mms.cheese_mms_data || crlf;
   END LOOP;
END LOOP;