AMIS, Data Driven Blog – Oracle & Microsoft Azure
Getting value from IoT, Integration and Data Analytics
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 […]
0 
0 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. […]
0 0 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 […]
0 0 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 […]
0 0 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.
0 0 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
0 0 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 […]
0 0 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
0 0 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 […]
0 0 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 […]
0 0 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.
0 0 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. […]
0 0 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 […]
0 0 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 […]
0 0 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 […]
0 0 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 […]
0 0 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, ‘+’ […]
0 0 Solving a Suduku with one SQL-statement, is that possible? A lot of people won’t believe it, but yes, it can be done.
0 0 If you want to write a program to solve sudokus, you can use almost any programming language. And PL/SQL too!
0 0 Many people want to pivot the result of their queries. I have no idea why, but they do. For instance,
0 0 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 […]
0 0 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
0 0 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 […]
0 0 
