Creating a collection in init file fails silently

[tsegismont]

I'm trying to create a collection in an init file like this one:

ALTER SESSION SET CONTAINER=FREEPDB1;

CREATE OR REPLACE TYPE STRING_ARRAY AS VARYING ARRAY(127) of VARCHAR2(255 char);
CREATE TABLE test_collections
(
  id                   number(10, 0),
  string_array_element STRING_ARRAY,
  primary key (id)
);

COMMIT;

Then the container is started like this:

docker run -t -i -p 1521:1521 \
  -e ORACLE_RANDOM_PASSWORD=true \
  -e APP_USER=vertx \
  -e APP_USER_PASSWORD=vertx \
  --mount type=bind,source="$(pwd)"/src/test/resources/tck/import.sql,target=/container-entrypoint-initdb.d/import.sql,readonly \
  gvenzl/oracle-free:23-slim-faststart

In the log file, I can see:

CONTAINER: running /container-entrypoint-initdb.d/import.sql ...

Session altered.

CONTAINER: DONE: running /container-entrypoint-initdb.d/import.sql

Nothing about the type or table creation.

Then when I run a test with the Oracle JDBC Client, I get this:

io.vertx.oracleclient.OracleException: Error : 942, Position : 15, SQL = TRUNCATE TABLE test_collections, Original SQL = TRUNCATE TABLE test_collections, Error Message = ORA-00942: table or view does not exist

This problem happens only when creating a custom type and the corresponding table.

I fail to understand what's the problem, would you be able to help? Thank you

[tsegismont]

I also noticed that when running the container without sql initialization:

docker run -t -i -p 1521:1521 \
  -e ORACLE_RANDOM_PASSWORD=true \
  -e APP_USER=vertx \
  -e APP_USER_PASSWORD=vertx \
  gvenzl/oracle-free:23-slim-faststart

And creating the Oracle collection and dependent table with JDBC, it works fine.

[gvenzl]

Hey @tsegismont,

Thanks a lot for using these images!

I think you are running into a peculiar little side effect of executing SQL statements via the SQL prompt (which, ultimately, the init scripts facility is using).

When looking closer at the output you have provided:

CONTAINER: running /container-entrypoint-initdb.d/import.sql ...

Session altered.

CONTAINER: DONE: running /container-entrypoint-initdb.d/import.sql

it becomes apparent that there is only a response from the ALTER SESSION statement. However, responses for the type and table creation are missing, i.e. Type created. and Table created. respectively.

I think to remember that there was one peculiar thing with Oracle Database TYPEs, that is, they are PL/SQL entities, not SQL. And as such, they require a termination character of / (because a PL/SQL procedure could contain many ; characters).

I just tested this on livesql.oracle.com, and indeed, without the / after the CREATE TYPE statement, LiveSQL interprets the rest of the lines as part of being a PL/SQL block.

So, long story short, try this instead (no COMMIT; needed, in Oracle Database, DDL is not transactional):

ALTER SESSION SET CONTAINER=FREEPDB1;

CREATE OR REPLACE TYPE STRING_ARRAY AS VARYING ARRAY(127) of VARCHAR2(255 char);
/

CREATE TABLE test_collections
(
  id                   number(10, 0),
  string_array_element STRING_ARRAY,
  primary key (id)
);

[tsegismont]

@gvenzl thank you for creating these images. We (the Eclipse Vert.x community) use them to test our Reactive Oracle Client.

I will try what you suggest and keep you informed.

:hugs:

[tsegismont]

About the COMMIT statement, in fact it's a leftover from the original init file (which contains DML statements).

[gvenzl]

Hey @tsegismont,

Oh, how very cool! I'd be honored if we could add Vert.X to the list of users here: https://github.com/gvenzl/oci-oracle-free/blob/main/README.md#users-of-these-images

Did the fix work?

[gvenzl]

Hey @tsegismont, just wanted to check in whether the fix worked.

[tsegismont]

Sorry @gvenzl , I wasn't able to come back to this before today. Your suggestion did the trick, I can see this in the logs now:

[ORACLE] Type created.

Thank you very much!

[gvenzl]

Awesome, thanks a lot!