Re: PLSQL return varchar2 value

From: Nicolas Issakides <nissakid_at_planete.net>
Date: 1998/02/24
Message-ID: <34f333f9.0_at_Kampot.pci>#1/1

Thanks you for trying to help me.

Here is a more detailled explanation of the PL/SQL problem:

let say you have the following table :

SQL> desc toto

 Name                            Null?    Type
 ------------------------------- -------- ----
 C1                                       VARCHAR2(10)
 C2                                       VARCHAR2(10)
 C3                                       VARCHAR2(10)

if you do the folowing :

create or replace view xx as
select c1,c2,(c1 || c2) cccl from toto
;
you get :

SQL> desc xx

 Name                            Null?    Type
 ------------------------------- -------- ----
 C1                                       VARCHAR2(10)
 C2                                       VARCHAR2(10)
 CCCL                                     VARCHAR2(20)

BUT if you have :

create or replace function Xcat (

     p1 in varchar2,
     p2 in varchar2,
     p3 in varchar2 default  '',
     p4 in varchar2 default  ''
) 
return varchar2 as      

begin
 return (p1 || p2 || p3|| p4);
end;
/

create or replace view xx as
select c1,c2,xcat(c1,c2) ccc from toto
;

will return :

SQL> desc xx

 Name                            Null?    Type
 ------------------------------- -------- ----
 C1                                       VARCHAR2(10)
 C2                                       VARCHAR2(10)
 CCC                                      VARCHAR2(4000)

In article <6ctseq$7r4_at_post.percombank.kiev.ua>, "Andrew Protasov" <protasov_at_percombank.kiev.ua> wrote:
>Hi Nicolas,
>
>Try this
>
>select substr(f(dummy),1,200) from dual;
>
>where f is your PL/SQL function and 200 - necessary length.
>
> Andrew Protasov
>
NB: The above solution work and give an adequate answer to the problem but I try to find a more 'clean' solution

Nicolas

Nissakid_at_planete.net
Microsoft Certified Product Specialist
AUI Member

The above represents my own opinion not my employer's and does not necessarily constitute the official policy of my employer. Any opinion developed through this account represent a private conversation. Received on Tue Feb 24 1998 - 00:00:00 CET