Re: Can't create table in procedure

Keep using dynamic SQL during the rest of your procedure. The package will invalidate if you do the create table - trick to compile, and then remove the table afterwards.

-Jørgen

<sw_at_weinerfamily.org> wrote in message
news:3A267501.2B6C2203_at_weinerfamily.org...
> Ok,
>
> Now that I have a create table in a stored procedure, I find that the
> rest of the proc that refers to the table is failing because the table
> doesn't exist when the proc is compiled.
>
> Any ideas?
>
> Thanks!
>
> Andrew Velichko wrote:
>
> > Hi!
> >
> > To perform DDL statements from PL/SQL you should
> > use native dynamic SQL like
> > CREATE OR REPLACE
> > PROCEDURE x AS
> > BEGIN
> > execute immediate 'CREATE TABLE XX
> > AS SELECT * FROM transaction where transaction_id < 500';
> > END;
> > it's under Oracle8i or for previous versions use DBMS_SQL package.
> >
> > Andrew Velichko
> > Brainbench MVP for Oracle Developer 2000
> > http://www.brainbench.com
> > --------------------------------------------------------------
> >
> > <sw_at_weinerfamily.org> wrote in message
> > news:3A2575D9.CCDDF34C_at_weinerfamily.org...
> > > I don't know PL/SQL well. Can you tell me why this doesn't work?
> > > Is there a problem creating tables in procedures?
> > >
> > > CREATE OR REPLACE
> > > PROCEDURE x AS
> > > BEGIN
> > > CREATE TABLE XX
> > > AS SELECT * FROM transaction where transaction_id < 500
> > > END;
> > >
> > >
> > > The error states it didn't expect CREATE
> > >
>
Received on Thu Nov 30 2000 - 18:07:13 CST