Reblog: Oracle Database XE, APEX, ORDS, Tomcat and httpd on CentOS 7

March 29, 2018 at 10:17 am | Posted in Oracle Developement | Leave a comment

Here is a nice guide for a full-stack APEX configuration:


Quick & Dirty: Populate number array from string.

March 13, 2018 at 1:09 pm | Posted in Oracle Developement | 2 Comments

Here is a quick way to convert a list of colon separated numbers into a number array:

    l_list_of_numbers  varchar2(32767) := '1:2:3';
    l_string_array     apex_application_global.vc_arr2;
    l_number_array     apex_application_global.n_arr;
    l_string_array := apex_util.string_to_table(l_list_of_numbers); 

    select *
    bulk   collect into l_number_array
    from   table(l_string_array); 

    for idx in 1..l_number_array.count loop
    end loop;


Downloading Oracle Files With the Command Line

March 8, 2018 at 6:40 pm | Posted in Oracle Developement | Leave a comment

Here is a quick trick that allows you to download files from OTN through the command line. In order to download OTN files you need to agree to the license agreement. That is easy enough in a browser, but not available with wget. However, wget allows you to include a cookie file. To get to the browser’s cookies, I use Chrome with the cookie.txt export extension.

So the first step is to go to the download page of the product you want to download. For example SQLcl: and agree to the license. This step is now recorded in the browser’s cookies. Now simply click the cookes.txt export icon in the browser. This opens up a pop-up window with all the cookies. Copy and paste the text into a cookies.txt file on your server (/u01/cookies.txt).

Now you can use wget with the –load-cookies option:

wget –load-cookies=/u01/cookies.txt –http-user=your.otn@username –ask-password “”

You’ll be prompted for the OTN password and the download begins.

Note for each product you download, you will need to agree to the license and use those particular cookies.





12 Things Developers Will Love in 12.2

March 7, 2018 at 11:19 am | Posted in Oracle Developement | Leave a comment

This is a reblog from Chris Saxon:


Getting started with Liquibase

February 27, 2018 at 4:16 pm | Posted in Oracle Developement | Leave a comment

Liquibase is a source code control management tool for database schemas. It basically consists of a command line tool that logs into your database and applies schema changes from files. The files store the incremental changes to the schema and are logged in a table named DATABASECHANGELOG in that schema. By looking at that table, Liquibase knows which changes need to be applied.

It is designed to work along with source code control management systems like Git, and automation servers like Jenkins. The goal is to automate database changes through a DevOps process, without the help of a database administrator.

The basic steps for deploying a database change with Liquibase are:

  1. Create a changelog file with the code.
  2. Run a Liquibase update to apply the code.

Download the Liquibase archive and place it into a convenient directory on your server.

Changelog Files

The changelog files can be written in XML, JSON, YAML, or SQL. In this post, I will focus on SQL and XML because they are more appropriate for Oracle work.

The changelog files contain at least an author, a change ID, and the code. Additionally, you can add a comment, a release tag, and a rollback statement:

--liquibase formatted sql
--changeset christoph:1
--comment create department table
create table department (
    id number generated always as identity,
    name varchar2(30),
    active varchar2(1) default 'N',
    constraint department_pk primary key (id)

--rollback drop table department;

The first comment tells Liquibase that this is an SQL file and not the expected XML. The –changeset comment tells the author and change number. The –comment is a descriptive comment, and the –rollback states how to undo the above code.

Before we execute this changefile, let’s create the file that tells Liquibase how to connect to the database:

driver : oracle.jdbc.OracleDriver
classpath : /path/to/ojdbc8.jar
url : jdbc:oracle:thin:@localhost:1521/pdb1
username : scott
password : tiger
changeLogFile : changelog_1.sql

From the directory where you unzipped Liquibase, run:

./liquibase update
Liquibase Update Successful

This is basically it. The department table is now created in the scott schema, and you can check the DATABASECHANGELOG table (automatically created when Liquibase is first run)  to see when the change was applied, and who the author was:

select * from databasechangelog;


To undo this change, use the rollback command:

./liquibase rollbackCount 1
Liquibase Rollback Successful

This will drop the table, as specified in the –rollback directive. It will also remove the corresponding row from DATABASECHANGELOG.

ChangeLog Master

It is a good idea to create a changelog “master” file. This file can then call other files that need to be included. This way you don’t have to specify the changelog file, each time you run the command. This is the way to do it if you are running an automated process.

The changelog master file is an XML file that looks like this


This file calls two changelog files that perform DML, and two files that compile PL/SQL code. It is a good practice to keep your DML and PL/SQL code separate. The final directive creates a release tag, which will show up in the TAG column of the DATABASECHANGELOG table. These tags can be used to rollback:

./liquibase rollback 1.0.1
Liquibase Rollback Successful


You can now use Liquibase as part of your DevOps process. If you use an automation server like Jenkins, you could automatically deploy database changes to your test database as soon as new code gets committed.

Let me know in the comments whether you are using Liquibase, and how you’re doing it.


Reset Password By Values in 12c

January 9, 2018 at 2:21 pm | Posted in Oracle Developement | Leave a comment

In prior Oracle version you used to be able to hack a users password by using the identified by values syntax. This no longer works in 12c, but there is a hack for the hack as blogged by Brian Peasland.

Here is a quick query that will generate the alter user command:

select u.username
 ,'alter user '||u.username||' identified by values '''||s.spare4||''';' cmd
 from dba_users u
 join sys.user$ s
 on u.user_id = s.user#
 where u.username = upper('&username');



Getting to Grips with the APEX Interactive Grid API

December 12, 2017 at 8:01 pm | Posted in Oracle Developement | Leave a comment

Matt Mulvaney has an excellent post regading the APEX interactive grid:


Identity Columns and Data Pump

November 7, 2017 at 11:36 am | Posted in Oracle Developement | Leave a comment

via Identity Columns and Data Pump

ORDS REST Modules with PL/SQL

October 25, 2017 at 9:00 am | Posted in Oracle Developement | Leave a comment

Here is one of the best references I’ve found for creating ORDS REST services with PL/SQL.

There are examples of the various methods (GET, POST, PUT, DELETE), as well as examples for authentication.

One thing to note is that REST modules created with PL/SQL are separate from those created in APEX. Therefore, modules created in APEX cannot be edited with PLSQL.

Creating REST modules in PL/SQL is the more robust method, as it allows you to customize it better. Also, having any sort of logic inside stored procedures and database objects is always a good idea.



ODTUG GeekAThon 2017

September 25, 2017 at 9:11 am | Posted in Oracle Developement | Leave a comment

This year, ODTUG sponsored another GeekAThon, challenging nerds to come up with cool ideas using Bluetooth Low Energy beacons. Here is my entry for this year’s contest:

BLE ID Badge for Amazon Echo

ODTUG GeekAThon 2017

Team: Texas Heat

Christoph Alexander Ruepprich, a.k.a Rocket, Christoph Martin Ruepprich


The BLE (Bluetooth Low Energy) beacon from the KScope conference badges serve as personal identifiers for the Amazon Echo. By determining the ID of the nearest badge, the Echo responds with personal data for the badge bearer.



Using the noble library for Node.js, I was able to determine the individual IDs of the two Bluetooth beacons. Using Node.js on a MacBook Pro, I wrote an Alexa skill that would scan Bluetooth signals. If a signal with one of the previously identified IDs was found the Alexa skill could respond with individualized information.



1 Laptop computer (in this case a MacBook, but a Windows or Linux PC with built in Bluetooth would work, too).

  • 2 BLE Beacons
  • 1 Amazon Echo Dot



Node.js version 8.4.0

Node.js modules:

Ngrok ( to set up a secure tunnel to laptop. This provides a https address with a valid certificate to the localhost, and thus to the Node.js application.



There are two parts to this application: One to handle the Alexa skill, the other to do the Bluetooth scanning.

When the application starts, an express.js webserver is instantiated and the noble Bluetooth module starts scanning immediately. The scanner looks for the two identified BLE beacon IDs and keeps track of them in two variables: closeBle, for the beacon with the stronger signal, and farBle for the beacon with the weaker signal. The signal strength and other attributes of the beacons are recognized by noble, and provided in JSON format.

The webserver waits for incoming POST requests from Alexa. When a request comes in from Alexa, the application checks the ID of closeBle, and then formats the response depending who the owner is.

In this demo we have two beacons: One for the police officer, and one for the hacker. If the application sees that the police officer is closer, it responds with data pertinent to the officer. If the hacker’s badge signal is stronger, the application pretends that the beacon’s ID is not recognized and responds with a message saying that a hacking attempt was made.

Alexa Skill

The Alexa skill uses the invocation Police Headquarters. This is the name by which the skill can be invoked on the Echo. It is configured to use the ngrok URI pointing to the Node.js application on the laptop. The interaction model simply consists of an intent called briefing which is called when the police officer asks for his briefing or mission.



Next Page »

Entries and comments feeds.