DEFAULT Value Behavior [message #658511] |
Fri, 16 December 2016 01:36 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi,
Initially I have created the with out default values to the columns.But I had requirement that some of the columns should need some default value for some being. After inserting certain rows the default value is not required.So after inserting required info into the table, then I had make it as default to NULL.
Here the problem is after modifying the table with default value as null, DATA_DEFAULT column in user_tab_columns is not treating the value as NULL instead its considering this value as some not null value.
SQL> CREATE TABLE NULL_TEST (NO1 VARCHAR2(200), NO2 VARCHAR2(200));
Table created.
SQL> SELECT TABLE_NAME , COLUMN_NAME , DATA_DEFAULT FROM SYS.USER_TAB_COLUMNS WHERE TABLE_NAME LIKE 'NULL_TEST' AND DATA_DEFAULT IS NULL AND COLUMN_NAME ='NO1';
TABLE_NAME COLUMN_NAME DATA_DEFAULT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
NULL_TEST NO1
SQL> ALTER TABLE NULL_TEST MODIFY NO1 VARCHAR2(20) DEFAULT '1';
Table altered.
SQL> INSERT INTO NULL_TEST(NO2) VALUES('with no1 default value as 1');
1 row created.
SQL> select * from null_test;
NO1 NO2
-------------------- ------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
1 with no1 default value as 1
SQL> SELECT TABLE_NAME , COLUMN_NAME , DATA_DEFAULT FROM SYS.USER_TAB_COLUMNS WHERE TABLE_NAME LIKE 'NULL_TEST' AND DATA_DEFAULT IS NULL AND COLUMN_NAME ='NO1';
no rows selected
SQL> select * from null_test WHERE NO1 IS NULL;
no rows selected
SQL> ALTER TABLE NULL_TEST MODIFY NO1 VARCHAR2(20) DEFAULT NULL;
Table altered.
SQL> INSERT INTO NULL_TEST(NO2) VALUES('with no1 default value as NULL');
1 row created.
SQL> SELECT * FROM NULL_TEST WHERE NO1 IS NULL;
NO1 NO2
-------------------- ------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
with no1 default value as NULL
SQL> SELECT TABLE_NAME , COLUMN_NAME , DATA_DEFAULT FROM SYS.USER_TAB_COLUMNS WHERE TABLE_NAME LIKE 'NULL_TEST' AND DATA_DEFAULT IS NULL AND COLUMN_NAME ='NO1';
no rows selected
SQL>
When I am querying from the USER_TAB_COLUMNS where data default is null , its giving the no rows selected where as same is working fine if I am querying from the table with column is null
CM: changed quote tags to code tags
[Updated on: Fri, 16 December 2016 03:09] by Moderator Report message to a moderator
|
|
|
Re: DEFAULT Value Behavior [message #658512 is a reply to message #658511] |
Fri, 16 December 2016 01:52 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You'll see the reason if you reverse engineer the view. Start with looking at the source code in cdcore.sql, which will take you the dba_tab_cols_v$ view defined in cdcore_mig.sql
--update: I see you are on release 11, it may be a bit different. I was speaking of 12.
[Updated on: Fri, 16 December 2016 01:54] Report message to a moderator
|
|
|
|
|
|
Re: DEFAULT Value Behavior [message #658531 is a reply to message #658511] |
Fri, 16 December 2016 06:43 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
This is normal behavior. Not having default value is same as having default value of NULL. Since (or maybe that's why) oracle doesn't offer NODEFAULT option the only way to go back to no default behavior is to change default value to explicit NULL.
SY.
|
|
|
|
|
Re: DEFAULT Value Behavior [message #658534 is a reply to message #658532] |
Fri, 16 December 2016 10:07 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
saipradyumn wrote on Fri, 16 December 2016 09:51
But my requirement is to find out columns which contains default value as null
Well, I can set default value to be NULL many ways:
CREATE TABLE TBL(COL1 DATE)
/
ALTER TABLE TBL MODIFY COL1 DATE DEFAULT NULL
/
ALTER TABLE TBL MODIFY COL1 DATE DEFAULT ''
/
ALTER TABLE TBL MODIFY COL1 DATE DEFAULT NULLIF(SYSDATE,DATE '2020-01-01')
/CREATE TABLE T(
C1 DATE,
C2 DATE DEFAULT NULL,
C3 DATE DEFAULT '',
C4 DATE NULLIF(SYSDATE,DATE '2016-12-16')
)
/
Also, DATA_DEFAULT data type is LONG, so you can't do much in SQL. You'd need to, for example, write a function:
CREATE OR REPLACE
FUNCTION GET_DATA_DEFAULT(
P_TABLE_NAME VARCHAR2,
P_COLUMN_NAME VARCHAR2,
P_TABLE_OWNER VARCHAR2 DEFAULT USER
)
RETURN VARCHAR2
IS
V_DATA_DEFAULT LONG;
BEGIN
SELECT DATA_DEFAULT
INTO V_DATA_DEFAULT
FROM DBA_TAB_COLUMNS
WHERE OWNER = P_TABLE_OWNER
AND TABLE_NAME = P_TABLE_NAME
AND COLUMN_NAME = P_COLUMN_NAME;
EXECUTE IMMEDIATE 'SELECT ' || NVL(V_DATA_DEFAULT,'NULL') || ' FROM DUAL'
INTO V_DATA_DEFAULT;
RETURN V_DATA_DEFAULT;
END;
/
Now:
SQL> CREATE TABLE T(
2 C1 DATE,
3 C2 DATE DEFAULT NULL,
4 C3 DATE DEFAULT '',
5 C4 DATE DEFAULT NULLIF(TRUNC(SYSDATE),DATE '2016-12-16'),
6 C5 DATE DEFAULT SYSDATE
7 )
8 /
Table created.
SQL> SELECT COLUMN_NAME
2 FROM USER_TAB_COLUMNS
3 WHERE TABLE_NAME = 'T'
4 AND GET_DATA_DEFAULT(TABLE_NAME,COLUMN_NAME) IS NULL
5 /
COLUMN_NAME
------------------------------------------------------------------------------
C4
C3
C2
C1
SQL>
SY.
|
|
|
Re: DEFAULT Value Behavior [message #658535 is a reply to message #658534] |
Fri, 16 December 2016 11:45 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
And keep in mind, the above shows what would be default value now. For example, column C4 wouldn't be returned if you run the above tomorrow since default value for C4 is conditional.
SY.
|
|
|
|