[Bug]: Oracle Stored Procedure

[JakeR92]

Module

Core

Testcontainers version

1.19.8

Using the latest Testcontainers version?

Yes

Host OS

Linux

Host Arch

x86

Docker version

Client: Docker Engine - Community
 Version:           27.2.0
 API version:       1.47
 Go version:        go1.21.13
 Git commit:        3ab4256
 Built:             Tue Aug 27 14:15:13 2024
 OS/Arch:           linux/amd64
 Context:           default

Server: Docker Engine - Community
 Engine:
  Version:          27.2.0
  API version:      1.47 (minimum version 1.24)
  Go version:       go1.21.13
  Git commit:       3ab5c7d
  Built:            Tue Aug 27 14:15:13 2024
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.7.21
  GitCommit:        472731909fa34bd7bc9c087e4c27943f9835f111
 runc:
  Version:          1.1.13
  GitCommit:        v1.1.13-0-g58aa920
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

What happened?

The init script runner removes all ";" when splitting, however oracles stored procedure command requires the ";" at the end otherwise it gets a compile error. Can you change it not to remove the ";" from the split commands?

Relevant log output

No response

Additional Information

No response

[eddumelendez]

Hi @JakeR92, can you please share a project that reproduce the issue?

/cc @inponomarev who has been contributing to this feature :)

[JakeR92]

Hi,

I have the following stored procedure in an sql file that is passed into the test container to be run upon startup.
CREATE TABLE "Konnekt"."ProcedureVarChar"
(
"primaryKey" VARCHAR2(255 CHAR) NOT NULL,
"uniqueKey" VARCHAR2(255 CHAR) NOT NULL,
"mandatory" VARCHAR2(255 CHAR) NOT NULL,
"optional" VARCHAR2(255 CHAR),
"defaultValue" VARCHAR2(255 CHAR) DEFAULT 'default' NOT NULL,
PRIMARY KEY ("primaryKey"),
UNIQUE ("uniqueKey")
);

INSERT INTO "Konnekt"."ProcedureVarChar" ("primaryKey", "uniqueKey", "mandatory", "optional", "defaultValue")
VALUES ('pk1', 'uq1', 'man', 'opt', 'Hello World');
INSERT INTO "Konnekt"."ProcedureVarChar" ("primaryKey", "uniqueKey", "mandatory", "optional")
VALUES ('pk2', 'uq2', 'man', 'opt');
INSERT INTO "Konnekt"."ProcedureVarChar" ("primaryKey", "uniqueKey", "mandatory")
VALUES ('pk3', 'uq3', 'man');

CREATE TABLE "Konnekt"."ProcedureVarCharBK" AS
SELECT *
FROM "Konnekt"."ProcedureVarChar";

CREATE OR REPLACE PROCEDURE "Konnekt"."insert_varchar_data"(
p_primarykey VARCHAR2,
p_unique VARCHAR2,
p_mandatory VARCHAR2,
p_optional VARCHAR2 DEFAULT NULL,
p_default VARCHAR2 DEFAULT NULL
)
IS
BEGIN
IF p_default IS NULL THEN
INSERT INTO "Konnekt"."ProcedureVarChar" ("primaryKey", "uniqueKey", "mandatory", "optional")
VALUES (p_primarykey, p_unique, p_mandatory, p_optional);
ELSE
INSERT INTO "Konnekt"."ProcedureVarChar" ("primaryKey", "uniqueKey", "mandatory", "optional", "defaultValue")
VALUES (p_primarykey, p_unique, p_mandatory, p_optional, p_default);
END IF;
END;

Whenever I call the function it gets compile error. I debugged it and found that by the time the script runner runs the commands it is:

CREATE OR REPLACE PROCEDURE "Konnekt"."insert_varchar_data"(
p_primarykey VARCHAR2,
p_unique VARCHAR2,
p_mandatory VARCHAR2,
p_optional VARCHAR2 DEFAULT NULL,
p_default VARCHAR2 DEFAULT NULL
)
IS
BEGIN
IF p_default IS NULL THEN
INSERT INTO "Konnekt"."ProcedureVarChar" ("primaryKey", "uniqueKey", "mandatory", "optional")
VALUES (p_primarykey, p_unique, p_mandatory, p_optional);
ELSE
INSERT INTO "Konnekt"."ProcedureVarChar" ("primaryKey", "uniqueKey", "mandatory", "optional", "defaultValue")
VALUES (p_primarykey, p_unique, p_mandatory, p_optional, p_default);
END IF;
END

Without the final ";". Oracle requires this final ";" otherwise it will not compile properly.

[JakeR92]

Any updates?

[tetv]

If the statement ends with ;; can we split by the last ; and keep the first ; as part of the statement?

Example: init.oracle.sql:

CREATE OR REPLACE PROCEDURE "sp_get_all_my_table"(total_count OUT INTEGER) AS
BEGIN
    SELECT COUNT(*) INTO total_count FROM "MyTable";
END;;

Split by last ; and therefore, the statement will be:

CREATE OR REPLACE PROCEDURE "sp_get_all_my_table"(total_count OUT INTEGER) AS
BEGIN
    SELECT COUNT(*) INTO total_count FROM "MyTable";
END;

[TomasTokaMrazek]

One dirty workaround I've found is to split the init script. For every statement use one file and supply the list of scripts to testcontainers in withInitScripts().

I was a bit surprised to find out, that testcontainers for some reason execute init script by itself instead of simply taking the file and mounting it to proper directory (in this case /docker-entrypoint-initdb.d) for the database to handle it by itself.

In my opinion if the image supports initialization scripts, which is the case for image gvenzl/oracle-free, it should delegate running the scripts to the image itself instead of using testcontainers ScriptUtils.