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 impossible. Try DBMS_REDEFINITION: here’s how.
“In which package is this procedure placed?”
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 to the design documentation to understand the change request.
The design documentation should have been updated to reflect the new package structure — but it isn’t.
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).
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.
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?
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.
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 value of SQLCODE.
I recently thought of using a Java method in PL/SQL. And so I wrote the Java stored procedure 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.
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