FTPS with PL/SQL

Anton Scheffer 15

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 […]

Create an Excel-file with PL/SQL

Anton Scheffer 136

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 […]

Utl_compress, gzip and zlib

Anton Scheffer 12

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. […]

Unwrapping 10G wrapped PL/SQL

Anton Scheffer 30

  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

Anton Scheffer 2

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

Anton Scheffer

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

Anton Scheffer 6

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, ‘+’ […]