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.
[click to continue…]
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.
[click to continue…]
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.
[click to continue…]
Some solution requirements are best addressed with Java code in the Oracle database. This article shows you use cases where Java methods in Oracle are useful, how Oracle lets you store Java methods and how they can be called from SQL or PL/SQL.
[click to continue…]
Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.
This can be achieved by locking the row in Oracle before update. Here’s how.
[click to continue…]
One way to get a smaller CLOB from another CLOB is to follow the extract CLOB from JSON example: use DBMS_LOB INSTR/SUBSTR functions to obtain the CLOB fragment of interest. We could look at the same problem from another angle: delete the CLOB fragments *not* of interest, retaining only what we want, with DBMS_LOB.FRAGMENT_DELETE.
[click to continue…]
"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.
[click to continue…]
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?
[click to continue…]
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.
[click to continue…]
