It is quite normal for Oracle database objects to become INVALID, especially when a dependency chain is broken. This article takes a closer look at:
{ 0 comments }
Consider a PL/SQL stored procedure that handles a heavy transaction. The procedure is extremely slow – when executed from a UI, the application hangs for minutes. On analysis it is found that the procedure is performing a complex series of steps, a portion of which are non-critical and need not hold up the entire transaction. In other words, it would be acceptable if:
Oracle PL/SQL helps us achieve these objectives with asynchronous processing using Oracle job scheduler DBMS_SCHEDULER. Here’s a demo to show you how.
{ 3 comments }
When we need to enforce uniqueness on a combination of table columns *only if* a certain condition is true, Oracle helps us implement this using a function-based index.
{ 2 comments }
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.
{ 0 comments }
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 look at how JSON can be persisted in Oracle, and uses cases where doing so would be apt.
{ 0 comments }
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.
{ 4 comments }
“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.
{ 0 comments }
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).
{ 1 comment }
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?
{ 0 comments }
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.
{ 0 comments }
Oratable is for Oracle newbies. You'll find here tips, tricks and tutorials about cool things you can do with the Oracle database.
