This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Urgent

 
Shiv Sidhaarth
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Case is not a problem. I checked it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic