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

From: <erich.keane_at_verizon.net>
Date: 13 Oct 2006 06:35:12 -0700
Message-ID: <1160746512.076366.191170@b28g2000cwb.googlegroups.com>

fitzjarrell_at_cox.net wrote:
> erich.keane_at_verizon.net wrote:
> > The server is on my computer, and will never ever be used by anyone but
> > me. These databases will NEVER even get into anything more than that.
> > I am testing an Oracle interface layer, and created it as the System
> > account. When it comes time to actually do something, a DBA will be
> > designing the data structure.
> >
> > It is easier to just run the oracle as system, so that I don't have to
> > deal with other things at the moment.
> >
> > If you look, the table is even named 'test', which should have given a
> > hint.
> >
> > DA Morgan wrote:
> > > erich.keane_at_verizon.net wrote:
> > > > 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.
> > >
> > > Ease of what? Ease of trashing the system. If you are being paid ... you
> > > are being paid to do a good job. Please reconsider.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damorgan_at_x.washington.edu
> > > (replace x with u to respond)
> > > Puget Sound Oracle Users Group
> > > www.psoug.org

>

> It doesn't matter if all this database stores is your grandmother's
> knitting patterns, using the SYSTEM user is an extremely bad idea as
> these objects are stored in, surprise of surprises, the SYSTEM
> tablespace, a tablespace reserved for SYS and SYSTEM owned objects
> necessary for the proper operation of your database. Please do not
> attempt to interchange 'easy' with 'lazy', as the latter is precisely
> your situation. Creating a TEST user and associated tablespace is not
> a difficult task:

>

> SQL> create tablespace test datafile 'd:\my\this\is\easy\test.dbf' size
> 500M;

>

> Tablespace created.

>

> SQL> create user test identified by hokeypassword default tablespace
> test temporary tablespace temp quota unlimited on test;

>

> User created.

>

> SQL> grant create session, create table to test;

>

> Grant succeeded.

>

> SQL> connect test/hokeypassword
> Connected.
> SQL> create table "OpenBOXTest" ("ObxTestCol2" number default 0,
> "OtherStuffHere" varchar2(40));

>

> Table created.

>


> SQL>

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

>

> Table altered.

>


> SQL>

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

>

> Table altered.

>


> SQL>

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

>

> Table altered.

>


> SQL>

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

>

> Table altered.

>


> SQL>

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

>

> Table altered.

>

> SQL>

>

> The SYSTEM tablespace is never a proper location for user objects.
> Stop using it as a repository for every testbed object you could
> possibly think to create.

Ok, I have agreed that it is a bad idea from the start. I did it because I am lazy! The database is meaningless, and stores Zero data. >From now on, I will store it as a separate user. You win on that. Can we please get to the issue I am having?

My issue still remains, I am attempting to execute through ODP.Net a single event with the database, and am getting errors still. I followed the advice above to try a dynamic query, and am getting the illegal character (ORA-00911) error:

BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE "TESTUSER"."OpenBOXTest" DROP

CONSTRAINT "SYS_C0013604";
ALTER TABLE "OpenBOXTest" DROP COLUMN "ObxTestCol2" ;
ALTER TABLE "OpenBOXTest"  ADD ("ObxTestCol3" integer  DEFAULT 0 );

ALTER TABLE "TESTUSER"."OpenBOXTest" ADD "PKConstraint_OBXTest" primary key ("ObxTestCol1" ) ';
END; If I remove the semi-colons, I get:
ORA-01735: invalid ALTER TABLE option
ORA-06512: at line 2

I was able to get this to work correctly in MSSql by just combining the different queries with a semicolon, and sending it through the Sql Data Provider. Received on Fri Oct 13 2006 - 08:35:12 CDT