Solved

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

Posted on 2006-11-07
8
1,898 Views
Last Modified: 2007-11-11

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
Comment
Question by:AlbertYou
  • 3
  • 2
  • 2
8 Comments
 
LVL 2

Expert Comment

by:syed555
ID: 17895332
Hi,
You can write a function in Database and return desired size of varchar2.
0
 
LVL 2

Author Comment

by:AlbertYou
ID: 17896071

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
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 125 total points
ID: 17896114
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:AlbertYou
ID: 17896195

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
 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 125 total points
ID: 17896234
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
 
LVL 2

Expert Comment

by:syed555
ID: 17904196
AlbertYou,

You can declare function in this way..

Function myFun return Tabl.Col%Type is
begin
...
end;
0
 
LVL 2

Author Comment

by:AlbertYou
ID: 17911931

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question