Menu

#49 ORA-00904 Invalid Identifier error on export

v1.0_(example)
open
nobody
None
5
2023-02-20
2023-02-20
Anonymous
No

Receive following error on export job:

2023-02-20 03:05:23,267 [job- 1] INFO - resolving CHILD -> SHOT_STATUS (CHD_REC_STAT_FKR.SHOT_STATUS) n:1 on A.RECORD_STATUS_ID=B.RECORD_STATUS_ID...
2023-02-20 03:05:23,436 [jailer-main] ERROR - Job-error
net.sf.jailer.database.SqlException: "ORA-00904: "B"."RECORD_STATUS_ID": invalid identifier
" in statement "Insert into JAILER_ENTITY (r_entitygraph, PK, birthday, type) Select distinct 5923 as graph_id, B.ROWID as PK, 2 as birthday, 1152 as type From MCIR.SHOT_STATUS B left join JAILER_ENTITY Duplicate on Duplicate.r_entitygraph=5923 and Duplicate.type=1152 and Duplicate.PK=B.ROWID and Duplicate.PK0 is null and Duplicate.PK1 is null and Duplicate.PK2 is null and Duplicate.PK3 is null and Duplicate.PK4 is null and Duplicate.PK5 is null and Duplicate.PK6 is null and Duplicate.PK7 is null and Duplicate.PK8 is null and Duplicate.PK9 is null, MCIR.CHILD A , JAILER_ENTITY E Where (E.r_entitygraph=5923 and E.birthday = 1 and E.type=186 and E.PK is null and E.PK0=A.CHILD_ID and E.PK1 is null and E.PK2 is null and E.PK3 is null and E.PK4 is null and E.PK5 is null and E.PK6 is null and E.PK7 is null and E.PK8 is null and E.PK9 is null) and Duplicate.type is null and (A.RECORD_STATUS_ID=B.RECORD_STATUS_ID) "
at net.sf.jailer.database.Session.executeUpdate(Session.java:851) ~[jailer.jar:?]
at net.sf.jailer.entitygraph.remote.RemoteEntityGraph.addEntities(RemoteEntityGraph.java:616) ~[jailer.jar:?]
at net.sf.jailer.entitygraph.remote.RemoteEntityGraph.resolveAssociation(RemoteEntityGraph.java:482) ~[jailer.jar:?]
at net.sf.jailer.subsetting.SubsettingEngine$3.run(SubsettingEngine.java:472) ~[jailer.jar:?]
at net.sf.jailer.subsetting.SubsettingEngine$4.run(SubsettingEngine.java:505) ~[jailer.jar:?]
at net.sf.jailer.util.JobManager$JobRunner.run(JobManager.java:100) ~[jailer.jar:?]
at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_352]
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "B"."RECORD_STATUS_ID": invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) ~[?:?]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) ~[?:?]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951) ~[?:?]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513) ~[?:?]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) ~[?:?]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) ~[?:?]
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195) ~[?:?]
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036) ~[?:?]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336) ~[?:?]
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1845) ~[?:?]
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1810) ~[?:?]
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:294) ~[?:?]
at net.sf.jailer.database.Session.executeUpdate(Session.java:827) ~[jailer.jar:?]
... 6 more

Command:

Geralds-Pro-2:vm_disk_images bragggf$ 2023-02-20 03:05:11,911 [jailer-main] INFO - arguments: export tmp/em-03-03-49-314.jm oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:[server masked]:1521:[sid masked] [user maksed] - '<password>' -jdbcjar /usr/local/opt/oraclejdbcdriver/ojdbc6.jar -e /Volumes/Inland-2TBNvm/bragggf/git/kla/mcir-imm-assess/test_scripts/jailer_child_export.sql -isolation-level 1 -use-rowid-if-needed -threads 4 -entities 50 -format SQL -schemamapping MCIR=MCIR -source-schemamapping MCIR=MCIR -scope GLOBAL -datamodel datamodel/MCIRP-PROD</password>

offending SQL
====================

Insert into
JAILER_ENTITY (r_entitygraph, PK, birthday, type)
Select
distinct 5923 as graph_id,
B.ROWID as PK,
2 as birthday,
1152 as type
From
MCIR.SHOT_STATUS B
left join JAILER_ENTITY Duplicate on Duplicate.r_entitygraph = 5923
and Duplicate.type = 1152
and Duplicate.PK = B.ROWID
and Duplicate.PK0 is null
and Duplicate.PK1 is null
and Duplicate.PK2 is null
and Duplicate.PK3 is null
and Duplicate.PK4 is null
and Duplicate.PK5 is null
and Duplicate.PK6 is null
and Duplicate.PK7 is null
and Duplicate.PK8 is null
and Duplicate.PK9 is null,
MCIR.CHILD A,
JAILER_ENTITY E
Where
(
E.r_entitygraph = 5923
and E.birthday = 1
and E.type = 186
and E.PK is null
and E.PK0 = A.CHILD_ID
and E.PK1 is null
and E.PK2 is null
and E.PK3 is null
and E.PK4 is null
and E.PK5 is null
and E.PK6 is null
and E.PK7 is null
and E.PK8 is null
and E.PK9 is null
)
and Duplicate.type is null
and (A.RECORD_STATUS_ID = B.RECORD_STATUS_ID)

Discussion

  • Anonymous

    Anonymous - 2023-02-20

    Contact info:

    Gerry.Bragg@ConsiliumDBS.com

     
  • Ralf Wisser

    Ralf Wisser - 2023-02-20

    Hi,

    the Oracle error message says that the column
    "MCIR"."SHOT_STATUS"."RECORD_STATUS_ID"
    does not exist in the database you want to export from.

    Can you please check if this is indeed the case?

     

    Last edit: Ralf Wisser 2023-02-20

Anonymous
Anonymous

Add attachments
Cancel





MongoDB Logo MongoDB