Home » SQL & PL/SQL » SQL & PL/SQL » How do I create a mat view with zero length columns ? (10g2)
How do I create a mat view with zero length columns ? [message #313372] Fri, 11 April 2008 09:40 Go to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Hi,

I'm trying to create a materialized view which has a varchar2 column in it that must be NULL.
Altough I get zero-length error messages when trying to use build-in functions I can get what I want by creating my own function that returns a NULL.
Is there a better way for achieving this ?

CREATE MATERIALIZED VIEW "BAAN"."ORDER_HEADER_walter"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" 
  BUILD IMMEDIATE
  USING INDEX 
  REFRESH FORCE ON DEMAND
  WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
  DISABLE QUERY REWRITE
AS SELECT 
	a.T$ORNO  order_no,
	--nvl(NULL,NULL) alpha,
        --nullif(1,1),
        --NULL as leeg,
        AAA(),  -- My function just returns NULL. This works.
        ...
        ...
Re: How do I create a mat view with zero length columns ? [message #313374 is a reply to message #313372] Fri, 11 April 2008 09:48 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What would be the purpose of a column in an MV containing only nulls and a function returning null?
Re: How do I create a mat view with zero length columns ? [message #313377 is a reply to message #313374] Fri, 11 April 2008 09:55 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
The columns must be present for another database that will put data in these columns but initially they must be empty.

Walter
Re: How do I create a mat view with zero length columns ? [message #313380 is a reply to message #313372] Fri, 11 April 2008 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> initially they must be empty.
Why must the column be "empty"?
Re: How do I create a mat view with zero length columns ? [message #313382 is a reply to message #313380] Fri, 11 April 2008 10:02 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
If they are filled by another source, then why use an MV and not an "ordinary" table?
Re: How do I create a mat view with zero length columns ? [message #313383 is a reply to message #313380] Fri, 11 April 2008 10:06 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
The other database will check these empty columns and fill them as required. Also I don't have any default values I can put in the columns.

skooman,

I'll have to check your question whit our DBA when he gets back but would a CREATE TABLE allow me to create these empty columns ?

Walter
Re: How do I create a mat view with zero length columns ? [message #313384 is a reply to message #313372] Fri, 11 April 2008 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>The other database will check these empty columns
This can only be done by a Full Table Scan which could result in a scalability issue.
Re: How do I create a mat view with zero length columns ? [message #313386 is a reply to message #313384] Fri, 11 April 2008 10:21 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks for your help so far. I'll get back to this on monday.
Re: How do I create a mat view with zero length columns ? [message #313424 is a reply to message #313382] Fri, 11 April 2008 21:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
skooman wrote on Sat, 12 April 2008 01:02
If they are filled by another source, then why use an MV and not an "ordinary" table?

I agree. MV is a bad solution.

The CAST() function should answer your question. But I think the MV is a silly idea.

CAST(NULL AS VARCHAR2(20))


Ross Leishman
Re: How do I create a mat view with zero length columns ? [message #314265 is a reply to message #313424] Wed, 16 April 2008 04:18 Go to previous message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks for all your help but the null columns will be left out.

regards,

Walter
Previous Topic: What would be the best solution to copy/process the data?
Next Topic: Problem with the Oracle Comment's syntax (merged 2 threads)
Goto Forum:
  


Current Time: Thu Dec 08 22:07:13 CST 2016

Total time taken to generate the page: 0.11938 seconds