• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

trying to get rows to ResultSet with CallableStatement and stored function

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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>
 
reply
    Bookmark Topic Watch Topic
  • New Topic