Re: Can't create table in procedure

From: Andrew Velichko <andrew_velichko_at_yahoo.com>
Date: Wed, 29 Nov 2000 16:41:11 -0500
Message-ID: <kKeV5.73662$Z2.999085@nnrp1.uunet.ca>

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 Wed Nov 29 2000 - 15:41:11 CST