Author: Anton Scheffer

Just a quick blog to get me through the Christmas eve. More than 5 years ago I wrote a blog about FTPS from an Oracle databse. In that blog …

Some time ago I noticed this blog from Dan McGhan. In that blog he compares several ways to generate JSON from relational data in a Oracle Database. I had …

Last week I read this blogpost by Scott Wesley. In this post he describes that he uses a custom aggregate function to create large JSON-strings. And for that he …

Doing a FTP-job with PL/SQL is not difficult. A basic implementation of RFC 959 can be written in a few hundred lines. See for instance ORACLE-BASE, How to FTP …

I was working on this great Apex plugin to load Excel sheets with more than 50 columns into the database. And because I had all those great, but secret …

At the OTN SQL and PLSQL forum I promised to publish some code I use for a project I’ still working on. This code allows you to select the …

Just a quick blog about a simple trick to select a blob across a database link, especially for a collegue of mine, Harry Dragstra. Say, you have a table …

It has been more than a year since I published my previous blog on generating PDF with pl/sql. In that time I’ve rewritten as_pdf two times, so now its …

For this project I took an Apex-plugin I have written, (IR) Report to Excel (xlsx), and turned it into a PL/SQL package. With this package it’s very easy to …

I’ve written a small package (1500 lines). But with this package you can generate a PDF-document with a few lines of PL/SQL code. It’s small because it lacks some …

Some days ago a collegue of mine asked if I could made something for him to unzip a Microsoft Word 2007 docx file. And of course in the database …

Oracle has a a supplied package utl_compress, which can be used to compress and decompress data with PL/SQL. According to the documentation it uses the “Lempel-Ziv compression algorithme”, and …

Oracle Database 11g Release 2 introduces a new feature called Recursive Subquery Factoring. My collegue Lucas sees it as a substitute for Connect By based hierarchical querying, Oracle RDBMS …

  Many people have tried to unwrap wrapped PL/SQL. Most people haven’t succeeded in doing it, but since Pete Finnegan’s presentation on the 2006 Black Hat conference, http://www.blackhat.com/presentations/bh-usa-06/BH-US-06-Finnigan.pdf, unwrapping …

Puzzelen met SQL – Testdata Generatie Anton Scheffer en Alex Nuijten   Dit artikel is de on-line tegenhanger van de rubriek Puzzelen met SQL die verschijnt in de Optimize, …

In Oracle 11G the supplied package DBMS_SQL is extended with two new procedures: to_cursor_number, which transfers a refcursor to a "dbms_sql cursor" to_refcursor, which transfers a "dbms_sql cursor" to …

A nice little trick on Oracle 11G is using XMLQuery as an eval function: SQL>l   1  select substr( sys_connect_by_path( level, '*' ), 2 ) || ' = ' …

Solving a Suduku with one SQL-statement, is that possible? A lot of people won’t believe it, but yes, it can be done.

If you want to write a program to solve sudokus, you can use almost any programming language. And PL/SQL too!  

Many people want to pivot the result of their queries. I have no idea why, but they do. For instance,

A little while ago, Jonathan Lewis presented a three day seminar in The Netherlands. You may have read other blogs on this site about this seminar. AMIS had sent …

An easy way to get your Designer ERD’s in a PDF-files is using PDFCreator. PDFCreator will install a new virtual printer. Using this printer to print your ERD in …

Steven Feuerstein has written a PLSQL-utility str2list in which he uses dynamic sql to fill collections of different types. While I was playing with the “new” Oracle types ANYTYPE, …