can we create a table using stored procedures?

This is my question
Can we create tables using stored procedures?


I treid like this but it din't work

SQL> create procedure create_table (table_name in varchar2,column1 in varchar2,column2 in varchar2)
as
2 begin
3 create table table_name (column1 varchar2(10),column2 varchar2(10));
4 end;
5 /


when i execute the above statement its showing the following waring

" Warning: Procedure created with compilation errors. "


please help me

You might be able to do it with the DBMS_SQL package. I think you could use the PARSE and EXECUTE procedures by putting your create statement(s) into a varchar and passing it to with an open cursor. Sorry this is so vague, but I'm not sure of the syntax. Hope this provides some direction.

You cant create tables from PL/SQL unless you use a "dynamic sql". If you are using 8i it is as simple as this:

begin
EXCUTE_IMMEDIATE 'create table table_name (column1 varchar2(10),column2 varchar2(10))';
end;

If you are on 8.0 or 7.* you'll have to use package DBMS_SQL. Consult your PL/SQL manuals.

HTH,

You must use Dynamic SQL to execute DDL on the fly:

create procedure create_table (table_name in varchar2,column1 in varchar2,column2 in varchar2)
as
begin
___execute immediate 'create table ' || table_name || ' ( '
___ || column1 || ' varchar2(10), '
___ || column2 || ' varchar2(10));
end;

For more info go to: http://www.oracle.com/oramag/oracle/00-nov/o60sql.html

In 8.0 you can use procedure exec_ddl_statement in package dbms_utility for ddl statements:

procedure create_table (table_name in varchar2,column1 in varchar2,column2 in varchar2)
as
begin
dbms_utility.exec_ddl_statement('create table '||table_name||' ('||column1|| ' varchar2(10), '|| column2 ||' varchar2(10))');
end;

YES, you can create table using stored procedures using SQL_DBMS package. Check out the functions inside them, sql_parse, sql_execute
Regards
Ganesh .R