Re: Begin... END; gives odd error:

erich.keane_at_verizon.net wrote:
> Ok, Ive researched this for what seems like forever, but I still get
> the same error.
> I first tried it without the Begin and End, and I got Oracle Error
> 00911 (as expected). Everywhere I looked said to add Begin before it,
> and End; after it, yet I get that error.
>
> Please help!
>
>
> Here is the command, and error text, I know the query does not entirely
> make sense, but it is generated that way for a reason:
>
> BEGIN
> ALTER TABLE "SYSTEM"."OpenBOXTest" DROP PRIMARY KEY;
> ALTER TABLE "OpenBOXTest" DROP COLUMN "ObxTestCol2" ;
> ALTER TABLE "OpenBOXTest" ADD ("ObxTestCol3" integer DEFAULT ''0'' );
> ALTER TABLE "SYSTEM"."OpenBOXTest" ADD PRIMARY KEY ("ObxTestCol1" );
> END;
> Error report:
> ORA-06550: line 2, column 1:
> PLS-00103: Encountered the symbol "ALTER" when expecting one of the
> following:
>
> begin case declare exit for goto if loop mod null pragma
> raise return select update while with <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall merge
> <a single-quoted SQL string> pipe
> <an alternatively-quoted SQL string>
> The symbol "lock was inserted before "ALTER" to continue.
> ORA-06550: line 2, column 36:
> PLS-00103: Encountered the symbol "DROP" when expecting one of the
> following:
>
> . , @ in <an identifier>
> <a double-quoted delimited-identifier> partition subpartition

You cannot directly perform DDL within a PL/SQL block. Also, WHY is this table owned by SYSTEM? Looking at the ALTER TABLE statements I find:

ALTER TABLE "SYSTEM"."OpenBOXTest" DROP PRIMARY KEY; <=== not valid

Alter table "OpenBOXText" drop constraint <constraint name here>;

ALTER TABLE "OpenBOXTest" DROP COLUMN "ObxTestCol2" ; <== should work unless this is your pk column

ALTER TABLE "OpenBOXTest" ADD ("ObxTestCol3" integer DEFAULT ''0'' );  <=== no "" around the actual default value

ALTER TABLE "SYSTEM"."OpenBOXTest" ADD PRIMARY KEY ("ObxTestCol1" ); <=== invalid syntax

As an example of working syntax:

SQL> create table "OpenBOXTest" ("ObxTestCol2" number default 0, "OtherStuffHere" varchar2(40));

Table created.

SQL> alter table "OpenBOXTest" add constraint "OpenBOXTest_PK" primary key("ObxTestCol2");

Table altered.

SQL> alter table "OpenBOXTest" drop constraint "OpenBOXTest_PK";

Table altered.

SQL> ALTER TABLE "OpenBOXTest" DROP COLUMN "ObxTestCol2" ;

Table altered.

SQL> ALTER TABLE "OpenBOXTest" ADD ("ObxTestCol3" integer DEFAULT 0);

Table altered.

SQL> alter table "OpenBOXTest" add constraint "OpenBOXTest_PK" primary key("ObxTestCol3");

Table altered.

SQL> Never use SYSTEM to create user objects; always create a regular user account for such activity.

David Fitzjarrell Received on Thu Oct 12 2006 - 15:28:40 CDT