DROP TABLE in a script failure

Discussion of open issues, suggestions and bugs regarding database management and development tools for Oracle
Post Reply
azuckerman
Posts: 4
Joined: Mon 13 Oct 2014 19:11

DROP TABLE in a script failure

Post by azuckerman » Mon 20 Oct 2014 16:17

I have a script that executes correctly in TOAD, but throws PLS-00103: Encountered the symbol "DROP" on the DROP TABLE line:

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,'abc@noreply.com');
INSERT INTO myTempTable (ACCT_NUM, COMM_ID, EMAIL_ADDRESS) VALUES ('0039921',13106,'def@noreply.com');
INSERT INTO myTempTable (ACCT_NUM, COMM_ID, EMAIL_ADDRESS) VALUES ('0677006',1091330,'ghi@noreply.com');
-- 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;
The output from the execution is:

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]
The last item from the output is where I highlight the DROP TABLE statement, and perform an Execute Statement.

Server version information:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Client version information:
Windows 7 64bit
11.2.0
64bit

dbForge Studio for Oracle:
Professional Edition
Version 3.6.389

alexa
Devart Team
Posts: 2430
Joined: Fri 24 Jun 2011 14:17

Re: DROP TABLE in a script failure

Post by alexa » Tue 21 Oct 2014 08:16

The problem happens due to missing '/' before DROP. Though, we will fix it in one of the next product builds.

azuckerman
Posts: 4
Joined: Mon 13 Oct 2014 19:11

Re: DROP TABLE in a script failure

Post by azuckerman » Wed 22 Oct 2014 00:04

So until then, if I have an anonymous PL/SQL block, terminate it with a trailing slash before any further non PL/SQL DML?

alexa
Devart Team
Posts: 2430
Joined: Fri 24 Jun 2011 14:17

Re: DROP TABLE in a script failure

Post by alexa » Wed 22 Oct 2014 07:57

That's right.

Post Reply