Home » SQL & PL/SQL » SQL & PL/SQL » Column Reappears Again (merged) (Oracle 10g, 10.2.0.1.0, Windows XP)
Column Reappears Again (merged) [message #447806] Thu, 18 March 2010 01:44 Go to next message
s4.ora
Messages: 63
Registered: March 2010
Member
I have this table in my Database

ADMIN_ID VARCHAR2(20 BYTE)
PASSWORD VARCHAR2(20 BYTE)
EMAIL_ID VARCHAR2(40 BYTE)
DOMAIN_ID VARCHAR2(20 BYTE)
ACTIVE_FLAG NUMBER
CREATE_DATE DATE
ACTIVATION_DATE DATE
ACCOUNT_STATUS VARCHAR2(10 BYTE)
REASON VARCHAR2(100 BYTE)
USER_ID VARCHAR2(255 CHAR)
FIRST_NAME VARCHAR2(100 BYTE)
LAST_NAME VARCHAR2(100 BYTE)

After dropping the column USER_ID i described the table structure and saw that the column is not there,
this was OK but supprisingly it appears again. I have tried it in SQL Developer as well as from Oracle SQL Prompt,
but the same problem sustains. I am not getting any idea what is going wrong..Can anyone help me??
Re: Automatic Column Addition [message #447809 is a reply to message #447806] Thu, 18 March 2010 01:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Automatic Column Addition [message #447811 is a reply to message #447806] Thu, 18 March 2010 01:56 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please don`t describe your Problem..
copy/paste your session information (what you tried and the Output)

So that we can help you..

sriram Smile
Re: Automatic Column Addition [message #447814 is a reply to message #447806] Thu, 18 March 2010 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet you are not looking at the same:
- database
- schema
- object

Regards
Michel
Column Reappears Again [message #447817 is a reply to message #447806] Thu, 18 March 2010 02:25 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
I have created this table in my Database using the following statement

CREATE TABLE ADMIN_LOGIN
(ADMIN_ID VARCHAR2(20 BYTE) NOT NULL,
PASSWORD VARCHAR2(20 BYTE),
EMAIL_ID VARCHAR2(40 BYTE),
DOMAIN_ID VARCHAR2(20 BYTE),
ACTIVE_FLAG NUMBER DEFAULT 0,
CREATE_DATE DATE,
ACTIVATION_DATE DATE,
ACCOUNT_STATUS VARCHAR2(10 BYTE),
REASON VARCHAR2(100 BYTE),
FIRST_NAME VARCHAR2(100 BYTE),
LAST_NAME VARCHAR2(100 BYTE),
CONSTRAINT ADMIN_LOGIN_PK PRIMARY KEY (ADMIN_ID),
CONSTRAINT ADMIN_LOGIN_CHK2 CHECK (ACCOUNT_STATUS IN ('BLOCK', 'UNBLOCK')),
CONSTRAINT ADMIN_LOGIN_DOMAIN_FK FOREIGN KEY (DOMAIN_ID) REFERENCES ONST_DOMAIN (DOMAIN_ID));

After dropping the column USER_ID i described the table structure and saw that the column is not there,
this was OK but supprisingly it appears again. I have tried it in SQL Developer as well as from Oracle SQL Prompt,
but the same problem sustains.

This is what i have done from Oracle SQL Prompt.

SQL> desc ADMIN_LOGIN
Name Null? Type
----------------------------------------- -------- ----------------------------
ADMIN_ID NOT NULL VARCHAR2(20)
PASSWORD VARCHAR2(20)
EMAIL_ID VARCHAR2(40)
DOMAIN_ID VARCHAR2(20)
ACTIVE_FLAG NUMBER
CREATE_DATE DATE
ACTIVATION_DATE DATE
ACCOUNT_STATUS VARCHAR2(10)
REASON VARCHAR2(100)
USER_ID VARCHAR2(255 CHAR)
FIRST_NAME VARCHAR2(100)
LAST_NAME VARCHAR2(100)

SQL> alter table ADMIN_LOGIN drop column USER_ID;

Table altered.

SQL> desc ADMIN_LOGIN
Name Null? Type
----------------------------------------- -------- ----------------------------
ADMIN_ID NOT NULL VARCHAR2(20)
PASSWORD VARCHAR2(20)
EMAIL_ID VARCHAR2(40)
DOMAIN_ID VARCHAR2(20)
ACTIVE_FLAG NUMBER
CREATE_DATE DATE
ACTIVATION_DATE DATE
ACCOUNT_STATUS VARCHAR2(10)
REASON VARCHAR2(100)
FIRST_NAME VARCHAR2(100)
LAST_NAME VARCHAR2(100)

Here after dropping the column the Table Structure shows that the column is not there
but the column re-appears again. When i Connect to SQL Developer the Column is showing There.
I am connecting to the same database. I am not getting any idea what is going wrong..Can anyone help me??
Re: Column Reappears Again [message #447818 is a reply to message #447817] Thu, 18 March 2010 02:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another user/process has added it again, you are checking a different schema, you are checking a different database or you are checking a different object (view vs table, different table)
Columns do NOT undrop.
Re: Column Reappears Again [message #447819 is a reply to message #447817] Thu, 18 March 2010 02:30 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
More over No such "USER_ID" column in your create table script

sriram Smile
Re: Column Reappears Again [message #447825 is a reply to message #447819] Thu, 18 March 2010 03:21 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Thanks for the advice,
Sorry...i did that mistake..

this was the create table syntax

CREATE TABLE ADMIN_LOGIN
(ADMIN_ID VARCHAR2(20 BYTE) NOT NULL,
PASSWORD VARCHAR2(20 BYTE),
EMAIL_ID VARCHAR2(40 BYTE),
DOMAIN_ID VARCHAR2(20 BYTE),
ACTIVE_FLAG NUMBER DEFAULT 0,
CREATE_DATE DATE,
ACTIVATION_DATE DATE,
ACCOUNT_STATUS VARCHAR2(10 BYTE),
REASON VARCHAR2(100 BYTE),
USER_ID VARCHAR2(255 CHAR),
FIRST_NAME VARCHAR2(100 BYTE),
LAST_NAME VARCHAR2(100 BYTE),
CONSTRAINT ADMIN_LOGIN_PK PRIMARY KEY (ADMIN_ID),
CONSTRAINT ADMIN_LOGIN_CHK2 CHECK (ACCOUNT_STATUS IN ('BLOCK', 'UNBLOCK')),
CONSTRAINT ADMIN_LOGIN_DOMAIN_FK FOREIGN KEY (DOMAIN_ID) REFERENCES ONST_DOMAIN (DOMAIN_ID));
Re: Column Reappears Again [message #447850 is a reply to message #447825] Thu, 18 March 2010 04:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Go to SQL developer, right click on the 'Tables' icon in the schema objects tree in the object navigator, and select 'Refresh'

I think you're just looking at old data.
Re: Column Reappears Again [message #447856 is a reply to message #447850] Thu, 18 March 2010 04:36 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Thank up, Thanks for the Advice, i'll check it out.
Re: Column Reappears Again [message #447858 is a reply to message #447850] Thu, 18 March 2010 04:41 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
I have tried it in SQL Developer as well as from Oracle SQL Prompt


sriram Smile
Re: Column Reappears Again [message #447920 is a reply to message #447825] Thu, 18 March 2010 08:07 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
sen.suvro wrote on Thu, 18 March 2010 03:21
Thanks for the advice,
Sorry...i did that mistake..

this was the create table syntax


I just cannot trust anything you say based on "mistakes" like this. You still haven't shown proof that the column re-appears after dropping it. Show us your Sql*Plus session where this "appears" to happen, and no faking it as your previous create table code was.
Re: Column Reappears Again [message #448003 is a reply to message #447920] Fri, 19 March 2010 00:20 Go to previous messageGo to next message
s4.ora
Messages: 63
Registered: March 2010
Member
I have done the whole work from SQL Developer. Created the Table and then dropped the Column.
What happened exactly was that the column re-apeared again..

At this point I became confused.. so i tried to drop the column from SQl Prompt. This is what i did.

SQL> desc admin_login
Name Null? Type
----------------------------------------- -------- ----------------------------

ADMIN_ID NOT NULL VARCHAR2(20)
PASSWORD VARCHAR2(20)
EMAIL_ID VARCHAR2(40)
DOMAIN_ID VARCHAR2(20)
ACTIVE_FLAG NUMBER
CREATE_DATE DATE
ACTIVATION_DATE DATE
ACCOUNT_STATUS VARCHAR2(10)
REASON VARCHAR2(100)
FIRST_NAME VARCHAR2(100)
LAST_NAME VARCHAR2(100)
USER_ID VARCHAR2(255 CHAR)

SQL> alter table ADMIN_LOGIN drop column USER_ID;

Table altered.

SQL> desc admin_login
Name Null? Type
----------------------------------------- -------- ----------------------------

ADMIN_ID NOT NULL VARCHAR2(20)
PASSWORD VARCHAR2(20)
EMAIL_ID VARCHAR2(40)
DOMAIN_ID VARCHAR2(20)
ACTIVE_FLAG NUMBER
CREATE_DATE DATE
ACTIVATION_DATE DATE
ACCOUNT_STATUS VARCHAR2(10)
REASON VARCHAR2(100)
FIRST_NAME VARCHAR2(100)
LAST_NAME VARCHAR2(100)

After dropping the column USER_ID i described the table structure and saw that the column is not there,
this was OK but supprisingly it appears again in SQL Developer.

As for the "Create Table Statement" i just coppied it from the SQL Developer, when i said "Sorry...i did that mistake..",
i meant that i copied that portion of the Statement from the SQL Developer after dropping the column from SQL Developer
instead of copying it before drop, this was then when i wrote

" I have created this table in my Database using the following statement

CREATE TABLE ADMIN_LOGIN
(ADMIN_ID VARCHAR2(20 BYTE) NOT NULL,
PASSWORD VARCHAR2(20 BYTE),
EMAIL_ID VARCHAR2(40 BYTE),
DOMAIN_ID VARCHAR2(20 BYTE),
ACTIVE_FLAG NUMBER DEFAULT 0,
CREATE_DATE DATE,
ACTIVATION_DATE DATE,
ACCOUNT_STATUS VARCHAR2(10 BYTE),
REASON VARCHAR2(100 BYTE),
FIRST_NAME VARCHAR2(100 BYTE),
LAST_NAME VARCHAR2(100 BYTE),
CONSTRAINT ADMIN_LOGIN_PK PRIMARY KEY (ADMIN_ID),
CONSTRAINT ADMIN_LOGIN_CHK2 CHECK (ACCOUNT_STATUS IN ('BLOCK', 'UNBLOCK')),
CONSTRAINT ADMIN_LOGIN_DOMAIN_FK FOREIGN KEY (DOMAIN_ID) REFERENCES ONST_DOMAIN (DOMAIN_ID)); "

The total work was done from SQL Developer except the "Alter Table" which i detailed above.
Re: Column Reappears Again [message #448005 is a reply to message #448003] Fri, 19 March 2010 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

How can we reproduce your problem?
provide all necessary SQL to do so; without any manual intervention
Re: Column Reappears Again [message #448024 is a reply to message #448005] Fri, 19 March 2010 02:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you drop some other column (for example, REASON)? Does it reappear again too?
Re: Column Reappears Again [message #448099 is a reply to message #448003] Fri, 19 March 2010 08:18 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
sen.suvro wrote on Fri, 19 March 2010 00:20

" I have created this table in my Database using the following statement

CREATE TABLE ADMIN_LOGIN
(ADMIN_ID VARCHAR2(20 BYTE) NOT NULL,
PASSWORD VARCHAR2(20 BYTE),
EMAIL_ID VARCHAR2(40 BYTE),
DOMAIN_ID VARCHAR2(20 BYTE),
ACTIVE_FLAG NUMBER DEFAULT 0,
CREATE_DATE DATE,
ACTIVATION_DATE DATE,
ACCOUNT_STATUS VARCHAR2(10 BYTE),
REASON VARCHAR2(100 BYTE),
FIRST_NAME VARCHAR2(100 BYTE),
LAST_NAME VARCHAR2(100 BYTE),
CONSTRAINT ADMIN_LOGIN_PK PRIMARY KEY (ADMIN_ID),
CONSTRAINT ADMIN_LOGIN_CHK2 CHECK (ACCOUNT_STATUS IN ('BLOCK', 'UNBLOCK')),
CONSTRAINT ADMIN_LOGIN_DOMAIN_FK FOREIGN KEY (DOMAIN_ID) REFERENCES ONST_DOMAIN (DOMAIN_ID)); "

The total work was done from SQL Developer except the "Alter Table" which i detailed above.


Once again, I do not see any column called USER_ID in your table creation script. Why don't you do as asked? Copy and paste your entire SQL*Plus session and stop wasting our time, or do you not understand what we are asking? Jeez!
Leave out the SQL*Developer part altogether. You are connecting to a different user or database.

[Updated on: Fri, 19 March 2010 08:22]

Report message to a moderator

Re: Column Reappears Again [message #448101 is a reply to message #448099] Fri, 19 March 2010 08:23 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Actually Quote:
As for the "Create Table Statement" i just coppied it from the SQL Developer, when i said "Sorry...i did that mistake..",
i meant that i copied that portion of the Statement from the SQL Developer after dropping the column from SQL Developer
instead of copying it before drop, this was then when i wrote


And I am Waiting for
Quote:
What happens if you drop some other column (for example, REASON)? Does it reappear again too?


Quote:
How can we reproduce your problem?
provide all necessary SQL to do so; without any manual intervention


sriram Smile
Previous Topic: DB link creating problem.
Next Topic: Invalid character
Goto Forum:
  


Current Time: Sun Dec 11 02:19:36 CST 2016

Total time taken to generate the page: 0.10518 seconds