[resolved] tOracleSP sys_refcursor issue

One Star

[resolved] tOracleSP sys_refcursor issue

Hi,
I have a simple oracle SP that return a RECORDSET as a sys_refcursor.
I keep getting the following error:
Exception in component tOracleSP_1
java.sql.SQLException: Invalid column type: 1111
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269)
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3500)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:122)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:271)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:362)
at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1340)
Here is the SP:
create or replace
procedure sp_get_apc_logon
(
p_win_user in varchar2,
datacur out sys_refcursor
)
as
begin
open datacur for select insight_user, brand from bir_apc.apc_logons;
end sp_get_apc_logon;
I am on version TOS V5.2. I have checked the forum and seen a similar issue that had worked on TOSV2.4. http://www.talendforge.org/forum/viewtopic.php?id=8686
Am thinking this maybe a regression issue with V5?
Can any one help? We are running oracle 11g.
Regards, Allen

Accepted Solutions
One Star

Re: [resolved] tOracleSP sys_refcursor issue

Hi Shong,
Thanks for your help. I fould the issue, I was using OUT instead of RECORDSET.
Allen

All Replies
Community Manager

Re: [resolved] tOracleSP sys_refcursor issue

Hi
It seems the data type of column is invalid for the value. Can you upload a screenshot of tParseRecordSet? For testing, I suggest you to use a tOracleInput to execute the following query and see what's data you get from the table.
"select insight_user, brand from bir_apc.apc_logons"

Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tOracleSP sys_refcursor issue

Hi Shong,
Thanks for your help. I fould the issue, I was using OUT instead of RECORDSET.
Allen