APPS-ORACLE.RU

In one of the previous posts I showed How even empty trigger increases redo generation, but running the test from that post, I have found that this behaviour a bit changed on 12.2:
In my old test case, values of column A were equal to values of B, [...]

On versions 11.2.0.2 – 11.2.0.4 Oracle uses objects' statistics to make direct path reads decision (of course, if “_direct_read_decision_statistics_driven” haven't changed it to “false”), and we can force serial direct reads on statement level using sql profiles with hints INDEX_STATS/TABLES_STATS, but since at least 12.1.0.2 this decision ignores statistics.

Btw, thanks [...]

JSON is a simple data interchange format, an alternative to XML that's gaining wider favor by the day especially for big data storage and REST web services. With release 12c, Oracle has introduced JSON support too – handy new features for storage and retrieval of JSON data.

Here's a [...]

Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.

What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not [...]

Unfortunately associative arrays still require more “coding”:
we still can't use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don't have convinient iterators and even function to get all keys…
That's why I want to show my templates for such things like iterator [...]

I see quite often when developers ask questions about connected components:

I've troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ersand instead of :colon! I have never seen this before and I couldn't find anything about this in documentation…
Unfortunately SQL*Plus doesn't support ampersand yet, even if you disable define (“set define [...]

Typical scenario: in a project's design phase, procedureA is meant to be placed in packageX. During implementation, packages are refactored: packageX gets split into packageY and packageZ. All goes well – the application gets deployed and is running merrily – till a change request comes in.

A new developer refers [...]

Proxy user authentication is a powerful feature in Oracle that lets one database user (proxy user) connect to the database "on behalf of" another user (client user).

With proxy login, the proxy user can act as if it is the client user with all of the client user's access rights, without [...]

Here's a prototype for using the SQL/XML function XMLTABLE to map XML data into relational rows and columns.

This solution uses the standard EMP table – the same can be extended to work with any XMLTYPE-relational mapping.

An application receives employee data [...]

A comparison between static SQL vs dynamic SQL shows us pretty clearly that, if there exists a choice, we are better off choosing static SQL.

So, when should we use dynamic SQL?

Oracle documentation tells us that we need dynamic SQL to run:

I've reviewed this book recently, and I highly recommend it as it has almost all that needed to become strong Oracle developer. You can check at least the table of contents:

ORACLE SQL & PL/SQL Golden Diary: by Asim Chowdhury
New Book Demystifies Complex Cross-Version Oracle Problem Solving

Compiled [...]

Some questions do not have definitive answers. "Is a full table scan bad? Should this design be denormalized? Will partitioning this table help?" The answers vary widely depending on the specifics on the problem.

Fortunately, "Should I use static SQL or dynamic SQL?" is not one of those questions.

You can come [...]

SQLCODE and SQLERRM are Oracle's built-in error reporting functions in PL/SQL.

When an error occurs in PL/SQL at runtime:

SQLCODE returns the number of the last encountered error.

SQLERRM returns the message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current [...]

I recently thought of using Java's built-in BigInteger.gcd() method inside a PL/SQL wrapper to calculate GCD in SQLs.

And so I wrote the Java code and ran it on Oracle XE.

Things didn't go as planned.

After the post on models for storing XML data in Oracle and examples of binary XML storage, here's a detailed look at the rigorous and performant structured XML storage or object-relational storage.

Object-relational storage of XML data is based on "shredding" the XML content into a set of SQL [...]

The previous post on storing XML data in Oracle gave an overview of binary XML storage: compact, flexible, compatible with XML data with or without associated XML schema.

This post shows working examples of binary XML storage

1.1. without XML Schema
1.2 with XML Schema

Oracle provides an abstract SQL data type called XMLType for storing XML data in the database. You can create an XMLType table, or an XMLType column in a relational table, to persist XML data.

Different storage models are available in Oracle to best fit the nature of the XML data [...]

Oracle has built-in functions to convert relational data into XML format easily. These functions comes under the umbrella of SQL/XML, a specification that supports the mapping and manipulation of XML from SQL.

This post shows you how to generate XML from relational data using Oracle SQL/XML functions as building blocks.

Let's [...]

Oracle's UTL_MATCH package contains functions to perform fuzzy matching between two strings based on one of these algorithms:

Let's understand the algorithms and see UTL_MATCH subprograms in action.

Note: It is not necessary to understand the nitty-gritties of the algorithms to use UTL_MATCH. The deep dive is for [...]

In a recent project, a "shadow" table had to be compared periodically with its main source table to identify the differences between the two tables.

The nature of differences fell into one of these buckets: