PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension for SQL. PL/SQL includes features such as conditional statements, loop processing and exception handling.

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

Extract Very Long String or CLOB from JSON [pre 12.2]

Extract CLOB from CLOB

"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.

This case study shows how to extract CLOB data from JSON CLOB, in an Oracle database with no/limited JSON parsing features.

Read more

Convert Relational Data to JSON in Oracle: PL/JSON Method

Convert Relational Data to JSON
In the JSON series so far, we’ve talked about how to store JSON data in Oracle and apply JSON conditional checks, and how to query JSON data and convert it to relational form. What if you want the opposite i.e. to convert relational data to JSON form? That’s doable too – let’s see how.

Convert Relational Data to JSON

What you have: a relational table in Oracle.

What you want: data extracted from said relational table in JSON format.

How do you do it?

Read more