Weblog for the AMIS Technology corner
Anton Scheffer
This user hasn't shared any biographical information
Posts by Anton Scheffer
Create an Excel-file with PL/SQL
19/2/2011 - 4:24 pm
Posted in Devel. + PL/SQL tools, General | 73 comments
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.
begin
as_xlsx.query2sheet( ’select * from dual’ );
as_xlsx.save( ‘MY_DIR’, ‘my.xlsx’ );
end;
The main [...]
AS_PDF, generating a PDF-document with some plsql
20/10/2010 - 9:28 pm
Tags: pdf, plsql
Posted in Database, Devel. + PL/SQL tools, General | 87 comments
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 besides that it’s fairly complete.
Parsing a Microsoft Word docx, and unzip zipfiles, with PL/SQL
9/6/2010 - 7:46 pm
Posted in Database, Devel. + PL/SQL tools, General, XML | 9 comments
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
13/3/2010 - 8:38 pm
Tags: plsql
Posted in Database, Devel. + PL/SQL tools, General | 11 comments
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 algorithm”, and “The output of the UTL_COMPRESS compressed data is compatible with gzip”. That means it’s following the RFC 1952 specs, RFC 1952. And that may be [...]
Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring
13/10/2009 - 11:11 am
Posted in Database, Devel. + PL/SQL tools, General, Oracle | 45 comments
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 feature I couldn’t come up [...]
Unwrapping 10G wrapped PL/SQL
3/2/2009 - 12:31 am
Posted in Devel. + PL/SQL tools, Oracle | 30 comments
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 method to unwrap code on a [...]
Puzzelen met SQL – Testdata Generatie
22/10/2007 - 12:51 pm
Posted in Devel. + PL/SQL tools, General | 2 comments
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 belangrijker nog bij het bouwen van de demo-applicaties [...]
Oracle 11G: describing a refcursor
22/7/2007 - 1:41 am
Posted in Devel. + PL/SQL tools, General | No comments
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.
SYS@LAB> declare
2 rc sys_refcursor;
3 v varchar2(10);
4 n number;
5 c integer;
6 cnt [...]
Oracle 11G: XMLQuery = eval
14/7/2007 - 7:39 pm
Posted in Database, Devel. + PL/SQL tools, General, XML | 6 comments
A nice little trick on Oracle 11G is using XMLQuery as an eval function:
SYS@LAB> 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, ‘+’ ), 2 ) || ‘ = ‘ || [...]
Solving a Sudoku with 1 SQL-statement: the Model-clause
5/6/2007 - 1:34 am
Posted in Devel. + PL/SQL tools, General, Oracle | 15 comments
Solving a Suduku with one SQL-statement, is that possible? A lot of people won’t believe it, but yes, it can be done.
