• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 57
  • Last Modified:

Table_Creation_using_procedure

Hi Expert,

How can i create a table using plsql  procedure where table & column name should be input parameter !!

Thanks in Advance
0
MIHIR KAR
Asked:
MIHIR KAR
2 Solutions
 
Geert GOracle dbaCommented:
why ?
that's a real mess

use a sqlplus script instead

define tabname=&1
define colname=&2
create table &tabname. (&colname. varchar2(50 char));

Open in new window


you didn't mention the column type had to be a parameter too
0
 
slightwv (䄆 Netminder) Commented:
You should really never need to do this in Oracle.

But since you asked, something like this.  I didn't test it so there might be some minor issues:
create or replace procedure create_my_table(p_table_name in varchar2, p_column_name in varchar2) is
begin
execute immediate 'create table ' || p_table_name || '(' || p_column_name || ' char(1))';
end;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I still wouldn't ever do things this way.  I would be interested in the reasons you want to do this.  There are likely better ways.

I took the example and expanded it to allow you to pass in the entire list of columns and data_types.

Here is the tested run output:
SQL> describe test_tab;
ERROR:
ORA-04043: object test_tab does not exist


SQL>
SQL> create or replace procedure create_my_table(p_table_name in varchar2, p_column_list in varchar2) is
  2  begin
  3
  4  dbms_output.put_line('create table ' || p_table_name || '(' || p_column_list || ')');
  5
  6  execute immediate 'create table ' || p_table_name || '(' || p_column_list || ')';
  7  end;
  8  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> exec create_my_table('test_tab','col1 char(1), col2 number');
create table test_tab(col1 char(1), col2 number)

PL/SQL procedure successfully completed.

SQL>
SQL> describe test_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               CHAR(1)
 COL2                                               NUMBER

Open in new window

0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, the "execute immediate" command in PL\SQL will allow to you to create a table (or do any other DDL action) in a PL\SQL procedure.  But, in Oracle, this is almost never the best way to get things done.  What kind of business problem do you expect this to help you with?
0
 
MIHIR KAR#Oracle_DB  #UNIX beginnerAuthor Commented:
Thanks A lot !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Tackle projects and never again get stuck behind a technical roadblock.
Join Now