Logging Triggers

Oracle Logging Trigger Results

This article demonstrates how you can write log files from triggers on different tables to the same logging table. This approach leverages Oracle’s object types and column substitutability features. It also eliminates the requirement to create a unique logging table for each logging trigger. The trick to accomplishing this requires mastering two skills.

The first skill requires you to learn how to create user-defined types (UDTs) and subtypes. The UDT stores (more...)

Critical Triggers

Oracle Critical and Non-critical Triggers

This article demonstrates how you can write critical and non-critical row-level triggers. You may ask yourself, what are critical and non-critical triggers? That’s a great question. A critical trigger stops processing and raises an exception within the scope of an Application Programming Interface (API). An API is typically a series of end-user forms that help you solve business problems. A non-critical trigger either allows users to perform undesired behavior or (more...)

Oracle Trigger Basics

Oracle Trigger Basics

Once you master the basics of inserting, updating, and deleting data from the database, you typically learn about database triggers. Database triggers are coding elements that let you manage events beyond the limit of database constraints.

Before you can appreciate the power of database triggers, you need to understand what database constraints can and can’t do. Then, together we’ll explore how you can implement database triggers.
Database constraints let you manage events. (more...)

Oracle VM Server x86: How to get a redundant network for the heartbeat

A while ago i played around with Oracle VM Manager

Types & Column Substitutability

Object Types and Column Substitutability

This article shows you how to use extend parent (or superclass) objects. You extend parent classes when you implement specialized behaviors (or methods) in subtypes. That’s because SQL statements can’t work with specialized methods when a table’s column stores subclasses in a superclass column type.

Substitutability is the process of storing subtypes in a super type column. It is a powerful feature of the Oracle database. The “type evolution” feature (more...)

Types & Subtypes

Object Types and Subtypes

This article teaches you how to use subtypes or subclasses. You can define an object type with or without dependencies. Object types can have two types of dependencies. The simplest case occurs when you define an object attribute with an object type instead of a data type. The more complex case occurs when you define an object subtype because it inherits the behavior of the base object type. The base object (more...)

Type Getters & Setters

Object Types with Getters and Setters

This article is for you when you know the basics about how you work Oracle’s object types. It teaches you how to write effective getters, setters, comparators, and static methods. Please read my Object Types & Bodies Basic article if you’re not sure how to work with object types.

Getters access an object instance and return values from an instance variable. Along with getters, you have setters. Setters let (more...)

Type & Body Basics

Object Types and Bodies Basics

Oracle Database 10g gave us a new way to write PL/SQL – object types. Object types are different from standard PL/SQL functions, procedures, and packages. While you can pin packages in memory, object types go one step further. You can instantiate them, which means you can start them, assign values to their variables, and put them into your PGA’s memory. Object types provide you with new challenges writing programs (more...)

UKOUG Oracle Conferences – why bother?

The excellent DOAG Oracle conference is currently taking place in Nuremberg in Germany.

I’ve been thinking about the UKOUG Conference due to take place in Liverpool on 3rd-5th December 2018. About 2 weeks away.

UK Oracle User Group Tech18 conference image

Why would you bother paying to attend these conferences? Why should I, or my company, use our money to attend? Oracle provide free conferences and 1-day events around the world talking about the Oracle product set. Oracle Partners also provide free (more...)

ALTER TABLE ADD column with DEFAULT value and Virtual Private Database

I recently came over a real strange behaviour with ALTER TABLE ADD column with DEFAULT value on one of my customers.

They are using a Software Product based on WebLogic and Oracle Database, from time to time we receive some new version of this Software Product which includes Database related changes, e.g. add new tables, add columns to an existing table and so on.

Within the latest Software Update, we could observe that the (more...)

Table order

Over the last few days I’ve highlighted on Twitter a couple of older posts showing show a change in the order that tables appear in the from clause could affect the execution plan of a query. In one case the note was purely theoretical describing a feature of the way the optimizer works with simple query blocks, in the other case the note was about an anomaly with table elimination that could appear with both (more...)

Bulgarian Oracle User Group (BGOUG) 2018 : The Journey Home

It was a 03:00 start, which is never a good thing. I got down to reception to meet my fellow travellers and we started on our trip to the airport. As we walked out of the hotel we were greeted by a lite scattering of snow. It was clearly visible on some of the mountains the day before, but it was quite a surprise to see it here, especially as I left my balcony door (more...)

Polymorphic Table Functions

I have been working on a presentation on Polymorphic Table Functions. During this time I was looking for a real use case for Polymorphic Table Functions. I came up with an example which is not very useful in real life, but very useful to explain the technique.
At my current job I came across a piece of code that I had to copy and adjust to fit the needs for that specific case. The idea (more...)

opatch investigations

This blogpost is about opatch and how to obtain information about the current oracle home(s), and how to obtain information about the patches to be applied.

Patches that can be applied using opatch are provided by oracle as zip files which have the following naming convention:
p[patchnumber]_[baseversion]_[platform]-[architecture].zip. The patch normally contains an XML file called ‘PatchSearch.xml’ and a directory with the patch number. Inside the patch number directory there is a README.txt (more...)

opatch versions

This blogpost is about oracle’s patching tool for the database and grid infrastructure: opatch. I personally have a love/hate relationship with opatch. In essence, opatch automates a lot of things that would be very error prone if it were to be done by hand, which is a good thing. However, there are a lot of things surrounding opatch that I despise. An example of that is the version numbering of opatch itself.

Versions and more (more...)

Preprocessing External Tables

A question that comes up now and again is there a way in Oracle Database 11g Express Edition to mimic some behavior in the Oracle Standard or Enterprise editions. Many of these questions arise because developers want to migrate a behavior they’ve implemented in Java to the Express Edition. Sometimes the answer is no but many times the answer is yes. The yes answers come with a how.

This article answers the question: “How (more...)

Oracle Digital Assistant: Hooking up your chatbot to twitter.

In my previous post I described how to use node-red to interact with twitter. That post was based on some initial research which was eventually used as a basis for the post that you are reading now. For one of our projects I had to setup an Oracle Chatbot on twitter. Now as you might know, Oracle Digital Assistant supports many channels, such as web, facebook, android and iOS, but twitter is not

Oracle SaaS – Business Applications in the Cloud – as of Oracle OpenWorld 2018

imageOracle has two main pillars on which its (future) business rests – dixit Larry Ellison: Oracle Cloud Infrastructure (including the Autonomous Database) and SaaS. In this article, I will relate some of the key announcement from Oracle regarding the business applications.

It is useful to realize that Oracle’s portfolio of business applications can be regarded in various ways.

There are horizontal applications – with generic functionality that is applicable to more or less every organization (more...)

Design considerations to replicate Data Lake site for Availability

Why replicate a data lake? Building an enterprise data lake demands heavy weightlifting in terms of cost, planning, technical design, architecture, and operational excellence. Building another infrastructure as a standby will shoot up both capex and opex. Therefore, before proceeding to the planning phase, one must have a strong justification and clear objective behind setting … Continue reading "Design considerations to replicate Data Lake site for Availability"

Oracle Database 18c: Installation via rpm (without runInstaller.sh)

Three months after writing the post Where to find the oracle-database-ee-18c-1.0-1.x86_64.rpm package? Oracle released the rpms on their download page:


https://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html

So let's give it a try.


First thing is to install the prerequisites:
# yum install oracle-database-preinstall-18c


...
=================================================================================================================================================
Package Arch Version Paketquelle Größe
=================================================================================================================================================
Installieren:
oracle-database-preinstall-18c x86_64 1.0-1.el7 ol7_latest 18 k
Als Abhängigkeiten installiert:
bc x86_64 1.06.95-13.el7 ol7_latest 114 k
bind-libs x86_64 32:9. (more...)