Liquibase deploy error when having an insert containing a (Oracle) SYS.ANYDATA type column

Liquibase Development
, ,
1

Hello Team,

I am encountering an issue and I need your help and/or guidance (fix, workaround, not fixable… I’ll take anything :smiley: ).

Here are the facts (example):

Having an Oracle table (TestTable) containing a SYS.ANYDATA type column:

TestTable:
Id (NUMBER(3)), SysAnyDataCol (SYS.ANYDATA)

When trying to run a liquibase update containing a changeset of the following format:

    <changeSet author="Eduard" id="1">
        <insert tableName="TestTable">
            <column name="Id" valueNumeric="1"/>
            <column name="SysAnyDataCol" valueNumeric="30"/>
        </insert>
    </changeSet>

The deploy fails with the following error:

Running Changeset: data/TestTable.xml::1::Eduard
 ORA-00932: inconsistent datatypes: expected ANYDATA got NUMBER
 [Failed SQL: (932) INSERT INTO EDUARD.TestTable (Id, SysAnyDataCol) VALUES (1, 30)]

Any ideas how to address this ? I am open to any workaround, the end goal is to make that numeric value fit the SYS.ANYDATA col.

I also tried the following with no success:

<column name="SysAnyDataCol" value="1"/> (errors out, same error)
<column name="SysAnyDataCol" valueComputed="SYS.ANYDATA.convertNumber(30)"/> (this works BUT the value inserted in the DB is actually: "(OPAQUE)" ). 
<sql> type changeset, containing plain SQL Insert (errors out, same error)

Thanks in advance for any given input on this!
Eduard

2

Update (solution found): this is actually the solution/workaround as it seems the value that is inserted (OPAQUE) is correct (the thing is, a function is needed to be written to fetch back numeric value 30 from Oracle - that’s a separate story altogether).

So like a nice to have would be if this can be the default behavior of Liquiabase at gen data:
if the target column is SYS.ANYDATA then place a

 valueComputed="SYS.ANYDATA.convertNumber(<current value>)"

instead of

 value="<current value>"

in the generated changeset / insert / column node.

Thanks,
Eduard

1 Like