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

<erich.keane_at_verizon.net> wrote in message news:1160746512.076366.191170_at_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.
>

1. Oracle!=MS SQL Server.
2. Usually a bad idea to have mixed case object names. Legal, but causes problems.
3. Read the docs and the examples in the docs. You have to issue execute immediate for each statement.
4. Usually a bad idea to do DDL from an application. (see #1) If it is just a set up thing that's fine (though most people just run a sql+ script, simple direct.)

Jim Received on Fri Oct 13 2006 - 09:17:57 CDT