Help required - record type

[urchin556]

Hi ,

I have a package which uses a record type(defined in specification)


TYPE cont_rec IS RECORD
(cont_id Number
,cont_number Number
,cont_number_modifier Number
,s_l_id Number);

TYPE cont_tbl IS TABLE OF cont_rec INDEX BY BINARY_INTEGER;

and in package body a procedure is called

go_cont(p_id In Number,
x_name in Varchar2,
x_con out cont_tbl
x_tab1 out vARCHAR2);

I need to write a procedure to call go_cont procedure in it..get the data from record type out parameter and manipulate it.
The problem is i dont know how to retrieve the data from a record type.
Please help me.

 

[SantaMufasa]

Urchin,

Here are:

1) The package header definition
2) The package body definition
3) Invocation of the package from an anonymous PL/SQL block.

create or replace package urchin is
    TYPE cont_rec IS RECORD
        (cont_id Number
        ,cont_number Number 
        ,cont_number_modifier Number
        ,s_l_id Number);
    TYPE cont_tbl IS TABLE OF cont_rec INDEX BY BINARY_INTEGER;
    procedure go_cont
        (p_id In Number
        ,x_name in Varchar2
        ,x_con out cont_tbl
        ,x_tab1 out vARCHAR2);
end;
/
create or replace package body urchin is
    procedure go_cont
        (p_id In Number
        ,x_name in Varchar2
        ,x_con out cont_tbl
        ,x_tab1 out vARCHAR2) is
    begin
        x_con(p_id).cont_id              := p_id;
        x_con(p_id).cont_number          := p_id;
        x_con(p_id).cont_number_modifier := p_id;
        x_con(p_id).s_l_id               := p_id;
        x_tab1 := p_id||': '||x_name;
    end;
end;
/

set serveroutput on format wrap
declare
    y urchin.cont_tbl;
    y_tab    varchar2(50);
begin
    for i in 1..3 loop
        urchin.go_cont(i,'Call #'||i,y,y_tab);
        dbms_output.put_line(y_tab||' = '
            ||y(i).cont_id||','
            ||y(i).cont_number||','
            ||y(i).cont_number_modifier||','
            ||y(i).s_l_id);
    end loop;
end;
/

1: Call #1 = 1,1,1,1
2: Call #2 = 2,2,2,2
3: Call #3 = 3,3,3,3

PL/SQL procedure successfully completed.

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[urchin556]

Thanks.

I dont know how many records will be returned.So is there any way to handle that. rather than doing this

for i in 1..3 loop

thanks a lot again

 

[SantaMufasa]

Sure...I just used the "Index FOR Loop" as an easy illustration of looping through a table's index/subscript, but you can process unlimited rows by replacing the "Index FOR Loop" with a "CURSOR FOR Loop":

BEGIN
...
    for x in (SELECT * from some_table
               WHERE <condition>
               ORDER BY <expression_list>) LOOP
        ...
        urchin.go_cont(<argument-1>
                      ,<argument-2>
                      ,<array/record argument>
                      ,<argument-4>);
        ...
    end loop;
end;
/

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[urchin556]

Thanks. I really appreciate your help.