2

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).

Improve this question
3
  • . . I suspect this is an artifact of the default value being stored as long. Is the extra character really a space or might it just look like one? Commented Nov 13, 2020 at 12:05
  • Your query is not looking at values within the table; it is looking at the definition of the table in the data dictionary. So as long as Oracle handles it properly what difference does it make? Commented Nov 14, 2020 at 20:00
  • @Belayer - The OP explained pretty clearly (in my opinion) how he is using this. He is comparing what "Oracle knows" (which is the data dictionary) to his specifications, to make sure that the create table statements were executed correctly according to specification. This should be done upfront, before any actual data is populated in the tables. Do you now understand what difference that makes? You don't want to wait until you populate data, and only then see if the defaults look ok. Commented Nov 15, 2020 at 15:57

2 Answers 2

Reset to default
1

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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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;
/
Improve this answer

1 Comment

trim is not a good solution; the default value may contain leading or trailing spaces, which should be preserved - but they will be trimmed away along with the unwanted last space.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.