Finally, sftp from an Oracle database using plain plsql
![Finally, sftp from an Oracle database using plain plsql]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "Finally, sftp from an Oracle database using plain plsql")
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 …
Author: Anton Scheffer
as_json: Relational to JSON in Oracle Database
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 …
Using an aggregation function to query a JSON-string straight from SQL
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 …
FTPS with PL/SQL
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 …
Solving PLS-00753: malformed or corrupted wrapped unit within Apex SQL Workshop
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 …
Read an Excel xlsx with PL/SQL
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 …
Select a blob across a database link, without getting ORA-22992
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 …
Generating a PDF-document with some plsql: as_pdf_mini => as_pdf3
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 …
Create an Excel-file with PL/SQL
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 …
AS_PDF, generating a PDF-document with some plsql
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 …
Parsing a Microsoft Word docx, and unzip zipfiles, with PL/SQL
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 …
Utl_compress, gzip and zlib
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 …
Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring
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 …
Unwrapping 10G wrapped PL/SQL
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 …
Puzzelen met SQL – Testdata Generatie
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, …
Oracle 11G: describing a refcursor
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 …
Oracle 11G: XMLQuery = eval
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 ) || ' = ' …
Solving a Sudoku with 1 SQL-statement: the Model-clause
Solving a Suduku with one SQL-statement, is that possible? A lot of people won’t believe it, but yes, it can be done.
Solving a Sudoku with Collections
If you want to write a program to solve sudokus, you can use almost any programming language. And PL/SQL too!
Pivot dynamic data with pipelined function
Many people want to pivot the result of their queries. I have no idea why, but they do. For instance,
More Jonathan Lewis
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 …
Designer ERD as pdf
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 …
Anydata and Collections
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, …