[eclipselink-users] StoredFunctionCall with IN List parameter

["Cozart, Michele L" <Michele.L.Cozart@xxxxxxxxx>]

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);

 

1)    What am I missing or have wrong?  Please help!

 

2)    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@maine.gov

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