TO_DATE or not TO_DATE

Like a middle aged man visiting the gym for the first time in 10 years, I am merely going to flex my blogging muscles here…

One of the recurring issues that annoys me on the OTN SQL & PL/SQL forum, and also the APEX forum is the misunderstanding of what (more...)

Version 1.7 of Alexandria Utility Library for PL/SQL

Uncategorized
| Feb 17, 2013
The latest version of Alexandria, the utility library for PL/SQL, is now available for download. The previous version has been downloaded more than 2,000 times in the last 8 months.



The docs/readme.txt file contains a fairly detailed list of additions, enhancements and bug fixes. Below are some of the more noteworthy changes:

Improved installation script


You can now choose between installing the full library (close to 50 packages as of this version), by running the main /setup/install.sql script, or you can install just the core set of packages via setup/install_core.sql and then choose additional sets of (more...)

Interview with Yalim Gerger (CEO Formspider – the PL/SQL Web Framework)

Uncategorized
| Feb 12, 2013
In every other profession having a 20 year experience is a good thing, in ours you are treated as a has-been who is kept around only to maintain the legacy system. This is insane.

PL/SQL and Microsoft technology

Uncategorized
| Jan 16, 2013
Many companies tend to have mixed environments, with Oracle databases (and PL/SQL) running business-critical back-office systems, as well as Microsoft products for front-office applications such as email, word processing and collaboration/file sharing (ie Outlook, Office and Sharepoint), and of course the desktop operating system itself (Windows, with Internet Explorer, Internet Information Server, all integrated via Active Directory).



Working in these mixed environments means that you, as an Oracle PL/SQL developer, frequently need to work/integrate with various Microsoft technologies from PL/SQL.

Over the last couple of years, I've written a number of blog posts on this topic. This post is just (more...)

Error Wrangling

Last week the OTN SQL and PL/SQL Forum hosted of those threads which generate heat and insight without coming to a firm conclusion: this one was titled WHEN OTHERS is a bug. Eventually Rahul, the OP, complained that he was as confused as ever. The problem is, his question asked for a proof of Tom Kyte's opinion that, well, that WHEN OTHERS is a bug. We can't proof an opinion, even an opinion from a well-respected source like Tom. All we can do is weigh in with our own opinions on the topic.

One of the most interesting things in (more...)

GOTOs, considered

Extreme programming is old hat now, safe even. The world is ready for something new, something tougher, something that'll... break through. You know? . And here is what the world's been waiting for: Transgressive Programming.

The Transgressive Manifesto is quite short:

It's okay to use GOTO.
The single underlying principle is that we value willful controversy over mindless conformity.

I do have a serious point here. Even programmers who haven't read the original article (because they can't spell Dijkstra and so can't find it through Google) know that GOTOs are "considered harmful". But as Marshall and Webber point out, (more...)

Tom Kyte lays down in one deck what Oracle Database 12c is all about

Tom Kyte on many new Database 12c features: http://t.co/z98lLxHH     Via: (https://twitter.com/lucasjellema/status/257019546974683136) Filed under: DB Architecture, Oracle 12c, plsql Tagged: LinkedIn, New Features, OOW, Oracle 12c, Tom Kyte

Fisher-Yates (Knuth) Shuffle in PL/SQL

We were recently looking for a way to easily anonymize some customer data, specifically bank account numbers.

Part of our testing mechanism is to verify that a bank account and sort-code combination is valid and we retrieve the bank details for that combination (using a commercial account lookup service).  So we couldn't just replace the sort-code and/or account number with random numbers.

Conversely, replacing all account numbers with a handful of known, "safe" working values would lead to skewed data spreads, so that wouldn't work.

A better solution is to shuffle the existing data, moving the sort-code and account (more...)

SQL Solution for the Third International NoCOUG SQL & NoSQL Challenge

SQL vs NoSQL: Third International NoCOUG SQL & NoSQL Challenge sponsored by Pythian
As published in the 102nd issue of the NoCOUG Journal

THE WICKED WITCH OF THE WEST NEEDS HELP

BE IT KNOWN BY THESE PRESENTS that the Wicked Witch of the West needs your help to create
a (more...)

Database (schema) backup to the cloud with PL/SQL

Uncategorized
| Aug 13, 2012
In my last blog post I described using the DBMS_DATAPUMP and DBMS_JOB packages to automate database schema backups from the database itself, rather than using batch files to do so. I also mentioned that "since the files are written to the database server itself, you need some kind of process to move the files to another server or storage location, so the backups don't disappear along with the database if the database server itself is somehow lost or corrupted. [One] option is to upload the backup file from the database to some online ("cloud") storage service, such as Amazon S3. (more...)

Simple database (schema) backup using PL/SQL

Uncategorized
| Jul 23, 2012
Usually, database backups are the responsibility of DBAs, and not something that developers really care or think too much about. However, if you are a lone developer, or part of a small team that doesn't have the luxury of having a dedicated DBA, then you might have to deal with backups yourself.

There are several ways to make Oracle backups; what I will be concentrating on here is the "Data Pump Export" method. You may already be familiar with the command-line "expdp" command which allows you to create a dump (.dmp) file containing your database objects (schemas, tables, procedures, (more...)

Um patch para o SQL Navigator 6.7

Uncategorized
| Jul 20, 2012
Para quem usa o SQL Navigator 6.7 e tem notado que "às vezes" prende, tal parece ter origem na "feature History" que guarda os SQL (tec..) efetuados e permite uma pesquisa nos mesmos. É possivel efetuar uma "limpeza dos comandos efetuados" antes de uma data data ou toidos de modo a evitar que o ficheiro fique muito grande (parece que apartir de 600K é que o problema é encontrado) "Before you deleting your history file, you can have a backup if needed, the history file is in: xp: C:\Documents and Settings\UserName\Application Data\Quest Software\SQL Navigator 6.6.0\Unified (more...)

How to simulate DML Operations on XML data using XU (XQuery Update)

The following SQL pattern can be used to simulate DML operations on XML data by utilizing XU (XQuery Update) functionalities.

This query will do the following XML data transformation by using XU insert/delete/rename/replace statements.

1) Convert all the XML Attributes to Elements.                ( XU Delete & Insert)

2) Change the  (more...)

Using SSL client certificates for authentication in UTL_HTTP

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...)

How to validate XML data using XSD in Oracle XML DB

The following XML Schema can be used to validate the XML input data in the XML DB.

It enforces the following validation rules:

1) Parent_Tab/Parent_Row/dep_id is the (primary) key within Parent_Tab.
2) Parent_Tab/Parent_Row/dep_name should be one of the xs:enumeration list in DeptnameType and it is also unique within Parent_Tab.
3) (more...)

MS Exchange API for PL/SQL

Uncategorized
| May 26, 2012
As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!



Features


Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.

Prerequisites



Calling Java procedure from database

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. (more...)

SQL Navigator 6.7 released

Uncategorized
| May 9, 2012
Quest Software released the 6.7 version of SQL Navigator. Mainly it corrects some bugs.

How to do XML based data manipulation in SQL and XSLT

The following SQL pattern can be used to do XML based data manipulation and transformation by utilizing XSLT functionality.

This query will do the following data manipulation and transformation:

1) Rename the xml tag <FirstName> to <FN> when the position of the element node is
either the last one or (more...)

Parsing a CSV file in PL/SQL

The ability to parse a CSV file in PL/SQL seems like a simple requirement and one would think that you could either a) easily implement it yourself, or b) find some examples of it on the web.   Well if you have tried option A, you probably realized it gets real tricky when you have commas and double quotes in your actual data as well as having them as your deliminators and optionally enclosed by characters as well.  Plus all that substr‘ing and instr‘ing can really hurt your head after a while.  If you tried option B, then (more...)