//AMIS Technology Blog » Oracle wizard » Page 2

Posts tagged Oracle wizard

Parsing a Microsoft Word docx, and unzip zipfiles, with PL/SQL

12
3 years ago
by Anton Scheffer in , , ,

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. (more…)

SIG Event

Utl_compress, gzip and zlib

12
3 years ago
by Anton Scheffer in , ,

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. And that may be very useful (but I have never used it), but I need compression (I’m working on a PDF-generator in PL/SQL) and decompression (unwrapping wrapped PL/SQL!) in the zlib-format, RFC 1950. Both formats use the same algorithm, RFC 1951, but have different headers and trailers. So can utl_compress be used to for compressing/decompressing data according to the zlib-specs. Yes! (more…)

SIG Event

Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring

3 years ago
by Anton Scheffer in , , ,

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 with anything, but on second thought:: solving Sudokus!

Say you have a sudoku like:

(more…)

Vacatures bij AMIS services

Unwrapping 10G wrapped PL/SQL

4 years ago
by Anton Scheffer in ,

 

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 10G database. According to the description it doesn’t seem too difficult to do:   “First, it is base64 decoded. Then, each byte is resubstituted with a second corresponding substitution table. Last, the text is decompressed, leaving the clear text of the PL/SQL.”   A year ago, after reading the book, I first tried to unwrap some code. Without success! But this year I was looking for a nice project to kill some time and I decided to give it another go. From Litchfield’s description it is clear that you need a substitution table. Litchfield didn’t reveal the table. I couldn’t find it with Google. Looking for it in the wrap utility it not allowed (and difficult too, see for instance this work from some Israeli researchers More >
SIG Event

Puzzelen met SQL – Testdata Generatie

5 years ago
by Anton Scheffer in ,
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 en vooral bij het testen van nieuw ontwikkelde applicaties is het samenstellen van een set van voorbeeld-, demo- of test-data. Eindeloos voornamen, achternamen, plaatsnamen en geboortedata invoeren is stomvervelend en als er ook nog eisen aan de kwaliteit worden gesteld – zoals een evenwichtige verdeling over een waardenbereik (more…)

SIG Event

Oracle 11G: describing a refcursor

5 years ago
by Anton Scheffer in ,

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 integer; cnt integer; dt dbms_sql.desc_tab3; begin open rc for 'select dummy, cast( 4 as number(3,1)) from dual'; c := dbms_sql.to_cursor_number( rc ); dbms_sql.describe_columns3( c, cnt, dt ); dbms_output.put_line( 'no. columns = ' || cnt ); for i in 1 .. cnt loop dbms_output.put_line( dt(i).col_type ); dbms_output.put_line( dt(i).col_name ); dbms_output.put_line( dt(i).col_max_len ); dbms_output.put_line( dt(i).col_precision ); dbms_output.put_line( dt(i).col_scale ); end loop; rc := dbms_sql.to_refcursor( c ); fetch rc into v, n; close rc; end; / no. columns = 2 1 DUMMY 1 0 0 2 CAST(4ASNUMBER(3,1)) 22 3 1 PL/SQL procedure successfully completed.

Anton

Go to Top