CODEWITHSUNDEEP
oracle-databaseoracle11gdatabase-administration
Naga
Naga

Reputation: 225

zero-length columns are not allowed - Creating a table from view

I designed a process to copy data from views into corresponding tables in oracle. All I do is call a procedure passing 'view name' as parameter and this creates corresponding table (if not exists else drop and creates the table). So this happens dynamically and I have around 50 views, and they are scheduled as oracle jobs.

Now coming to issue I have a few tables failing with below error on some days...

ORA-01723: zero-length columns are not allowed

I am aware the reason for this is a couple of columns in the view being null, but not on all days. Yes I should be using a CAST for those columns, but as I mentioned this happens dynamically I have no pre idea which those columns are or which view is that? Any leads to identify if there are "zero-length columns" in view before I start creating table so that I can think of some solution. Any better remedy for this is really appreciated.

Note:

  1. CREATE TABLE TABLE_NAME AS SELECT * FROM VIEW_NAME --> This is my table creation SQL.
  2. The reason I choose this is 'INSERT INTO' may take more time and resource due to logging and locking.

Thanks, Naga'

Upvotes: 1

Views: 21179

Answers (2)

David Aldridge
David Aldridge

Reputation: 52376

The reason I choose this is 'INSERT INTO' may take more time and resource due to logging and locking.

I think that you have based your method of dropping and recreating the tables every time on a false belief. Truncated them and using a direct path insert, optionally with nologging, would give you practically the same result without this problem. If you're using:

create table .. as select from ...

... then you're fully logging the operation anyway.

Upvotes: 0

suPPLer
suPPLer

Reputation: 489

As you said you call a procedure to create a table from view. So I assume that you already are using dynamic SQL (Native Dynamic SQL aka EXECUTE IMMEDIATE statement or DBMS_SQL package) to execute DDL statements. Then you could generate more complicated CREATE TABLE AS statement with using the Oracle dictionary views such as USER_VIEWS and USER_TAB_COLUMNS to get info about columns' types, length, scale and whatever else you may need to write a right CAST call.

A dirty example is below:

create or replace view v 
as 
  select decode(dummy, 'Y', '123') s 
       , 1 n
       , 2.2 f
       , cast (1.1 as number(5,3)) fs
    from dual
/

set serveroutput on
declare
  l_query varchar2(32767) := 'create table t as select <column list> from v';
  l_type varchar2(100);
begin
  for tc in (
    select * from user_tab_columns 
     where table_name = 'V'
     order by column_id
  ) loop
    l_type := tc.data_type;
    l_type := l_type || 
      case tc.data_type 
        when 'NUMBER' then 
          case when tc.data_precision is not null then '(' || tc.data_precision || case when tc.data_scale is not null then ','||tc.data_scale end || ')' end
        when 'VARCHAR2' then 
          '(' || tc.char_length || ' ' || case tc.char_used when 'C' then 'char' else 'byte' end || ')' 
       end;
    l_query := replace(l_query, '<column list>', 'cast("'||tc.column_name||'" as '|| l_type ||') "'||tc.column_name||'" ,<column list>');
  end loop;
  l_query := replace(l_query, ',<column list>');

  dbms_output.put_line(l_query);
end;
/

Results:

view V created.
anonymous block completed
create table t as select cast("S" as VARCHAR2(3 char)),cast("N" as NUMBER),cast("F" as NUMBER),cast("FS" as NUMBER(5,3)) from v

Good luck.

Upvotes: 1

Related Questions