Working with an Oracle database using Ballerina

Ballerina version used: 0.992.0
Oracle database version used: Oracle-xe-11g (Follow these instructions explained here to install Oracle-xe-11g with Docker).


This Sample is written using ballerinax/jdbc module. Before running this sample you need to copy the Oracle JDBC driver jar into $BALLERINA_HOME/bre/lib.

First, create the table using the following command:

CREATE TABLE oracledb(id NUMBER, byte_array RAW(10))

view raw createTable.sql hosted with ❤ by GitHub

This is a sample to insert byte array into oracle database:

	import ballerina/io;
	import ballerinax/jdbc;
	import ballerina/sql;
	jdbc:Client testDB = new({
	url: "jdbc:oracle:thin:@localhost:49161:xe",
	username: "system",
	password: "oracle",
	poolOptions: { maximumPoolSize: 1 }
	});
	byte[] bitVal2 = [1, 2];
	public function main() {
	var dt1 = testDB->update("INSERT INTO oracledb(id, byte_array) VALUES (1, ?)", bitVal2);
	handleUpdate(dt1, "Update oracledb table");
	}
	function handleUpdate(sql:UpdateResult|error returned, string message) {
	if (returned is sql:UpdateResult) {
	io:println(message + " status: " + returned.updatedRowCount);
	} else {
	io:println(message + " failed: " + <string>returned.detail().message);
	}
	}

view raw insertByteArray.bal hosted with ❤ by GitHub

Running the sample:

ballerina run insertByteArray.bal

Output:

Update oracledb table status: 1