Oracle Patch/Collect Identity Field Issue

[A-Team-2020]

I am trying to write back to and on-prem Oracle table from Power Apps. The table has an identity column called ID which is also the primary key.

When trying to Patch defaults or Collect the table, I get the below error:

But I shouldn't have to supply this column as it is auto-incremented in the DB. If I try to pass a value for ID I get this error:

Which is what I'd expect. So how to write back to this table??

[C-Papa]

Do you want to post your patch code to allow us to see if there are any issues?

[A-Team-2020]

I'm just doing:

Patch(
    Schema.Table,
    Defaults(Schema.Table),
    {
        UPC:"123456"

    }

)

Or

Collect(
    Schema.TableName,

    {

        UPC:"123456"

    }

)

No errors in either until submitted, then the same errors as in the OP.

[ljurbergs]

Has there been a solution identified to the above? We have the same problem.

[luissimoespt]

Any updates on this topic?
How is it possible that PowerApps does not support a basic and fundamental feature such as an Identity field in Oracle database?

[rswain]

The error message you are receiving indicates that you are trying to insert a value into the ID column, which is an identity column and should not be manually inserted. In Oracle, identity columns are created using a sequence and a trigger, and the database engine generates the value automatically.

To work around this issue, you can exclude the ID column from the Patch or Collect function. Oracle will automatically generate a unique value for the ID column when the row is inserted.

Here's an example of how to exclude the ID column from a Patch function:

Patch(
    MyOracleTable,
    {
        Column1: "Value1",
        Column2: "Value2"
    }
);
In this example, Column1 and Column2 are the columns you want to update, and MyOracleTable is the name of your table in Oracle. When you run this Patch function, Oracle will generate a unique value for the ID column.

You can do the same thing with the Collect function:

Collect(
    MyCollection,
    {
        Column1: "Value1",
        Column2: "Value2"
    }
);
In this example, MyCollection is the name of your collection in Power Apps. When you run this Collect function, Power Apps will add a new row to the collection with a unique value for the ID column.

If you are still encountering issues, you may want to check the settings of the identity column in Oracle to make sure that it is set up correctly.

[luissimoespt]

Hi rswain,

I have tried this approach since its the standard practice but it does not work and it gives an error saying that the ID field needs to be provided ( ID is set as "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY ) 

PS: Have also tried to use ALWAYS

Can you post an example of your oracle structure and the app code that is currently working?

[A-Team-2020]

He's just posted a ChatGPT output. It's not even correct, check the first Patch example, it's missing argument 2.

[A-Team-2020]

Hi @rswain, thanks for the reply, however I don't think your response really covers the question.

In my first example, ID was excluded from the Patch, and the error stated it was required.

In the second example, ID was included and he error stated it is auto generated and therefore not required.

So two conflicting error messages. I have since given up entirely on using this connector.

[pjdiii]

Having exact issue as OP using PowerApps patch and Oracle with an Identity field. Any working solution would be greatly appreciated.