Syslog is a standard for message logging, often employed in *NIX environments. It allows separation of the software that generates messages, the system that stores them, and the software that reports and analyzes them. Each message is labeled with a facility code, indicating the software type generating the message, and assigned […]
PL/SQL
ETL using Oracle DBMS_HS_PASSTHROUGH and SQL Server
While I prefer a “loosely coupled architecture” for replication between Oracle and SQL Server, sometimes a direct (database) link cannot be avoided. By using DBMS_HS_PASSTHROUGH for data extraction the 2 other ETL processes (transformation and load) can be configured and administered with more flexibility, providing an almost acceptable level of […]
Golden Gate 12c and DIY Sequence Replication with PL/SQL
Recently, while migrating AIX 11gR2 Databases to Oracle Linux 12cR1 on an ODA X5-2, our setup of Sequence Replication by Oracle Golden Gate appeared to be faulty. The target side sequences were not automatically incremented. The problem came to light during the migration of acceptance databases, and under some time […]
Dump Oracle data into a delimited ascii file with PL/SQL
This is how I dump data from an Oracle Database (tested on 8i,9i,10g,11g,12c) to a delimited ascii file: Next to the query and the generated filename the Dump_Delimited function takes another 6 parameters, each one with a default value. Check out the PL/SQL, and BTW… the basics for this code […]
DIY Parallelization with Oracle DBMS_DATAPUMP
Oracle dbms_datapump provides a parallel option for exports and imports, but some objects cannot be processed in this mode. In a migration project from AIX 11gR2 to ODA X5-2 ( OL 5.9 ) 12c that included an initial load for Golden Gate, I had to deal with one of those […]
Oracle 12c STIG Password Generator in PL/SQL
Creating or modifying an Oracle Database user password can be done by using any standard password generator you can find on the WEB. But I wanted a password to comply to “ora12c_strong_verify_function”, and this isn’t as easy to generate as you might expect. Though most generators provide options to include […]
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, […]
Virtual Private Database…
Some time ago I was asked to assist in fixing or at least finding the cause of a performance problem. The application ran fine until the Virtual Private Database (VPD) policy was applied. Oracle claims there should be near zero impact on your application when you implement VPD, then how […]
Continuous Delivery and the Oracle database (III)
In this series of blogs about Continuous Delivery and the Oracle database, I describe how to automate deployments (installations). In the previous two Blogs I have described the tools and techniques used to create and install migration scripts. In this Blog I will describe the ‘(un)happy flow’ for a database […]
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. […]
Continuous Delivery and the Oracle database (II)
In the previous Blog I have described how to implement CD for an Oracle database by using migration scripts. In this Blog I will describe how to create migration scripts (automagically). DML scripts This is the more simple case because you need them less often. There are various data compare […]
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 some spare time, so I tried my own JSON generator, build around 3 nested Oracle types, on the examples he used. […]
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 used a solution as described in this post by Carsten Czarski. That post of Scott reminded me of a post by […]
How-to set the current database schema of an application using a global context
Question: We have customers who want to work with different database schemas and we want to determine dynamically – depending on a choice during or after login – which database schema the application in a given session should use. Is it possible to set this up in the database? Answer: […]
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 […]
PL/SQL vs SQL
There is a ‘rule’, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote […]
Using Table Functions
Overview of table functions Table functions are functions that produce a collection or rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. The […]
Typical
After a presentation by Lucas Jellema I decided to try something with types in Oracle. One of the issues posed in this presentation was that the type cannot self-reference. Neither direct nor indirect. A table like the emp table cannot be expressed as an object type. The table has […]
ADF Performance Tuning: Improve Your Oracle ADF App Response Time by as Much as 70 Percent
Performance needs to be ingrained in your application – it cannot be added in during the last stages of development. In this video I discuss how you can optimize the performance of your Oracle ADF Fusion application, diagnose and solve typical performance problems, and build an efficient, responsive, scalable ADF […]
ADF performance tuning: Overview Video published on the ADF Performance Monitor
A good performance is the key to the success of a web application. Oracle ADF applications are no exception to this rule. ADF performance tuning can be time intensive, costly and quite a challenge when performance issues require developers to delve deep into the inner workings of the ADF framework. […]
Easy setup of the query_partition_clause of row_number() for dedup(lication)
Finding and getting rid of duplicate records in a table is easy…. The row_number() over ( <query_partition_clause> <order_by_clause> ) analytic function is helpful in determining not only the number of duplicate records, but also in identifying them. Setting up the partition clause though with each and every attribute that is […]
The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
Oracle OpenWorld is a monster event – 10Ks of attendees, thousands of sessions and 100Ks of private conversations that all help convey and define the message about Oracle’s strategy and the roadmap for its close to 4000 thousand products. Concurrent with OOW is the JavaOne conference that – at a […]
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.
OOW13: summarizing one week and 2000 sessions in 3 hours and a bit – the yearly AMIS OOW Review session – 10th October
On Thursday 10th of October, the 12 man strong AMIS delegation at Oracle OpenWorld and JavaOne 2013 will present its findings in a 3 hour session at AMIS HQ in Nieuwegein, The Netherlands. You are welcome to attend this free session (from 16.30 on, food provided). Please register here: http://www.amis.nl/nl-NL/evenementen/technologie-evenementen/oow-review. […]
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 […]
Het Oracle OpenWorld Preview Evenement (5 september 2013) – 15 sprekers & sessies
Vanaf 22 september vindt in San Francisco de Oracle OpenWorld conferentie plaats: hét evenement waar Oracle haar productstrategie uit de doeken doet en waar Oracle specialisten van over de hele wereld ervaringen uitwisselen. Tegelijk met Oracle OpenWorld wordt ook de JavaOne conferentie georganiseerd, het trefpunt voor de wereldwijde Java gemeenschap. […]
Oracle Database SQL – Recursive Subquery to inspect events in football matches – find the MVP
In a recent post (https://technology.amis.nl/2013/07/24/oracle-database-12c-find-most-valuable-player-using-match_recognize-in-sql/) I described how we can use the new Oracle Database 12c MATCH_RECOGNIZE operator to spot patterns in records and derive results from those patterns. I used the MATCH_RECOGNIZE to find the most valuable player in a football team (US readers: I mean the sports that […]
Oracle Database 12c: Find most valuable player using MATCH_RECOGNIZE in SQL
One of the very nice new features in SQL in Oracle Database 12c is the MATCH_RECOGNIZE operator. MATCH_RECOGNIZE adds a facility for spotting patterns in records. This allows us to locate records that are part of some kind of pattern relative to other records. It does sound similar to what […]
Oracle Database 12c: Flashback Moving Forward
Flashback started out as a feature in Oracle Database 9i. Although to be honest it is just the opening up of a mechanism that has been at the core of the Oracle Database from very early on: the ability to have concurrent sessions and transactions and allow transaction rollback and […]
Oracle Database 12c: Pattern Matching through MATCH_RECOGNIZE in SQL
Oracle 8i (8.1.6 if I remember correctly) started with the introduction of a that wonderful new phenomenon in SQL: Analytical Functions. Functions that basically allow the result for one row in the result set to be calculated using the values in other rows. This allowed to look forward (lag) and […]
Oracle Database 12c: joining and outer joining with collections
Joining is a key ingredient in most SQL queries. Using collections (aka nested tables) that are produced on the fly inside the SQL query or that are returned by a PL/SQL function that is invoked from a TABLE operator in the query is a powerful weapon as well. Oracle Database […]
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 […]
Oracle Database 12c: In Line PL/SQL Functions in SQL queries
At long last it’s here: Oracle Database 12cR1. The download of this fresh software package is hardly done. It is time to start sharing some of our early experiences with this major milestone in the history of Oracle’s flagship product. The importance of the 12c release lies not necessarily on […]
JDeveloper code templates
When you write a lot of code, no matter what sophisticated framework you use, there are pieces of code that you end up writing over and over again (especially when using Java). Writing logging code in Java is a good example. A common pattern is to initialize a static logger […]
Hotsos 2013 – Presentation material “Creating Structure in Unstructured Data”
Hereby, for those who want another look or for people to share, my presentation content “Creating Structure in Unstructured Data” given during the Hotsos 2013 Symposium on Monday morning. HTH Marco Hotsos 2013 – Creating Structure in Unstructured Data from Marco Gralike
Oracle SQL: Using subquery factoring in an INSERT statement
I use subquery factoring a lot, to make my SQL queries easier to write. This basically means that most of my queries these days start with WITH rather than SELECT. (this feature was introduced in the 9i release of the Oracle Database). Today I was wondering whether I can also […]
Using TRUNC in SQL to get the first date in a period
While working on a Web application that required some page to report data aggregated on several levels of time period – by month, quarter or year – I ran into a very handy way to use the TRUNC function. I was not aware of this usage until now – and […]
Hotsos Revisited 2013
Van 3 tot en met 7 maart vindt in Irving, Texas, het internationale Oracle performance Hotsos Symposium plaats. Dit jaar belooft het symposium een garantstelling voor inhoudelijk hoogstaande presentaties en discussies, want naast presentaties van Tom Kyte, Cary Millsap, Maria Colgan en Steven Feuerstein over performance, worden er ook onderwerpen […]
OTN Yathra 2013 – The six city Oracle tour of India
The Oracle ACE directors and Java champions are organizing an evangelist event called ‘OTNYathra 2013’ between 15th & 27th February 2013. The event is sponsored by the Oracle Technology Network. I will be one of the speakers on this tour – presenting two or three presentations at each of the […]
Basisregistraties Adressen en Gebouwen – Het importeren van Kadaster BAG data in een Oracle Database
Vorig jaar heb ik behoorlijk wat vragen gekregen over of er een tool was, een methodiek, om BAG data van het Nederlandse Kadaster in een Oracle database te krijgen voor allerlei doeleinden. Basisregistraties Adressen en Gebouwen (BAG) data wordt onder andere uitgeleverd door het Kadaster in XML bestanden waarin alle […]