# Database Discussions > Oracle >  CREATE Table in a stored procedure

## juvamine

Hi,

Excuse me but my English is quite bad, I wil try to explain my problem.

I want to create a table in a stored procedure, apparantly it is impossible :/

Anybody would have a solution for me ?
Thanks
juvamine

----------


## jkoopmann

Yes, you can create a table through a procedure. Here is some quick output to show.
1. describe that the table does not exist.
2. create a procedure
3. execute the procedure
4. describe that the table was created.


25-FEB-05 : SYS@k10gutf8 AS SYSDBA > desc testtable
ERROR:
ORA-04043: object testtable does not exist


25-FEB-05 : SYS@k10gutf8 AS SYSDBA > create PROCEDURE createta
  2
  3  BEGIN
  4      EXECUTE IMMEDIATE
  5      'CREATE TABLE testtable  '||
  6      '      (mycolumn          NUMBER, '||
  7      '       CONSTRAINT pk_testtable PRIMARY KEY (mycolumn
  8
  9  END createtable;
 10  /

Procedure created.

25-FEB-05 : SYS@k10gutf8 AS SYSDBA > exec createtable

PL/SQL procedure successfully completed.

25-FEB-05 : SYS@k10gutf8 AS SYSDBA > desc testtable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 MYCOLUMN                                  NOT NULL NUMBER

----------


## juvamine

Thanks a lot !!!

execute command, is able to execute all sql query ?

EXECUTE COMMAND 'my sql query';

yes ?

thx again

juvamine

----------


## pvzweden

The "execute immediate" call is able to execute a string of SQL with a maximum of 2000 chars if i remember correctly. When you use ;'s in the statement its considered by the engine to be a block of PL/SQL instead of a mere query so it then needs to have a begin end; stucture.

----------


## jkoopmann

i have used EXECUTE IMMEIDATE command with a LONG data type to create not only tables but procedures, packages, and package bodies. this data type stores up to 2GB and the last package i created was over 13,000 characters without spaces and over 17,000 characters with spaces. 

so i think it just depends on the data type being used. then again i have concatenated many LONG data types together without any problems.

----------


## pvzweden

Hmmm... you could be right then. Always use varchar2 to store the SQL string and most of the time the string stays small. I  use it a lot in normal procedures with the conveniance of binding the vars. I used to concatenate everything and got sometimes an error saying SQL string to large. But then again could be just me selecting a to small datatype.

----------


## juvamine

maybe your 2 versions of Oracle ar different...that explain the difference in the weight of the varchar.
In my case, it's OK  :Smilie: )

----------

