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)
3 create table table_name (column1 varchar2(10),column2 varchar2(10));
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:
EXCUTE_IMMEDIATE 'create table table_name (column1 varchar2(10),column2 varchar2(10))';
If you are on 8.0 or 7.* you'll have to use package DBMS_SQL. Consult your PL/SQL manuals.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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)
___execute immediate 'create table ' || table_name || ' ( '
___ || column1 || ' varchar2(10), '
___ || column2 || ' varchar2(10));
For more info go to: [url]http://www.oracle.com/oramag/oracle/00-nov/o60sql.html[/url]
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)
dbms_utility.exec_ddl_statement('create table '||table_name||' ('||column1|| ' varchar2(10), '|| column2 ||' varchar2(10))');
YES, you can create table using stored procedures using SQL_DBMS package. Check out the functions inside them, sql_parse, sql_execute
A Winner never Quits, A Quitter never Wins
Click Here to Expand Forum to Full Width