Re: specifying length of return VARCHAR2

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 31 Jul 1999 22:57:03 +0100
Message-ID: <933458544.25078.2.nnrp-01.9e984b29@news.demon.co.uk>

I think I may have to withdraw my vote on that one. I broke Rule #1 - if it looks good, test it.

Can you post an example that demonstrates this to be working, the following suggests that it doesn't (run against 8.0.4 and 8.1.5)

create or replace package vc50 as

     dummy_vc50 varchar2(50);
     subtype vc50 is dummy_vc50%type;

     function return_vc50 (i_vc in varchar2) return vc50;
     pragma restrict_references(return_vc50, wnds, rnds, wnps , rnps);

end;
/

create or replace package body vc50 as

function return_vc50(i_vc in varchar2) return vc50 is

v_vc50 vc50;

begin

     v_vc50 := i_vc;
     return v_vc50;

end;

end;
/

create table vc50_demo as
select vc50.return_vc50(dummy) dummy from dual;

describe vc50_demo

With the output:

---

Package created.

Package body created.

Table created.

Name                                   Null?    Type
-------------------------------------- -------- --------------------------
DUMMY                                           VARCHAR2(4000)   <<---

should be varchar2(50) ?

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Jonathan Lewis wrote in message
<933363032.18258.0.nnrp-10.9e984b29_at_news.demon.co.uk>...
>That gets my vote for cute trick of the week
>Thanks.
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>gregory_s_at_my-deja.com wrote in message <7nsspv$sdk$1_at_nnrp1.deja.com>...
>>I've come across a solution to the oft-quoted limitation of PL/SQL
>>that the length of a VARCHAR2 function return cannot be specified.
>>
>>The typical work-around is to truncate the result where it is used
>>in a view. This is makes for heavy syntax when a common function
>>is used many times.
>>
>>However, at least in Oracle 8, one can do the following:
>>
>>dummy_variable VARCHAR2(150);
>>SUBTYPE t_return IS dummy_variable%TYPE;
>>FUNCTION get (ix IN NUMBER) RETURN t_return;
>>
>>Works nicely. Hope that helps.
>
>
>
Received on Sat Jul 31 1999 - 16:57:03 CDT