Home » SQL & PL/SQL » SQL & PL/SQL » DEFAULT Value Behavior (Oracle 11 )
DEFAULT Value Behavior [message #658511] Fri, 16 December 2016 01:36 Go to next message
saipradyumn
Messages: 361
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 Go to previous messageGo to next message
John Watson
Messages: 7618
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 #658513 is a reply to message #658512] Fri, 16 December 2016 03:09 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks for your response.

Please let me know how to get the source code cdcore.sql from Developer toll /Data base

Re: DEFAULT Value Behavior [message #658515 is a reply to message #658513] Fri, 16 December 2016 03:13 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Come on, man. It's just a SQL script. I'm sure you can find it in your Oracle Home if you look.
Re: DEFAULT Value Behavior [message #658529 is a reply to message #658511] Fri, 16 December 2016 06:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8884
Registered: November 2002
Location: California, USA
Senior Member
Consider the difference between no default value specified and default of null value. If there is no default value specified, then it defaults to null, but it is shown differently than a default of null value. No default value result in nothing in the data_default column, whereas specifying a null default value results in the word NULLL in the data_default column. Please see the simplified demonstration below.

-- no default value specified:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE NULL_TEST (NO1 VARCHAR2(30))
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NULL_TEST' AND COLUMN_NAME ='NO1'
  2  /

DATA_DEFAULT
--------------------------------------------------------------------------------


1 row selected.

-- default value of 1:
SCOTT@orcl_12.1.0.2.0> ALTER TABLE NULL_TEST MODIFY NO1 VARCHAR2(30) DEFAULT  '1'
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NULL_TEST' AND COLUMN_NAME ='NO1'
  2  /

DATA_DEFAULT
--------------------------------------------------------------------------------
'1'

1 row selected.

-- default value of null:
SCOTT@orcl_12.1.0.2.0> ALTER TABLE NULL_TEST MODIFY NO1 VARCHAR2(30) DEFAULT NULL
  2  /

Table altered.

SCOTT@orcl_12.1.0.2.0> SELECT DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NULL_TEST' AND COLUMN_NAME ='NO1'
  2  /

DATA_DEFAULT
--------------------------------------------------------------------------------
NULL

1 row selected.


Re: DEFAULT Value Behavior [message #658531 is a reply to message #658511] Fri, 16 December 2016 06:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 #658532 is a reply to message #658531] Fri, 16 December 2016 08:51 Go to previous messageGo to next message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

But my requirement is to find out columns which contains default value as null . It has to include both columns .
i.e one is with out any default value and another one is by explicitly modifying the default value to null with the help alter command .

select  *  from user_tab_columns  where data_default is null  

As you explained the above is query is not working when I explicitly modified the value to NULL.

Please provide the alternate

Thanks
SaiPradyumn
Re: DEFAULT Value Behavior [message #658533 is a reply to message #658532] Fri, 16 December 2016 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 26194
Registered: January 2009
Location: SoCal
Senior Member
>SELECT TABLE_NAME , COLUMN_NAME , DATA_DEFAULT FROM SYS.USER_TAB_COLUMNS

You should only ever use SYS schema for Oracle database upgrade & maintenance.
You should NOT use SYS for any type of application development or application deployment.
Re: DEFAULT Value Behavior [message #658534 is a reply to message #658532] Fri, 16 December 2016 10:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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.
Re: DEFAULT Value Behavior [message #658582 is a reply to message #658535] Tue, 20 December 2016 05:33 Go to previous message
saipradyumn
Messages: 361
Registered: October 2011
Location: Hyderabad
Senior Member

Thank you very much Solomon Yakobson. My requirement was satisfied
Previous Topic: how to generate an email on a database event?
Next Topic: sorting of an alphanumeric value through oracle pl/sql
Goto Forum:
  


Current Time: Thu Oct 18 22:34:30 CDT 2018