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

BTW, I forgot to mention, I am executing this through a ODP.Net app.

> 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:

Table is owned by system for ease at the moment. This is on a simple development database, so the owner is set by the configuration. Note that none of the hiarchy will make it to production at all.

> ALTER TABLE "SYSTEM"."OpenBOXTest" DROP PRIMARY KEY; <=== not valid
> Alter table "OpenBOXText" drop constraint <constraint name here>;

I tried it with your setup as well previously, though our Oracle 9i server works with mine as well.

> 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

Oops, application issue, thanks for pointing that out!

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

Noted, but again, our oracle server runs this individually with no sissue.

> 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

Thanks for your help overall, but for a better example of what is going on:

An application is attempting to generate a bunch of code to make alterations to a database (again, the current one is just a dev. environment). The generated code (still in development, I come from a MSSQL, so the slight differences in syntax is throwing me) works perfectly individually, but the BEGIN... END; doesn't seem to be allowing it to process through ODP.Net as it has been implied through other sites.

I am sorry if I missed your point, but thank you for the pointers, I have noted them in our generation code.

Thanks! Received on Thu Oct 12 2006 - 16:03:46 CDT