How to declare null column in SQL oracle in Select statement

Question

I'm trying to create a NULL column in the SELECT statement but it doesn't work:

SELECT mytable. *, NULL as ColumnName
FROM mytable;

The error code is

Error report -
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 -  "zero-length columns are not allowed"
*Cause:    Columns with zero length were not allowed.
*Action:   Correct the use of the column.

So it seems like it's not possible to do that. Is there any choice to create a NULL column in the SELECT statement?

Thank you,

Gordon Linoff · Accepted Answer · 2017-12-22 12:49:27Z

11

You can cast() NULL to whatever type you want:

SELECT t.*, CAST(NULL as VARCHAR2(100)) as ColumnName
FROM mytable t;

Note: Your code should work just as a SELECT. The issue comes if you are trying to save the data.

answered Dec 22, 2017 at 12:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How to declare null column in SQL oracle in Select statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related