Re: How to create table by store procedure in Oracle9i

> 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;
A few notes:

1. Probably, you'll want to grant the right to create tables to the user that executes/creates the procedure:

   grant create table to ...

2) Why don't you use execute immediate, it looks a lot nicer

   create or replace procedure new_myproc is    begin
     execute immediate 'create table atest(userid number(14))';    end;
   /

3) Probably the most important point:

   Use temporary tables instead.

   create global temporary table atest (      userid number(14));

   Almost always if someone wants to dynamically create tables    temporary tables will do what they need.

hth
Rene

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html