CODEWITHSUNDEEP
sqloracle-database
mahesh
mahesh

Reputation: 143

Create Table from View - Oracle SQL SQL Error: ORA-01723: zero-length columns are not allowed

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

Upvotes: 14

Views: 42135

Answers (2)

Niharika Pathak
Niharika Pathak

Reputation: 1

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:

  1. Create a table with any name. For example abc using MV table structure.

  2. 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;

  3. now create MV script like below

    drop MV; create mv mv_name( columns )

    REFRESH COMPLETE ON DEMAND

    WITH PRIMARY KEY

    AS

    select * from VW ;

  4. EXECUTE the original VW script

  5. now drop the table you created.

  1. Execute the table_script.sql >> schema XYZ
  2. Execute FORCE VIEW script
  3. Execute MATERIALIZED VIEW script
  4. Execute FORCE VIEW original script
  5. Execute Drop_table.txt

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

Regards

Niharika

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 21

Related Questions