DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: can we create a table using stored procedures?

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question

    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


  2. #2
    Join Date
    Dec 2000
    Posts
    9
    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.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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]
    Jeff Hunter

  5. #5
    Join Date
    Jul 2000
    Posts
    296
    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;


  6. #6
    Join Date
    Jun 2000
    Location
    Chennai/India
    Posts
    90
    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
    A Winner never Quits, A Quitter never Wins
    Ganesh .R

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width