This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes trying to get rows to ResultSet with CallableStatement and stored function Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "trying to get rows to ResultSet with CallableStatement and stored function" Watch "trying to get rows to ResultSet with CallableStatement and stored function" New topic
Author

trying to get rows to ResultSet with CallableStatement and stored function

Yuri Savhenko
Greenhorn

Joined: Sep 23, 2010
Posts: 3
Hello!

I'm using JDBC with Oracle XE 11g2, driver ojdbc6.jar (Oracle Database 11g Release 2 (11.2.0.3) JDBC Drivers, JDBC Thin for All Platforms) from oracle.com

I've got a table
<code>
CREATE TABLE ORGANIZATIONS
(
CODE VARCHAR2(4),
DESCRIPTION VARCHAR2(255)
);
</code>

And I'm trying to get all organizations into ResultSet using CallableStatement with stored function:
<code>
CallableStatement cs = connection.prepareCall("{? = call FN_GET_ROWS}");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
</code>

I've created two types:
<code>
CREATE OR REPLACE
TYPE TEST_OBJ_TYPE AS OBJECT (
CODE VARCHAR2(4),
DESCRIPTION VARCHAR2(255)
)
</code>

and

<code>
CREATE OR REPLACE
TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
</code>

and my function is

<code>
CREATE OR REPLACE
FUNCTION FN_GET_ROWS RETURN TEST_TABTYPE AS V_Test_Tabtype Test_TabType;
BEGIN
SELECT TEST_OBJ_TYPE(A.CODE, A.DESCRIPTION)
BULK COLLECT INTO V_Test_TabType
FROM
(SELECT CODE, DESCRIPTION
FROM ASM.ORGANIZATIONS
) A;
RETURN V_Test_TabType;
END;
</code>

but when I run my program I've got error:

<code>
P LS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
</code>
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: trying to get rows to ResultSet with CallableStatement and stored function
 
Similar Threads
error in Oracle function
Error executing Oracle function from JSP page
stuck up in the problem
java stored procedure problems
PL / SQL Error