Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] StoredFunctionCall with IN List parameter

You need to be using the Oracle platform class specific to the version of the driver/database you are using. Try specifying it using <property name="eclipselink.target-database" value="org.eclipse.persistence.platform.database.oracle.Oracle11Platform"/>
in the persistence.xml


Best Regards,
Chris



On 28/06/2012 4:11 PM, Cozart, Michele L wrote:
Hi David,

I've made the changes you suggested and now I'm getting an sql exception; Unsupported feature error code: 17023
What I've tried:

1)     Changed Oracle driver version from ojdbc6 v 11.1.0.7.0 to ojdbc14 v10.2.0.4.0

2)    Upgraded to eclipselink 2.4.0 (not for this issue but to be on current version)

3)    Added then uncommented: call.setResult()

4)    Talked very dirty (swore)

5)
Let me know if you need anything else.  Thanks,  Michele


Here's the updated portion of the method containing your suggestions (stack trace follows):

             em = super.getEntityManager();
             emJPA = org.eclipse.persistence.jpa.JpaHelper.getEntityManager(em);

             txn = emJPA.getTransaction();

             StoredFunctionCall call = new StoredFunctionCall();

             call.setProcedureName("EDMRDBA.USERS_PKG.GET_USERS_NAME_TEST");

             // Only in parameters (not results) are added using the .addNamedArgument method
             call.addNamedArgument("in_users_id_list", "in_users_id_list", 2003, "USERS_ID_LIST_TYPE");

             // The next method is required when you're calling
             // a stored function.
// Note: I tried both with and without the following line of code.  Same error either way.
     //        call.setResult("out_usersname", String.class);

             ValueReadQuery query = new ValueReadQuery();
             query.setCall(call);
             query.addArgument("in_users_id_list", java.sql.Array.class);

             // Shouldn't be necessary since I'm sending in the userids
             // as a List<String>  already.  In case it does matter:
             List<String>  userIds = new ArrayList<String>();

             for (int i = 0; i<  userIdsList.size(); i++)
             {
                 userIds.add(i, userIdsList.get(i));
            }

             query.addArgumentValue(userIds);
              String queryResult = (String)emJPA.getActiveSession().executeQuery(query);

Here's the stack trace and below is the code with the changes you suggested:
[EL Finer]: connection: 2012-06-28 15:49:25.102--ServerSession(31393583)--Thread(Thread[http-8080-6,5,main])--client acquired: 19364023
[EL Finer]: transaction: 2012-06-28 15:49:25.102--ClientSession(19364023)--Thread(Thread[http-8080-6,5,main])--acquire unit of work: 6702683
[EL Finest]: query: 2012-06-28 15:49:25.102--UnitOfWork(6702683)--Thread(Thread[http-8080-6,5,main])--Execute query ValueReadQuery()
[EL Finest]: connection: 2012-06-28 15:49:25.102--ServerSession(31393583)--Connection(3521682)--Thread(Thread[http-8080-6,5,main])--Connection acquired from connection pool [default].
[EL Fine]: sql: 2012-06-28 15:49:25.102--ServerSession(31393583)--Connection(3521682)--Thread(Thread[http-8080-6,5,main])--BEGIN ? := EDMRDBA.USERS_PKG.GET_USERS_NAME_TEST(in_users_id_list=>?); END;
             bind =>  [=>  , null]
[EL Fine]: sql: 2012-06-28 15:49:30.32--ServerSession(31393583)--Thread(Thread[http-8080-6,5,main])--SELECT 1 FROM DUAL
[EL Finest]: connection: 2012-06-28 15:49:30.32--ServerSession(31393583)--Connection(3521682)--Thread(Thread[http-8080-6,5,main])--Connection released to connection pool [default].
[EL Warning]: 2012-06-28 15:49:30.32--UnitOfWork(6702683)--Thread(Thread[http-8080-6,5,main])--Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.0.v20120608-r11652): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Unsupported feature
Error Code: 17023
Call: BEGIN ? := EDMRDBA.USERS_PKG.GET_USERS_NAME_TEST(in_users_id_list=>?); END;
             bind =>  [=>  , null]
Query: ValueReadQuery()
             at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
             at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:646)
             at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:537)
             at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1800)
             at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:566)
             at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
             at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
             at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:264)
             at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:246)
             at org.eclipse.persistence.queries.DataReadQuery.executeNonCursor(DataReadQuery.java:197)
             at org.eclipse.persistence.queries.DataReadQuery.executeDatabaseQuery(DataReadQuery.java:152)
             at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:852)
             at org.eclipse.persistence.queries.DataReadQuery.execute(DataReadQuery.java:137)
             at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:751)
             at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2875)
             at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1602)
             at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1584)
             at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1535)
             at dep.edmr.dao.impl.UsersDAOImpl.getSortedUsersNameMapByUserIds(UsersDAOImpl.java:282)
             at dep.edmr.business.UserController.getDepInspectorsNameMap(UserController.java:320)
             at dep.edmr.action.DepInspectorApprovalsAction.getDepInspectorNameMap(DepInspectorApprovalsAction.java:490)
             at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
             at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
             at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
             at java.lang.reflect.Method.invoke(Method.java:601)
             at ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:878)
             at ognl.OgnlRuntime.getMethodValue(OgnlRuntime.java:1441)
             at ognl.ObjectPropertyAccessor.getPossibleProperty(ObjectPropertyAccessor.java:60)
             at ognl.ObjectPropertyAccessor.getProperty(ObjectPropertyAccessor.java:147)
             at com.opensymphony.xwork2.ognl.accessor.ObjectAccessor.getProperty(ObjectAccessor.java:17)
             at ognl.OgnlRuntime.getProperty(OgnlRuntime.java:2303)
             at com.opensymphony.xwork2.ognl.accessor.CompoundRootAccessor.getProperty(CompoundRootAccessor.java:137)
             at ognl.OgnlRuntime.getProperty(OgnlRuntime.java:2303)
             at ognl.ASTProperty.getValueBody(ASTProperty.java:114)
             at ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
             at ognl.SimpleNode.getValue(SimpleNode.java:258)
             at ognl.Ognl.getValue(Ognl.java:494)
             at ognl.Ognl.getValue(Ognl.java:458)
             at com.opensymphony.xwork2.ognl.OgnlUtil.getValue(OgnlUtil.java:213)
             at com.opensymphony.xwork2.ognl.OgnlValueStack.getValueUsingOgnl(OgnlValueStack.java:277)
             at com.opensymphony.xwork2.ognl.OgnlValueStack.tryFindValue(OgnlValueStack.java:260)
             at com.opensymphony.xwork2.ognl.OgnlValueStack.tryFindValueWhenExpressionIsNotNull(OgnlValueStack.java:242)
             at com.opensymphony.xwork2.ognl.OgnlValueStack.findValue(OgnlValueStack.java:222)
             at org.apache.struts2.components.Component.findValue(Component.java:255)
             at org.apache.struts2.components.ListUIBean.evaluateExtraParams(ListUIBean.java:71)
             at org.apache.struts2.components.Select.evaluateExtraParams(Select.java:105)
             at org.apache.struts2.components.UIBean.evaluateParams(UIBean.java:858)
             at org.apache.struts2.components.UIBean.end(UIBean.java:510)
             at org.apache.struts2.views.jsp.ComponentTagSupport.doEndTag(ComponentTagSupport.java:42)
             at org.apache.jsp.depInspectorApprovalsListAll_jsp._jspx_meth_s_005fselect_005f0(depInspectorApprovalsListAll_jsp.java:2379)
             at org.apache.jsp.depInspectorApprovalsListAll_jsp._jspx_meth_s_005fform_005f1(depInspectorApprovalsListAll_jsp.java:2198)
             at org.apache.jsp.depInspectorApprovalsListAll_jsp._jspService(depInspectorApprovalsListAll_jsp.java:340)
             at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
             at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
             at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:386)
             at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
             at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
             at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
             at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
             at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
             at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
             at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
             at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
             at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
             at org.apache.struts2.dispatcher.ServletDispatcherResult.doExecute(ServletDispatcherResult.java:157)
             at org.apache.struts2.dispatcher.StrutsResultSupport.execute(StrutsResultSupport.java:186)
             at com.opensymphony.xwork2.DefaultActionInvocation.executeResult(DefaultActionInvocation.java:374)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:278)
             at org.apache.struts2.interceptor.ProfilingActivationInterceptor.intercept(ProfilingActivationInterceptor.java:104)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)
             at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:211)
             at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:211)
             at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:190)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:243)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171)
             at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at org.apache.struts2.interceptor.ScopeInterceptor.intercept(ScopeInterceptor.java:431)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:211)
             at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:90)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at dep.edmr.interceptor.RolesInterceptor.intercept(RolesInterceptor.java:40)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at dep.edmr.interceptor.AuthenticationInterceptor.intercept(AuthenticationInterceptor.java:34)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at dep.edmr.interceptor.LoginInterceptor.intercept(LoginInterceptor.java:98)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:192)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)
             at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
             at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)
             at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:510)
             at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)
             at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)
             at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
             at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
             at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
             at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
             at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
             at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
             at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
             at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
             at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:864)
             at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
             at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1665)
             at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.SQLException: Unsupported feature
             at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:120)
             at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:137)
             at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:250)
             at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:330)
             at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:355)
             at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:598)
             at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(DatabaseError.java:1071)
             at oracle.jdbc.driver.PhysicalConnection.createArrayOf(PhysicalConnection.java:16377)
             at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.createArray(DatabasePlatform.java:2904)
             at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.createArray(DatabasePlatform.java:2885)
             at org.eclipse.persistence.internal.databaseaccess.BindCallCustomParameter.convert(BindCallCustomParameter.java:142)
             at org.eclipse.persistence.internal.databaseaccess.InParameterForCallableStatement.set(InParameterForCallableStatement.java:30)
             at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:2256)
             at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:724)
             at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:587)
             ... 131 more











Michele Cozart
Senior Programmer Analyst, Natural Resources Application Development
State of Maine, Office of Information Technology
michele.l.cozart@xxxxxxxxx<mailto:michele.l.cozart@xxxxxxxxx>
207-287-7872 (Desk)
Confidentiality Notice: This email message, including any attachments, is for the sole use of the intended recipients and may contain confidential and privileged information. If you are not the intended recipient, or an authorized agent of the intended recipient, please immediately contact the sender by reply email and destroy/delete all copies of the original message. Any unauthorized review, use, copying, forwarding, disclosure, or distribution by other than the intended recipient or authorized agent is prohibited. - required



From: eclipselink-users-bounces@xxxxxxxxxxx [mailto:eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of DAVID MCCANN
Sent: Wednesday, June 27, 2012 3:49 PM
To: EclipseLink User Discussions
Subject: Re: [eclipselink-users] StoredFunctionCall with IN List parameter

Hey Michele,

The following should work for the simple case you mention below.  Note that when adding the named argument to the call you'll want to also include the SQL type (2003 for ARRAY) and the name of your nested table ("USERS_ID_LIST_TYPE").  Also, I would typically add the input parameter values to the query as opposed to the call.  You have the option of passing in a List of argument values when you execute the query if you'd rather not modify the query directly (see public Object executeQuery(DatabaseQuery query, List argumentValues) on Session).
StoredFunctionCall call = new StoredFunctionCall();
call.setProcedureName("EDMRDBA.USERS_PKG.GET_USERS_NAME_TEST");
call.addNamedArgument("in_users_id_list", "in_users_id_list", 2003, "USERS_ID_LIST_TYPE");

ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
query.addArgument("in_users_id_list", java.sql.Array.class);

List<Integer>  userIds = new ArrayList<Integer>();
userIds.add(1); userIds.add(2); userIds.add(3);

query.addArgumentValue(userIds);
...
Hopefully this will get you going.

Cheers,
Dave
________________________________

On 26/06/2012 1:54 PM, Cozart, Michele L wrote:
Hi there,

I need help with a stored function.  I can get the same function to work if I send in a string as the input parameter.  However, I can't seem to get the syntax correct for sending in a List of string and returning a cursor.  I keep getting the error message of invalid column type.

System Information:

Tomcat 6.0; jdk 1.6; maven project; eclipselink 2.3.0; javax.persistence-2.0.3; database Oracle 10g; oracle driver: ojdbc6-11.1.0.7.0; NetBeans 7.1.2 IDE

Here is the code etc. for just sending in the List (I'll deal with the cursor later):

Error message:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Invalid column type
Error Code: 17004
Call: BEGIN ? := EDMRDBA.USERS_PKG.GET_USERS_NAME_TEST(in_users_id_list=>?); END;
             bind =>  [=>  out_usersname, [WW116606, WW116961, WW129095, WW142238, WW158651, WW304257, WW336979, WW343012, WW363457, WW514782, WW591451, WW597272, WW600060, WW733389, WW740855, WW768264, WW776733, WW831845, WW888663, WW921190, WW935641, WW983789, WW994715]]
Query: ValueReadQuery()

Method setting up the query:
   public String getUsersNameListByUserIds(List<String>  userIdsList)
    {
         EntityManager em = null;
         EntityTransaction txn;

         org.eclipse.persistence.jpa.JpaEntityManager emJPA = null;
         String usersname = "";

         try
         {
             em = super.getEntityManager();
             emJPA = org.eclipse.persistence.jpa.JpaHelper.getEntityManager(em);

             txn = emJPA.getTransaction();

             StoredFunctionCall call = new StoredFunctionCall();

             call.setProcedureName("EDMRDBA.USERS_PKG.GET_USERS_NAME_TEST");

             // Only in parameters (not results) are added using the .addNamedArgument method
             call.addNamedArgumentValue("in_users_id_list", userIdsList);

             // The next method is required when you're calling
             // a stored function.
             call.setResult("out_usersname", String.class);

             ValueReadQuery query = new ValueReadQuery();
             query.setCall(call);
             query.addArgument("in_users_id_list");
             usersname = (String) emJPA.getActiveSession().executeQuery(query);
         }
         catch (RuntimeException e)
         {
             System.out.println("inside exception: " + e.getMessage());
             logger.debug("Exception caught in: " + this.getClass().getName() + " Error: " + e.getMessage());
         }
         finally
         {
             if (em.isOpen() || em != null)
             {
                 em.close();
             }
         }
         return usersname;

Database scripts:

CREATE OR REPLACE PACKAGE EDMRDBA.USERS_PKG AS

// Type that will be returned by the cursor
TYPE USERSNAME_REC IS RECORD (
    USERSID VARCHAR2(20),
    USERSNAME  VARCHAR2(100)
   );

// cursor to be returned when I get the cursor portion working
TYPE CURSOR_TYPE IS REF CURSOR;

FUNCTION GET_USERS_NAME_test(in_users_id_list IN USERS_ID_LIST_TYPE) RETURN VARCHAR2;

   END USERS_PKG;

CREATE OR REPLACE PACKAGE BODY EDMRDBA.USERS_PKG AS

FUNCTION GET_USERS_NAME_TEST(in_users_id_list IN  users_id_list_type) RETURN VARCHAR2 AS
    out_usersname VARCHAR2(255);
BEGIN
         SELECT
             userid INTO out_usersname--,
           FROM users
           WHERE userid IN (SELECT COLUMN_VALUE FROM TABLE(in_users_id_list));
    RETURN('test');
END;
END USERS_PKG;


CREATE OR REPLACE TYPE EDMRDBA.USERS_ID_LIST_TYPE
AS TABLE OF varchar2(8);


What am I missing or have wrong?  Please help!


When I add a cursor as the return value, would I need to use a PLSQLNamedStoredFunction since the cursor will be of the following type?


CREATE OR REPLACE TYPE EDMRDBA.O_USERSNAME_TYPE AS OBJECT (
    USERSID VARCHAR2(20),
    USERSNAME  VARCHAR2(100)

CREATE OR REPLACE TYPE EDMRDBA.USERS_NAME_TYPE
   AS TABLE OF O_USERSNAME_TYPE

Any help would be greatly appreciated.

Thanks,


Michele Cozart
Senior Programmer Analyst, Natural Resources Application Development
State of Maine, Office of Information Technology
michele.l.cozart@xxxxxxxxx<mailto:michele.l.cozart@xxxxxxxxx>
207-287-7872 (Desk)
Confidentiality Notice: This email message, including any attachments, is for the sole use of the intended recipients and may contain confidential and privileged information. If you are not the intended recipient, or an authorized agent of the intended recipient, please immediately contact the sender by reply email and destroy/delete all copies of the original message. Any unauthorized review, use, copying, forwarding, disclosure, or distribution by other than the intended recipient or authorized agent is prohibited. - required








_______________________________________________

eclipselink-users mailing list

eclipselink-users@xxxxxxxxxxx<mailto:eclipselink-users@xxxxxxxxxxx>

https://dev.eclipse.org/mailman/listinfo/eclipselink-users




_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


Back to the top