Hi, I'm having problems writing a value to a field of a variable which
is declared as a table subtype. I have a package which declares the
subtype (in the spec) as follows:

SUBTYPE rec_info IS info%ROWTYPE;

Within the body I declare a variable of type rec_info. I have two
procedures, one which inputs a variable of the same subtype which then
gets copied off to a local variable in the package. In the second
procedure, I want to overwrite some of the fields in the variable with
values into that procedure:


lr_info rec_info;

PROCEDURE p_save (pr_info IN rec_info) IS
BEGIN
lr_info := pr_info;
END;

PROCEDURE p_save (pv_text IN VARCHAR2) IS
BEGIN
lr_info.text := rtrim(pv_text);
END;


The first p_save procedure saves the record locally ok and I can see
values in it when I debug. However the second p_save does not overwrite
the value in lr_info.text and when I debug it shows me the previous
value already stored via pr_info. Hard coding a string to overwrite
lr_info.text does not work either.

If anyone can suggest what I can do I'd be very grateful!

Thanks.

On 16 May 2006 10:11:23 -0700, rajbrown@gmail.com wrote:

>Hi, I'm having problems writing a value to a field of a variable which
>is declared as a table subtype. I have a package which declares the
>subtype (in the spec) as follows:
>
>SUBTYPE rec_info IS info%ROWTYPE;
>
>Within the body I declare a variable of type rec_info. I have two
>procedures, one which inputs a variable of the same subtype which then
>gets copied off to a local variable in the package. In the second
>procedure, I want to overwrite some of the fields in the variable with
>values into that procedure:
>
>
>lr_info rec_info;
>
>PROCEDURE p_save (pr_info IN rec_info) IS
>BEGIN
> lr_info := pr_info;
>END;
>
>PROCEDURE p_save (pv_text IN VARCHAR2) IS
>BEGIN
> lr_info.text := rtrim(pv_text);
>END;
>
>
>The first p_save procedure saves the record locally ok and I can see
>values in it when I debug. However the second p_save does not overwrite
>the value in lr_info.text and when I debug it shows me the previous
>value already stored via pr_info. Hard coding a string to overwrite
>lr_info.text does not work either.
>
>If anyone can suggest what I can do I'd be very grateful!
>
>Thanks.


did you try to pass lr_info as an IN OUT parameter to the 2nd proc.
It must be some scoping problem.

--
Sybrand Bakker, Senior Oracle DBA

<rajbrown@gmail.com> wrote in message
news:1147799483.845643.217070@y43g2000cwc.googlegr oups.com...
> Hi, I'm having problems writing a value to a field of a variable which
> is declared as a table subtype. I have a package which declares the
> subtype (in the spec) as follows:
>
> SUBTYPE rec_info IS info%ROWTYPE;
>
> Within the body I declare a variable of type rec_info. I have two
> procedures, one which inputs a variable of the same subtype which then
> gets copied off to a local variable in the package. In the second
> procedure, I want to overwrite some of the fields in the variable with
> values into that procedure:
>
>
> lr_info rec_info;
>
> PROCEDURE p_save (pr_info IN rec_info) IS
> BEGIN
> lr_info := pr_info;
> END;
>
> PROCEDURE p_save (pv_text IN VARCHAR2) IS
> BEGIN
> lr_info.text := rtrim(pv_text);
> END;
>
>
> The first p_save procedure saves the record locally ok and I can see
> values in it when I debug. However the second p_save does not overwrite
> the value in lr_info.text and when I debug it shows me the previous
> value already stored via pr_info. Hard coding a string to overwrite
> lr_info.text does not work either.
>
>

First of all, why SUBtype and not just TYPE? Next question
is what version of Oracle? Of what type is REC_INFO.TEXT?
Try specifying pv_text as IN REC_INFO.TEXT%TYPE.

--
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.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.

<rajbrown@gmail.com> wrote in message
news:1147860134.462408.174200@i39g2000cwa.googlegr oups.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

Thanks for looking into it. I've discovered the problem and its down to
the debugger in PL/SQL Developer not showing the correct info. There
was another issue with my code which made it look like things further
down the line weren't working, which prompted the debug. The record
fields not being updated correctly in the code above would have
explained this problem which is where I got confused! I did a
dbms_output.put_line on the variable values and they reflected the
correct position, yet the watch window didn't!

Sorry for the confusion, and thanks for your help.