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>