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
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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#Hadoop #Oracle_DB  #UNIX beginnerAuthor Commented:
Thanks A lot !!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.