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 := 'firstname.lastname@example.org'; 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;
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.