Hi All,
Requesting your help,I came across a weird scenario that a table in Oracle has the same column_name and the datatype , not sure how this is possible , has anybody came across such a scenario or can figure out how can this happen?
The Query below in stored procedure had failed with ORA-01422: exact fetch returns more than one requested number of rows
SELECT DATA_TYPE
INTO V_DATA_TYPE
FROM all_TAB_COLUMNS
WHERE TABLE_NAME = 'T148462192'
AND COLUMN_NAME = 'F5448944';
We resolved the case by dropping that column and adding it again from application UI and post addition we only had one column in the table with the name"F5448944" (a general case ) , however curious how this problem would have happened ?
Database Version:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
Querying Dictionary views from SQL Developer Version 4.1.3.20 Build MAIN-20.78:
select * from ALL_TAB_COLUMNS where TABLE_NAME = 'T148462192' and column_name ='F5448944';
| OWNER | TABLE_NAME | COLUMN_NAME | DATA_TYPE | DATA_TYPE_MOD | DATA_TYPE_OWNER | DATA_LENGTH | DATA_PRECISION | DATA_SCALE | NULLABLE | COLUMN_ID | DEFAULT_LENGTH | DATA_DEFAULT | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | LAST_ANALYZED | SAMPLE_SIZE | CHARACTER_SET_NAME | CHAR_COL_DECL_LENGTH | GLOBAL_STATS | USER_STATS | AVG_COL_LEN | CHAR_LENGTH | CHAR_USED | V80_FMT_IMAGE | DATA_UPGRADED | HISTOGRAM | DEFAULT_ON_NULL | IDENTITY_COLUMN | EVALUATION_EDITION | UNUSABLE_BEFORE | UNUSABLE_BEGINNING |
| XYZ | T148462192 | F5448944 | VARCHAR2 | 250 | N | 6 | 0 | 0 | 0 | 0 | 18-Aug-17 | CHAR_CS | 250 | YES | NO | 0 | 250 | C | NO | YES | NONE | NO | NO |
| XYZ | T148462192 | F5448944 | VARCHAR2 | 250 | N | 6 | 0 | 0 | 0 | 0 | 18-Aug-17 | CHAR_CS | 250 | YES | NO | 0 | 250 | C | NO | YES | NONE | NO | NO |
Describing the table from SQL developer shows two columns with the same name "F5448944";
desc XYZ.T148462192;
| Name | Null | Type |
| E55862201 | NOT NULL | NUMBER |
| F45830211 | NOT NULL | NUMBER(10) |
| ROW_IDENTIFIER | NOT NULL | NUMBER(10) |
| ROW_VERSION | NOT NULL | NUMBER(10) |
| F58383131 | NOT NULL | VARCHAR2(250 CHAR) |
| F5448944 | NOT NULL | VARCHAR2(250 CHAR) |
| F5448944 | NOT NULL | VARCHAR2(250 CHAR) |
| F5448945 | NOT NULL | VARCHAR2(250 CHAR) |
| F56161098 | NOT NULL | VARCHAR2(250 CHAR) |
| F58381670 | | VARCHAR2(250 CHAR) |
| F23415603 | | VARCHAR2(250 CHAR) |
| F7484279 | | VARCHAR2(250 CHAR) |
| F5220943 | | VARCHAR2(250 CHAR) |
| F4445695 | | VARCHAR2(250 CHAR) |
| F4445554 | | VARCHAR2(250 CHAR) |
| F40378356 | | VARCHAR2(250 CHAR) |
| F58350556 | | DATE |
| F39945465 | | DATE |
| F7488061 | | NUMBER |
| F5220950 | | NUMBER |
| F8486345 | | NUMBER |
| F4445852 | | VARCHAR2(250 CHAR) |
| F7484394 | | VARCHAR2(250 CHAR) |
| F8130081 | | VARCHAR2(250 CHAR) |
| F8130082 | | VARCHAR2(250 CHAR) |
| F8130083 | | VARCHAR2(250 CHAR) |
| F114531875 | | VARCHAR2(250 CHAR) |
| F3616055 | | VARCHAR2(250 CHAR) |
| F3616056 | | VARCHAR2(250 CHAR) |
| F8002370 | | VARCHAR2(250 CHAR) |
| REASON | | VARCHAR2(250 CHAR) |
| F115851366 | | VARCHAR2(250 CHAR) |
| F81684929 | | DATE |
| F115851365 | | NUMBER(10) |
| F23365588 | NOT NULL | VARCHAR2(250 CHAR) |
However Interestingly when I described the same table using SQL PLUS , I could see the column "F5448944" shown only once , below is the snapshot from Beyond compare (A comparing tool ) , the left is from SQL developer describe and right is the SQL PLUS describe .
Any help is appreciated . Thanks in Advance .
- Gaurav