wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL" Watch "SQL" New topic
Author

SQL

Roberto Favaro
Greenhorn

Joined: Mar 23, 2004
Posts: 24
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;
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
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
 
jQuery in Action, 2nd edition
 
subject: SQL
 
Similar Threads
SMTP Authentication in Oracle Mail using utl_smtp package
Nuber of records affected
Callable Statement
Varchar & Varchar2 in Stored procedure
Oracle Mail Error