I was looking for PL/SQL examples to use metadata and data filtering in datapump API, but I didn't find any. So here is one example. It uses table reload_dev_tables to specify what schemas/tables should be exported using data pump and what where clause should be set.
Structure for reload_dev_tables:
There are a few Oracle database replication solutions on the market:
- Oracle Streams (powerful, included with RDBMS license (Oracle SE has trigger-based capture, EE mines redo logs and log buffer), but deprecated - no longer developed, complex to manage)
- Oracle GoldenGate (powerful, but very expensive)
- Tungsten (heterogeneous, but from Oracle (more...)
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.
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 (more...)
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 (more...)
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 126.96.36.199).
First, load the Java source into database. (more...)
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
-rw-r--r-- 1 oracle oinstall 11629 May (more...)
When instance fails in Oracle RAC, the services that were using this instance as a preferred instance are automatically relocated to instances marked as available for this service. But after the failed instance recovers and starts up again, the relocated services are not moved back and need manual srvctl relocate service command from administrator to move them back.
Here is a little Bash script to automate this process. Oracle Clusterware (Grid Infrastructure) can execute user callout scripts on FAN events, like INSTANCE up/down. Place this script under $GRID_HOME/racg/usrco/ and set the execute bits on the file. Then clusterware will execute (more...)
I've been doing lately quite many database clustering implementations; Oracle RAC and since we have many MySQL instances in production, had to find a good way to make MySQL instances highly available also.
One good solution for this is managing MySQL instances with clusterware and since we are planning to use Oracle RAC on Oracle Enterprise Linux anyway, then Oracle Clusterware is an excellent candidate for this task. Also... Oracle Clusterware is included with Oracle Enterprise Linux at no additional charge.
Requirements I had:
- Multiple MySQL instances running in the same cluster, in case of node failure affected MySQL instances (more...)
Tested with Centos 5 and PostgreSQL 8.4.
Sadly PostgreSQL does not have remap_schema parameter in its data import tool pg_restore, so importing tables to another schema in destination database is a little bit tricky.
Here is a Bash script I use to connect to the source database over SSH tunnel, export two tables (srv.stat_export and disp.trip) and then import them to the destination database under schema etl.
Download the script: import_table_to_another_schema.sh
# Open SSH tunnel to remote database
# Make sure, that passwordless authentication is enabled
ssh -C -N -L 15432:livedb2:5432 (more...)
There is an interesting W3C Draft, that enables websites to just simply ask web browser to report the users geographical location, and then the web browser will try the best available location method, like GeoIP, WIFI location or GPS. I have currently tested it on Firefox 3.6 and Google Chrome; Internet Explorer 8.0 does not support it yet.
W3C Geolocation API Draft
Mozilla documentation for Geolocation
How to use it in APEX?
If you are just interested in recording the users location, then using an on-demand application process should be the easiest solution:
First, create two application items: (more...)
My use case for this: one big database, where applications have some shemas. But also, quite a lot of developers need access to the database, to see the production data and security requires each user to have his/her own personal account and of course also requires password expiration and complexity. Authenticating database users against organizations central Active Directory would be just perfect for this case.
Oracle offers this solution for Enterprise Edition, but its an extra cost option - Oracle Advanced Security. If these are not an option for you, then its also possible to achieve the same task, in (more...)
I started to play around with DBFS (Database Filesystem), a new interesting feature in Oracle 11.2.
There are some excellent guides on how to set it up:
But both of them had one "not very clean" part in them - compiling FUSE kernel driver from source. Actually this is not necessary and there are precompiled FUSE rpm packages available:
After RPM package is installed, you need to add the OS user who will mount the filesystem (for example oracle) to OS group fuse.
usermod -a -G fuse oracle
A small SQL code sample this time, how to read RSS file in SQL. I'm using it to display and aggregate some RSS feeds in APEX application.
All columns are directly read from XML (plus some additinal code to get the time zone correct), except pubdate_local that is pubdate converted to date datatype in local time zone. The first example is reading from a local file localnews.rss under directory DIR1.
CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
FROM ( SELECT title,
REPLACE(pubdate, 'PDT', 'PST PDT'),
An excellent joint conference for Estonian, Finnish, Latvian and Russian Oracle User Groups in Tallinn, 20. - 21. May 2010.
Speakers also include Tom Kyte, Tanel Põder, Chris J. Date and Steven Feuerstein.
Read the agenda here and register in OUGF home page (250€+VAT registration fee).
In addition, just before the conference, 17.-18. May, Chris Date will perform his "How to Write Correct SQL and Know It: A Relational Approach to SQL" seminar in Helsinki. More info here and more detailed information here.
In this part I'll look at some features of Oracle Database for getting the data out in XML format, with SQL code only. In part 1 I looked at some features for reading/parsing XML with pure SQL.
Populating the tables for examples
Loading the departments into table DEPARTMENTS.
CREATE TABLE departments AS
COLUMNS id FOR ORDINALITY,
name VARCHAR2 (100 CHAR) PATH 'name') dep;
Employee data to table EMPLOYEES.
CREATE TABLE employees AS
I have tried to read Oracle XMLDB Developer Guide and my first reaction was that parsing XML is very complicated in Oracle... And lately I got a task to rewrite some PL/SQL code to use a different SOAP service and the existing code had a few hundred rows just to parse XML (with XML DOM API)! Actually, starting from 10.2, this job is much easier...
Here I will go over some methods for working with XML, that are usable directly from SQL. In this first part, reading and extracting data from XML.
XML for the examples
<?xml version="1. (more...)
I love database performance visualization tools and now Oracle has released small desktop widgets that give a quick overview of the targets in OEM Grid Control.
The one I really like is High-Load Databases widget. That shows a quick overview of how the top databases are performing at the moment - total number of average active sessions and a graph how this load is divided between the CPU/IO/Other wait classes.
With a click of a button, the screen changes to the new rectangular style performance graph, also showing the latest ADDM findings.
One feature request to Oracle - please add (more...)
I needed to develop one small application that communicates with external mobile devices, being like a central server where mobile clients send their data and receive configuration parameters. Reporting will be added later using Oracle APEX.
It's a small application for a customer demo, so it sounded like a perfect opportunity to have my first look at the Oracle XE database :)
Oracle XE comes with a pre-configured XDB HTTP listener for APEX.
I don't need the full APEX engine for my simple request server, just a PL/SQL package using PL/SQL Web Toolkit will do just fine and there is (more...)
I got a task some time ago, to copy a-lot-of-millions of rows from Oracle to MySQL. As fast as possible.
In the last year I have used Oracle Heterogeneous Services (HSODBC) a lot to select data from different non-Oracle databases, but I had yet to try to unload data from Oracle with best performance.
My first thought was, of course, that the process needs to run in parallel. Here I'll first take a look at the overall HSODBC performance and then look how can I make this process run in parallel.
In this test I use Oracle Database 11.1. (more...)