Toad World® Forums

How to execute procedure conatining object type as in parameter?


#1

create or replace
procedure GETINFO(P_IDS in IDS_TABLE) is — IDS_TABLE is a table of object type conatins id1,id2,id3 cols.
begin
for I in 1 … P_IDS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(P_IDS(i).ID1 || ‘,’ || P_IDS(i).ID2 || ‘,’ || P_IDS(i).ID3);
END LOOP;
end GETINFO;

how to execute this procedure to get the out put?


#2

Hi gurus. Can you please help me understand


#3

Given the lack of a response so far, the following should help:

create or replace type person_type as object (

last_name varchar2(30),

middle_name varchar2(30),

first_name varchar2(30)

);

create or replace procedure display_person (pPerson in person_type)

as

begin

dbms_output.enable;

– Has a person been defined?

if (pPerson is null) then

dbms_output.put_line(‘A mystery person has been passed. I cannot cope with this!’);

dbms_output.put_line(’ ');

return;

end if;

– We have a person. Display the details.

dbms_output.put_line(‘First Name:’ || pPerson.first_name);

dbms_output.put_line(‘Middle Name:’ || pPerson.middle_name);

dbms_output.put_line(‘Last Name:’ || pPerson.last_name);

dbms_output.put_line(’ ');

end;

declare

you person_type; – You are a null person!

me person_type; – I am a real person!

other person_type; – Other, is a real person, with no details.

begin

me := person_type(‘Dunbar’,’[TeamT]’, ‘Norm’);

other := person_type(null, null, null);

display_person(pPerson => you);

display_person (pPerson => me);

display_person (pPerson => other);

end;

Execute the above in Toad, and the following will result:

A mystery person has been passed. I cannot cope with this!

First Name:Norm

Middle Name:[TeamT]

Last Name:Dunbar

First Name:

Middle Name:

Last Name:

HTH

Cheers,

Norm. [TeamT]


#4

Try again, using rich formatting, the above has been abused severely!

create or replace type person_type as object (
last_name varchar2(30),
middle_name varchar2(30),
first_name varchar2(30)
);

create or replace procedure display_person (pPerson in person_type)
as
begin
dbms_output.enable;

-- Has a person been defined?
if (pPerson is null) then
    dbms_output.put_line('A mystery person has been passed. I cannot cope with this!');
    dbms_output.put_line(' ');
    return;
end if;

-- We have a person. Display the details.
dbms_output.put_line('First Name:' || pPerson.first_name);
dbms_output.put_line('Middle Name:' || pPerson.middle_name);
dbms_output.put_line('Last Name:' || pPerson.last_name);
dbms_output.put_line(' ');

end;

declare
you person_type; – You are a null person!
me person_type; – I am a real person!
other person_type; – Other, is a real person, with no details.

begin
me := person_type(‘Dunbar’,’[TeamT]’, ‘Norm’);
other := person_type(null, null, null);
display_person(pPerson => you);
display_person (pPerson => me);
display_person (pPerson => other);
end;

If you execute the above, in Toad, the following will be seen on the DBMS_OUTPUT tag in the editor.

A mystery person has been passed. I cannot cope with this!

First Name:Norm
Middle Name:[TeamT]
Last Name:Dunbar

First Name:
Middle Name:
Last Name:

HTH

Cheers,

Norm. [TeamT]