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

More Jonathan Lewis

Anton Scheffer

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

Anton Scheffer

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

Anton Scheffer

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