• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

trying to get rows to ResultSet with CallableStatement and stored function

 
Yuri Savhenko
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic