🅾️ How do I run an Oracle transformation?

You want to transform data with SQL on an Oracle database. Unlike other backends, an Oracle transformation runs on your own Oracle server (it is not provisioned by Keboola), so you set up the database user and credentials yourself. This page takes you from nothing to a successful run.

Time: ~15 minutes · You will need: a Keboola project, access to an Oracle server where you can create a user, and one table in Storage to read from.

Before you start

• You manage the Oracle server. Keboola connects to it with credentials you provide, so the server must be reachable from Keboola and you are responsible for its availability.
• Have the sample CSV file (or any table) ready to upload to Storage as the input.

Step 1 — Create a database user

In Oracle, create a dedicated user for Keboola and grant it the privileges to open a session and create tables. Replace the password with your own:

CREATE USER KEBOOLA_TRANSFORMATION IDENTIFIED BY "secretPassword20" QUOTA UNLIMITED ON USERS;

GRANT CREATE SESSION TO KEBOOLA_TRANSFORMATION;
GRANT CREATE TABLE TO KEBOOLA_TRANSFORMATION;

Step 2 — Create the transformation and add credentials

1. Open Components → Transformations, click New Transformation, and choose Oracle Transformation.
2. Open the Database Credentials link in the configuration.
3. Enter the host, port, database/service, username, and password for the KEBOOLA_TRANSFORMATION user.
4. (Optional) Schema — an optional schema field under the database connection (db.schema in the configuration). Set it to run the transformation against a specific Oracle schema; leave it empty to use the connected user’s default schema.
5. Click Test Credentials, then Save.

Step 3 — Map the input

1. Upload the sample CSV file to Storage as a table.
2. In Input Mapping, add the table and set its Destination to source.
3. Save the mapping.

Step 4 — Write the SQL script

In the code editor, paste:

CREATE TABLE "result" AS SELECT * FROM "source";

You can split longer scripts into blocks.

Step 5 — Add the output mapping

1. In Output Mapping, set Source to result (the table the script creates).
2. Set Destination to a new Storage table, for example out.c-main.result.
3. Save the mapping.

Step 6 — Run it and confirm the result

1. Click Run on the transformation.
2. Wait for the job to finish with a success status.
3. Open Storage and confirm your destination table contains the rows from source.

Troubleshooting

Symptom	Likely cause	Fix
Credentials test fails	Server unreachable, wrong host/port/service, or user lacks CREATE SESSION	Verify connectivity and re-check the grants in Step 1.
table or view does not exist	Input destination name doesn’t match the script, or wrong schema	Ensure the input Destination is source; if you set Schema, confirm the objects live there.
Run succeeds but nothing in Storage	Missing/incorrect output mapping	Add an output mapping whose Source matches the table the script created (result).

Related

• Input and output mapping — how staging works.
• Tutorial: Manipulating data — guided first transformation.