2 Replies - 5786 Views - Last Post: 22 August 2013 - 05:06 AM

#1 jone kim  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 46
  • Joined: 07-January 10

define number of columns via user input of a table in STORED PROCEDURE

Posted 20 August 2013 - 02:13 AM

I want to create a table with dynamic columns i.e. number of columns to be defined by
user
using STORED PROCEDURE.

Let me clarify my requirement, I need to create a table (dyn_tbl)
via SP whose inputs will be "number of fields".
e.g.
----------------------------------------------------------------
CREATE OR REPLACE PROCEDURE dynamic_tbl (num_fields varchar2)
AS

num_fields varchar2(25);

BEGIN

END;
/

----------------------------------------------------------------
If user inputs num_fields = 3, o/p will be;

create table dyn_tbl
(
col_1 varchar2(20);
col_2 varchar2(20);
col_3 varchar2(20);
);


*********

If user inputs num_fields = 5, o/p will be;

create table dyn_tbl
(
col_1 varchar2(20);
col_2 varchar2(20);
col_3 varchar2(20);
col_4 varchar2(20);
col_5 varchar2(20);
);


************************************************

Here the column name differ only by there last character which is based on user
requirement.


How can I do it?

Is This A Good Question/Topic? 0
  • +

Replies To: define number of columns via user input of a table in STORED PROCEDURE

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,756
  • Joined: 12-June 08

Re: define number of columns via user input of a table in STORED PROCEDURE

Posted 20 August 2013 - 06:56 AM

You got me - a curious question - what's the business case where a user.. *A* user would be creating individual tables for themselves _AND_ defining dynamic column number?
Was This Post Helpful? 0
  • +
  • -

#3 jone kim  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 46
  • Joined: 07-January 10

Re: define number of columns via user input of a table in STORED PROCEDURE

Posted 22 August 2013 - 05:06 AM

View Postmodi123_1, on 20 August 2013 - 06:56 AM, said:

You got me - a curious question - what's the business case where a user.. *A* user would be creating individual tables for themselves _AND_ defining dynamic column number?


obviously I've the requirement.

can you please reply me how can I do it?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1