I am trying to retrieve BLOB values in BINARYDEFVAL column from sys.col$ table.

The BINARYDEFVAL of sys.ecol$ table stores default column values in BLOB format. For VARCHAR2 and NUMBER I am able to extract the default column value.

But, for DATE columns when I use utl_raw.cast_to_varchar2(dbms_lob.substr(BINARYDEFVAL)) I get some gibberish values like: xv| (and some special characters).

Though I can see the default value for one such column is having default value as : sysdate.

I think this is not the correct way to extract a default date value.

Please tell me how this can be resolved.

  • I'm not familiar with these tables. Please post link to Oracle Documents that describe these tables. You should never use undocumented "features". – Michael Kutz Jun 12 '18 at 12:28
  • Actually the table was already used in a PL/SQL function code by another developer. I found the reference in some blogs/article but not official Oracle documentation. Reference: hoopercharles.wordpress.com/2011/09 – Bibhuti Bhusan Padhi Jun 12 '18 at 14:27

Rather than relying on Oracle internals that could change, the correct way is to use the DBA_TAB_COLUMNS data dictionary view, specifically the DATA_DEFAULT column.

Documentation here.

  • Thank you Phil. But here the column type of DATA_DEFAULT is LONG. Could you please tell me there are any solutions to extract value from LONG column inline. Meanwhile I will also search for any solution also. – Bibhuti Bhusan Padhi Jun 12 '18 at 14:29

As it was already said, use DBA_TAB_COLUMNS.

create table bp.t1 as select * from dba_objects;
alter table bp.t1 add (d1 date default date'2018-06-13' not null);

select * from ecol$;

---------- ---------- --------------- ----------
     28857         27 7876060D010101    

with function raw_to_date (p_raw raw) return date as 
  l_date date;
  dbms_stats.convert_raw_value(p_raw, l_date);
  return l_date;
select raw_to_date(dbms_lob.substr(binarydefval)) as date_value from ecol$;

2018-06-13 00:00:00

This will not work for sysdate, because it returns the time the column was added.


select data_default from dba_tab_columns where table_name = 'T1' and column_name = 'D1';


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.