Re: PL/SQL Package Level Subtype Problem

From: Vladimir M. Zakharychev <bob--nospam--_at_dynamicpsp.com>
Date: Wed, 17 May 2006 16:24:46 +0400
Message-ID: <e4f4mh$1d9v$1@hypnos.nordnet.ru>

<rajbrown_at_gmail.com> wrote in message
news:1147860134.462408.174200_at_i39g2000cwa.googlegroups.com...
> I'm using Oracle 9i - sorry, should have stated that. Using subtype was
> a recommendation from a colleague - I'll try and find out why this was
> suggested (sorry, quite new to plsql myself). What would the equivalent
> TYPE syntax for this be? Can't seem to find a TYPE declaration which
> allows you to specify a table%rowtype...
>
> REC_INFO.TEXT is a varchar2 - I've tried specifying pv_text as
> rec_info.text%type as you suggested, but unfortunately I get the same
> results.
>
> Interestingly, if I do not supply a value in pr_info.text in the first
> procedure (so its NULL), the second procedure then allows me to set the
> value. Only when I set a value in the first instance does it not let me
> overwrite it. In fact, whatever I seem to do, as soon as a value is
> assigned I cannot overwrite it.
>

Well, right, sorry for confusion, you can't use TYPE here, SUBTYPE is correct. However, I was unable to reproduce described behavior on my test 9.2.0.7 instance - what's exact version of yours? Here's my test case:

create table testinfo (id number(10,0), text varchar2(100)) /
insert into testinfo values (1,'some stuff') /
commit;
create or replace package test_pkg
as

subtype rec_info is testinfo%rowtype;

procedure p_save ( ri in rec_info);

procedure p_save (txt in varchar2);

function get_ri return rec_info;

end test_pkg;
/

create or replace package body test_pkg
as

l_info rec_info;

procedure p_save ( ri in rec_info)
is
begin
l_info := ri;
end p_save;

procedure p_save (txt in varchar2)
is
begin
l_info.text := rtrim(txt);
end p_save;

function get_ri return rec_info
is
begin
return l_info;
end get_ri;

end test_pkg;
/

set serveroutput on

declare
ri test_pkg.rec_info;
begin
select * into ri from testinfo where rownum = 1;

test_pkg.p_save(ri);
dbms_output.put_line(ri.id||'='||ri.text);
test_pkg.p_save('different stuff');

ri := test_pkg.get_ri;
dbms_output.put_line(ri.id||'='||ri.text); ri.text := 'something completely different'; test_pkg.p_save(ri);
dbms_output.put_line(ri.id||'='||ri.text); end;

/

1=some stuff
1=different stuff
1=something completely different

What will be the output on your db?

-- 
   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)
   http://www.dynamicpsp.com 

Received on Wed May 17 2006 - 07:24:46 CDT