procedure return resultset

Hi,
Could someone pl tell me how to write a oracle procedure that returns one record(resultset) back to calling java program?
The procedure should also update another table immedietly, using value in one of the fields in the record as the value for the 'where' condition - ie. use record lock.
Tried this way...

create procedure...(... , rsDet OUT types.cursorType)

open rsDet for
Select s.com_no,u.uid from ft s,crec c
where .....

Update ft set com_date = sysdate
where com_no = ???

end <proc_name>;
The problem is, how do i get the value of the com_no obtained
from the earlier select statement, and assign it in the where condition of the update statement ?

thanks
rgds
csb

csb,
Use a function (and not a procedure).

Good Luck,
Avi.

Hello Avi,
Thanks for your reply.
Ok, i shall change that to a function...(but the procedure is working anyway). What about the update? Will it work if i chage it to a function? I will be using..

open rsDet for
Select s.com_no,u.uid from ft s,crec c
where ...FOR UPDATE

Update ft set com_date = sysdate
where com_no = ???

end <fn name>;
Again how can you assign the value of the com_no obtained
from the earlier select statement for this where condition?
Thanks
csb

csb,
You can combine the UPDATE with the SELECT, as in:

Is that good for you?

Maybe you need to become more familiar with Oracle's capabilities? Do you know about these Web sites:

http://tahiti.oracle.com

http://otn.oracle.com

http://asktom.oracle.com

Good Luck,
Avi.

Hello Avi
That code dooes'nt solve the prob,Avi...b'cause if i update, then i am not able to get the resultset back. Actually i want to update one table using a value obtained in the earlier select stmt and then return the resulset of the select query.

Secondly , if there is a nested field in the table, how do you read it using JDBC? and how should the query be?
appreciate your help
thnx
csb

csb,
So combine my first and second replies. First do the update, then return the result set. (Or am I still not understanding you?)

I realize it's silly of me to ask, but I get the impression you didn't look at the Web sites I suggested, did you?

Good Luck,
Avi.

Hi Abi
I am going through the documentation sites u suggested now.
thnx
csb

Have a doubt regarding nested tables...
One of the fields say 'lm_code' in the table tab1, is a nested column.At
first i will be inserting a record into tab1.Only on a subsequent update do need to enter a value into the nested coulumn. How do u do that?
thanks
csb

csb,
I'm not sure I understand you. Are you asking what the syntax is for updating a nested table column? If yes, then I suggest you refer to the "SQL Syntax Guide" (part of the Oracle documentation).

Good Luck,
Avi.

Hi Avi
This is the problem actually...
(1)

CREATE OR REPLACE PACKAGE types AS
TYPE cursorType IS REF CURSOR;
END;

create or replace procedure...(.... , rsDet OUT types.cursorTypes)

OPEN rsDet FOR
select C.UID, S.MDF_NO , F.COM_NO
from cust_rec c,sub_data s,fault f
where c.exchange_code = exg
and s.uid= c.uid...[FOR UPDATE?];

update fault set curr_stage = 'abc'
where com_no = <???> ;

end <proc name>;

The problem is in the UPDATE stmt, - the where condition....I want to
update fault table using the value of com_no obtained from the select
statement above. How can u use the value of com_no from the above
select statement?...and then return the resultset?.

(2)
How do you then read from a nested table using JDBC?.
Suppose i have two tables..

tab1
com_no vc(4)
lm_code lm1_nt

lm1_nt (nested table)
si_no number
lm_code vc(5)
lm_date sysdate

tab2
com_no vc(4)
name vc(50)

one of the fields in tab1 is a nested column(lm_code).
At first i will be inserting a record into
tab1, but will not be inserting any data into the nested field(lm_code
would be null). Only in a subsequent updation of tab1, will i be
entering value into the nested field.
How do u insert values into this nested table in that case?
When i try to insert a value using

'Insert into THE (select lm_code from fault
where lm_code is null) values (LM1_TY(1,'2232',SYSDATE))

i am getting the erros msg...

where lm_code is null) values (LM1_TY(1,'2232',SYSDATE))
*
ERROR at line 2:
ORA-22908: reference to NULL table value

(3)
How to get the name and
the two records last entered in the nested table for a particular
com_no along with the resultset?. Would varray be a better option
here?
How to query and then how do you read it in the Java program using JDBC?

Thanks
csb

Hi
Ok..I am now able to get the results i wanted..however how do you read then using JDBC?
the resultset obtained is
ID_NO F_NO COM COD O_DATE NO
----------------- ------------ ---------- ------ --------- ----------
19980900566 COT1/ROT4 3337900 2222 04-MAY-05 2284
19980900566 COT1/ROT4 3337900 2323 05-MAY-05 2284

How to read the different value of COD and O_DATE into a variable in java?
...
id = rs.getSting(1);
fno = rs.getString(2);
com = rsGetString(3);

cod1 = <need value 2222 here>
cod2 = <and 2323 here>
id1 = <04-MAY-05 here>
id2 = <05-MAY-05 here>

thanks
rgds
csb

csb,
The Oracle documentation and/or the Web sites previously mentioned, contain the answers to your questions. [Are you having difficulty understanding the information available there?]

For reading DATE columns from "ResultSet"s, I usually use the "getTimestamp()" method, and for reading NUMBER columns, I use the "getBigDecimal()" method.

Good Luck,
Avi.

Hi Avi
I found the answer to the 2nd prb and i modified the first(did update first and then opened the cursor).However i still dont have a clue to the third question...ie how to find out if the nested column has more than one record for a particular com_no and then how to read them using JDBC in the applet
thanks
rgds

csb,
In case you haven't already done so, have a look at my last reply in this discussion -- it may be relevant for you.

Good Luck,
Avi.