One link that I have to send to developers quite frequently is how to use XMLTABLE in SQL queries to bind comma separated list of values instead of generating large IN list directly to the query (and this way avoid new sqlid/cursor/wasted memory for each different value combination provided). The link that I usually send is this, but in this post I’d like to expand it a little, so it would work even when the string contains special XML characters.

For numbers, the usage is simple:

> var num_list varchar2(100)
> exec :num_list := '2668,2669,2670'

PL/SQL procedure successfully completed.

> SELECT id FROM ath_case WHERE id IN (
 SELECT (column_value).getNumberVal() FROM xmltable(:num_list)
 );

        ID
----------
      2668
      2669
      2670

> exec :num_list := '2671,2672,2673,2674'

PL/SQL procedure successfully completed.

> SELECT id FROM ath_case WHERE id IN (
 SELECT (column_value).getNumberVal() FROM xmltable(:num_list)
 );

        ID
----------
      2671
      2672
      2673
      2674

If the binded list consists of strings, then some extra steps are needed – the comma-separated has to be enclosed with double-quotes and the values have to be XML-encoded (XML special characters, like " replaced with codes).

> var str_list varchar2(100)
> exec :str_list := '"GI1","BI1"'

PL/SQL procedure successfully completed.

> SELECT u.first_name FROM ath_user u 
 JOIN ath_team t ON u.id = t.manager_id 
 WHERE t.name IN (
 SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list)
 );

FIRST_NAME
-----------
Riho
Kaur

> exec :str_list := '"OS1","OS2"'

PL/SQL procedure successfully completed.

> SELECT u.first_name FROM ath_user u 
 JOIN ath_team t ON u.id = t.manager_id 
 WHERE t.name IN (
 SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list)
 );

FIRST_NAME
-----------
Markko
Aive

> set define off
> exec :str_list := '"value1","value2","value " with quot","value & with amp"';

PL/SQL procedure successfully completed.

> SELECT DBMS_XMLGEN.CONVERT((column_value).getStringVal(), 1) FROM xmltable(:str_list);

DBMS_XMLGEN.CONVERT((COLUMN_VALUE).GETSTRINGVAL(),1)
-------------------------------------------------------------------------
value1
value2
value " with quot
value & with amp

Starting from 11.2 its possible to use SSL client certificates to authenticate yourself to a remote web service using SSL client certificates. I did not find much information on it using Google or documentation, that is why I’m writing this post.

Please refer to this post by Tim Hall to get started on connecting to HTTPS service using UTL_HTTP, all of this is needed before continuing with SSL client certificate authentication.

The first thing you need is to generate user certificate request inside Oracle Wallet, sign it by CA and load the returned certificate back to Wallet. I’m not going to very detailed steps here, but basically (using Oracle Wallet Manager OWM):

1. Open the wallet you created using Tim Hall’s post mentioned previously.
2. Go to Operations > Add Certificate Request
3. Fill in all the needed fields
4. After certificate request has been created, go to Operations > Export Certificate Request
5. Send the request to a Certification Authority (that the remote service trusts) for signing and wait for a reply (in a form of signed certificate)
6. Import the signed certificate to wallet – go to Operations > Import User Certificate

If you are using 11g OWM/ORAPKI and when importing the user certificate to wallet OWM displays an error or ORAPKI corrupts your wallet, you can just use OWM/ORAPKI programs from 10gR2 database client. This is due to bug Bug 9395937: UNABLE TO IMPORT USER CERTIFICATE IN OWM 11.1, WORKS IN 10.2.

Next thing is to add ACL privileges inside the database. UTL_HTTP documentation requires the use of use-client-certificates ACL privilege. How to do that I’ll refer to Tim Hall’s post again Fine-Grained Access to Network Services in Oracle Database 11g Release 1. In the example below I already have ACL all_access.xml and I’m granting connect and use-client-certificates privileges to CLTEST schema.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('all_access.xml','CLTEST', true, 'connect');

PL/SQL procedure successfully completed.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('all_access.xml','CLTEST', true, 'use-client-certificates');

PL/SQL procedure successfully completed.

Now the step that is not mentioned in UTL_HTTP documentation and got me stuck for weeks until I opened SR to Oracle Support. The network ACL needs also privileges on the Wallet file using DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL('all_access.xml','file:/path/to/oracle/wallet');

PL/SQL procedure successfully completed.

After the privileges have been assigned, you can use UTL_HTTP to query remote web service like you do with normal HTTPS connection. If the remote web service requests client to be authenticated using certificates, UTL_HTTP automatically handles it in the background and uses the user certificate located in the wallet. For example:

SQL> SELECT utl_http.request('https://secure.service.com/status', '', 'file://path/to/oracle/wallet', 'WalletPassword') FROM dual;

Back-story: A developer came to me and wanted explanation for a weird behavior in MySQL. They inserted a record (to InnoDB table), committed, and after receiving a message (on another application) tried to read that inserted record immediately, but the newly inserted record was not found. Problem only happened in production, but not always (quite frequently).

After comparing the MySQL parameter files between production and development environments I discovered that in production autocommit was disabled to make MySQL behave more like Oracle. This setting was removed from development after we rebuilt the environment (to use multiple MySQL instances with Oracle Clusterware, instead of one large MySQL instance), but the rebuild was not yet done in production.

The default transaction level for MySQL InnoDB is REPEATABLE READ (unlike Oracle, that has READ COMMITTED as default), that means that the SELECT query always returns the data at the time point when the transaction was started. If autocommit is off, then the first issued select statement will open the transaction and any subsequent select statement will return the data at the time point when the first select was issued, until transaction is ended with COMMIT/ROLLBACK. If autocommit is enabled, SELECT statement is run in a self-contained transaction, ending with COMMIT, so the end result is like READ COMMITTED isolation level in Oracle.

Here is an example what you’d expect to see as a result:

mysql session 1$ create table test (id integer unsigned primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql session 1$ set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
Empty set (0.01 sec)

  mysql session 2$ begin;
  Query OK, 0 rows affected (0.00 sec)

  mysql session 2$ insert into test values (1);
  Query OK, 1 row affected (0.05 sec)

  mysql session 2$ commit;
  Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

The same example of having autocommit off and transaction isolation level set as default:

mysql session 1$ set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

  mysql session 2$ begin;
  Query OK, 0 rows affected (0.00 sec)

  mysql session 2$ insert into test values (2);
  Query OK, 1 row affected (0.05 sec)

  mysql session 2$ commit;
  Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql session 1$ commit;
Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

Now, autocommit off and transaction isolation level set to READ COMMITTED:

mysql session 1$ set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql session 1$ set session transaction isolation level read committed;
Query OK, 0 rows affected (0.03 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

  mysql session 2$ begin;
  Query OK, 0 rows affected (0.00 sec)

  mysql session 2$ insert into test values (3);
  Query OK, 1 row affected (0.05 sec)

  mysql session 2$ commit;
  Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

You can read more about how autocommit is handled by InnoDB in documentation.

Another key component in our puzzle – application server used connection pools for managing MySQL connections, so after Java session requested a connection from pool, it was only a matter of chance whether the connection already had a transaction left open by the previous user of that connection, did the previous user commit, or was it a brand new connection.

Oracle Database has had the possibility to run Java code inside the database for a long time. It’s a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn’t successful in implementing it under 11.2.0.2).

First, load the Java source into database. The code below shows:

• How to return simple datatype (int) from Java function – makeConnection
• How to return Oracle CLOB datatype from Java – makeConnectionClob
• How to execute SQL from Java, in the same calling session

Note that method main is just added for testing from command line.

Then you need to create a wrapper package in database. This declares the PL/SQL wrapper function names and input/output parameters.

Download the source: java_source.java and PL/SQL wrapper.sql.

When you first execute the code, you will most likely get some privilege errors, but the error message will tell you how to grant the needed privileges. For example, for this code the following grants were needed:

exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStore', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.trustStore', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStorePassword', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', 'site.that.requires.user.cert', 'resolve' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', '1.2.3.4:443', 'connect,resolve' );

Oracle Wallet Manager and orapki do not let you extract the private key associated with user certificate located in Oracle Wallet. If you need it for some reason, for example testing with external tools like wget, then its possible to extract the private key using openssl, since Orale Wallet (ewallet.p12 file) is just PKCS#12 file.

[oracle@jfadboc1n01 wallet]$ openssl pkcs12 -in /home/oracle/wallet/ewallet.p12 -nocerts -out private_key.pem
Enter Import Password:
MAC verified OK
Warning unsupported bag type: secretBag
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
[oracle@jfadboc1n01 wallet]$ ls -l
total 16
-rw-r--r-- 1 oracle oinstall 11629 May 15 11:38 ewallet.p12
-rw-r--r-- 1 oracle oinstall  1879 May 17 08:53 private_key.pem

Here private_key.pem contains the private key extracted from Oracle Wallet.

The first password that openssl asks (Enter Import Password) is the wallet password, the other password (Enter PEM pass phrase) is used to protect the exported key.