---json
{
"aliases": [
{ "path": ":docs:system:oracle:howto" }
],
"page_id": "l2f9lqci85g7mrwvrj9a7"
}
---
====== How to start and use the Oracle database in Tabulify ======
===== About =====
This howto shows you how to:
* start an [[:docs:system:oracle:oracle|oracle database]] with the [[:docs:service:docker|oracle docker service]]
* and use the ''@oracle'' [[docs:connection:howtos|default howto oracle connection]] in tabul command.
===== Steps =====
==== Docker Installation ====
[[:docs:service:howtos|howto docker services]] are an easy way to start locally a database instance.
Because [[:docs:service:howtos|howto services]] are [[:docs:service:docker|docker services]], you should have [[https://docs.docker.com/engine/install/|docker installed]] on your computer.
==== Check the service ====
By default, the [[docs:conf:tabul.yml|user configuration vault]] is created with the [[:docs:service:howtos|howto docker services]].
You can check if the oracle service is present and see its [[:docs:service:service#attributes|attributes]] with:
* the [[:docs:tabul:service:info|tabul service info]] command
* or the [[docs:conf:tabul.yml|configuration vault]]
tabul service info oracle
Information about the service (oracle)
Attribute Value Description
----------- ------------------------------------------------------------------------------------------------------------------------------ --------------------------
COMMAND The command
ENVIRONMENT {APP_USER=tabulify, APP_USER_PASSWORD=oracle, ORACLE_ALLOW_REMOTE=true, ORACLE_DISABLE_ASYNCH_IO=true, ORACLE_PASSWORD=oracle} The environment attributes
IMAGE ghcr.io/gvenzl/oracle-free:23.7-slim-faststart The image
IS_STARTED true The status of the service
NAME oracle The name of the service
ORIGIN CONF The origin of the system
PORTS 1521:1521 The ports
TYPE docker The type of the system
VOLUMES The volumes
tabul data print --type text .tabul/.tabul.yml@home | yq '.spec.services.oracle'
environment:
APP_USER: tabulify
APP_USER_PASSWORD: oracle
ORACLE_ALLOW_REMOTE: 'true'
ORACLE_DISABLE_ASYNCH_IO: 'true'
ORACLE_PASSWORD: oracle
image: ghcr.io/gvenzl/oracle-free:23.7-slim-faststart
ports:
- 1521:1521
type: docker
If the service is not present, move, delete your [[docs:conf:tabul.yml|user configuration vault file]]
==== Start the oracle service ====
To start the docker service, you can use
* the [[:docs:tabul:service:start_command|tabul service start]] command
* or the ''docker command''
to start the [[https://github.com/gvenzl/oci-oracle-free|oci Oracle Free image]]
tabul service start oracle
1 service was started
Name Type
------ ------
oracle docker
You can use start a local database to connect with this connection with the following command:
docker run \
-e ORACLE_DISABLE_ASYNCH_IO=true \
-e ORACLE_ALLOW_REMOTE=true \
-e ORACLE_PASSWORD=oracle \
-e APP_USER=hr \
-e APP_USER_PASSWORD=oracle \
-p 1521:1521 \
-d \
--name oracle \
ghcr.io/gvenzl/oracle-free:23.7-slim-faststart
==== Check that you can ping/connect to the service with the connection ====
tabul connection ping oracle
The connection (oracle) has been pinged successfully
==== Connection Info ====
By default, the [[docs:conf:tabul.yml|user configuration vault]] is created with the [[:docs:service:howtos|howto docker services]].
You can check the service with the [[:docs:tabul:service:info|tabul service info]] command
tabul connection info oracle
Information about the connection (oracle)
Attribute Value Description
-------------------------- ------------------------------------------- --------------------------------------------------------------------
BOOLEAN_DATA_TYPE Native Boolean data type used to store boolean values
BUILDER_CACHE_ENABLED true Enable or disable the builder cache
CHAR_DEFAULT_PRECISION 1 Default CHAR precision
COMMENT The howto oracle connection A connection description
DATABASE_MAJOR_VERSION The major version number of the database
DATABASE_MINOR_VERSION The minor version number of the database
DATABASE_PRODUCT_NAME The name of the database
DATABASE_PRODUCT_VERSION The version of the database
DATE_DATA_TYPE NATIVE Date data type used to store date values
DRIVER oracle.jdbc.OracleDriver The jdbc driver class
DRIVER_NAME The driver name
DRIVER_VERSION The driver version
HOST Host Server
JDBC_MAJOR_VERSION The major version number of JDBC
JDBC_MINOR_VERSION The minor version number of JDBC
LOGIN_STATEMENTS ALTER SESSION SET CURRENT_SCHEMA = tabulify Statements that runs after a connection has been established
MAX_CONCURRENT_THREAD The maximum number of threads that can be created against the system
MAX_NAME_IN_PATH The maximum number of names in a path
NAME oracle The name of the connection
NAME_QUOTING_DISABLED_CASE UPPERCASE The case to apply when quoting is disabled
NAME_QUOTING_ENABLED false Enable quoting of names
NATIVES Native Driver attributes (jdbc properties, ...)
NCHAR_DEFAULT_PRECISION 1 Default NCHAR precision
NVARCHAR_DEFAULT_PRECISION 0 Default NVARCHAR precision
ORIGIN CONF The origin of the connection
PASSWORD oracle The user password
PORT 1521 Host Port
SUPPORT_BATCH_UPDATES If the system supports batch SQL updates
SUPPORT_NAMED_PARAMETERS If the system supports named parameters in the SQL statement
TIMESTAMP_DATA_TYPE NATIVE Timestamp data type used to store timestamp values
TIME_DATA_TYPE NATIVE Time format data type to store time values
URI jdbc:oracle:thin:@localhost:1521/freepdb1 The uri of the connection
USER system The user name
VARCHAR_DEFAULT_PRECISION 0 Default VARCHAR precision
WORKING_PATH TABULIFY. The working path (Schema for database, directory for file system)
You can also check the connection attributes with the [[docs:conf:tabul.yml|configuration vault]]
tabul data print --type text .tabul/.tabul.yml@home | yq '.spec.connections.oracle'
comment: The howto oracle connection
driver: oracle.jdbc.OracleDriver
login-statements: ALTER SESSION SET CURRENT_SCHEMA = tabulify
name-quoting-enabled: false
password: oracle
uri: jdbc:oracle:thin:@localhost:1521/freepdb1
user: system
If the connection is not present, move or delete your [[docs:conf:tabul.yml|user configuration vault file]].
==== Use the oracle connection ====
You then can use the ''@oracle'' connection in place of any other connection
Example:
* replacing the ''@sqlite'' connection by the ''@oracle''
Drop all
tabul data drop --no-strict-selection --force *@oracle
# with sqlite
# tabul data upsert characters.csv@howto @sqlite
# with oracle
tabul data upsert characters.csv@howto @oracle
# with sqlite
# tabul data list *@sqlite
# with oracle
tabul data list *@oracle
path media_type
---------- ----------
CHARACTERS table
In particularly, you can follow:
* the [[:howto:getting_started:getting_started|getting started guide]]
* and all SQL howto ([[:howto:sql_table:sql_table|Sql Table]], Sql View, ...)
select from pages where path like ':howto:sql_table%' or path like ':howto:sql_view:%' order by title asc limit 10
$title
${description | head(200, "...") } [[$path| ]]
\\
==== Stop the oracle service ====
Once, you don't need the oracle service, you can stop it:
* with the [[:docs:tabul:service:stop|tabul service stop]] command
* or with Docker
tabul service stop oracle
1 service was stopped
Name Type
------ ------
oracle docker
You can also stop it with Docker
docker stop oracle