[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1946
  • Last Modified:

Cast a varchar2(4000) column as varchar2(200)


Hi you experts,

I have a view looks like the following.

create or repace view EMP_V as
   select empno,
            ename,
            concate_addr( city,address1,address2)  addr_desc
     from emp;

The addr_desc is now defined as varchar2(4000) by the database.
I know that I can cast it into varchar2(200) by using the CAST function in Oracle9i/10g:
    cast(concate_addr( city,address1,address2) as varchar2(200))
But how can I do it in Oracle8i ?

Thanks in advance.


0
AlbertYou
Asked:
AlbertYou
  • 3
  • 2
  • 2
2 Solutions
 
syed555Commented:
Hi,
You can write a function in Database and return desired size of varchar2.
0
 
AlbertYouAuthor Commented:

Hi syed555,

I know that the concate_addr function can return a specified size of varchar2.
But it is not legal to declare the varchar2 length when creating functions:

function concate_addr(......) return varchar2(200) is
......

Whenever selecting a varchar2 function as a view column,
the column length is automatically set to 4000 by the database.

My question is: how to cast the declared varchar2(4000) view column into varchar2(200) ?
0
 
GGuzdziolCommented:
I don't have Oracle 8i now...but in 9i adding SUBSTR works:

create or replace view ...
as
select ...,
    substr(..., 1, 200) addr_desc,
    ...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AlbertYouAuthor Commented:

Hi GGuzdziol,

Thanks for your kind help.
The substr function seems working.

However, this might consume CPU resources whenever selecting the column from the view.
Is there any way to "declare" the length a view column which is returned by a function ?

0
 
GGuzdziolCommented:
You can oncatenate values in view's code so that Oracle can predict what is maximum size of the expression, i.e. instead of
concate_addr( city,address1,address2)
write
city || <some delim here i.e. chr(13) || chr(10)> || address ...
0
 
syed555Commented:
AlbertYou,

You can declare function in this way..

Function myFun return Tabl.Col%Type is
begin
...
end;
0
 
AlbertYouAuthor Commented:

Hi syed555,

Thanks for your input.

I can create a function returning Tabl.Col%Type as you metioned.
However, when I include the function in the SELECT column list of a view,
the view still declares the type of the column as VARCHAR2(4000).

What I want is to declare the column type as Tabl.Col%Type
without paying any performance overheads.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now