Re: Can't create table in procedure

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 - 16:41:29 CET