Sample Header Ad - 728x90

Insert/Update table on Pentaho when the table has a TYPE defined by user

1 vote
0 answers
867 views
I'm trying to make a remote connection from computer A to computer B where they have the same database but computer B doesn't have data in the table PERSONA_Y_ESTADOS, the goal is to get 3 rows from the table that is in computer A (which is populated) to table B which is empty. However it's giving me these errors.
2020/08/02 18:37:10 - Spoon - Running transformation using the Kettle execution engine
2020/08/02 18:37:10 - Spoon - Transformation opened.
2020/08/02 18:37:10 - Spoon - Launching transformation [pupu]...
2020/08/02 18:37:10 - Spoon - Started the transformation execution.
2020/08/02 18:37:10 - Insert / update.0 - ERROR (version 9.0.0.0-423, build 9.0.0.0-423 from 2020-01-31 04.53.04 by buildguy) : Error in step, asking everyone to stop because of:
2020/08/02 18:37:10 - Insert / update.0 - ERROR (version 9.0.0.0-423, build 9.0.0.0-423 from 2020-01-31 04.53.04 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2020/08/02 18:37:10 - Insert / update.0 - Error looking up row in database
2020/08/02 18:37:10 - Insert / update.0 - ORA-00904: "FECHAS_INICIO_FIN.FECHA_FIN": invalid identifier

2020/08/02 18:37:10 - Insert / update.0 - 
2020/08/02 18:37:10 - Insert / update.0 - 	at org.pentaho.di.core.database.Database.getLookup(Database.java:3108)
2020/08/02 18:37:10 - Insert / update.0 - 	at org.pentaho.di.core.database.Database.getLookup(Database.java:3087)
2020/08/02 18:37:10 - Insert / update.0 - 	at org.pentaho.di.core.database.Database.getLookup(Database.java:3083)
2020/08/02 18:37:10 - Insert / update.0 - 	at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:89)
2020/08/02 18:37:10 - Insert / update.0 - 	at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:299)
2020/08/02 18:37:10 - Insert / update.0 - 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2020/08/02 18:37:10 - Insert / update.0 - 	at java.lang.Thread.run(Thread.java:748)
2020/08/02 18:37:10 - Insert / update.0 - Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "FECHAS_INICIO_FIN.FECHA_FIN": invalid identifier

2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
2020/08/02 18:37:10 - Insert / update.0 - 	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
2020/08/02 18:37:10 - Insert / update.0 - 	at org.pentaho.di.core.database.Database.getLookup(Database.java:3093)
2020/08/02 18:37:10 - Insert / update.0 - 	... 6 more
2020/08/02 18:37:10 - pupu - Transformation detected one or more steps with errors.
2020/08/02 18:37:10 - pupu - Transformation is killing the other steps!
2020/08/02 18:37:10 - pupu - ERROR (version 9.0.0.0-423, build 9.0.0.0-423 from 2020-01-31 04.53.04 by buildguy) : Errors detected!
2020/08/02 18:37:10 - Spoon - The transformation has finished!!
2020/08/02 18:37:10 - pupu - ERROR (version 9.0.0.0-423, build 9.0.0.0-423 from 2020-01-31 04.53.04 by buildguy) : Errors detected!
2020/08/02 18:37:10 - pupu - ERROR (version 9.0.0.0-423, build 9.0.0.0-423 from 2020-01-31 04.53.04 by buildguy) : Errors detected!
I assume this is due to the TYPE I have created for that table, however I don't know how to solve the problem without removing the TYPE (I really don't want to do that). Here's the script for the creation of my table:
CREATE OR REPLACE TYPE Fechas_inicio_fin AS OBJECT(
fecha_inicio date,
fecha_fin date
);
/
CREATE TABLE Persona_y_Estado (
  id number primary key,
  fechas_inicio_fin Fechas_inicio_fin,
  fk_persona number,
  fk_estado number
);
/
insert into Persona_y_estado (fechas_inicio_fin, fk_persona, fk_estado) values (fechas_inicio_fin(TO_DATE('6/20/2020', 'mm/dd/yyyy'), TO_DATE('1/1/2050', 'mm/dd/yyyy')), 1, 1);
insert into Persona_y_estado (fechas_inicio_fin, fk_persona, fk_estado) values (fechas_inicio_fin(TO_DATE('6/20/2020', 'mm/dd/yyyy'), TO_DATE('1/1/2050', 'mm/dd/yyyy')), 2, 1);
insert into Persona_y_estado (fechas_inicio_fin, fk_persona, fk_estado) values (fechas_inicio_fin(TO_DATE('6/20/2020', 'mm/dd/yyyy'), TO_DATE('1/1/2050', 'mm/dd/yyyy')), 3, 1);
Asked by FlowMafia (111 rep)
Aug 2, 2020, 10:47 PM