aspose file tools*
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 Spring in Action this week in the Spring 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>
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: trying to get rows to ResultSet with CallableStatement and stored function