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 I already mentioned that SFTP using plsql is possible. Not loading some Java classes in the database and adding some plsql […]
Anton Scheffer
as_json: Relational to JSON in Oracle Database
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 some spare time, so I tried my own JSON generator, build around 3 nested Oracle types, on the examples he used. […]
Using an aggregation function to query a JSON-string straight from SQL
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 used a solution as described in this post by Carsten Czarski. That post of Scott reminded me of a post by […]
FTPS with PL/SQL
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 with Oracle PL/SQL or Oracle FAQ’s But what if you want to secure your FTP transmission. Google doesn’t find any pure […]
Solving PLS-00753: malformed or corrupted wrapped unit within Apex SQL Workshop
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 ideas, to solve all the problems I used a wrapped package to store all the functionality of the plugin.
Read an Excel xlsx with PL/SQL
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 content from an Excel document
Select a blob across a database link, without getting ORA-22992
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 with a blob on a remote database When you use a normal select statement to get all columns you run into […]
Generating a PDF-document with some plsql: as_pdf_mini => as_pdf3
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 time for as_pdf3
Create an Excel-file with PL/SQL
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 create an Excel 2007 file with only a few lines of PL/SQL code. The main purpose for this package is getting […]
AS_PDF, generating a PDF-document with some plsql
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 functionality. It can only use the standard PDF fonts, and that means that it can only use the WINDOWS-1252 encoding/characterset. But […]
Parsing a Microsoft Word docx, and unzip zipfiles, with PL/SQL
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 and without using Java.
Utl_compress, gzip and zlib
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 “The output of the UTL_COMPRESS compressed data is compatible with gzip”. That means it’s following the RFC 1952 specs, RFC 1952. […]
Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring
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 11gR2 – new style hierarchical querying using Recursive Subquery Factoring. When I first was thinking about a pratical use for this […]
Unwrapping 10G wrapped PL/SQL
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 PL/SQL on the older database versions, pre 10G, is possible. David Litchfield, in his book “The Oracle Hacker’s Handbook”, describes a […]
Puzzelen met SQL – Testdata Generatie
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, het vakblad voor Oracle ontwikkelaars in Nederland. Een van de grote en vervelende uitdagingen bij het maken van SQL Puzzels maar […]
Oracle 11G: describing a refcursor
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 refcursor These can be used, together with dbms_sql.describe_columns to describe a refcursor. declare rc sys_refcursor; v varchar2(10); n number; c […]
Oracle 11G: XMLQuery = eval
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 ) || ‘ = ‘ || 2 XMLQuery( substr( sys_connect_by_path( level, ‘*’ ), 2 ) RETURNING CONTENT).getnumberval() product 3 , substr( sys_connect_by_path( level, ‘+’ […]
Solving a Sudoku with 1 SQL-statement: the Model-clause
Solving a Suduku with one SQL-statement, is that possible? A lot of people won’t believe it, but yes, it can be done.
Solving a Sudoku with Collections
If you want to write a program to solve sudokus, you can use almost any programming language. And PL/SQL too!
Pivot dynamic data with pipelined function
Many people want to pivot the result of their queries. I have no idea why, but they do. For instance,
More Jonathan Lewis
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 six people to attend. And yes, I was one of the lucky ones too. Although the seminar should be for developers […]
Designer ERD as pdf
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 Designer puts your ERD into a PDF-file. Check the “Fit to Page” checkbox for best results. Anton
Anydata and Collections
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, ANYDATA and ANYDATSET I found that the ANYDATA type can be used to do something simular. So I have written a […]