How do i execute a script upon database startup

Docker Hub
1

I have a requirement of running a software script just after the container is built. Ive tried mounting a new volume and executing it using the whole path but i keep getting the below error.

thank you

version: '3'
services:
  db:
    image: oracle/oracle19c:new
    environment:
      - ORACLE_SID=ORCLCDB
      - ORACLE_PDB=ORCLPDB1
      - ORACLE_PWD=password123
    ports:
      - 1521:1521
    volumes:
      - oracle:/opt/oracle/oradata
      - ./scripts:/scripts
      - ./software:/software
      - ./scripts/:/opt/oracle/scripts/startup/
    #entrypoint: /scripts/oracle-init.sh
    command: bash -c "/scripts/oracle-init.sh"
    healthcheck:
      test: [ "CMD", "/opt/oracle/checkDBStatus.sh"]
      interval: 5s
      #timeout: 10s
      retries: 50
      #start_period: 40s

postbuild.sql in the scripts folder

alter session set container=ORCLPDB1;
@/software/utPLSQL-develop/utPLSQL-develop/source/install_headless.sql

Session altered.
db_1  |
db_1  | SP2-0310: unable to open file "/software/utPLSQL-develop/utPLSQL-develop/source/install_headless.sql"
2

Are you aware oracle has a github repo for their docker-images that actually helps you to build your image?
The README.md even covers Running scripts after setup and on startup

3

thanks for pointing it out. Ive updated the description with what im trying to achieve but it cant find the path?
any thoughts pls

4

So basically the problem is while executing your postbuild.sql script.

Since you didn’t share any details about your directory structure and the file content, I can only guess that either the file does not exist on that path or the permissions prevent the container process from accessing the file.

5

okay, ive managed to attach the software to the volume and it can now be seen.

The problem im now having is that it does not do a complete install [of utlPLSQL Releases · utPLSQL/utPLSQL · GitHub] when i call the global script "install_headless.sql - which then calls other referenced scripts and somehow its incomplete when its runs. When i do it manually, it works smoothly.


1  |
db_1  | Creating utPLSQL user UT3
db_1  | --------------------------------------------------------------
db_1  | Installing utPLSQL v3 framework into UT3 schema
db_1  | --------------------------------------------------------------
db_1  | Switching current schema to UT3
db_1  | --------------------------------------------------------------
db_1  | Installing component UT_DBMS_OUTPUT_CACHE
db_1  | O/S Message: No such file or directory
db_1  |
db_1  |
db_1  | DONE: Executing user defined scripts
db_1  |
6

Manually where? Inside the container? On a oracle database installation on the host?

8

Inside the container and calling the same script as sys user

9

any suggestions on how i can tackle this?
thanks

10

I am too far away from oracle databases to know what the problem is or how to tackle it.

Though, we do know that a) executing scripts on database creation is possible (but fails) and b) executing the script manually inside the container works as expected. So you might want to investigate what the entry point script does different in calling the scripts.

11

i think the problem is rather general.

Yes if we specify a .sql or .sh , it would work generally But if we specify a path to a parent sql that has sub sql files on the same folder, it does not like it. Error is the same as above.

The error is no Oracle specific but how Docker-compose does not like embedding a path to a bunch of sql files.

12

I am going to state the obvious: If you plan to run your scripts inside the container, they need to be configured to use the paths that exist inside the container.

13

i can confirm the path does exist in the container and i can execute this manually inside the container and “does not fail”
where as if executed via docker compose the script does not run as expected.

This is how the header script looks like

@@set_install_params.sql

@@create_utplsql_owner.sql &&ut3_owner &&ut3_password &&ut3_tablespace
@@install.sql &&ut3_owner
@@create_synonyms_and_grants_for_public.sql &&ut3_owner

exit