SQL

[Roberto Favaro]

Im using a stored procedure to test data stored in my oracle database.I havnt access to the database other than through the java application. I was testing the oracle database seperatly but im getting an error.My procedure is returning more data than I have called.Is there a way to loop through and display all the data.Im trying to display a members orders.

DECLARE
me Members.emailAddress%type;
mfn Members.firstName%type;
mln Members.lastName%type;
mp Members.passwd%type;
orid Orders.orderId%type;
ord Orders.dates%type;
ors Orders.status%type;
statuss boolean;

procedure get_members_details(
member_emailAddress in Members.emailAddress%type,
member_firstName out Members.firstName%type,
member_lastName out Members.lastName%type,
member_passwd out Members.passwd%type,
order_orderId out Orders.orderId%type,
order_dates out Orders.dates%type,
order_status out Orders.status%type,
statuss out boolean) is
begin
select firstName,lastName,passwd,orderId,dates,status
into member_firstName,member_lastName,member_passwd,order_orderId,order_dates,order_status
from Members,Orders
where Members.memberId = Orders.memberId
and Members.emailAddress = member_emailAddress;
statuss := true;

exception
when no_data_found then
statuss := false;
end;
begin
me := '[e-mail]';
get_members_details(me,mfn,mln,mp,orid,ord,ors,statuss);
if (statuss) then
dbms_output.put_line(me || ' ' || mfn || ' ' || mln || ' ' || mp || ' ' ||
orid || ' ' || ord || ' ' || ors);
else
dbms_output.put_line('Member' || me || 'not found');
end if;
end;

[George Stoianov]

I apologize if this sounds silly, my Oracle PL/SQL skills are kind of rusty, but you say you do not have access to the db in anyway except from Java, right???

If yes then the statement you have, I assume the one you posted, should create a stored procedure but there is no CREATE PROCEDURE in there.... To loop through the data in java your stored procedure has to return it from the DB in some way and I don't see that happening, there should be an OUT parameter and it is not an object in most cases, if memory serve me right, so you may have to concatenate a few things, you are interested in and slap them in the return parameter in java and print so you can double check.

I hope this makes sense.
George