Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » How to call Oracle function from eclipselink
How to call Oracle function from eclipselink [message #652481] Fri, 04 February 2011 09:06 Go to next message
imran raza khan is currently offline imran raza khanFriend
Messages: 18
Registered: January 2011
Junior Member
Hi All,

I want to call oracle function which is getting two varchar parameter and returning one varchar.

I tried to use @NamedStoredProcedureQuery, but getting following error

04-Feb-2011 02:54:48:083 ERROR [pool-1-thread-1] - Error==
Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'RANDOMPIN'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error Code: 6550
Call: BEGIN RandomPin(tpin=>?, p_msisdn=>?, p_stat=>?); END;
bind => [=> tpin, 00, G]
Query: ReadAllQuery(name="callTelePin" referenceClass=TelePin )

Regards,
imran
Re: How to call Oracle function from eclipselink [message #652482 is a reply to message #652481] Fri, 04 February 2011 09:18 Go to previous messageGo to next message
imran raza khan is currently offline imran raza khanFriend
Messages: 18
Registered: January 2011
Junior Member
Hi,

Following is code of entity

@Entity
@NamedStoredProcedureQuery(
name="callTelePin",
resultClass=TelePin.class,
procedureName="RandomPin",
returnsResultSet=true,
parameters={
@StoredProcedureParameter(queryParameter="tpin" ,direction=Direction.OUT, jdbcType = Types.VARCHAR, type=String.class),
@StoredProcedureParameter(queryParameter="p_msisdn", direction=Direction.IN, type=String.class),
@StoredProcedureParameter(queryParameter="p_stat", direction=Direction.IN, type=String.class)
}
)
public class TelePin implements Serializable {

private static final long serialVersionUID = 1L;

public TelePin() {
super();
}

@Id
private String tpin;
private String p_msisdn;
private String p_stat;


public String getTpin() {
return tpin;
}
public void setTpin(String tpin) {
this.tpin = tpin;
}
public String getP_msisdn() {
return p_msisdn;
}
public void setP_msisdn(String p_msisdn) {
this.p_msisdn = p_msisdn;
}
public String getP_stat() {
return p_stat;
}
public void setP_stat(String p_stat) {
this.p_stat = p_stat;
}


}


and following is code snippet how i am calling

Query q2 = tabsEM.createNamedQuery("callTelePin");
q2.setParameter("p_msisdn", '3288009');
q2.setParameter("p_stat", "G");

tp = (TelePin) q2.getSingleResult();

System.out.println("Dear Customer, Your generated pincode is ="+tp.getTpin());


Regards,
imran

[Updated on: Fri, 04 February 2011 09:19]

Report message to a moderator

Re: How to call Oracle function from eclipselink [message #652560 is a reply to message #652482] Fri, 04 February 2011 14:33 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Hello Imran,

This seems similar to http://www.eclipse.org/forums/index.php?t=msg&th=204031& amp;start=0&S=268bd545394728b9fcbdfb096c1c09a4

Can you provide the database stored proc signature? As in the linked post, you are also using the stored proc to return an incomplete entity - this will corrupt your cache, as the returned TelePin entity will be managed and only have the tpin id set.

Best Regards,
Chris
Re: How to call Oracle function from eclipselink [message #652562 is a reply to message #652560] Fri, 04 February 2011 14:37 Go to previous messageGo to next message
imran raza khan is currently offline imran raza khanFriend
Messages: 18
Registered: January 2011
Junior Member
Hi,

Thanks for reply infact this is not storedprocedure it is function and DBA provide access to me by this

CREATE OR REPLACE PACKAGE TABSMGR."W_VAS"
IS

-- Public function and procedure declarations
FUNCTION RandomPin (p_msisdn IN char, p_stat IN char)
RETURN char;

END W_VAS;

Regards
Re: How to call Oracle function from eclipselink [message #652977 is a reply to message #652560] Mon, 07 February 2011 19:39 Go to previous messageGo to next message
imran raza khan is currently offline imran raza khanFriend
Messages: 18
Registered: January 2011
Junior Member
Hi chris,

Any update regarding how to call Oracle Function.

Regards,
imran
Re: How to call Oracle function from eclipselink [message #653131 is a reply to message #652481] Tue, 08 February 2011 15:36 Go to previous message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

@NamedStoredProcedureQuery only works with stored procedures, not functions.

You can use the StoredFunctionCall API to define a query using the function (using a SessionCustomizer, or DescriptorCustomizer).

You could also just define it as a @NamedNativeQuery,

"Select RandomPin(?, ?) from dual"



James : Wiki : Book : Blog : Twitter
Previous Topic:@NamedStoredProcedureQuery & Out Parameter:
Next Topic:PostgreSQL/PostGIS stored function + Eclipselink converter problem
Goto Forum:
  


Current Time: Sat Dec 20 08:05:52 GMT 2014

Powered by FUDForum. Page generated in 0.01927 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software