DETERMINISTIC functions in Oracle

A PL/SQL function in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.

A little elaboration is in order here.

1. …will always produces the same output for a given input

Let’s see this with an example: a function get_primary_phone takes as input customer_id and returns the customer’s primary phone. Internally, the function executes SQL on a customer contact table, ranks and filters the result to get the customer’s primary phone number.

Continue Reading …

{ 2 comments }

INVALID Objects in Oracle

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:

  • typical reasons why Oracle objects become INVALID
  • how to identify INVALID objects in Oracle
  • how to convert INVALID objects to VALID

Continue Reading …

{ 0 comments }

Thumbnail image for How to Keep the Response Body with Non-2xx Responses in UTL_HTTP

How to Keep the Response Body with Non-2xx Responses in UTL_HTTP

Web service error handling options in PL/SQL UTL_HTTP, and how to make use of SET_RESPONSE_ERROR_CHECK to keep the response body with non-2xx responses.

Read the Full Article →
Thumbnail image for Running Procedures Asynchronously with Oracle Job Scheduler

Running Procedures Asynchronously with Oracle Job Scheduler

Oracle PL/SQL provides the ability to run procedures asynchronously using Oracle job scheduler DBMS_SCHEDULER. Here’s how to create and run such jobs.

Read the Full Article →
Thumbnail image for Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination Queries in Oracle: Analytics, Row Limiting Clause

How to implement pagination queries in Oracle, using (1) analytic functions (2) Oracle’s row limiting clause to fetch N rows from a given offset.

Read the Full Article →
Thumbnail image for Conditional Unique Index on Multiple Columns

Conditional Unique Index on Multiple Columns

Oracle’s function-based indexes can be used to build conditional uniqueness on table columns. Here’s a working example of a conditional unique index on multiple columns.

Read the Full Article →
Thumbnail image for Java Stored Procedures in Oracle

Java Stored Procedures in Oracle

On why you would want Java stored procedures in Oracle, and how to store and invoke these stored procedures via call specifications.

Read the Full Article →
Thumbnail image for REGEX to Split a Comma-Separated String into Rows

REGEX to Split a Comma-Separated String into Rows

How to split a comma-separated string into rows in Oracle SQL, using regex function REGEX_SUBSTR and recursion via CONNECT BY.

Read the Full Article →
Thumbnail image for How to Install and Configure SQL*Plus Instant Client on Windows

How to Install and Configure SQL*Plus Instant Client on Windows

Easy guide on how to install and configure SQL*Plus Instant Client on Windows, and how to use it to connect to remote databases,

Read the Full Article →
Thumbnail image for DML Error Logging: Supersized DML Operations Made Easy

DML Error Logging: Supersized DML Operations Made Easy

Oracle DML error logging lets you manage very large DML operations with high performance and robust error handling, with the addition of a LOG ERRORS INTO clause with the DML statement. An explanation and working example.

Read the Full Article →