[resolved] tOracleSP sys_refcursor issue

Anonymous — Sat, 16 Nov 2024 12:02:33 GMT

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. https://community.talend.com/t5/Design-and-Development/resolved-How-to-get-a-Oracle-REF-CURSOR-into-Talend/td-p/109527
Am thinking this maybe a regression issue with V5?
Can any one help? We are running oracle 11g.
Regards, Allen

Re: [resolved] tOracleSP sys_refcursor issue

Anonymous — Thu, 02 May 2013 05:19:20 GMT

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

Re: [resolved] tOracleSP sys_refcursor issue

Anonymous — Thu, 02 May 2013 10:32:34 GMT

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

topic Re: [resolved] tOracleSP sys_refcursor issue in Talend Studio

[resolved] tOracleSP sys_refcursor issue

Re: [resolved] tOracleSP sys_refcursor issue

Re: [resolved] tOracleSP sys_refcursor issue