can we create a table using stored procedures?

[srinivasm]

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

[ckolik]

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.

[jmodic]

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,

[marist89]

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: [url]http://www.oracle.com/oramag/oracle/00-nov/o60sql.html[/url]

[akkerend]

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;

[aarganesh]

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