Home » SQL & PL/SQL » SQL & PL/SQL » varchar length problem in materialized view with my function. (10g2)
varchar length problem in materialized view with my function. [message #314279] Wed, 16 April 2008 05:06 Go to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Hello,

I'm trying to create a materialized view which has a user defined function in the SELECT statement. The function must return a varchar2(30) but the resulting field in the MV has a length of 4000 (the default length).
How can I control this ? The resulting field must be 30 chars long.

Walter

My function :
create or replace
FUNCTION myfunc (i_orno IN NUMBER) RETURN VARCHAR2 AS retval VARCHAR2(30);
BEGIN
...
retval := 'Some order description';
END;


Create MV :
CREATE MATERIALIZED VIEW "BAAN"."ORDER_CS3_HEADER"
  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,
	myfunc(a.T$ORNO) as description
   FROM
   ...


The resulting MV :
order_no	NUMBER	No		1
description	VARCHAR2(4000 BYTE)	Yes		2

Re: varchar length problem in materialized view with my function. [message #314298 is a reply to message #314279] Wed, 16 April 2008 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Didn't you read the answer Ross gave you in your previous topic:
[quote title=rleishman wrote on Sat, 12 April 2008 04:36]
skooman wrote on Sat, 12 April 2008 01:02
[...]
The CAST() function should answer your question [...]
CAST(NULL AS VARCHAR2(20))


Ross Leishman

Regards
Michel

[Updated on: Wed, 16 April 2008 05:37]

Report message to a moderator

Re: varchar length problem in materialized view with my function. [message #314317 is a reply to message #314298] Wed, 16 April 2008 06:10 Go to previous messageGo to next message
walter01
Messages: 28
Registered: April 2008
Junior Member
Hi Michel,

This seems to work.
It didn't occur to me that I could use my function as an expression for the CAST function.
But why isn't the return datatype of my function not sufficient for the CREATE MV command to make it a length of 30 ?

regards,

Walter
Re: varchar length problem in materialized view with my function. [message #314322 is a reply to message #314317] Wed, 16 April 2008 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because your function does not return a varchar2(30) but a not constrained varchar2 and maximum length for a varchar2 in SQL is 4000.

Regards
Michel

[Edit: added missing word]

[Updated on: Wed, 16 April 2008 08:02]

Report message to a moderator

Re: varchar length problem in materialized view with my function. [message #314344 is a reply to message #314322] Wed, 16 April 2008 07:19 Go to previous message
walter01
Messages: 28
Registered: April 2008
Junior Member
Thanks Michel.

Walter
Previous Topic: UTL_FILE Line Length Problem
Next Topic: Create SQL string
Goto Forum:
  


Current Time: Fri Dec 09 03:57:54 CST 2016

Total time taken to generate the page: 0.10023 seconds