Wrong characters in default value

[MarcielDeg]

I got a problem running DDL query to set a default value to a column:

Test case: Running in a SQL Window:

CREATE TABLE TEST1 (ID NUMBER(10), STATUS NUMBER(2))

ALTER TABLE TEST1 MODIFY STATUS DEFAULT 0

SELECT USER_TAB_COLS.DATA_DEFAULT
  FROM USER_TAB_COLS
 WHERE USER_TAB_COLS.TABLE_NAME = 'TEST1'
   AND USER_TAB_COLS.COLUMN_NAME = 'STATUS'

In this case, default value has an extra enter (0D0A):

But, if I run alter table query in this way:

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE TEST1 MODIFY STATUS DEFAULT 0';
END;

it works correctly:

I tried oracle client versions 11, 19 and 21, with the same result. I believe that PL/SQL is manipulating SQL improperly before executing.

I'm using PL/SQL Developer v15.0.4

 

[Marco Kalter]

Perhaps the statement from the SQL Window is sent to the server with an extra linefeed. I'm not sure why this linefeed would affect the outcome, but we'll investigate.

 

[fabrice]

you could try to put a ; after each command.

 

[MarcielDeg]

you could try to put a ; after each command.

the same error happens

 

[MarcielDeg]

Oracle has some questionable features. The command below generates the default value "0 " (with an extra space), not "0":

ALTER TABLE TEST1 MODIFY STATUS DEFAULT 0 NOT NULL

To generate without the extra space, it is necessary to join the value with the NOT NULL command:

ALTER TABLE TEST1 MODIFY STATUS DEFAULT 0NOT NULL

Ugly, and it feels wrong.

 

[William Robertson]

The same thing happens in SQL*Plus, so I don't think it's a PL/SQL Dev issue.

The default value is stored as a string so a conversion to numeric is required at insert time anyway, but maybe the stray CRLF requires an extra processing step that could show up at high volumes. Maybe someone less lazy than me could test it and see if it makes any measurable difference for a 10 million row insert.

 

[MarcielDeg]

The same thing happens in SQL*Plus, so I don't think it's a PL/SQL Dev issue.

I also use DataGrip, and the reported extra "enter" problem does not occur with it.