Home » SQL & PL/SQL » SQL & PL/SQL » Add columns between tables (oracle 10.2.0.4.0,windows)
Add columns between tables [message #423398] Thu, 24 September 2009 00:11 Go to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Hi All,

I want to add columns between tables.

I followed the below steps and it was successful.The table
which i created was a small table and contains no constraints,
comments,indexes etc...

My question here is, if the table contains all constraints,comments,
indexes,PK's,FK's etc...
will i able to add columns in between the table by using the below steps ?
Since i want to try it in production database.


Here are the steps:

To add a column in middle of the table of varchar data type:

SQL>create table test (ename varchar2(20),salary number);
Table created

SQL>desc test;
Name Null? Type
---------------------- ----------- ---------------
ENAME VARCHAR2(20) SALARY NUMBER

[i]SQL>rename test to test1;
Table renamed
[ii]SQL>create table test2 (id varchar2(20));
Table created
[iii]SQL>create table test as(select test1.ename,test2.id,
test1.salary from test1,test2);
Table created
........................................................................................
SQL>desc test;
Name Null? Type
----------------------------------------- -------- --------------
ENAME VARCHAR2(20)
ID VARCHAR2(20)
SALARY NUMBER


Regards,
Re: Add columns between tables [message #423401 is a reply to message #423398] Thu, 24 September 2009 00:37 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Hi,
setting the position of a column in a table is (for the vast, vast majority of cases) pointless. Why do you think you need to do this?
Re: Add columns between tables [message #423403 is a reply to message #423401] Thu, 24 September 2009 00:46 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

For production release.

Please tell me one thing ,does it really matters to add columns between the tables?

Regards,
Re: Add columns between tables [message #423408 is a reply to message #423403] Thu, 24 September 2009 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
does it really matters to add columns between the tables?

Only YOU can answer. Does it matter for you? Why?

Regards
Michel
Re: Add columns between tables [message #423409 is a reply to message #423408] Thu, 24 September 2009 01:14 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Hi michel...

How are you?? nice to see you responsing my question after a long time.
i thought you would be angry on me .Hope you remember.


"Only YOU can answer. Does it matter for you? Why?"

I said it already. its for production release.Its an request from the user and
they want to rebuild the table in this way( to add columns in between tables).

Please let me know whether i can drop all the constraints,indexes,
comments and recreate then while adding columns between table .
Is that a right way to do that ??


Regards,
Re: Add columns between tables [message #423410 is a reply to message #423409] Thu, 24 September 2009 01:20 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
"For a production release". Still doesn't answer the question. What is the purpose, the business requirement for this. You will have to recreate all of the constraints, indexes etc after recreating the table if you insiston this course of action.
Re: Add columns between tables [message #423411 is a reply to message #423409] Thu, 24 September 2009 01:25 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Why don't you use views and show the column in order the user wants?
Re: Add columns between tables [message #423415 is a reply to message #423411] Thu, 24 September 2009 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bonker wrote on Thu, 24 September 2009 08:25
Why don't you use views and show the column in order the user wants?

Why the user wants an order?

Regards
Michel
Re: Add columns between tables [message #423416 is a reply to message #423409] Thu, 24 September 2009 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How are you?? nice to see you responsing my question after a long time.
i thought you would be angry on me .Hope you remember.

Sorry, I don't.

Regards
Michel
Re: Add columns between tables [message #423418 is a reply to message #423411] Thu, 24 September 2009 01:43 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Please go through the steps. you will get to know which column im adding
between tables and let me know whether these steps will work
with table containing constraints,indexes,comments etc...

Here are the steps :

1.Create table

SQL>create table Mohan (class varchar2(3),labour varchar2(1));
Table created

SQL> desc mohan
Name Null? Type
----------------------------------------- -------- ----------------------------
class VARCHAR2(3)
labour VARCHAR2(1)

Dropping the constraint (havnt tried this step yet)
just added for your reference.

ALTER TABLE mohan DROP CONSTRAINT SYS_c8977665
/

2.Renaming table mohan to new table
SQL>rename mohan to test1;

Table renamed

3.Creating new table test2 with one column which is to be added between tables

SQL>create table test2 (mohan_col1 VARCHAR2(20 CHAR));
Table created

4.Recreating the table mohan and adding the
column (mohan_col1) in between the tables

SQL>create table mohan as(select test1.class,test2.mohan_col1,
test1.labour from test1,test2);
Table created
........................................................................................

SQL>
SQL> desc mohan
Name Null? Type
----------------------------------------- -------- ----------------------------
class VARCHAR2(3)
BALAJIC_COL1 VARCHAR2(20 CHAR)
labour VARCHAR2(1)

SQL>
SQL>

finally,

Recreate the comments, indexes, constrinats, triggers, grants
ALTER TABLE mohan LOGGING
Recreate PK which is dropped .
Reissue grant statements.

Regards,



Re: Add columns between tables [message #423419 is a reply to message #423415] Thu, 24 September 2009 01:44 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member


I donno michel.
Re: Add columns between tables [message #423422 is a reply to message #423419] Thu, 24 September 2009 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Mohan10g wrote on Thu, 24 September 2009 08:44

I donno michel.

So you should ask instead of dumbly do what they ask and do a lot of work for nothing or wrong thing.

Regards
Michel

Re: Add columns between tables [message #423423 is a reply to message #423415] Thu, 24 September 2009 02:05 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Michel wrote at Thu, 24 September 2009 12:06
Why the user wants an order?


Mohan10g wrote at Thu, 24 September 2009 11:44

I said it already. its for production release.Its an request from the user


It seemed to me that user wanted columns to appear in a particular order in a table and therefore I suggested use of views.
Re: Add columns between tables [message #423566 is a reply to message #423423] Fri, 25 September 2009 06:06 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Hi all,


Im going to rebuild the table by adding columns between tables.

I have one doubt on rebuilding the table.

Since the table contains constraints ,indexes,comments etc..
i need to either drop or disable the constraints,indexes,comments etc...
and then recreate it later once the table is rebuild.

My question here is , is it good practice to DROP the constraints,
indexes,comments or DISABLE the constraints,indexes,comments
etc.before rebuilding the table??



Thanks

Re: Add columns between tables [message #423567 is a reply to message #423566] Fri, 25 September 2009 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "rebuilding the table" mean?

Regards
Michel
Re: Add columns between tables [message #423569 is a reply to message #423567] Fri, 25 September 2009 06:16 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member



It means adding column between tables.

Regards,
Re: Add columns between tables [message #423570 is a reply to message #423569] Fri, 25 September 2009 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How? I think you already described the whole process in a previous post, didn't you?

Regards
Michel
Re: Add columns between tables [message #423571 is a reply to message #423569] Fri, 25 September 2009 06:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Then it's a term you just made up.

HOW are you planning to do this totally pointless thing?

If it's by the steps that you listed a couple of posts ago then you'll have to drop the constraints, to maintain uniqueness of constraint names.

It is so much easier to just add the column normally and either create a view that shows the columns in the required order, or get whichever piece of client software they're using to show the columns in a differnt order.
Re: Add columns between tables [message #423572 is a reply to message #423571] Fri, 25 September 2009 06:26 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Thanks for your inputs.


"It is so much easier to just add the column normally and either create a view
that shows the columns in the required order."

Can you provide me the steps for this??


Regards,

Re: Add columns between tables [message #423573 is a reply to message #423572] Fri, 25 September 2009 06:29 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Steps :

1) Look up alter table syntax.
2) Add the column.
3) Lookup create view syntax.
4) Create the view.
Re: Add columns between tables [message #423574 is a reply to message #423572] Fri, 25 September 2009 06:30 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Look up the syntax for ALTER TABLE .. ADD COLUMN
and
CREATE VIEW
Re: Add columns between tables [message #423578 is a reply to message #423574] Fri, 25 September 2009 06:52 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Thanks for your response.

Steps :

SQL>create table test (ename varchar2(20),salary number);

This is the table which im going to create .

lastname varchar2(20) is the column which i want to add between the columns ename and salary.

Can you provide the view syntax based on above example.

Regards,
Re: Add columns between tables [message #423582 is a reply to message #423578] Fri, 25 September 2009 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you make the effor to read the manual.

Regards
Michel
Re: Add columns between tables [message #423586 is a reply to message #423569] Fri, 25 September 2009 07:37 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
Mohan10g wrote on Fri, 25 September 2009 07:16


It means adding column between tables.

Regards,


What does adding columns between tables mean? That's like adding ingredients between recipes. It makes no sense.
Re: Add columns between tables [message #423593 is a reply to message #423586] Fri, 25 September 2009 07:59 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member


Its an business requirement.
Re: Add columns between tables [message #423598 is a reply to message #423593] Fri, 25 September 2009 08:42 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
The point that Joy was making is that the terminology that you are using doesn't make sense. i.e.
"Putting a column between tables" doesn't physically make sense, rather than the requirement itself making no sense (although this is also true to an extent). However, we have assumed that what you actually mean is that you want to put a column in between other columns within a table.
Now, rather than expecting to be spoon fed the code (which we tend not to do here) go and have a look at the SQL reference manuals for the syntax that has been previously mentioned, and give it a try.
Re: Add columns between tables [message #423603 is a reply to message #423593] Fri, 25 September 2009 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Mohan10g wrote on Fri, 25 September 2009 14:59

Its an business requirement.

This is one of silliest answer I have seen.
How column order can be a BUSINESS requirement?
I never heard a CEO saying "I want the columns in the table in that order".

Regards
Michel

Re: Add columns between tables [message #423607 is a reply to message #423603] Fri, 25 September 2009 09:18 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Fri, 25 September 2009 15:02
I never heard a CEO saying "I want the columns in the table in that order".

You may not have heard a CEO say that, but I have certainly heard many a business 'requirement' along similar lines (Note: I am NOT saying that it makes sense). The issue arises when people supply 'solutions' to what they want to happen, rather than a description of what the want to happen i.e.
Actual business requirement
The report must display the columns in the following order ...

'Non-Business Requirement'
Please change the structure of the table so that the columns are in the following order..

I have lost count of the number of times i have had to ask "What is it you actually want to happen? (This week!)

[Updated on: Fri, 25 September 2009 09:19]

Report message to a moderator

Re: Add columns between tables [message #424861 is a reply to message #423398] Tue, 06 October 2009 05:10 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
Mohan10g

what do you think about cartesian join? http://www.orafaq.com/wiki/Cartesian_join

Re: Add columns between tables [message #424862 is a reply to message #424861] Tue, 06 October 2009 05:12 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
ora_baby wrote on Tue, 06 October 2009 11:10
Mohan10g

what do you think about cartesian join? http://www.orafaq.com/wiki/Cartesian_join



What do you think cartesian join has to do with this thread?
Re: Add columns between tables [message #424958 is a reply to message #423398] Tue, 06 October 2009 14:29 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
create table test (ename varchar2(20),salary number);
insert into test(ename, salary) values ('e1',1);
insert into test(ename, salary) values ('e2',2);
rename test to test1;
create table test2 (id varchar2(20));
insert into test2 (id) values (3);
insert into test2 (id) values (4);
create table test as(select test1.ename,test2.id,test1.salary from test1,test2);
select * from test;

ENAME                ID                       SALARY
-------------------- -------------------- ----------
e1                   3                             1
e1                   4                             1
e2                   3                             2
e2                   4                             2


cookiemonster, is it good?
Re: Add columns between tables [message #424970 is a reply to message #424958] Tue, 06 October 2009 17:13 Go to previous message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
ora_baby wrote on Tue, 06 October 2009 20:29

cookiemonster, is it good?

No, it bears no significant relation to the thread.
Previous Topic: Error while creating Package Body
Next Topic: case statement in a stored procedure
Goto Forum:
  


Current Time: Wed Sep 28 04:04:58 CDT 2016

Total time taken to generate the page: 0.12896 seconds