DETERMINISTIC Functions in Oracle

DETERMINISTIC functions

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.

Read more

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

Keep Response Body non-200 HTTP Status in PLSQL

When UTL_HTTP web service calls nicely return 2xx status codes, all is well with the world. Things get tricky when a web service call encounters an error and sends back a non-2xx response. How should the calling PL/SQL code handle this scenario? Can we read the response body from PL/SQL in case of error?

Here’s an overview of web service error handling options available in PL/SQL UTL_HTTP, and how to make use of SET_RESPONSE_ERROR_CHECK to keep the response body with non-200 responses.

Read more

Running Procedures Asynchronously with Oracle Job Scheduler

Running Procedures Asynchronously with Oracle Job Scheduler

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:

  • some of the steps are run asynchronously while a slimmer main transaction completes
  • failures (if any) in the asynchronous steps do not cause a failure in the main transaction

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.

Read more

Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination Queries in Oracle OFFSET FETCH

Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows (say N): if N is 20, then the first page shows 1-20 rows, a “Next” navigation moves control to the second page with 21-40 rows, and so on.

This article demonstrates how to write queries to achieve this style of pagination in Oracle.

Read more

REGEX to Split a Comma-Separated String into Rows

REGEX to Split String to Rows

A typical scenario that involves splitting a comma-separated string into rows:

  • in a UI-driven query, the user would enter a list of IDs into an input box, and
  • the application should retrieve details for the input list of IDs

Search Input boxThe count of IDs in the list can vary — the user may enter a single value or multiple, and the underlying SQL should fetch the details for all of them.

This sounds simple enough on the face of it: bind a parameter to a comma-separated list of values such as ‘A1,A2,A4’ and then look for corresponding rows using SQL for those IDs.

There’s a catch though.

Read more