I’ve moved!

Please come by my new site at ruepprich.com.

ORA-12545 When running Oracle inside Docker

When trying to make an http request from my Oracle database running inside a Docker container, I received the following error:

SQL> select utl_http.request('http://api.open-notify.org/iss-now.json') from dual;

Error starting at line : 1 in command -
select utl_http.request('http://api.open-notify.org/iss-now.json') from dual
Error report -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-12545: Connect failed because target host or object does not exist
ORA-06512: at line 1

This puzzled me quite a bit. The error pretty much says that it cannot resolve the hostname api.open-notify.org. When I run a simple curl command from my host, I get the expected result.

$ curl http://api.open-notify.org/iss-now.json
{"message": "success", "iss_position": {"longitude": "-144.6590", "latitude": "-36.9405"}, "timestamp": 1531175688}[oracle@jcat-server sh]

Then it dawned on me to try the curl command from within the container:

docker exec -it 76f4 curl http://api.open-notify.org/iss-now.json
curl: (6) Could not resolve host: api.open-notify.org

So it was the container that could not resolve the domain name.

After some research, I found that I need to run the docker image with the –dns parameter, which tells the container where to do DNS lookups. You can add multiple –dns parameters to your run command. I used it with Google’s DNS server and the one specified in my /etc/resolv.conf file. So the run command looks something like this:

docker run -d \
  -p 1521:1521 \
  -v /u01/oradata/xe:/u01/app/oracle \
  --dns "some.ip.address.169" \
  --dns "8.8.8.8" \
  --name oracle_xe \
sath89/oracle-xe-11g

When I now try the curl command to test, I get the correct response:

$ docker exec -it 1234 curl http://api.open-notify.org/iss-now.json
{"message": "success", "iss_position": {"longitude": "-73.8368", "latitude": "-49.7166"}, "timestamp": 1531176511}

Now trying it from inside the database, works, too:

SQL> select utl_http.request('http://api.open-notify.org/iss-now.json') from dual;
UTL_HTTP.REQUEST('HTTP://API.OPEN-NOTIFY.ORG/ISS-NOW.JSON')
{"message": "success", "iss_position": {"longitude": "-67.2304", "latitude": "-48.1655"}, "timestamp": 1531176585}

BTW: Don’t forget to set your ACL!

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

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

http://dsavenko.me/oracledb-apex-ords-tomcat-httpd-centos7-all-in-one-guide-introduction/

Quick & Dirty: Populate number array from string.

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

declare
    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;
begin
    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
        dbms_output.put_line(l_number_array(idx));
    end loop;
end;

Downloading Oracle Files With the Command Line

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: http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html 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 “http://download.oracle.com/otn/java/sqldeveloper/sqlcl-17.4.0.354.2224-no-jre.zip”

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

This is a reblog from Chris Saxon:

https://blogs.oracle.com/sql/12-things-developers-will-love-about-oracle-database-12c-release-2

Getting started with Liquibase

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:

changelog_1.sql

--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 liquibase.properties 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

Integration

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

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

Matt Mulvaney has an excellent post regading the APEX interactive grid: http://www.explorer.uk.com/getting-grips-apex-interactive-grid-api/

Identity Columns and Data Pump

via Identity Columns and Data Pump