• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL

 
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

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 := 'superdrog@gmail.com';
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;
 
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
George
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic