Re: How to create table by store procedure in Oracle9i

From: Eric Parker <eric.parker.spamless_at_virgin.net>
Date: Tue, 8 Jul 2003 09:57:19 +0100
Message-ID: <VHvOa.6445$nP.1782@newsfep4-winn.server.ntli.net>

"SouthVN" <nphuongtt_at_yahoo.com> wrote in message news:ee28214c.0307072351.32a76068_at_posting.google.com...
> Hi All !
>
> How to create table by store procedure in Oracle9i ?
>
> Please help me !
>
>
>
> CREATE OR REPLACE procedure New_myproc IS
>
> VT_Statement varchar2(1000):='CREATE TABLE ATest (USERID NUMBER
> (14),MAKH VARCHAR2 (15),SOMAY VARCHAR2 (9),DVUID VARCHAR2 (15),TIEN
> NUMBER (14,2),KYHIEUDVU VARCHAR2 (15))';
>
> VT_cursor integer;
> VT_exe integer;
>
> begin
>
> VT_cursor:= dbms_sql.OPEN_CURSOR;
> dbms_sql.PARSE(VT_cursor,VT_Statement,2);
> VT_exe:= dbms_sql.EXECUTE(VT_cursor);
> dbms_sql.CLOSE_CURSOR(VT_cursor);
>
> END;
>
> This store had one error, but I can't find it.
> Please help me !
> Thanks you very much

SouthVN

It's always nice to know what version of Oracle you're running and OS (so in future).

Creating tables dynamically is unusual. Do you have a good reason to do this ?
Anyway -
One solution that will create your table is :

CREATE OR REPLACE procedure New_myproc IS

VT_Statement varchar2(1000):='CREATE TABLE ATest (USERID NUMBER (14),MAKH VARCHAR2 (15),SOMAY VARCHAR2 (9),DVUID VARCHAR2 (15),TIEN NUMBER (14,2),KYHIEUDVU VARCHAR2 (15))'; BEGIN EXECUTE IMMEDIATE VT_Statement;

END;
/

HTH eric Received on Tue Jul 08 2003 - 03:57:19 CDT