APPS-ORACLE.RU

When you have an enormous load of data to copy/merge from one table to another, you are probably concerned about:

Oracle PL/SQL gives you the ability to perform DML operations in bulk instead of via the regular row-by-row FOR loop. This article shows you how to use bulk DML and handle exceptions along the way.

You [...]

Oracle PL/SQL gives you the ability to perform DML operations in bulk instead of via the regular row-by-row FOR loop. This article shows you how to use bulk DML and handle exceptions along the way.

You [...]

In the article on SQL/JSON query functions we saw how JSON_TABLE converts JSON data to relational form. This article further explores various JSON_TABLE options for parsing and handling errors in JSON data.

For this demo consider JSON data [...]

In the article on SQL/JSON query functions we saw how JSON_TABLE converts JSON data to relational form. This article further explores various JSON_TABLE options for parsing and handling errors in JSON data.

For this demo consider JSON data [...]

A unique constraint and a unique index in Oracle, on the face of it, look very alike. Both enforce uniqueness, and the unique index seems to piggyback on the constraint when the constraint is created or dropped.

You might [...]

A unique constraint and a unique index in Oracle, on the face of it, look very alike. Both enforce uniqueness, and the unique index seems to piggyback on the constraint when the constraint is created or dropped.

You might [...]

Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.

This can be achieved by locking the row in Oracle before [...]

Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.

This can be achieved by locking the row in Oracle before [...]

One way to get a smaller CLOB from another CLOB is to follow the extract CLOB from JSON example: use DBMS_LOB INSTR/SUBSTR functions to obtain the CLOB fragment of interest. We could look at the same problem from another angle: [...]

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it's much better to create another pipelined function [...]

"Wheels within wheels", as Monty Bodkin would say. Extracting a very long string or CLOB from a JSON CLOB (very long => larger than max_string_size of 32767), in a pre-12.2 Oracle database, turned out to be more complex than it appeared at first sight.

This case study shows how to [...]

When “serveroutput” is enabled, SQL*Plus executes “BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;” after each command.
That's why I don't like when it is always enabled: it adds extra calls and round-trips and it is inconvenient when I want to get a plan of the last executed query:

This interesting question was posted on our russian forum yesterday:

We have a huge PL/SQL package and this simple function returns wrong result when it's located at the end of package body:

JSON conditionals check for the existence [...]

The last article talked about how to store JSON in the database. This article shows you how to retrieve it meaningfully using various query approaches in Oracle 12c.

For the demo, we'll use the same old CUSTOMER table with JSON metadata.

The table:

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 [...]

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:

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 [...]