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 […]
plsql
Machine Learning in Oracle Database – Classification of Conference Abstracts based on Text Analysis
Machine Learning is hot. The ability to have an automated system predict, classify, recommend and even decide based on models derived from past experience is quite attractive. And with the number of obvious applications of machine learning – Netflix and Amazon recommendations, intelligent chat bots, license plate recognition in parking […]
Executing command line scripts from the Oracle Forms client – using WebUtil
Our challenge: we are building the new ERP system – a pure HTML5 browser based application in Oracle ADF. This application replaces the current Oracle Forms based application. For more than a year and a half – the users will have a hybrid situation on their hands: some of their […]
The state of the flagship as per Oracle OpenWorld 2016 – Oracle Database 12c Release 2, Exadata Express Cloud Service, SQL and PL/SQL
The new 12cR2 release of Oracle Database had already been announced and discussed at length over the past year. And now it is finally available. And only as cloud service for now. Initially through the Exadata Express Cloud Service – which consists of a single PDB in a multitenant CDB, […]
New PL/SQL pragma (12cR2) to deprecate program units – sign of a modern programming language
Bryn Llewellyn (Distinguished Product Manager, Database Division, Oracle) presented at OOW2016 on new features in PL/SQL in Oracle Database 12cR2. One of the features that stood out was a new pragma deprecate that can be added to program units such as functions and procedures inside packages. This pragma is used […]
One of the many nice new features in 12c database: code based access control
Topic of this blog is a nice new feature in 12c, not the plsql package I built that’s using it. So here’s the story.. For one of our customers we needed to have a simple schema comparison tool that would be able to check, as part of application deployment activity, […]
AMIS25 Beyond the Horizon Conference – Session Resource Repository
Below you will find an overview of all the sessions that took place at the AMIS25 Beyond the Horizon conference (1-3 June 2016, Katwijk, The Netherlands) along with the associated session resources – such as the presentation slides and where applicable the source code. Presenter(s) Title Session Resources Aino […]
Parse JSON Array in SQL and PL/SQL – turn to a Nested Table
Transferring data between technologies and application tiers is done using various formats – binary, native on the one hand and open, text based such as CSV, XML and JSON on the other. Use of JSON is rapidly growing as a growing number of platforms and technologies provides support for JSON. […]
Reflections after Oracle OpenWorld 2015 – Data Management (Database)
This article gives an overview of some of the most eye catching stories from Oracle OpenWorld 2015 around Data Management. It discusses on premises products such as Oracle Database as well as cloud services around the Database. Data Management in the Oracle Integrated Cloud Platform Services composition covers Big Data, […]
Time is of the essence – The Fourth Dimension in Oracle Database 12c (on Flashback and Temporal Database)
Time has always been an important dimension for data in any database with topics like when was data created, when are records valid, how did records evolve over time, can we compare with yesteryear or even travel through time and data. The Oracle Database 12c release added a number of […]
Publish a REST service from PL/SQL to handle HTTP POST requests – using the embedded PL/SQL gateway
Oracle Database can act as an HTTP server – using the Embedded PL/SQL Gateway (the 10g successor of the MOD_PLSQL gateway). With just a few statements, we can have the Oracle Database become a listener to HTTP requests (GET or POST). When requests are received at the configured host, port […]
Invoke a REST service from PL/SQL – make an HTTP POST request using UTL_HTTP in Oracle Database 11g XE
This article is small and simple. It discusses how from PL/SQL an HTTP POST request can be made to a REST service. This particular service is exposed at http://localhost:9002/cinema and it expects a POST call. Making HTTP requests from PL/SQL is fairly simple, using the supplied package UTL_HTTP. Starting in […]
Donderdag 16 april/Thursday April 16th – Speedy Joe’s – Using asynchronous interaction in Java EE to turn the world’s slowest restaurant into a super performant place
De Java SIG (Special Interest Group) van AMIS organiseert op donderdag 16 april een bijeenkomst (publiek toegankelijk) over asynchrone interacties in Java EE (web) applicaties. Synchrone interacties in de echte wereld en in IT applicaties kunnen de boel behoorlijk ophouden. Synchroon betekent wachten – en resources vasthouden. Dat kan een […]
Set values for bind parameters in a database view using application context – single round-trip context manipulation and querying
While writing an article on database session context for the OTech Magazine’s Spring issue, I tried out whether I can send a SQL statement from a database consumer such as the SOA Suite Database Adapter or an ADF BC ViewObject that passes in the values for some bind parameters that […]
Everything That Is Really Useful in Oracle Database 12c for Application Developers – Oracle OpenWorld 2014
A few days ago, I presented a session at Oracle OpenWorld 2014 about the most useful features for application developers in Oracle Database 12c (12.1.0.1 and 12.1.0.2). The main topics I demonstrated were JSON, Flashback, Temporal Validity, Match Recognize (pattern matching), In Line PL/SQL and the SQL Translation (Profiles) Framework. […]
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 with Oracle PL/SQL or Oracle FAQ’s But what if you want to secure your FTP transmission. Google doesn’t find any pure […]
SQL: combine inline PL/SQL function with inline view in Oracle Database 12c SQL Query
Oracle Database 7.3 was the first to support inline views – using the select * from (select * from) syntax. Oracle Database 9i added the with clause for subquery factoring, using a syntax like with vw as (select * from) , vw2 as (select * from vw) select * from […]
Notes from Finland – impressions from the OUGF 2014 Harmony Conference
I am currently attending the OUGF 2014 Harmony Conference in a beautiful setting on a Finnish lakeside. This conference has a great line up in terms of speakers and sessions – truly impressive. It is a two day event with 6 parallel tracks – complemented with sauna, camping and pretty […]
Steven Feuerstein Master Class. Anti-Pattern PL/SQL Programming + 12c New PL/SQL Features, December 12 and 13 2013
12 and 13 December 2013 AMIS will host a two day masterclas with Oracle PL/SQL guru Steven Feuerstein. The Design Pattern movement reminds us that most of the code we write is similar to something written last week – or maybe even an hour ago. Once you identify a common […]
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 ideas, to solve all the problems I used a wrapped package to store all the functionality of the plugin.
AMIS presenteert: twee-daagse masterclass PL/SQL 12c door Steven Feuerstein
Steven Feuerstein, dé PL/SQL autoriteit, komt op 12 en 13 december naar AMIS in Nieuwegein. Twee dagen lang verzorgt hij een masterclass waar u intensief door hem persoonlijk gecoached wordt. Intensief en persoonlijk Normaal verzorgt hij seminars waar hij vanaf een podium voor een grote groep mensen presenteert. Nu hij […]
Oracle Database 12c: PL/SQL package UTL_CALL_STACK for programmatically inspecting the PL/SQL Call Stack
Oracle Database 12c ships with a brand new supplied package called UTL_CALL_STACK. This package provides an API for inspecting the PL/SQL Callstack. The package complements the DBMS_ UTILITY.FORMAT_CALL_STACK that returns a pretty print human readable overview of the callstack. Here is an example of how this package could be […]
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 content from an Excel document
The Very Very Latest in Database Development – slides from the Expertezed presentation
I have just completed my first ever presentation on the Expertezed.com network – http://www.expertezed.com/ , a reprise from my session on Oracle OpenWorld 2012. This presentation includes a number of slides regarding 12c features, based on the session and slides from Tom Kyte (Top 12 new features) and my notes […]
The APEX of Business Value… or: the Business Value of APEX? Cloud takes Oracle APEX to new heights!
The attraction of APEX has increased tremendously with the recent launch of the Oracle Cloud. APEX already supported departmental development and deployment of business applications with minimal involvement from the IT department (only a database needed be made available). Positioned as the ideal replacement for MS Access, APEX probably has […]
Kom kennismaken met AMIS en doe mee met uitdagende projecten
Hierbij nodigen we je uit om met ons kennis te komen maken. Ben jij een (junior) Oracle consultant die een stap verder wil maken? Wil je verder groeien en ontwikkelen tot principal consultant? AMIS geeft je de kans om die stap te zetten. Bij ons krijg je de ruimte om […]
OOW 2012: The Very Very Latest in Database Development (CON4792)
Database development in the Oracle Database is crucial for creating well balanced multi tier applications. This presentation describes a number of useful facilities and application architecture considerations around the database, taking into account some of the most recent insights. The official slide deck from this presentation at Oracle Open World […]
Oracle RDBMS 10GR1: solution to avoid character encoding in XML with UPDATEXML
On a recent project, I ran into a problem with an XML document, that had to be enclosed within another XML document, generated from a database query. The problem I ran into was the character encoding of the XMLElement function, which eventually was worked around with UPDATEXML. In this blog […]
Reduce occurrence of ORA-04068 while upgrading PL/SQL packages by moving global variables to Application Context
This article will argue against the use of (stateful) global PL/SQL variables – as they are both a potential problem for stateless web applications and the availability experienced by end users during database upgrades. In various database releases, Oracle did a lot of work in supporting online redefinition of database […]
ADF 11g Business components – Create PL/SQL based entities
While building a (new) ADF 11g application you sometimes have to deal with the fact that you have to use existing PL/SQL procedures for storing your data. This article describes how you can overwrite your ADF business component’s default behavior to execute its DML statements using PL/SQL stored procedures. […]
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 time for as_pdf3
Java is niet eng! introductie van Java voor PL/SQL ontwikkelaars – donderdag 5 april
Veel PL/SQL ontwikkelaars hebben (n)iets met Java. Aan de ene kant zien ze dat Java op veel plekken wordt gebruikt, binnen en buiten de Oracle technology stack. En dat kennis van Java af en toe best handig zou kunnen zijn – zeker als je als klassiek Oracle ontwikkelaar gebruik wil […]
Jouw toekomst als Oracle professional AMIS informatieavond over jouw ontwikkelingspad (28 februari)
Als je dit leest en je bent Oracle professional – ontwikkelaar of administrator – is de kans groot dat je jouw carriere bent gestart in de jaren ‘90, in het tijdperk van Oracle7, Oracle Forms en Client/Server applicaties – net voor de opkomst van Java, internet, mobiel en SOA. Misschien […]
Jouw toekomst als Oracle professional – AMIS !!EXTRA!! informatieavond over jouw ontwikkelingspad
Als je dit leest en je bent Oracle professional – ontwikkelaar of administrator – is de kans groot dat je jouw carriere bent gestart in de jaren ’90, in het tijdperk van Oracle7, Oracle Forms en Client/Server applicaties – net voor de opkomst van Java, internet, mobiel en SOA. Misschien […]
Publishing to CometD Bayeux Channel from inside the Oracle Database – PL/SQL based push to CometD Web Client
In recent articles, I have introduced CometD as framework for Server to Client and Client to Client Push: Running CometD 2 examples – locally on Tomcat using Maven and NetBeans https://technology.amis.nl/blog/14709/running-cometd-2-examples-locally-on-tomcat-using-maven-and-netbeans, CometD 2 Java Client Sample – open project in NetBeans based on Maven pom file, modify sources and run […]
Absolutely Typical – The whole story on Types and how they power PL/SQL Interoperability (UKOUG, 2011)
This presentation will hopefully convince database developers that types in the Oracle Database are worth their salt – and more. With the recent improvements in 11gR2, the pieces are available to complete the puzzle of structured and modern programming with a touch of OO and more importantly to create a […]
Implementing Web Services backed by a Database PL/SQL API using the Oracle Service Bus
This article accompanies an article on the Architecture section of Oracle Technology Network (OTN): Implementing the Enterprise Service Bus Pattern to Expose Database Backed Services. It provides a detailed description of the implementation of the ESB architecture design pattern – the same that is introduced in the article on OTN […]
2 dagen seminar door Steven Feuerstein: Best of Oracle PL/SQL (8 en 9 december)
In dit tweedaagse seminar neemt Steven Feuerstein je mee ver voorbij de basismogelijkheden van PL/SQL. Steven zal tijdens dit seminar de best practices behandelen die hij op tientallen plekken in de wereld heeft verzameld en die hij ook mede door zijn nauwe samenwerking met het PL/SQL product team van Oracle […]
Business Rule: Only One per Day, but keep the time
The business rule states: Only one entry is allowed per ID and per day and the time should be recorded. The table involved (simplified for the blog post) SQL> create table test 2 (id number 3 ,inspection_dt date 4 ); Wouldn’t it be nice if it was possible to do […]
Creating JSON document straight from SQL query – using LISTAGG and With Clause
This very brief article demonstrates how a fairly complex, nested JSON document can be created using a single SQL query. The main features used in the SQL statement are the WITH clause to define the inline views per object (Department, Manager, Employee), Scalar Subquery to retrieve the result from an […]