Forum Stats

  • 3,875,356 Users
  • 2,266,907 Discussions
  • 7,912,175 Comments

Discussions

lb genschema leads to unique constraint error

User_W45BH
User_W45BH Member Posts: 2 Blue Ribbon

Hi,

with the new version of sqlcl (21.3.0.278.1045) liquibase failes with genschema:

> lb genschema

[...]

[Method processCaptureTable]: Processing has failed for your request.

ORA-00001: unique constraint (SCHEMA1.DATABASECHANGELOG_EXPORT_PK) violated

Anyone else has seen this behaviour? Any suggestions?

Best regards,

Diego

Tagged:

Answers

  • Paavo
    Paavo Member Posts: 756 Silver Badge
    edited Oct 25, 2021 12:10PM

    Hi, yes

    [Method processCaptureTable]: Processing has failed for your request.
    ORA-00001: unique constraint (X.DATABASECHANGELOG_EXPORT_PK) violated
     >lb version
    
    
    Liquibase version: 4.4.2
    Extension Version: 21.3.1.0
    
    
    
  • bglazyr
    bglazyr Member Posts: 49 Bronze Badge

    I have the same problem

    SQL> lb genschema -debug

    [{log=false, replace=false, dir=, type=, sql=false, password=, enable=true, context=, grants=false, silent=false, debug=true, runalways=false, synonyms=false, emit_schema=false, count=, changelog=, label=, runonchange=false, url=, command=genschema, fail=false, help=false, report=true, name=, privs=true, user=, isSqlCl=true}]



    Export Flags Used:


    Export Grants          false

    Export Synonyms        false

    [Method loadCaptureTable]:

                    Executing

    [Type - TYPE_SPEC]:                         258 ms

    [Type - TYPE_BODY]:                          75 ms

    [Type - SEQUENCE]:                          201 ms

    [Type - DIRECTORY]:                           5 ms

    [Type - CLUSTER]:                            36 ms

    [Type - TABLE]:                            6529 ms

    [Type - MATERIALIZED_VIEW_LOG]:             126 ms

    [Type - MATERIALIZED_VIEW]:                 239 ms

    [Type - VIEW]:                              840 ms

    [Type - REF_CONSTRAINT]:                   1718 ms

    [Type - DIMENSION]:                           8 ms

    [Type - FUNCTION]:                          144 ms

    [Type - PROCEDURE]:                         374 ms

    [Type - PACKAGE_SPEC]:                      517 ms

    [Type - DB_LINK]:                            16 ms

    [Type - SYNONYM]:                            50 ms

    [Type - INDEX]:                            2987 ms

    [Type - TRIGGER]:                           257 ms

    [Type - PACKAGE_BODY]:                    13796 ms

    [Type - JOB]:                                74 ms

                    End

    [Method loadCaptureTable]:                28251 ms

    [Method processCaptureTable]: --------------------------------------------------------------------------------

    Oracle Liquibase Exception Handler additional information available using -debug

    --------------------------------------------------------------------------------

    processError

    --------------------------------------------------------------------------------

    java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)

           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)

           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)

           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)

           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)

           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)

           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)

           at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1038)

           at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1507)

           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1287)

           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)

           at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3910)

           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3889)

           at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.insertCommentCaptureTable(SchemaGenerator.java:901)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.processCaptureTable(SchemaGenerator.java:1070)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.doSchemaExport(SchemaGenerator.java:186)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.LiquibaseActions.genDbSchemaWork(LiquibaseActions.java:545)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.liquibaseCommand.handleEvent(liquibaseCommand.java:206)

           at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)

           at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:337)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1056)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:364)

    Caused by: Error : 1, Position : 0, Sql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), OriginalSql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), Error Msg = ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)

           ... 25 more


    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------

    Oracle Liquibase Exception Handler additional information available using -debug

    --------------------------------------------------------------------------------

    processCaptureTable

    --------------------------------------------------------------------------------

    java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)

           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)

           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)

           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)

           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)

           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)

           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)

           at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1038)

           at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1507)

           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1287)

           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)

           at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3910)

           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3889)

           at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.insertCommentCaptureTable(SchemaGenerator.java:901)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.processCaptureTable(SchemaGenerator.java:1070)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.doSchemaExport(SchemaGenerator.java:186)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.LiquibaseActions.genDbSchemaWork(LiquibaseActions.java:545)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.liquibaseCommand.handleEvent(liquibaseCommand.java:206)

           at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)

           at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:337)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1056)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:364)

    Caused by: Error : 1, Position : 0, Sql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), OriginalSql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), Error Msg = ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)

           ... 25 more


    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------

    Oracle Liquibase Exception Handler additional information available using -debug

    --------------------------------------------------------------------------------

    processCaptureTable

    --------------------------------------------------------------------------------

    java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)

           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)

           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)

           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)

           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)

           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)

           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)

           at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1038)

           at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1507)

           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1287)

           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)

           at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3910)

           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3889)

           at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.insertCommentCaptureTable(SchemaGenerator.java:901)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.processCaptureTable(SchemaGenerator.java:1070)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.doSchemaExport(SchemaGenerator.java:186)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.LiquibaseActions.genDbSchemaWork(LiquibaseActions.java:545)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.liquibaseCommand.handleEvent(liquibaseCommand.java:206)

           at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)

           at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:337)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1056)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:364)

    Caused by: Error : 1, Position : 0, Sql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), OriginalSql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), Error Msg = ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)

           ... 25 more


    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------

    Oracle Liquibase Exception Handler additional information available using -debug

    --------------------------------------------------------------------------------

    processCaptureTable

    --------------------------------------------------------------------------------

    java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)

           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)

           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)

           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)

           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)

           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)

           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)

           at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1038)

           at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1507)

           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1287)

           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)

           at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3910)

           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3889)

           at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.insertCommentCaptureTable(SchemaGenerator.java:901)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.processCaptureTable(SchemaGenerator.java:1070)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.doSchemaExport(SchemaGenerator.java:186)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.LiquibaseActions.genDbSchemaWork(LiquibaseActions.java:545)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.liquibaseCommand.handleEvent(liquibaseCommand.java:206)

           at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)

           at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:337)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1056)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:364)

    Caused by: Error : 1, Position : 0, Sql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), OriginalSql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), Error Msg = ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)

           ... 25 more


    --------------------------------------------------------------------------------

    --------------------------------------------------------------------------------

    Oracle Liquibase Exception Handler additional information available using -debug

    --------------------------------------------------------------------------------

    Processing has failed for your request.

    {0}

    {1}


    --------------------------------------------------------------------------------

    java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)

           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)

           at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)

           at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)

           at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)

           at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)

           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:148)

           at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1038)

           at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1507)

           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1287)

           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3735)

           at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3910)

           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3889)

           at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.insertCommentCaptureTable(SchemaGenerator.java:901)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.processCaptureTable(SchemaGenerator.java:1070)

           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.doSchemaExport(SchemaGenerator.java:186)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.LiquibaseActions.genDbSchemaWork(LiquibaseActions.java:545)

           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.liquibaseCommand.handleEvent(liquibaseCommand.java:206)

           at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)

           at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)

           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:337)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1056)

           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:364)

    Caused by: Error : 1, Position : 0, Sql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), OriginalSql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), Error Msg = ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated


           at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)

           ... 25 more


    --------------------------------------------------------------------------------

    Processing has failed for your request.

    ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated

    SQL> lb version

    Liquibase version:  4.4.2

    Extension Version:  21.3.3.0

    SQL> lb genschema ORA-00001: unique constraint (SED4.DATABASECHANGELOG_EXPORT_PK) violated

  • User_ZV0AU
    User_ZV0AU Member Posts: 1 Red Ribbon

    I get the same error message "ORA-00001: unique constraint (SCHEMA.DATABASECHANGELOG_EXPORT_PK) violated"

    Is a fix in the pipeline?

  • Jan-Marcel
    Jan-Marcel Member Posts: 172 Bronze Badge
    edited Dec 10, 2021 1:03PM

    We just ran into the same problem and solved it.

    It seems to be caused by the restrictions of the number of items you can have for a certain type. In our case we have more than 1000 triggers in our database schema.

    Analyzing the code reveals the following:

    package: sqlcl_lb_capture

    procedure: captureobjecttype

    This procedure has a variable counter which starts at 1000. This counter is used to populate the seq colomn during the insert of rows in the databasechangelog_export table. So the first row in that table starts with seq value of 1000.

    During the sortcapturedobjects the seq column of this table is updated. This sortcapturedobjects has an variable l_count which starts at 0 and is increased for each object member by 10. So after our 1000 triggers, it might try to update the seq of the specified row to an already existing value.


    We solved this by manually creating the sqlcl_lb_capture package in which we have increased the starting value of the counter variable to 10000.


    It would be nice if this could be fixed in the Liquibase software as well.

    user5495474
  • db-geek
    db-geek Member Posts: 41 Bronze Badge

    The error

    [Method processCaptureTable]: --------------------------------------------------------------------------------
    Oracle Liquibase Exception Handler additional information available using -debug
    --------------------------------------------------------------------------------
    checkError
    --------------------------------------------------------------------------------
    java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (DATA_PUBLIC.DATABASECHANGELOG_EXPORT_PK) violated
    
           at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1256)
           at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1138)
           at oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe(T2CPreparedStatement.java:784)
           at oracle.jdbc.driver.T2CPreparedStatement.executeForRows(T2CPreparedStatement.java:1027)
           at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1531)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1311)
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3746)
           at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3918)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897)
           at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)
           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.insertCommentCaptureTable(SchemaGenerator.java:911)
           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.processCaptureTable(SchemaGenerator.java:1083)
           at oracle.dbtools.raptor.liquibase.generator.SchemaGenerator.doSchemaExport(SchemaGenerator.java:186)
           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.LiquibaseActions.genDbSchemaWork(LiquibaseActions.java:581)
           at oracle.dbtools.raptor.scriptrunner.commands.liquibase.liquibaseCommand.handleEvent(liquibaseCommand.java:206)
           at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)
           at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:231)
           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:355)
           at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:238)
           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:357)
           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.runSqlcl(SqlCli.java:1080)
           at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:384)
    Caused by: Error : 1, Position : 0, Sql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:object_name), OriginalSql = insert into DATABASECHANGELOG_EXPORT (rank,type,seq,meta,file_name,object_name) values (:rank,:type,:seq,:meta,:filename,:ob
    ject_name), Error Msg = ORA-00001: unique constraint (DATA_PUBLIC.DATABASECHANGELOG_EXPORT_PK) violated
    
           at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:1270)
           ... 21 more
    
    

    when executing

    lb genschema
    

    is already available in actual SQLcl version:

    SQL> lb version
    Liquibase version:   4.6.1
    Extension Version:   21.4.0.0
    

    From my point of view, the problem doesn't belong to the number of existing objects in the database schema.

    I found an use case, where this problem occurs with only two objects available, one table and a view on this table with the same columns:

    /* ****************************************************
     * creation of table
     * ***************************************************/
    CREATE TABLE t_dim_scd2_row_type
    (id_tdsr                         NUMBER(1)
         CONSTRAINT nn_tdsr01 NOT NULL
     ,scd2_row_type_description      VARCHAR2(40 CHAR)
         CONSTRAINT nn_tdsr21 NOT NULL
     ,CONSTRAINT pk_tdsr
          PRIMARY KEY (id_tdsr
                      )
    )
    ORGANIZATION INDEX
    PCTTHRESHOLD 20
    PCTFREE 5
    ;
    
    --
    -- comments
    --
    COMMENT ON TABLE t_dim_scd2_row_type IS 'supporting object for assignment of records when used for processing historical data of slowly change dimension type 2 [TDSR]';
    
    COMMENT ON COLUMN t_dim_scd2_row_type.id_tdsr IS 'unique numerical ID of record';
    COMMENT ON COLUMN t_dim_scd2_row_type.scd2_row_type_description IS 'Description of record';
    
    
    /* ****************************************************
     * creation of view on table
     * ***************************************************/
    CREATE OR REPLACE FORCE VIEW v_dim_scd2_row_type
    (id_tdsr
     ,scd2_row_type_description
    )
    AS
    SELECT
           tdsr.id_tdsr
           ,tdsr.scd2_row_type_description
    FROM   t_dim_scd2_row_type tdsr
    WHERE  0 = 0
    ;
    
    --
    -- comments
    --
    COMMENT ON TABLE v_dim_scd2_row_type IS 'supporting object for assignment of records when used for processing historical data of slowly change dimension type 2 [VDSR]';
    
    COMMENT ON COLUMN v_dim_scd2_row_type.id_tdsr IS 'unique numerical ID of record';
    COMMENT ON COLUMN v_dim_scd2_row_type.scd2_row_type_description IS 'Description of record';
    
    

    When I create the view without comments, "lb genschema" runs without any problem. When I add at least one comment on the view or a view column, the unique constraint error is raised.

    Also, when the view is created with comments and there are no comments on the table, everything is fine. When there is at least one comment on the table, the unique constraint error is raised again.

    So the problem seems to be at generation of object comments, especially with column comments with the same column name belonging to different objects.

    bglazyrUser_XX7PC
  • bglazyr
    bglazyr Member Posts: 49 Bronze Badge
    edited Jan 8, 2022 11:58PM

    Hi! Is anybody from support/development is watching this thread?

    I can confirm, that on latest sqlcl (SQLcl: Release 21.4.0.0 Production Build: 21.4.0.348.1716) testcase described by db-geek (see above message) generate error.

    But in our project we don't have comments on view. So, probably there are more errors in this code.

    Best regards,

    Boris

  • User_1P99F
    User_1P99F Member Posts: 4 Red Ribbon

    Hi,

    Any updates?

    I 'm getting the same error when i try genschema in a large

    database with over 1000 tables and views.

    Thanks

  • MarcoFabbri
    MarcoFabbri Member Posts: 1 Red Ribbon

    Hi all,

    same issue here with sqlcl 22.3 and a database with more than 1000 objects.

    Any solution proposed?

    Thanks