[BUG] Oracle provider in 3.3.2-rc1

[b0rv3g4r4]

Description

Im trying to start a fresh elsa instance in 3.3 with Oracle provider but im getting this error:

The property 'SerializedPayload' cannot be added to the type 'StoredTrigger' because no property type was specified and there is no corresponding CLR property or field. To add a shadow state property, the property type must be specified.

I think that the 3_3 migrations are wrong for oracle. Looking the types in SetupForOracle dont match with the migrations.

[sfmskywalker]

Could you elaborate a bit on the types that you noticed that don't match with the migrations? I haven't spot anything strange so far.

[b0rv3g4r4]

For example the SerializedPayload, in the migrations are defined as NVARCHAR(2000) but this fields need to be NCLOB as defined in SetupForOracle

[sfmskywalker]

I see, yes. I have pushed a branch that fixes the migrations, and is being built here: https://github.com/elsa-workflows/elsa-core/actions/runs/13063460256

I haven't figured out yet what connection string to use to successfully connect to the Oracle container (FREE), so I haven't been able to test it at runtime yet. Perhaps you can help me?

This is my compose:

  oracle:
      image: container-registry.oracle.com/database/free:latest
      container_name: oracle
      environment:
        ORACLE_PDB: ORCLPDB1
        ORACLE_PWD: elsa
      ports:
        - "1521:1521"
        - "5500:5500"
      volumes:
        - oracle-data-free:/opt/oracle/oradata
      shm_size: '1g'

The Oracle container has started successfully and I am able to access its terminal and issue SQL commands.

However, the following connection string doesn't work - the driver complaints about an unknown user:

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01435: user does not exist

This is my connection string:

User Id=SYSTEM;Password=elsa;Data Source=localhost:1521/FREE;

Any suggestions?

[b0rv3g4r4]

I have an oracle XE like this:

version: "3.7"

services:
  oracle:
    container_name: oracle-db
    image: <image builded in private registry> from oracle xe
    volumes:
      - ./oracle-data:/opt/oracle/oradata
      -  ./oracle-setup:/opt/oracle/scripts/setup
    environment:
      ORACLE_PWD: secret
    ports:
      - 1521:1521
      - 5500:5500

Then i have an initial sql to create the user and schema in the oracle-setup folder

alter session set "_ORACLE_SCRIPT"=true;
CREATE USER APP_USER IDENTIFIED BY secret;
GRANT ALL PRIVILEGES TO APP_USER;

And the connection string is like:

  "OracleConnectionString": "Data Source=(DESCRIPTION = (ADDRESS_LIST = (FAILOVER =ON) (LOAD_BALANCE = OFF) (ADDRESS = (PROTOCOL =TCP)(HOST=localhost)(PORT=1521))) (CONNECT_DATA = (SID= XE) ));User Id=APP_USER;Password=secret;"

With ur connection string try my format changing the SID to FREE

[sfmskywalker]

That was helpful, thanks! I was able to start an Oracle DB and perform some tests and debugging. I created a PR that fixed all of the issues for me. I'll wait for you to confirm that it works well on your end too before merging.

[b0rv3g4r4]

Nice! I will try it tomorrow.
I have seen one more detail 🥺.
From what I have seen, taking advantage of the upgrade we are also uploading our project to .net 9 and the latest version of Oracle.Entityframework by default breaks compatibility with Oracle 19 for example (which is our case). I noticed it when I saw the BOOLEAN type in the migrations because it also happened to me when regenerating our internal ones with the update. I fix it with the UseOracleSQLCompatibility or staying in package version 8.21.160
(https://docs.oracle.com/en/database/oracle/oracle-database/23/odpnt/EFCoreDesupportedAPIs.html)

What would be the best approach to have this resolved in Elsa? Custom migrations?

Maybe having 2 different versions of the module of oracle provider?

Oracle is a thing...

I hate him deeply hahaha. But the client rules in some things...

Tell me if you can think of a better approach and I can do the PR. Or if you need help and I make the PR with another "version" of Oracle to support Oracle 19 in its migrations

[b0rv3g4r4]

i temporaly solved by creating manual SQL Script to load the tables with correct types de Oracle and using the next sentence:

Even so, I think we should see how to support multiple migrations depending on the Oracle version.

[sfmskywalker]

Glad to see you were able to solve the issue, if only temporarily.

As for supporting multiple versions of Oracle, I'm not sure that's something I want to take on, for the same reason I don't want to do this for various MySQL versions, either. As it stands today, we already have a number of migrations supporting different database providers, for different modules. Should we add support for another Oracle provider, we would also need to (eventually) add this to other modules.

Given that, it might not be unreasonable to let users generate their own migrations. This way, they can target any version of any DB provider supported by EF Core.

Open for other suggestions.

[b0rv3g4r4]

I agree with you, I think the most flexible option would be to allow the user to comfortably generate their own migrations and extend the existing provider modules (ModelBuilder etc.)

[sfmskywalker]

Great. I'll open an issue for documenting the process of generating custom migrations that fit into the existing provider modules.