• Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL  RSS feed

Roberto Favaro
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

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
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;

when no_data_found then
statuss := false;
me := 'superdrog@gmail.com';
if (statuss) then
dbms_output.put_line(me || ' ' || mfn || ' ' || mln || ' ' || mp || ' ' ||
orid || ' ' || ord || ' ' || ors);
dbms_output.put_line('Member' || me || 'not found');
end if;
George Stoianov
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!