Devart Forums

Code: Select all

CREATE TABLE myTempTable
(
   acct_num        CHAR (7) NOT NULL,
   comm_id         NUMBER NOT NULL,
   email_address   VARCHAR2 (50) NOT NULL
);

-- About 500 of these INSERT statements...
INSERT INTO myTempTable (ACCT_NUM, COMM_ID, EMAIL_ADDRESS) VALUES ('0214601',2139,'[email protected]');
INSERT INTO myTempTable (ACCT_NUM, COMM_ID, EMAIL_ADDRESS) VALUES ('0039921',13106,'[email protected]');
INSERT INTO myTempTable (ACCT_NUM, COMM_ID, EMAIL_ADDRESS) VALUES ('0677006',1091330,'[email protected]');
-- cut the rest of them for brevity

COMMIT;

DECLARE
  CURSOR UPDATE_EMAIL_ROWS
  IS
    SELECT UPD.COMM_ID,
           UPD.EMAIL_ADDRESS,
           UPD.ACCT_NUM,
           A.ACCT_TYPE_CD,
           '14' PROCESS_TYPE_CD, --Change type,
           TRUNC (SYSDATE) ACCT_ACTV_BEG_DT,
           'SOME_USER' UPDATE_USER_ID
    FROM   myTempTable UPD
           INNER JOIN ACCT_COMM AC
             ON     UPD.ACCT_NUM = AC.ACCT_NUM
                AND UPD.COMM_ID = AC.COMM_ID
           INNER JOIN COMM C ON C.COMM_ID = UPD.COMM_ID
           INNER JOIN ACCOUNT A ON AC.ACCT_NUM = A.ACCT_NUM;

  V_UPDATE_EMAIL    UPDATE_EMAIL_ROWS%ROWTYPE;
  V_SEQ_ID          MLSTN.SEQ_ID%TYPE := 1;
  V_NOTE_SEQ_ID     MLSTN_NOTE.SEQ_ID%TYPE := 1;
BEGIN
  -- Set the output buffer to a million rows.
  DBMS_OUTPUT.ENABLE (1000000);

  -- Open the cursor and merge all of the rows  into one row.
  OPEN UPDATE_EMAIL_ROWS;

  LOOP
    FETCH UPDATE_EMAIL_ROWS INTO V_UPDATE_EMAIL;

    IF (NOT (UPDATE_EMAIL_ROWS%NOTFOUND)) THEN
      DBMS_OUTPUT.PUT_LINE ('Processing Account Number: ' || V_UPDATE_EMAIL.ACCT_NUM);
      PK_FOUNDATION_ACTIVITY.PR_ADDMODIFY_ACCT_ACTV (V_UPDATE_EMAIL.ACCT_NUM,
                                                         V_UPDATE_EMAIL.PROCESS_TYPE_CD,
                                                         V_UPDATE_EMAIL.ACCT_ACTV_BEG_DT,
                                                         V_UPDATE_EMAIL.ACCT_TYPE_CD,
                                                         '3', --In Progress,
                                                         V_UPDATE_EMAIL.UPDATE_USER_ID);

      UPDATE COMM
      SET    EMAIL_ADDR = V_UPDATE_EMAIL.EMAIL_ADDRESS
      WHERE  COMM_ID = V_UPDATE_EMAIL.COMM_ID;
    END IF;

    EXIT WHEN UPDATE_EMAIL_ROWS%NOTFOUND;
  END LOOP;

  COMMIT;
END;

DROP TABLE myTempTable;

Code: Select all

...
1 row inserted [0.002s]
1 row inserted [0.002s]
Execute succeeded [0.003s]
Error (554,1): PLS-00103: Encountered the symbol "DROP" 

------------ Done: fixitscript.sql -------------
fixitscript.sql: Execute succeeded [0.246s]