Oracle 12c Data Default Value

user_3363728 0
Viewed 278

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Table:

CREATE TABLE t1 (
  c1 VARCHAR2(100 CHAR) DEFAULT 'SOME DATA' NOT NULL, 
  c2 DATE DEFAULT SYSDATE NOT NULL, 
  c3 NUMBER DEFAULT 10 NOT NULL);

Select:

BEGIN
  FOR c IN (SELECT * FROM user_tab_columns WHERE table_name = 'T1') LOOP
    DBMS_output.put_line('"' || c.data_default || '"');
  END LOOP;
END;

Result:

"'SOME DATA' "
"SYSDATE "
"10 "

Why there are spaces at the end of DATA DEFAULT? I need to compare data default in Oracle dictionnary with data default in project metadata, but this space ruins comparison.

EDIT: @GordonLinoff asks "Is the extra character really a space?" Due to LONG datatype of DATA_DEFAULT column I have found only one test:

DECLARE
  l_ret   VARCHAR2(32000);
  l_dump  VARCHAR2(32000);
BEGIN
  FOR c IN (SELECT * FROM user_tab_columns WHERE table_name = 'T1') LOOP
    DBMS_output.put_line('"' || c.data_default || '"');
    l_ret := c.data_default;
    SELECT dump(l_ret) 
      INTO l_dump
      FROM dual;
    DBMS_output.put_line(l_dump);
  END LOOP;
END;

Result:

"10 "
Typ=1 Len=3: 49,48,32
"SYSDATE "
Typ=1 Len=8: 83,89,83,68,65,84,69,32
"'SOME DATA' "
Typ=1 Len=12: 39,83,79,77,69,32,68,65,84,65,39,32

So, it is a space (32).

oracle12c plsql oracle sql string
2 Answers

Interesting.

A simple test shows that the extra space is only added when the column for which you define a default value is declared NOT NULL. (So, contrary to what @GordonLinoff speculated, this is not an artifact of the default value being stored as LONG.)

Why Oracle is doing this... who knows.

In any case, as you compare the default values from the dictionary to your specifications (project metadata), the simplest solution seems to be to conditionally add a trailing space at the end of the other term in the comparison. (Conditional on whether the column is NOT NULL.)

Alternatively, if you really need to manipulate the purported default value directly, you could also retrieve the DEFAULT_LENGTH value from USER_TAB_COLUMNS, so that you can easily remove just the trailing space (when the column is not nullable) while preserving any trailing spaces that may be part of the actual, desired default value. (For example: default value of exactly one space for a non-NULL text column; the DEFAULT_VALUE is reported as two spaces, and you only want to trim the second space, but not the first.)

Admin1

You can just trim() that:

BEGIN
  FOR c IN (SELECT * FROM user_tab_columns WHERE table_name = 'T1') LOOP
    DBMS_output.put_line('"' || trim(c.data_default) || '"');
  END LOOP;
END;
/
user_106767160
Related