Create Table from View - Oracle Sql Sql Error: Ora-01723: Zero-Length Columns Are Not Allowed

Question

I need to create a table from view with statement like

CREATE  TABLE NEW_TABLE AS
    SELECT *
    from VIEW

It is giving error message as below. It is not possible to create table from view (with Select * statement)?

Error report -
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 -  "zero-length columns are not allowed

Accepted Answer

I have this problem when NULL is specified for a column, but the type is not specified. Arrgh!

You will need to look at the code. This often happens when I use:

select '' as x

Because I think '' should have the right type.

In any case, the solution is simple:

select cast(NULL as varchar2(255)),
       cast(NULL as number)

or whatever the type is.

You'll need to either change the view definition, or use a query with a subquery with explicit casts.

Answer 2

Error >> "Zero length columns are not allowed "

I recently encountered the similar issue and in such cases I always google and come to stack overflow site for the solution but this time I didn't get the solution for my issue. I checked different other sites too but no help. I tried several ways but no luck. Finally my manager suggested me few tips and tricks and finally we were able to resolve this.

I am posting the steps below which I followed that time and resolve the issue. So, I was getting the above error "Zero length columns are not allowed". When I checked the MV script, I found most of the columns having zero length, and I was not able to create the MV because of this. This is how I fixed it:

Create a table with any name. For example abc using MV table structure.
Our MV was using force view as a source. So I created VW script using that table abc as a source like

CREATE OR REPLACE FORCE VIEW xyz.VW ( columns ) as select * from abc;
now create MV script like below

drop MV; create mv mv_name( columns )

REFRESH COMPLETE ON DEMAND

WITH PRIMARY KEY

AS

select * from VW ;
EXECUTE the original VW script
now drop the table you created.

Execute the table_script.sql >> schema XYZ
Execute FORCE VIEW script
Execute MATERIALIZED VIEW script
Execute FORCE VIEW original script
Execute Drop_table.txt

My manager helped me to resolve this issue . Thanks Ashish .

Regards

Niharika

Create Table from View - Oracle Sql Sql Error: Ora-01723: Zero-Length Columns Are Not Allowed

2 Answers

Must Read

Your Answer

James H. Sterling

Create Table from View - Oracle Sql Sql Error: Ora-01723: Zero-Length Columns Are Not Allowed

2 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

James H. Sterling