CODEWITHSUNDEEP
sqloracle10g
lecogiteur
lecogiteur

Reputation: 467

How to verify the default value in Oracle

When I try to verfiy if column have a default value set, I Have a problem when I try to verify if the default value is null.

SELECT a.data_default FROM all_tab_columns a WHERE a.table_name = 'MY_TABLE' and a.column_name = 'COL1' AND a.data_default IS NOT NUL

It's return NULL in Long type. So how to verify that the default is not null and not equals to NULL in Long type

Upvotes: 1

Views: 1843

Answers (3)

Puneet
Puneet

Reputation: 11

You can try:

SELECT a.data_default 
FROM all_tab_columns a 
WHERE a.table_name = 'MY_TABLE' 
and a.column_name = 'COL1' 
AND to_char(a.data_default) IS NOT NULL;

Actually Data_default column is Long data type, so might be giving that error.

Upvotes: 0

Christian Arias
Christian Arias

Reputation: 61

The problem is the type LONG. The workaround is using pl/sql. Hte first part use a cursor to select only columns with default value, then in the loop filter that ones that has been specified like "DEFAULT NULL". Then only show that ones that have default is not null and not equals to NULL

BEGIN
FOR rec in
  ( SELECT table_name, column_name, a.data_default
      FROM all_tab_columns A
     WHERE a.table_name = 'MY_TABLE'
     AND a.column_name = 'COL1'
     AND a.data_default IS NOT NULL ) LOOP
  IF UPPER(rec.data_default) NOT LIKE '%NULL%' THEN
    DBMS_OUTPUT.PUT_LINE(rec.table_name||' '||rec.column_name||' '||rec.DATA_default);<br>
  END IF;
END LOOP;
END;
/

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

coalesce is supported in both Oracle and SQL Server and serves essentially the same function as nvl and isnull. (There are some important differences, coalesce can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull matches the type of the first argument, that is not true for coalesce, at least on SQL Server.)

Upvotes: 0

Related Questions