*
The moose likes JDBC and the fly likes Urgent Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Urgent" Watch "Urgent" New topic
Author

Urgent

Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Hi,
Using callable statement, can i call a function inside package[In Oracle]? If so, how can i call? FYI, the function has select statement inside it and it returns all records in a table.
Thanks,
Sankar
Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
find following dummy code snippet if useful to u..
String strFunName = {?=call(?....?)}
where first ? is for return value and
second ? is for parameter u passed it can be any no.
now call the prepareCall method of con like in following steps
1) CallableStatement cStmt = con.prepareCall(strFunName );
2) cStmt.setXXX(parameterValue);
3) cStmt.registerOutParameter(1, Types.NUMERIC)
4) cStmt.executeUpdate();
5) if the return type is int..
int value = cStmt.getInt(1);
thats it
Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Hi,
Thanks for ur reply. I tried it. But, it seems i cant call a function using callable statement. Bcos, it gave the error as
********************************************************
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'MYRECRTN' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
*********************************************************
I have declared and defined MYRECRTN as function inside mypackage. I prepared the callable statement using "{call mypackage.myrecrtn()}".
Is there any other way to call a function from java???
Thanks in advance,
Sankar
Originally posted by Himanshu Khanna:
find following dummy code snippet if useful to u..
String strFunName = {?=call(?....?)}
where first ? is for return value and
second ? is for parameter u passed it can be any no.
now call the prepareCall method of con like in following steps
1) CallableStatement cStmt = con.prepareCall(strFunName );
2) cStmt.setXXX(parameterValue);
3) cStmt.registerOutParameter(1, Types.NUMERIC)
4) cStmt.executeUpdate();
5) if the return type is int..
int value = cStmt.getInt(1);
thats it

Himanshu Khanna
Greenhorn

Joined: Aug 15, 2001
Posts: 29
Hey , it may be a case of Lower case / Upper case .Please check the names of the package & the function have the same case in Oracle as well as Ur java code
Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Case is not a problem. I checked it.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Urgent