How do I initialize the database correctly?

[dventimihasura]

Hi! Thanks so much for offering this repo and Docker image. Sorry, but I'm new to Oracle and I don't understand how to initialize the database correctly. I know the documentation says startup scripts in docker-entrypoint-startdb.d will do this, and I believe it. In fact, it even seems to work insofar as the startup logs show that my scripts are running, tables are being created, and data are being inserted. The trouble is, afterward when I log into the database, I can't find the tables. It probably means they're being created in a database and/or a schema in a way I don't understand. Anyway, here's the environment stanza in my docker-compose.yaml file.

    environment:
      ORACLE_PASSWORD: chinook
      ORACLE_DATABASE: chinook
      APP_USER: chinook
      APP_USER_PASSWORD: chinook

Afterward, I connect to the database with JDBC and this url: jdbc:oracle:thin:@localhost:1522/chinook and userid chinook and password chinook but for the life of me I can't find the tables my scripts created (Album, Track, etc.).

Should I connect to a different database, or as a different user? Sorry, I'm really confused here. Thanks!

[gvenzl]

Hey @dventimihasura,

Thanks a lot for using these images!

By default, all scripts are run against the instance itself (XE database) so that users have the option of modifying database parameters, creating new pluggable databases, etc.

What you are probably missing in your startup scripts is connecting to the chinook user in the chinook database first, can that be?

The *.sql, *.sql.gz and *.sql.zip files will be executed in SQL*Plus as the SYS user connected to the Oracle instance (XE).

[dventimihasura]

Thanks @gvenzl and that's probably true. 🤔 As the initialization scripts are *.sql files and not .sh files (though I could adapt them if need be) how would I connect with different credentials to a different database in those scripts? If that can't be done with plain *.sql files I suppose my options are:

1. adapt the *.sql files into *.sh files that use sqlplus with the right credentials
2. just use the tables as they are in the XE instance...which I can live with

Anyway, what are your thoughts?

Thanks!

[gvenzl]

Hey @dventimihasura,

You can use the CONNECT command at the beginning of the script(s):

conn chinook/chinook@localhost/chinook
...

For example:

init.sql

conn chinook/chinook@localhost/chinook

create table test (id number, text varchar2(255));

insert into test values (1, 'Gerald');
commit;

Will result in:

$ podman run --name test \
> -e ORACLE_RANDOM_PASSWORD=y \
> -e APP_USER=chinook \
> -e APP_USER_PASSWORD=chinook \
> -e ORACLE_DATABASE=chinook \
> -v /home/gvenzl/test:/container-entrypoint-initdb.d \
> gvenzl/oracle-xe:21-slim-faststart
CONTAINER: starting up...
CONTAINER: first database startup, initializing...
CONTAINER: starting up Oracle Database...
...
...
...
CONTAINER: Executing user defined scripts...

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

Table created.


1 row created.


Commit complete.

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

CONTAINER: DONE: Executing user defined scripts.

After which I can run the following:

$ sqlplus chinook/chinook@localhost/chinook

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Feb 6 01:06:07 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 06 2023 01:04:50 +00:00

Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> select * from test;

	ID
----------
TEXT
--------------------------------------------------------------------------------
	 1
Gerald

Hope this helps!

[dventimihasura]

Sweet! Thanks! It helps a lot.

[gvenzl]

Glad to hear that! :)