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