Menu
If you create an Oracle table using "create as" where one of your fields is null you will get the error:
ORA-01723: zero-length columns are not allowed
Example query:
create table mytable as
select
field_a,
null brand_new_field
from anothertable;
How can you get around this?
660
Syntax. The basic syntax of NULL while creating a table. SQL> CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here, NOT NULL signifies that column should always accept an explicit value of the given data type.
The simplest way to put NULL into any column, regardless of the datatype, is: INSERT INTO emp (hiredate) VALUES (NULL); Don't use single-quotes around NULL. Putting it in single-quotes makes it a 4-character string value.
Here is an example of how to use the Oracle IS NULL condition in a SELECT statement: SELECT * FROM suppliers WHERE supplier_name IS NULL; This Oracle IS NULL example will return all records from the suppliers table where the supplier_name contains a null value.
Figured it out need to use cast(null as datatype)
create table mytable as
select
field_a,
cast(null as varchar(1)) brand_new_field
from anothertable;
Some more info here.
91
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With