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.

If the customer’s contact information changes in the table, the value returned by get_primary_phone(customer_id) will not produce the same output for the same input.

This implies that get_primary_phone(customer_id) cannot be deterministic.

In general, any function whose output depends on the contents of variables / conditions / tables / datasets not passed as IN parameters is non-deterministic.

So,
SYSDATE is non-deterministic – the current date and time will change depending on when SYSDATE is called, but
LAST_DAY(date) is deterministic – for a given input date, the last date of the month will be unchanged no matter when or how many times LAST_DAY(date) is called.

Another eligible deterministic function: Java arithmetic calculation.

2. …will have no side effects

This means that the function produces no other changes in local static variables, non-local variables, reference arguments, and performs no DML operations on other tables.

What’s the use of qualifying a function as DETERMINISTIC?

You may well ask. PL/SQL functions work fine without any of this DETERMINISTIC business: why, then, should we add this clause?

Here are a couple of reasons.

• The DETERMINISTIC clause is a shorthand to document a function’s special properties – that its output depends wholly on its inputs and that it produces no side effects

• Making a function DETERMINISTIC has a performance benefit – if the function is called multiple times within the scope of a single server call (e.g. execution of a PL/SQL block) with the same parameter values, the optimizer can choose to use the previously calculated result instead of invoking the function repeatedly.

More of the performance benefit in a demo below.

DETERMINISTIC functions: Syntax

Just plop in the word “DETERMINISTIC” after specifying the return type of the function.

Example: deterministic function is_number(p_value) – this checks if a string input is a valid number or not.

-- Function that tests if the input is a number
-- Returns 'Y' if number, else 'N'
create or replace function is_number 
(p_value in varchar2) 
return char 
deterministic 
is
   p_num number;
begin
   p_num := to_number(p_value);
   return 'Y';
exception
   when value_error then
      return 'N';
end;
/

DETERMINISTIC functions: Optimization in action

We’ll see how Oracle leverages a function’s deterministic property to avoid executing it multiple times for the same input.

For our tests, we will query a table EMPLOYEE (EMP_ID, EMP_NAME, DEPT) with 20 rows.

[Tests run on Oracle 11.2.0.2]

SQL> select * from employee;

EMP_ID EMP_NAME             DEPT
------ -------------------- ----------
   100 Steven King          515
   101 Neena Kochhar        515
   102 Lex De Haan          515
   103 Alexander Hunold     590
   104 Bruce Ernst          590
   105 David Austin         A90
   106 Valli Pataballa      590
   107 Diana Lorentz        590
   108 Nancy Greenberg      515
   109 Daniel Faviet        515
   110 John Chen            515
   111 Ismael Sciarra       515
   112 Jose Manuel Urman    A1A
   113 Luis Popp            515
   114 Den Raphaely         515
   115 Alexander Khoo       515
   116 Shelli Baida         515
   117 Sigal Tobias         515
   118 Guy Himuro           515
   119 Karen Colmenares     A1A

20 rows selected.

Column DEPT in this table is a string which may or may not be a number: we will call the function is_number(dept) defined above to ascertain if DEPT is a number or not.

SQL> select emp_id
  2       , emp_name
  3       , dept
  4       , is_number(dept) is_dept_numeric
  5  from employee;

EMP_ID EMP_NAME             DEPT       IS_DEPT_NUMERIC
------ -------------------- ---------- --------------------
   100 Steven King          515        Y
   101 Neena Kochhar        515        Y
   102 Lex De Haan          515        Y
   103 Alexander Hunold     590        Y
   104 Bruce Ernst          590        Y
   105 David Austin         A90        N
   106 Valli Pataballa      590        Y
   107 Diana Lorentz        590        Y
   108 Nancy Greenberg      515        Y
   109 Daniel Faviet        515        Y
   110 John Chen            515        Y
   111 Ismael Sciarra       515        Y
   112 Jose Manuel Urman    A1A        N
   113 Luis Popp            515        Y
   114 Den Raphaely         515        Y
   115 Alexander Khoo       515        Y
   116 Shelli Baida         515        Y
   117 Sigal Tobias         515        Y
   118 Guy Himuro           515        Y
   119 Karen Colmenares     A1A        N

20 rows selected.

Note that the same value of DEPT is repeated across several rows in the table: Oracle need not reevaluate the Y/N return for an input if it has done so already in the SQL for another row.

Without a DETERMINISTIC clause, Oracle will not be aware that it can avoid reevaluation, and proceed to execute is_number(dept) N times for N EMPLOYEE rows.

Let’s compare the SQL behavior before and after making the function DETERMINISTIC.

Before making the function DETERMINISTIC:

We will add a dbms_output.put_line to is_number(p_value), to check how many times the function gets invoked in our SQL.

Non-deterministic function:

-- Function that tests if the input is a number
-- Returns 'Y' if number, else 'N'
create or replace function is_number 
(p_value in varchar2) 
return char 
is
   p_num number;
begin
   dbms_output.put_line('is_number ('||p_value||') called');
   p_num := to_number(p_value);
   return 'Y';
exception
   when value_error then
      return 'N';
end;
/

Compile the function:

SQL> -- Function that tests if the input is a number
SQL> -- Returns 'Y' if number, else 'N'
SQL> -- non-deterministic
SQL> create or replace function is_number
  2  (p_value in varchar2)
  3  return char
  4  is
  5     p_num number;
  6  begin
  7     dbms_output.put_line('is_number ('||p_value||') called');
  8     p_num := to_number(p_value);
  9     return 'Y';
 10  exception
 11     when value_error then
 12        return 'N';
 13  end;
 14  /

Function created.

Now, run the SQL:

SQL> -- SQL result when the called function
SQL> -- is non-deterministic
SQL> select emp_id
  2       , emp_name
  3       , dept
  4       , is_number(dept) is_dept_numeric
  5  from employee;

EMP_ID EMP_NAME             DEPT       IS_DEPT_NUMERIC
------ -------------------- ---------- --------------------
   100 Steven King          515        Y
   101 Neena Kochhar        515        Y
   102 Lex De Haan          515        Y
   103 Alexander Hunold     590        Y
   104 Bruce Ernst          590        Y
   105 David Austin         A90        N
   106 Valli Pataballa      590        Y
   107 Diana Lorentz        590        Y
   108 Nancy Greenberg      515        Y
   109 Daniel Faviet        515        Y
   110 John Chen            515        Y
   111 Ismael Sciarra       515        Y
   112 Jose Manuel Urman    A1A        N
   113 Luis Popp            515        Y
   114 Den Raphaely         515        Y
   115 Alexander Khoo       515        Y
   116 Shelli Baida         515        Y
   117 Sigal Tobias         515        Y
   118 Guy Himuro           515        Y
   119 Karen Colmenares     A1A        N

20 rows selected.

is_number (515) called
is_number (515) called
is_number (515) called
is_number (590) called
is_number (590) called
is_number (A90) called
is_number (590) called
is_number (590) called
is_number (515) called
is_number (515) called
is_number (515) called
is_number (515) called
is_number (A1A) called
is_number (515) called
is_number (515) called
is_number (515) called
is_number (515) called
is_number (515) called
is_number (515) called
is_number (A1A) called

You can see that the non-deterministic function was called once per row i.e. 20 times for 20 rows.

After making the function DETERMINISTIC:

We will make exactly one change to the is_number(p_value) code: add the clause DETERMINISTIC.

Deterministic function:

-- Function that tests if the input is a number
-- Returns 'Y' if number, else 'N'
-- deterministic
create or replace function is_number 
(p_value in varchar2) 
return char 
deterministic
is
   p_num number;
begin
   dbms_output.put_line('is_number ('||p_value||') called');
   p_num := to_number(p_value);
   return 'Y';
exception
   when value_error then
      return 'N';
end;
/

Compile the function:

SQL> -- Function that tests if the input is a number
SQL> -- Returns 'Y' if number, else 'N'
SQL> -- determinitic
SQL> create or replace function is_number
  2  (p_value in varchar2)
  3  return char
  4  deterministic
  5  is
  6     p_num number;
  7  begin
  8     dbms_output.put_line('is_number ('||p_value||') called');
  9     p_num := to_number(p_value);
 10     return 'Y';
 11  exception
 12     when value_error then
 13        return 'N';
 14  end;
 15  /

Function created.

Now, run the SQL:

SQL> -- SQL result when the called function
SQL> -- is deterministic
SQL> select emp_id
  2       , emp_name
  3       , dept
  4       , is_number(dept) is_dept_numeric
  5  from employee;

EMP_ID EMP_NAME             DEPT       IS_DEPT_NUMERIC
------ -------------------- ---------- --------------------
   100 Steven King          515        Y
   101 Neena Kochhar        515        Y
   102 Lex De Haan          515        Y
   103 Alexander Hunold     590        Y
   104 Bruce Ernst          590        Y
   105 David Austin         A90        N
   106 Valli Pataballa      590        Y
   107 Diana Lorentz        590        Y
   108 Nancy Greenberg      515        Y
   109 Daniel Faviet        515        Y
   110 John Chen            515        Y
   111 Ismael Sciarra       515        Y
   112 Jose Manuel Urman    A1A        N
   113 Luis Popp            515        Y
   114 Den Raphaely         515        Y
   115 Alexander Khoo       515        Y
   116 Shelli Baida         515        Y
   117 Sigal Tobias         515        Y
   118 Guy Himuro           515        Y
   119 Karen Colmenares     A1A        N

20 rows selected.

is_number (515) called
is_number (515) called
is_number (590) called
is_number (A90) called
is_number (A1A) called
is_number (515) called
is_number (A1A) called

Lo! The non-deterministic function was called far fewer times this time round.

This difference in performance with the DETERMINISTIC clause can be significant when dealing with huge volumes of data.

Caution: Don’t mark non-deterministic functions as DETERMINISTIC!

As the saying goes, with great power comes great responsibility. Don’t let this performance benefit tempt you into marking non-deterministic functions as deterministic. Doing so might mislead Oracle to believe that the result of the function, once calculated, is good for reuse when in fact it isn’t — and you can end up with query results that are plain inaccurate.

Rule of thumb: If a function uses package variables or accesses the database in any way that might affect its output, do not make it deterministic.

Summary

This article describes when to use the DETERMINISTIC clause with Oracle functions and what optimization you might get when marking functions as deterministic.

For Further Reading

• Deterministic algorithm
• PL/SQL Language Reference 12c
• How to write a safe result-cached PL/SQL function
• Caching and worries about consistent data

The post DETERMINISTIC Functions in Oracle appeared first on Oratable.

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

Why do Oracle objects become INVALID?

Oracle objects often become invalid during patching or upgrades because objects they depend on get dropped or altered in some way.

Let’s see this in action with a table having a dependent view and procedure.

The table:

SQL>  -- Table on which other objects depend
SQL>  create table temp_table
  2   ( id number);

Table created.

The dependent view and procedure:

SQL>  -- Dependent view
SQL>  create view temp_view
  2   as select * from temp_table;

View created.

SQL>  -- Dependent procedure
SQL>  create procedure temp_proc
  2   as
  3     l_id   number;
  4   begin
  5     select id into l_id
  6     from temp_table;
  7   end;
  8   /

Procedure created.

All the three objects – the table, the view and procedure — are valid at this time:

SQL> -- Check object validity
SQL> select object_name
  2       , object_type
  3       , status
  4  from all_objects
  5  where object_name like 'TEMP%';

OBJECT_NAME        OBJECT_TYPE    STATUS
------------------ -------------- -------
TEMP_PROC          PROCEDURE      VALID
TEMP_TABLE         TABLE          VALID
TEMP_VIEW          VIEW           VALID

Now, we drop the table…

SQL> -- Drop the table; break the
SQL> -- dependency chain
SQL> drop table temp_table;

Table dropped.

…and check the status of the view and the procedure:

SQL> -- Check object validity
SQL> select object_name
  2       , object_type
  3       , status
  4  from all_objects
  5  where object_name like 'TEMP%';

OBJECT_NAME        OBJECT_TYPE    STATUS
------------------ -------------- -------
TEMP_PROC          PROCEDURE      INVALID
TEMP_VIEW          VIEW           INVALID

Outcome: no more table — and both of the dependent objects have become invalid.

Another way objects become invalid is when errors occur at the time of creation or change. For example, a stored procedure gets created/replaced as valid if it compiles successfully, else it is invalid.

We can test this out with a procedure containing a coding error (a missing quote in dbms_output.put_line):

SQL> -- Procedure with compilation error
SQL> -- Will get created as INVALID; will not autocompile
SQL> create or replace procedure temp_proc_with_bug
  2  as
  3  begin
  4    dbms_output.put_line('Missing closing quote!);
  5  end;
  6  /

Warning: Procedure created with compilation errors.

When we check ALL_OBJECTS, we find that this procedure got created despite the error, but with status INVALID.

SQL> -- Check object validity
SQL> select object_name
  2       , object_type
  3       , status
  4  from all_objects
  5  where object_name = 'TEMP_PROC_WITH_BUG';

OBJECT_NAME        OBJECT_TYPE    STATUS
------------------ -------------- -------
TEMP_PROC_WITH_BUG PROCEDURE      INVALID

How to identify INVALID objects in Oracle

The first step is to get the list of objects and their relevant details (type, last DDL time, etc.), with a filter for status INVALID. To get this information, query [DBA/ALL/USER]_OBJECTS depending on your privileges:

DBA_OBJECTS : All objects in the database
ALL_OBJECTS : All objects accessible to the user
USER_OBJECTS : All objects owned by the user

SQL> -- Check for invalid objects
SQL> select object_name
  2       , object_type
  3       , status
  4  from all_objects
  5  where status = 'INVALID';

OBJECT_NAME        OBJECT_TYPE    STATUS
------------------ -------------- -------
TEST_VIEW          VIEW           INVALID
TEMP_PROC          PROCEDURE      INVALID
TEMP_PROC_WITH_BUG PROCEDURE      INVALID

The next step is to find out *why* the object is invalid. To get this information, query [DBA/ALL/USER]_ERRORS depending on your privileges:

DBA_ERRORS : Current errors on all objects in the database
ALL_ERRORS : Current errors on all objects accessible to the user
USER_ERRORS : Current errors on all objects owned by the user

SQL> -- Check the error details
SQL> select name
  2       , type
  3       , line
  4       , position
  5       , text
  6  from all_errors
  7  where name like 'TEMP%';

NAME                 TYPE       LINE POSITION TEXT
-------------------- --------- ----- -------- -------------------------------------------
TEMP_PROC_WITH_BUG   PROCEDURE     4       24 PLS-00103: Encountered the symbol "Missing
                                              closing quote!);
                                              end;" when expecting one of the following:

                                                 ( ) - + case mod new not null <an identi
                                              fier>
                                                 <a double-quoted delimited-identifier> <
                                              a bind variable>
                                                 table continue avg count current exists
                                              max min prior sql
                                                 stddev sum variance execute multiset the
                                               both leading
                                                 trailing forall merge year month day hou
                                              r minute second
                                                 timezone_hour timezone_minute timezone_r
                                              egion timezone_abbr
                                                 time timestamp interval date
                                                 <a string literal with
 

Note that not all invalid objects will have errors listed for them in *_ERRORS; some will get an entry when we make an attempt to access them. You can see this in the results of the last two SQLs: though TEMP_VIEW and TEMP_PROC are listed as invalid in ALL_OBJECTS, they are absent from ALL_ERRORS as they have not been accessed yet.

Let’s try to access the invalid objects and check ALL_ERRORS again.

SQL> -- Query the INVALID view
SQL> select * from temp_view;
select * from temp_view
              *
ERROR at line 1:
ORA-04063: view "HR.TEMP_VIEW" has errors

SQL> -- Execute the INVALID package
SQL> exec temp_proc;
BEGIN temp_proc; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object HR.TEMP_PROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> -- Check the error details
SQL> select name
  2       , type
  3       , line
  4       , position
  5       , text
  6  from all_errors
  7  where name like 'TEMP%';

NAME                 TYPE       LINE POSITION TEXT
-------------------- --------- ----- -------- -------------------------------------------
TEMP_PROC            PROCEDURE     5        3 PL/SQL: SQL Statement ignored
TEMP_PROC            PROCEDURE     6        8 PL/SQL: ORA-00942: table or view does not e
                                              xist

TEMP_PROC_WITH_BUG   PROCEDURE     4       24 PLS-00103: Encountered the symbol "Missing
                                              closing quote!);
                                              end;" when expecting one of the following:

                                                 ( ) - + case mod new not null <an identi
                                              fier>
                                                 <a double-quoted delimited-identifier> <
                                              a bind variable>
                                                 table continue avg count current exists
                                              max min prior sql
                                                 stddev sum variance execute multiset the
                                               both leading
                                                 trailing forall merge year month day hou
                                              r minute second
                                                 timezone_hour timezone_minute timezone_r
                                              egion timezone_abbr
                                                 time timestamp interval date
                                                 <a string literal with

TEMP_VIEW            VIEW          0        0 ORA-00942: table or view does not exist

The errors are now listed in ALL_ERRORS, since an attempt was made to query or access the invalid objects.

How to convert INVALID objects to VALID

Most of the time, you do not need to explicitly recompile objects. When there are no compilation failures, the object will turn from invalid to valid as soon as it is executed or accessed again.

To see how this works, let’s reinstate the missing object (table TEMP_TABLE) which had caused dependent objects (view TEMP_VIEW and procedure TEMP_PROC) to become invalid, and then check the status of dependent objects.

SQL> -- Reinstate the dropped table
SQL>  create table temp_table
  2   ( id number);

Table created.

SQL> -- BEFORE querying dependent view
SQL> -- Check object validity
SQL> select object_name
  2       , object_type
  3       , status
  4  from all_objects
  5  where object_name like 'TEMP_VIEW';

OBJECT_NAME        OBJECT_TYPE    STATUS
------------------ -------------- -------
TEMP_VIEW          VIEW           INVALID

SQL> -- Query the dependent view
SQL> select * from temp_view;

no rows selected

SQL> -- AFTER querying dependent view
SQL> -- Check object validity
SQL> select object_name
  2       , object_type
  3       , status
  4  from all_objects
  5  where object_name like 'TEMP_VIEW';

OBJECT_NAME        OBJECT_TYPE    STATUS
------------------ -------------- -------
TEMP_VIEW          VIEW           VALID

…which goes to show that the invalid object fixed itself on its own.

If you want to compile invalid objects in advance — perhaps to distinguish between those that would auto-fix themselves and those that wouldn’t – you can follow one of the these approaches.

1. ALTER…COMPILE per object

Compile objects selectively, one at a time, using the syntax:

ALTER [object_type] [object_name] COMPILE;

SQL> ALTER PROCEDURE temp_proc COMPILE;

Procedure altered.

If the compilation fails, use “sho err” on SQL*Plus to display errors.

SQL> ALTER PROCEDURE temp_proc_with_bug COMPILE;

Warning: Procedure altered with compilation errors.

SQL> sho err
Errors for PROCEDURE TEMP_PROC_WITH_BUG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/24     PLS-00103: Encountered the symbol "Missing closing quote!);
         end;" when expecting one of the following:
         ( ) - + case mod new not null <an identifier>
         <a double-quoted delimited-identifier> <a bind variable>
         table continue avg count current exists max min prior sql
         stddev sum variance execute multiset the both leading
         trailing forall merge year month day hour minute second
         timezone_hour timezone_minute timezone_region timezone_abbr
         time timestamp interval date
         <a string literal with

2. DBMS_UTILITY.COMPILE_SCHEMA

Use DBMS_UTILITY.COMPILE_SCHEMA to compile all object types in the specified schema. This takes as input a “compile_all” option: if TRUE (the default), all objects in the schema are compiled, if FALSE only invalid objects are compiled.

exec dbms_utility.compile_schema(
    schema => '<schema_name>'
  , compile_all => FALSE -- Compile only INVALID objects
     );

Let’s see this action:

SQL> exec dbms_utility.compile_schema(schema => USER, compile_all => FALSE);

PL/SQL procedure successfully completed.

SQL> -- AFTER compile_schema
SQL> -- Check object validity
SQL> select object_name
  2       , object_type
  3       , status
  4  from all_objects
  5  where object_name like 'TEMP%';

OBJECT_NAME        OBJECT_TYPE    STATUS
------------------ -------------- -------
TEMP_PROC          PROCEDURE      VALID
TEMP_PROC_WITH_BUG PROCEDURE      INVALID
TEMP_TABLE         TABLE          VALID
TEMP_VIEW          VIEW           VALID

The objects that remain invalid after running DBMS_UTILITY.COMPILE_SCHEMA are those that need a review and fix of errors in *_ERRORS.

Summary

Oracle objects typically become invalid when a dependency chain is broken, or when they have errors at the time of compilation.

Invalid objects can be identified by querying *_OBJECTS with a filter on status = INVALID. Error details can be queried from *_ERRORS.

Invalid objects get automatically recompiled when they are accessed or executed. To compile them in advance, you can use ALTER…COMPILE per object, or DBMS_UTILITY.COMPILE_SCHEMA for all objects in the schema.

For Further Reading

• SQLCODE and SQLERRM
• DBMS_UTILITY.GET_PARAMETER_VALUE to find out your database name
• DBMS_UTILITY.GET_DEPENDENCY to find out object dependencies

The post Find Which Objects are INVALID in Oracle – and Why appeared first on Oratable.

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.

UTL_HTTP.SET_RESPONSE_ERROR_CHECK

SET_RESPONSE_ERROR_CHECK is a procedure in package UTL_HTTP. It determines whether or not UTL_HTTP.GET_RESPONSE will raise a PL/SQL exception when the web service returns an error status code  (4xx or 5xx).

• If SET_RESPONSE_ERROR_CHECK is set to TRUE, UTL_HTTP.GET_RESPONSE raises a PL/SQL exception HTTP_CLIENT_ERROR (for 4xx) or HTTP_SERVER_ERROR (for 5xx).

• If SET_RESPONSE_ERROR_CHECK is set to FALSE, UTL_HTTP.GET_RESPONSE does not raise a PL/SQL exception.

By default, SET_RESPONSE_ERROR_CHECK is FALSE. With this setting, it is possible to read the HTTP response from PL/SQL even if the HTTP status code returned from the web service indicates an error (4xx or 5xx).

SET_RESPONSE_ERROR_CHECK TRUE or FALSE: Which should be used?

We may choose to configure SET_RESPONSE_ERROR_CHECK as TRUE or FALSE depending on factors such as:

• Knowledge of the web service being called: What are the possible return statuses? What would be the content of the response body in case of error?

  If a web service contains important error details in the response body, it would make sense to configure SET_RESPONSE_ERROR_CHECK as FALSE and let the PL/SQL program read and report the response.

• Functional interpretation of return statuses: Does the web service error code truly represent an application error? “404 Not Found” may mean an invalid URL. It may also mean that a GET web service call returned no data and that’s 200…er, OK.

  Whether we propagate web service status of HTTP_NOT_FOUND as a PL/SQL exception or not would be guided by what it represents functionally.

• The overall solution: Should the web service error be quietly handled and logged, letting the rest of the program proceed? Should the error trigger an alternate path in the code? Should it bring the entire processing to a halt?

  How we design exception handling for web service calls with SET_RESPONSE_ERROR_CHECK would be driven by how this piece of code fits into the larger picture.

The following demo shows how PL/SQL program flow changes based on the TRUE/FALSE value configured for SET_RESPONSE_ERROR_CHECK. This should help you decide the approach to follow in your solution.

Demo Description: REST API GET Call via PL/SQL

For the demo, we will use a simple GET call to the math.js REST service.

HTTP GET URL http://api.mathjs.org/v4/ 
Parameter “expr” = the arithmetic expression to be evaluated

Webservice expected responses:

To test the behavior with different values in SET_RESPONSE_ERROR_CHECK, we will

1. Write two variants of a procedure call_restapi to call the REST GET request from PL/SQL: one with SET_RESPONSE_ERROR_CHECK = TRUE, another with SET_RESPONSE_ERROR_CHECK = FALSE.

2. Test SUCCESS scenario: Execute the procedure variants with a valid value of “expr” — this should show identical behavior regardless of the value of SET_RESPONSE_ERROR_CHECK.

3. Test FAILURE scenario: Execute the procedure variants with an invalid value of “expr” — this should show that:

   • with SET_RESPONSE_ERROR_CHECK set to TRUE, the procedure raises a PL/SQL exception when return status is 400.
   • with SET_RESPONSE_ERROR_CHECK set to FALSE, the procedure completes successfully without a PL/SQL exception. The response body can be read in the procedure.

Demo Scripts: Procedure with SET_RESPONSE_ERROR_CHECK TRUE/FALSE, Success Scenario Test, Failure Scenario Test

1. Procedure with SET_RESPONSE_ERROR_CHECK TRUE/FALSE

PL/SQL procedure call_restapi uses UTL_HTTP to call the REST GET API. The code now has SET_RESPONSE_ERROR_CHECK = TRUE. We will toggle its value as TRUE/FALSE for the tests.

-- REST API call demo with exception handling.
-- Evaluate a mathemathical expression via GET
-- request from PLSQL
create or replace procedure calc_restapi 
    (expression in varchar2)
is
  url           varchar2(50) := 'http://api.mathjs.org/v4/';
  request       UTL_HTTP.req;
  response      UTL_HTTP.resp;
  response_clob CLOB;
  response_buf  varchar2(32767);

begin
  -- Begin the GET request
  request := UTL_HTTP.begin_request
           (url || '?expr='|| expression
         , 'GET', 'HTTP/1.1');
   
  -- Response error check configuration
  -- TRUE => PL/SQL exception in case of response error
  -- FALSE => no PL/SQL exception
  UTL_HTTP.set_response_error_check (true);
  
  response := UTL_HTTP.get_response(request);
  
  -- Flow reaches here if a 4xx/5xx error occurs when
  -- set_response_error_check is FALSE
  if (response.status_code = UTL_HTTP.HTTP_OK) then
    dbms_output.put_line('Success: Received OK response');
  else
    dbms_output.put_line
    ('Failure: Received non-OK response: '
    ||response.status_code||' '||response.reason_phrase);
  end if;
 
  -- Create temporary LOB to hold the HTTP response
  DBMS_LOB.createtemporary(response_clob, FALSE);
  
  -- Loop to read response body chunk by chunk into CLOB
  begin
    loop
      UTL_HTTP.read_text(response
       , response_buf, 32766);
      DBMS_LOB.writeappend (response_clob
       , LENGTH(response_buf), response_buf);  
    end loop;
    
    DBMS_LOB.freetemporary(response_clob);
  exception
    when UTL_HTTP.end_of_body then
      UTL_HTTP.end_response(response);
  end;   

  dbms_output.put_line('Response : '
    ||substr(response_clob,1,200));
exception
  -- Flow reaches here if a 4xx/5xx error occurs when
  -- set_response_error_check is TRUE
  when others then
    dbms_output.put_line('Error: '
    || response.status_code ||' '
    || UTL_HTTP.GET_DETAILED_SQLCODE ||' ' 
    || substr(UTL_HTTP.GET_DETAILED_SQLERRM,1,200));
end;
/

2. SUCCESS Scenario Test Script

PL/SQL script to execute call_restapi with a valid arithmetic expression as input: this should return status 200 OK. The response body should contain the result of the arithmetic expression.

The expected response body is the result of the expression — 8 in this case.

-- Success case: status code 200 OK
-- Response body: result of expression
exec calc_restapi ('2*(7-3)');

2. FAILURE Scenario Test Script

PL/SQL script to execute call_restapi with an invalid arithmetic expression as input: this should return status 400 Bad Request. The response body should contain the result of the arithmetic expression.

The expected response body is the details of the error in the input expression.

-- Failure case: 4xx error
-- Response body: error details
-- SET_RESPONSE_ERROR_CHECK = TRUE
exec calc_restapi ('a');

Let’s see the demo in action.

PL/SQL REST API GET Call with SET_RESPONSE_ERROR_CHECK  = TRUE

1. Compile procedure with SET_RESPONSE_ERROR_CHECK TRUE

SQL> -- REST API call demo with exception handling.
SQL> -- Evaluate a mathemathical expression via GET
SQL> -- request from PLSQL
SQL> -- SET_RESPONSE_ERROR_CHECK = TRUE
SQL> create or replace procedure calc_restapi
  2      (expression in varchar2)
  3  is
  4    url           varchar2(50) := 'http://api.mathjs.org/v4/';
  5    request       UTL_HTTP.req;
  6    response      UTL_HTTP.resp;
  7    response_clob CLOB;
  8    response_buf  varchar2(32767);
  9
 10  begin
 11    -- Begin the GET request
 12    request := UTL_HTTP.begin_request
 13             (url || '?expr='|| expression
 14           , 'GET', 'HTTP/1.1');
 15
 16    -- Response error check configuration
 17    -- TRUE => PL/SQL exception in case of response error
 18    UTL_HTTP.set_response_error_check (true);
 19
 20    response := UTL_HTTP.get_response(request);
 21
 22    -- Flow reaches here if a 4xx/5xx error occurs when
 23    -- set_response_error_check is FALSE
 24    if (response.status_code = UTL_HTTP.HTTP_OK) then
 25      dbms_output.put_line('Success: Received OK response');
 26    else
 27      dbms_output.put_line
 28      ('Failure: Received non-OK response: '
 29      ||response.status_code||' '||response.reason_phrase);
 30    end if;
 31
 32    -- Create temporary LOB to hold the HTTP response
 33    DBMS_LOB.createtemporary(response_clob, FALSE);
 34
 35    -- Loop to read response body chunk by chunk into CLOB
 36    begin
 37      loop
 38        UTL_HTTP.read_text(response
 39         , response_buf, 32766);
 40        DBMS_LOB.writeappend (response_clob
 41         , LENGTH(response_buf), response_buf);
 42      end loop;
 43
 44      DBMS_LOB.freetemporary(response_clob);
 45    exception
 46      when UTL_HTTP.end_of_body then
 47        UTL_HTTP.end_response(response);
 48    end;
 49
 50    dbms_output.put_line('Response : '
 51      ||substr(response_clob,1,200));
 52  exception
 53    -- Flow reaches here if a 4xx/5xx error occurs when
 54    -- set_response_error_check is TRUE
 55    when others then
 56      dbms_output.put_line('Error: '
 57      || response.status_code ||' '
 58      || UTL_HTTP.GET_DETAILED_SQLCODE ||' '
 59      || substr(UTL_HTTP.GET_DETAILED_SQLERRM,1,200));
 60  end;
 61  /

Procedure created.

2. Run SUCCESS scenario test script

SQL> -- Success case: status code 200 OK
SQL> -- Response body: result of expression
SQL> exec calc_restapi ('2*(7-3)');
Success: Received OK response
Response : 8

PL/SQL procedure successfully completed.

3. Run FAILURE scenario test script

SQL> -- Failure case: 4xx error
SQL> -- Response body: error details
SQL> -- SET_RESPONSE_ERROR_CHECK = TRUE
SQL> exec calc_restapi ('a');
Error:  -29268 ORA-29268: HTTP client error 400 - Bad Request

PL/SQL procedure successfully completed.

You can see that the FAILURE test script did not execute the code after the call to UTL_HTTP.get_response, but jumped straight to the EXCEPTION section.

PL/SQL REST API GET Call with SET_RESPONSE_ERROR_CHECK  = FALSE

1. Compile procedure with SET_RESPONSE_ERROR_CHECK FALSE

SQL> -- REST API call demo with exception handling.
SQL> -- Evaluate a mathemathical expression via GET
SQL> -- request from PLSQL
SQL> -- SET_RESPONSE_ERROR_CHECK = FALSE
SQL> create or replace procedure calc_restapi
  2      (expression in varchar2)
  3  is
  4    url           varchar2(50) := 'http://api.mathjs.org/v4/';
  5    request       UTL_HTTP.req;
  6    response      UTL_HTTP.resp;
  7    response_clob CLOB;
  8    response_buf  varchar2(32767);
  9
 10  begin
 11    -- Begin the GET request
 12    request := UTL_HTTP.begin_request
 13             (url || '?expr='|| expression
 14           , 'GET', 'HTTP/1.1');
 15
 16    -- Response error check configuration
 17    -- FALSE => no PL/SQL exception
 18    UTL_HTTP.set_response_error_check (false);
 19
 20    response := UTL_HTTP.get_response(request);
 21
 22    -- Flow reaches here if a 4xx/5xx error occurs when
 23    -- set_response_error_check is FALSE
 24    if (response.status_code = UTL_HTTP.HTTP_OK) then
 25      dbms_output.put_line('Success: Received OK response');
 26    else
 27      dbms_output.put_line
 28      ('Failure: Received non-OK response: '
 29      ||response.status_code||' '||response.reason_phrase);
 30    end if;
 31
 32    -- Create temporary LOB to hold the HTTP response
 33    DBMS_LOB.createtemporary(response_clob, FALSE);
 34
 35    -- Loop to read response body chunk by chunk into CLOB
 36    begin
 37      loop
 38        UTL_HTTP.read_text(response
 39         , response_buf, 32766);
 40        DBMS_LOB.writeappend (response_clob
 41         , LENGTH(response_buf), response_buf);
 42      end loop;
 43
 44      DBMS_LOB.freetemporary(response_clob);
 45    exception
 46      when UTL_HTTP.end_of_body then
 47        UTL_HTTP.end_response(response);
 48    end;
 49
 50    dbms_output.put_line('Response : '
 51      ||substr(response_clob,1,200));
 52  exception
 53    -- Flow reaches here if a 4xx/5xx error occurs when
 54    -- set_response_error_check is TRUE
 55    when others then
 56      dbms_output.put_line('Error: '
 57      || response.status_code ||' '
 58      || UTL_HTTP.GET_DETAILED_SQLCODE ||' '
 59      || substr(UTL_HTTP.GET_DETAILED_SQLERRM,1,200));
 60  end;
 61  /

Procedure created.

2. Run SUCCESS scenario test script

SQL> -- Success case: status code 200 OK
SQL> -- Response body: result of expression
SQL> exec calc_restapi ('2*(7-3)');
Success: Received OK response
Response : 8

PL/SQL procedure successfully completed.

The result for a SUCCESS scenario is the same regardless of the value of SET_RESPONSE_ERROR_CHECK.

3. Run FAILURE scenario test script

SQL> -- Failure case: 4xx error
SQL> -- Response body: error details
SQL> -- SET_RESPONSE_ERROR_CHECK = FALSE
SQL> exec calc_restapi ('a');
Failure: Received non-OK response: 400 Bad Request
Response : Error: Undefined symbol a

You can see that the FAILURE test script executed the code after the call to UTL_HTTP.get_response, regardless of whether the web service raised an error or not. There was no PL/SQL exception – the code in the EXCEPTION block, which was executed in the previous case of SET_RESPONSE_ERROR_CHECK = TRUE, did not get executed with SET_RESPONSE_ERROR_CHECK = FALSE.

Summary

This article provides an overview of web service error handling options available in PL/SQL UTL_HTTP. It gives working examples of calling a REST GET API with a parameter, and shows:

• how to make a successful REST GET API call from PL/SQL
• how to handle failure cases in the web service call from PL/SQL, with different settings for SET_RESPONSE_ERROR_CHECK
• how to read the response body when the web service returns an error status (non-2xx response)

References

• 12c Documentation
• UTL_HTTP for web service calls in PL/SQL

The post How to Keep the Response Body with Non-2xx Responses in UTL_HTTP appeared first on Oratable.

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.

Use Case for Demo: Movie Ticketing Application

Let’s say this heavy and slow procedure is one that creates a booking in a movie ticketing application. In its current form, it does all of the following:

create_booking (booking_id)
{
  allocate_seats;
  capture_customer_details;
  receive_payment;
  notify_customer;
  upsell_food;
  update_central_crm;
}

The problem: The last three “non-critical” steps are slowing down the main flow. While it is desirable to have these steps work close to real-time, some lag with these steps is acceptable if it makes the main flow of create_booking faster.

-- BEFORE Slow create_booking 
-- All processing synchronous
create_booking (booking_id)
{

  -- Critical parts of booking: main flow, any failure 
  -- here must fail the entire booking
  allocate_seats;
  capture_customer_details;
  receive_payment;

  -- Non-critical parts of booking: desirable to have this 
  -- as close as possible to real-time but not at the cost 
  -- of slowing down the main flow
  notify_customer;
  upsell_food;
  update_central_crm;
 }

The solution: Move the last three “non-critical” steps to another procedure and invoke that procedure asynchronously. The final state should look like this algorithmically:

-- AFTER Fast create_booking 
-- Non-critical processing asynchronous
create_booking (booking_id)
{

  -- Critical parts of booking: main flow, any failure 
  -- here must fail the entire booking
  allocate_seats;
  capture_customer_details;
  receive_payment;

  -- Non-critical parts of booking: wrapped in 
  -- a separate procedure called asynchronously
  [async] post_booking_flow(booking_id);
 }

-- Async steps factored out into separate procedure
post_booking_flow (booking_id) 
{
  notify_customer;
  upsell_food;
  update_central_crm;
}

Oracle Job Scheduler to Run Procedure Asynchronously: Summary

To run a piece of code asynchronously via Oracle Job Scheduler, a summary of the steps needed:

1. Create procedure to be run asynchronously
2. Call the procedure via DBMS_SCHEDULER.CREATE_JOB in the main flow for a single immediate run (i.e. AUTO_DROP = TRUE, the default)

Oracle Job Scheduler to Run Job Asynchronously: Stepwise Scripts

1. Create procedure to be run asynchronously

This procedure is a wrapper to the portion of code in the main flow which is to be run asynchronously.

-- Procedure to be run asynchronously
create or replace procedure 
post_booking_flow (booking_id in varchar2) 
as
begin
  dbms_output.put_line('START post_booking_flow');
  notify_customer;
  upsell_food;
  update_central_crm;
  dbms_output.put_line('END post_booking_flow');
end;
/

2. Call the procedure via DBMS_SCHEDULER.CREATE_JOB for a single immediate run

The parameter values for CREATE_JOB to support single immediate run:

• job_name: A unique name, say ‘post_booking_flow_job’ concatenated with booking_id to prevent concurrency conflict
• job_type: PLSQL_BLOCK
• job_action: PL/SQL block invoking procedure created in step 1
• enabled: TRUE (default is FALSE)
• auto_drop: TRUE (default is TRUE)

-- Non-critical processing asynchronous
-- with DBMS_SCHEDULER.CREATE_JOB
create or replace procedure create_booking
(booking_id in varchar2)
as 
begin
  dbms_output.put_line('START create_booking');    
  -- Critical parts of booking: main flow, any failure 
  -- here must fail the entire booking
  allocate_seats;
  capture_customer_details;
  receive_payment;

  -- Non-critical parts of booking: wrapped in 
  -- a separate procedure called asynchronously
  dbms_output.put_line('Before post_booking_flow_job');
  dbms_scheduler.create_job (
  job_name   =>  'post_booking_flow_job'||booking_id,
  job_type   => 'PLSQL_BLOCK',
  job_action => 
    'BEGIN 
       post_booking_flow('''||booking_id||''');
     END;',
  enabled   =>  TRUE,  
  auto_drop =>  TRUE, 
  comments  =>  'Non-critical post-booking steps');
  
  dbms_output.put_line('After post_booking_flow_job');  
  dbms_output.put_line('END create_booking');  
end;
/

Oracle Job Scheduler to Run Job Asynchronously: Scripts When Run

SQL> -- Procedure to be run asynchronously
SQL> create or replace procedure
  2  post_booking_flow (booking_id in varchar2)
  3  as
  4  begin
  5    dbms_output.put_line('START post_booking_flow');
  6    notify_customer;
  7    upsell_food;
  8    update_central_crm;
  9    dbms_output.put_line('END post_booking_flow');
 10  end;
 11  /

Procedure created.

SQL> -- Non-critical processing asynchronous
SQL> -- with DBMS_SCHEDULER.CREATE_JOB
SQL> create or replace procedure create_booking
  2  (booking_id in varchar2)
  3  as
  4  begin
  5    dbms_output.put_line('START create_booking');
  6    -- Critical parts of booking: main flow, any failure
  7    -- here must fail the entire booking
  8    allocate_seats;
  9    capture_customer_details;
 10    receive_payment;
 11
 12    -- Non-critical parts of booking: wrapped in
 13    -- a separate procedure called asynchronously
 14    dbms_output.put_line('Before post_booking_flow_job');
 15    dbms_scheduler.create_job (
 16    job_name   =>  'post_booking_flow_job'||booking_id,
 17    job_type   => 'PLSQL_BLOCK',
 18    job_action =>
 19      'BEGIN
 20         post_booking_flow('''||booking_id||''');
 21       END;',
 22    enabled   =>  TRUE,
 23    auto_drop =>  TRUE,
 24    comments  =>  'Non-critical post-booking steps');
 25
 26    dbms_output.put_line('After post_booking_flow_job');
 27    dbms_output.put_line('END create_booking');
 28  end;
 29  /

Procedure created.

Test the asynchronous PL/SQL job

Pass the argument booking_id and test create_booking, now branching off into an asynchronous call to post_booking_flow_job||booking_id.

SQL>-- Running the procedure with async call
SQL> exec create_booking('A001');
START create_booking
Before post_booking_flow_job
After post_booking_flow_job
END create_booking

PL/SQL procedure successfully completed.

SQL> -- Running the procedure with async call
SQL> exec create_booking('A002');
START create_booking
Before post_booking_flow_job
After post_booking_flow_job
END create_booking

PL/SQL procedure successfully completed.

Good news: the asynchronous portion of create_booking code no longer causes a lag in the booking process.

Post Async Job Run: Verify Scheduler Logs

ALL_SCHEDULER_JOB_LOG displays log for the Scheduler jobs accessible to the current user.

ALL_SCHEDULER_JOB_RUN_DETAILS displays run details for the Scheduler jobs accessible to the current user.

SQL> -- Job logs generated in the last hour
SQL> select job_name, status
  2  from all_scheduler_job_log
  3  where job_name like 'POST_BOOKING_FLOW_JOB%'
  4  and log_date > sysdate - 1/24
  5  order by log_date desc;

JOB_NAME                  STATUS
------------------------- ----------
POST_BOOKING_FLOW_JOBA002 SUCCEEDED
POST_BOOKING_FLOW_JOBA001 SUCCEEDED

SQL> -- Job run details generated in the last hour
SQL> select job_name, status
  2  from all_scheduler_job_run_details
  3  where job_name like 'POST_BOOKING_FLOW_JOB%'
  4  and log_date > sysdate - 1/24
  5  order by log_date desc;

JOB_NAME                  STATUS
------------------------- ----------
POST_BOOKING_FLOW_JOBA002 SUCCEEDED
POST_BOOKING_FLOW_JOBA001 SUCCEEDED

Scheduler Privileges to Create Job Asynchronously

In addition to EXECUTE privilege on the package DBMS_SCHEDULER, one must have:

• CREATE JOB privilege to create a job in one’s own schema, and
• CREATE ANY JOB privilege to create a job in any schema except SYS

To invoke a PL/SQL program when creating a job, the owner of the job must be the owner of the program or have EXECUTE privilege on the program.

For Further Reading

Running procedures asynchronously is just one use case for DBMS_SCHEDULER – look up Oracle 12c documentation for the range of its features and utilities.

Also see: Get job name inside DBMS_SCHEDULER executed procedure

The post Running Procedures Asynchronously with Oracle Job Scheduler appeared first on Oratable.

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.

Two solutions are presented below with examples. The first uses analytic functions and is compatible with pre-12c versions of Oracle. The second, more compact solution uses Oracle’s row limiting clause.

Pagination query — setting the context

Pagination queries need some way to identify the start and end markers for rows to be returned for a page. Let’s say this is done using these variables:

• PAGE_SIZE: Count of rows to be returned by the query per fetch / number of rows to be displayed on a single page.
• PAGE_NUM: Page number for which the result set is required. This determines the starting position from which the ordered rows are to be returned.

  Starting position is calculated as:
  ((PAGE_NUM — 1) * PAGE_SIZE) + 1

  e.g. if PAGE_SIZE = 20:
  For PAGE_NUM = 1, starting position = 1.
  For PAGE_NUM = 2, starting position = 21.
  For PAGE_NUM = 3, starting position = 41.

For the demo, we will define PAGE_SIZE and PAGE_NUM as bind variables…

SQL> -- Count of rows to be returned by the SQL per fetch
SQL> var PAGE_SIZE number

SQL> -- Page number for which the result set is required
SQL> var PAGE_NUM number

…and assign them values:

SQL> exec :PAGE_SIZE := 5

PL/SQL procedure successfully completed.

SQL> exec :PAGE_NUM  := 2

PL/SQL procedure successfully completed.

With PAGE_SIZE = 5 and PAGE_NUM = 2, the query should fetch row numbers 6-10 from the result set.

Let’s see the pagination queries in action.

Pre-12c: Pagination with Analytic Function

The solution performs the following steps to get a specific subset of rows from the query results.

1. Order the result set and apply analytic function ROW_NUMBER to assign a running counter 1..N (say “row_index”) to each row.
2. Use the above result set as an inline view, and pick from it only those rows where row_index lies in the window defined by
   [((PAGE_NUM — 1) * PAGE_SIZE) + 1] and
   [PAGE_NUM * PAGE_SIZE].

The SQL:

-- Pre-12c Pagination SQL with analytic function
-- Bind variables:
-- PAGE_SIZE: Count of rows to be returned = 5
-- PAGE_NUM: Page number = 2
select  object_id, object_name  
from (select  a.object_id, a.object_name,
      row_number() over (order by a.object_id) row_index
      from dba_objects a
      )
where row_index between 
              ((:PAGE_NUM — 1) * :PAGE_SIZE) + 1 
          and (:PAGE_NUM * :PAGE_SIZE);

When run:

SQL> -- Pre-12c Pagination SQL with analytic function
SQL> -- Bind variables:
SQL> -- PAGE_SIZE: Count of rows to be returned = 5
SQL> -- PAGE_NUM: Page number = 2
SQL> select  object_id, object_name
  2  from (select  a.object_id, a.object_name,
  3        row_number() over (order by a.object_id) row_index
  4        from dba_objects a
  5        )
  6  where row_index between
  7                ((:PAGE_NUM - 1) * :PAGE_SIZE) + 1
  8            and (:PAGE_NUM * :PAGE_SIZE);

 OBJECT_ID OBJECT_NAME
---------- ---------------------
        13 UET$
        14 SEG$
        15 UNDO$
        16 TS$
        17 FILE$

Oracle 12c+ provides a more compact way of implementing the same functionality, using what’s called the row limiting clause.

12c: Row Limiting Clause with OFFSET/FETCH

The row limiting clause, with its support for ANSI-standard FETCH FIRST/NEXT and OFFSET clauses, eliminates the need for an inline view.

Syntax:

select [columns]
from [table]
order by [key]
offset [starting point for the FETCH — 1]
fetch next [count of rows to be returned i.e. PAGE_SIZE] rows only;       

Fetch can be specified in terms of percent as well, with percent only in place of rows only.

The SQL:

-- 12c Pagination SQL with OFFSET/FETCH
-- Bind variables:
-- PAGE_SIZE: Count of rows to be returned = 5
-- PAGE_NUM: Page number = 2
select object_id, object_name
from dba_objects 
order by object_id
offset (:PAGE_NUM - 1) * :PAGE_SIZE rows 
fetch next :PAGE_SIZE rows only;	

When run:

SQL> -- 12c Pagination SQL with OFFSET/FETCH
SQL> -- Bind variables:
SQL> -- PAGE_SIZE: Count of rows to be returned = 5
SQL> -- PAGE_NUM: Page number = 2
SQL> select object_id, object_name
  2  from dba_objects
  3  order by object_id
  4  offset (:PAGE_NUM - 1) * :PAGE_SIZE rows
  5  fetch next :PAGE_SIZE rows only;

 OBJECT_ID OBJECT_NAME
---------- ---------------------
        13 UET$
        14 SEG$
        15 UNDO$
        16 TS$
        17 FILE$

Note: With row limiting clause, 12c+ essentially provides a simplified way to represent the same query as you might use in a pre-12c database. Under the surface, Oracle applies query transformation to rewrite the row limiting clause to use analytics.

Summary

This article illustrates two ways of implementing pagination (i.e. rows N through M of a result set) in Oracle:

1. SQL query with filter on analytic function row_number()
2. SQL query with row limiting clause [available Oracle 12c onwards]

For Further Reading

• ORACLE-BASE: Paging Through Data
• 12c First N
• Pre-12c Nth Highest Salary
• Oracle Magazine (Oct 2013): Oracle Database 12c Part 1 Features

The post Pagination Queries in Oracle: Analytics, Row Limiting Clause appeared first on Oratable.

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.

What is a Function-Based Index?

A function-based index is created on the value of a function or expression. The function used for building the index can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL function, package function, or C callout.

A typical use case for implementing a function-based index is case-insensitive search. Let’s say the column first_name in table employees contains mixed-case characters. If we have a standard index on first_name, and we use UPPER(first_name) in the WHERE clauses, the standard index will get ignored. We resolve this problem with this function-based index on the employees table:

CREATE INDEX emp_idx_f1
ON employees(UPPER(first_name));

Let’s see next how a function-based index can help with building conditional uniqueness on multiple columns in a table.

Function-based Index for Conditional Uniqueness

Take the case of a table that stores the audit trail of changes in additional to the latest active record:

SQL> -- Table that stores audit trail of
SQL> -- changes in addition to the active row
SQL> create table demo_fbi (
  2     col1 number
  3   , col2 number
  4   , col3 varchar2(30)
  5   , created_date date
  6   , active_flag char(1) default 'Y'
  7       check (active_flag in ('Y','N')));

Table created.

In the table above, we want (col1, col2) to work as a composite unique key conditionally i.e. only if active_flag = ‘Y’. There can be multiple occurrences of the same (col1, col2) combination for rows with active_flag = ‘N’.

We implement this using CASE WHEN ensconced in the CREATE UNIQUE INDEX statement:

SQL> -- Conditional unique index on multiple columns
SQL> create unique index demo_fbi_idx
  2  on demo_fbi
  3    (case when active_flag = 'Y' then
  4              col1 else null end,
  5     case when active_flag = 'Y' then
  6              col2 else null end);

Index created.

Here the searched CASE construct builds the index on (col1,col2) only if active_flag = ‘Y’, else it sets the value to (null,null) – note that in Oracle, all-NULL index entries are not recorded. This meets the desired result of having an index on (col1,col2) only if active_flag = ‘Y’.

Let’s test this!

We will test the behavior of the conditional unique index on multiple columns, with an insert statement script:

-- 1. Insert valid row
-- (1,1) for inactive row
insert into demo_fbi values
(1, 1, 'TEST1', sysdate,'N');

-- 2. Insert valid rows
-- (1,1) again for inactive row
insert into demo_fbi values
(1, 1, 'TEST12', sysdate,'N');

-- 3. Insert valid row
-- (1,1) for active row
insert into demo_fbi values
(1, 1, 'TEST3', sysdate,'Y');

-- 4. Insert invalid row
-- (1,1) again for active row
insert into demo_fbi values
(1, 1, 'TEST4', sysdate,'Y');

-- 5. Insert valid row
-- (1,2) for active row
insert into demo_fbi values
(1, 2, 'TEST5', sysdate,'Y');

-- 6. Insert invalid row
-- (1,2) again for active row
insert into demo_fbi values
(1, 2, 'TEST6', sysdate,'Y');

The script above attempts to insert 6 rows, of which 2 are expected to fail due to conditional unique index violation on (col1, col2). The details of rows expected to fail are given as comments in the script.

When run:

SQL> -- 1. Insert valid row
SQL> -- (1,1) for inactive row
SQL> insert into demo_fbi values
  2  (1, 1, 'TEST1', sysdate,'N');

1 row created.

SQL>
SQL> -- 2. Insert valid rows
SQL> -- (1,1) again for inactive row
SQL> insert into demo_fbi values
  2  (1, 1, 'TEST2', sysdate,'N');

1 row created.

SQL>
SQL> -- 3. Insert valid row
SQL> -- (1,1) for active row
SQL> insert into demo_fbi values
  2  (1, 1, 'TEST3', sysdate,'Y');

1 row created.

SQL>
SQL> -- 4. Insert invalid row
SQL> -- (1,1) again for active row
SQL> insert into demo_fbi values
  2  (1, 1, 'TEST4', sysdate,'Y');
insert into demo_fbi values
*
ERROR at line 1:
ORA-00001: unique constraint (HR.DEMO_FBI_IDX)
violated


SQL>
SQL> -- 5. Insert valid row
SQL> -- (1,2) for active row
SQL> insert into demo_fbi values
  2  (1, 2, 'TEST5', sysdate,'Y');

1 row created.

SQL>
SQL> -- 6. Insert invalid row
SQL> -- (1,2) again for active row
SQL> insert into demo_fbi values
  2  (1, 2, 'TEST6', sysdate,'Y');
insert into demo_fbi values
*
ERROR at line 1:
ORA-00001: unique constraint (HR.DEMO_FBI_IDX)
violated

The result is as expected — 4 rows were inserted, and two did not pass the conditional unique index validation.

SQL> select * from demo_fbi;

      COL1       COL2 COL3   CREATED_D ACTIVE_FLAG
---------- ---------- ------ --------- -----------
         1          1 TEST1  02-OCT-18 N
         1          1 TEST2  02-OCT-18 N
         1          1 TEST3  02-OCT-18 Y
         1          2 TEST5  02-OCT-18 Y

Summary

Oracle’s function-based indexes can be used to build conditional uniqueness on table columns. This article shows how a conditional unique index works on a table that stores, along with its active records, the audit trail of changes to its records.

The post Conditional Unique Index on Multiple Columns appeared first on Oratable.

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.

Java stored procedures in Oracle: Why do you need them?

A common reason why you would want Java stored procedures in Oracle is that the equivalent PL/SQL is not feasible, performant or economical to build. A few use cases:

• Application-specific code that’s already in use in Java and needs to be migrated to the database: it might make more sense to make use of the working Java code than to recreate it from scratch in PLSQL.
• Out-of-the-box Java functions: Java packages might have pre-built functions for the problem at hand, and the cost of building their PL/SQL equivalent is prohibitively high. This could range from simple stuff such as primality testing with BigInteger or advanced cryptographic operations with javax.crypto.

How to store and invoke Java methods in Oracle

The steps are outlined below. A working example follows in the next section.

1. Create the Java source

Write the code (java_source_code) as you normally would in Java. Give the Java source code a name (java_source_name) and create it in the database, with the syntax:

create or replace and resolve java source named
"<java_source_name>" as 
<java_source_code>;

2. Publish the Java method through a call specification

To publish the Java method, create a “call specification” that maps the Java method name, parameter types and return types to their SQL counterparts. The syntax:

create or replace function <plsql_function_name>
  (<plsql_parameters>)
  return <plsql_return_type>
as language java
name ‘<java_source_name>.<method>
  (<java_parameters>)
  return <java_return_type>’;

There should be a 1:1 correspondence between the java parameters/return types and the SQL counterparts. For more on mappings and data conversion considerations, see the Oracle documentation for mappings between SQL Types and Java Types (12c).

A call specification is not a wrapper. A wrapper adds another layer of execution, whereas a call specification only publishes the existence of a Java method. So when you call the Java method via its call specification, this happens at runtime with minimal overhead.

3. Invoke the Java method via the call specification

The stored Java method can now be called using <plsql_function_name> like any regular PL/SQL function. The Java stored procedure can accept arguments, reference Java classes, and return Java result values.

Java in Oracle: Demo

The method used for this demo is BigInteger‘s nextProbablePrime, a pre-built solution that returns the first probable prime integer greater than the current value.

Before moving on to the demo, a little introduction to BigInteger: this is a custom Java class for handling very large numbers, with pre-built methods for mathematical operations. Read more about it in the Java 8 documentation. Note that the class BigInteger does not have a direct datatype mapping in PL/SQL, and so we need to convert the method’s input/output to a Java primitive type, which can be then mapped to the PL/SQL datatype NUMBER.

Let’s see in action, the steps to store and invoke a Java method in Oracle.

1. Create the Java source

Java source MathFunction with method nextProbablePrime — this is a wrapper for BigInteger.nextProbablePrime, with a switch of datatype: int from/to BigInteger.

-- Java source with method which returns the first 
-- integer greater than the input that's probably prime. 
create or replace and resolve java source named 
"MathFunction" as
import java.math.*;

/**
 *  Java class to calculate math functions
 */
public class MathFunction {

      /**
       * Wrapper for BigInteger's nextProbablePrime
       * function. Input as well as return type are
       * are mapped from int to/from BigInteger.
       * 
       * @param int n
       * @return int next probable prime > n
       */
       public static int nextProbablePrime(int n) {

            return BigInteger.valueOf(n)
                    .nextProbablePrime().intValue();
       }
    };
/

When run:

SQL> -- Java source with method which returns the first
SQL> -- integer greater than the input that's probably prime.
SQL> create or replace and resolve java source named
  2  "MathFunction" as
  3  import java.math.*;
  4
  5  /**
  6   *  Java class to calculate math functions
  7   */
  8  public class MathFunction {
  9
 10        /**
 11         * Wrapper for BigInteger's nextProbablePrime
 12         * function. Input as well as return type are
 13     * are mapped from int to/from BigInteger.
 14         *
 15         * @param int n
 16         * @return next probable prime > n
 17         */
 18         public static int nextProbablePrime(int n) {
 19
 20              return BigInteger.valueOf(n)
 21                      .nextProbablePrime().intValue();
 22         }
 23      };
 24  /

Java created.

SQL>

2. Publish the Java method through a call specification

The call specification for the source created in step 1 -  MathFunction.nextProbablePrime(int) with return type int.

-- Call specification for the Java source 
-- MathFunction.nextProbablePrime
create or replace function nextProbablePrime 
  (n number)
  return number
as language java
name 'MathFunction.nextProbablePrime(int) 
  return int';

When run:

SQL> -- Call specification for the Java source
SQL> -- MathFunction.nextProbablePrime
SQL> create or replace function nextProbablePrime
  2    (n number)
  3    return number
  4  as language java
  5  name 'MathFunction.nextProbablePrime(int)
  6    return int';
  7  /

Function created.

SQL>

3. Invoke the Java method via the call specification

The Java stored method is now ready for use via its call specification.

Examples:

SQL> select nextProbablePrime(36) from dual;

NEXTPROBABLEPRIME(36)
---------------------
                   37

SQL> select nextProbablePrime(2147) from dual;

NEXTPROBABLEPRIME(2147)
-----------------------
                   2153

Summary

Java methods can be stored in Oracle and invoked via a call specification. This article shows you how this can be done, with an example using the Java method BigInteger.nextProbablePrime  in the Oracle database.

The post Java Stored Procedures in Oracle appeared first on Oratable.

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

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

Value in String != Value in List

Let’s say this is the table data:

SQL> select * from customer;

ID NAME
-- --------------------
A1 Jim
A2 Michael
A3 Pam
A4 Kevin
A5 Angela

…and we need to get the data for ids A1,A2 and A4 from the table.

The user would enter A1,A2,A4 into the search screen and the application would apply it in SQL to match the data in the table.

Directly plugging the values from the search screen’s input box into the query does not work:

SQL> -- Customers with key filter
SQL> -- Wrong SQL!
SQL> select *
  2  from customer
  3  where id in ('A1,A2,A4');

no rows selected

The SQL should instead be looking into a set of discrete values via the IN clause, as in…

SQL> -- Customers with key filter
SQL> -- That's the SQL we want!
SQL> select *
  2  from customer
  3  where id in ('A1','A2','A4');

ID NAME
-- --------------------
A1 Jim
A2 Michael
A4 Kevin

The Classic String to Rows Conundrum

What was entered into the input box was a string: ‘A1, A2, A4’.

‘A1, A2, A4’ as-is in SQL IN will be interpreted as one unit of text — not as a list of discrete values ‘A1’, ‘A2’, and ‘A4’.

We need a way to convert the comma-separated text to rows.
How do we do it?
There are several ways. This article shows you a simple one, using a combination of REGEX_SUBSTR and CONNECT BY.

SQL: String to Rows Solution

The SQL that does get the result we seek:

-- Customers with key filter
-- REGEXP_SUBSTR and CONNECT BY to convert string to rows
select  *
from customer 
where id in
  (select regexp_substr('A1,A2,A4','[^,]+', 1, level) 
   from dual 
   connect BY regexp_substr('A1,A2,A4', '[^,]+', 1, level) 
   is not null);

When run:

SQL> -- Customers with key filter
SQL> -- REGEXP_SUBSTR and CONNECT BY to convert string to rows
SQL> select  *
  2  from customer
  3  where id in
  4    (select regexp_substr('A1,A2,A4','[^,]+', 1, level)
  5     from dual
  6     connect BY regexp_substr('A1,A2,A4', '[^,]+', 1, level)
  7     is not null);

ID NAME
-- --------------------
A1 Jim
A2 Michael
A4 Kevin

The trick is in the subquery that converts the input string ‘A1, A2, A4’ to the list of discrete values ‘A1’, ‘A2’, and ‘A4’.

Let’s examine the solution more closely.

String to Rows Solution: A Closer Look

The building blocks of the solution are:

(1) REGEX_SUBSTR for pattern matching

REGEXP_SUBSTR is a regular expression function in Oracle that finds a substring matching a regular expression pattern in a given text.

Syntax:
regex_substr(text, pattern, position, occurrence)

where

• text: Source string in which the pattern is to be searched.
  ‘A1, A2, A3’ in this example.
• pattern: regular expression that matches the substring to be returned.
  [^,]+ in this example, which matches any character sequence in the text that does not contain a comma.
• position:  Character of text where Oracle should begin the search. In this example, we will begin the search from 1.
• occurrence:  Nth occurrence of pattern in source string to be returned.

So for example, to get the 2nd occurrence of pattern in text which does not contain a comma:

SQL> -- REGEXP_SUBSTR to pick the 2nd value from string
SQL> select regexp_substr('A1,A2,A4','[^,]+', 1, 2)
  2  from dual;

RE
--
A2

To convert the entire string to rows, *all* occurrences of the substrings within commas are to be returned — so we need a way to iterate through 1..last occurrences of substrings in the SQL. That’s where CONNECT BY comes into play…

(2) CONNECT BY for 1..N row generation

Recursive CONNECT BY along with LEVEL can be used as a basic 1..N row generator.

The value returned by level can be used to indicate occurrence in REGEX_SUBSTR.

SQL> -- 1..N generator using CONNECT BY
SQL> select level
  2  from dual
  3  connect by level <= 4;

     LEVEL
----------
         1
         2
         3
         4

Putting both together, we get…

REGEX_SUBSTR + CONNECT BY for delimiter-separated string —> rows conversion

SQL> -- REGEXP_SUBSTR and CONNECT BY to convert string to rows
SQL> select regexp_substr('A1,A2,A4','[^,]+', 1, level)
  2  from dual
  3  connect BY regexp_substr('A1,A2,A4', '[^,]+', 1, level)
  4  is not null;

REGEXP_SUBSTR('A1,A2,A4'
------------------------
A1
A2
A4

Summary

A delimiter-separated string can be converted to a set of rows in Oracle SQL, with the combination of the regex function REGEX_SUBSTR and recursion via CONNECT BY. This feature can be used for splitting a single input string with comma-separated query parameters, into a list of values.

and

The post REGEX to Split a Comma-Separated String into Rows appeared first on Oratable.

GUI tools like TOAD and SQL Developer are all fine, but those who have cut their teeth on SQL*Plus will not do without good old command-line interface. And so, a message like the one below makes one want to set things right immediately…

Here’s a step-by-step guide for doing away with this error, by installing SQL*Plus Instant Client on Windows.

First things first: What is SQL*Plus Instant Client?

From the Oracle documentation:

SQL*Plus Instant Client is a standalone product with all the functionality of SQL*Plus command-line. It connects to existing remote Oracle databases, but does not include its own database. It is easy to install and uses significantly less disk space than the full Oracle Database Client installation required to use SQL*Plus command-line.

If all you want is a simple command-line interface to connect to remote Oracle databases, Instant Client meets your purpose well.

On to the installation…

1. Download the installers from OTN: Basic OCI + SQL*Plus

SQL*Plus Instant Client package needs OCI Instant Client as a pre-requisite. For things to work, the two must be (i) of the same version, and (ii) installed in the same directory.

To download:

Go to OTN’s Instant Client Downloads for MS Windows.

Accept the license agreement.

Choose the packages you want to install — in this case:

• Basic Light Package – Smaller version of the Basic OCI package, with only English error messages
• SQL*Plus Package – The SQL*Plus command line tool for SQL and PL/SQL queries

Versions used: SQL*Plus 12.2.0.1.0, for a Windows 64-bit environment.

Click on the hyperlinks to start download — this will download the ZIP files to your local machine.

2. Extract the downloads

Extract both the ZIPs to the same local folder, say D:\Tools\sqlplus. A new subfolder (instantclient_12_2 in this case) will get created.

3. Edit the PATH variable to include the SQL*Plus Instant Client directory

Add the directory containing the Instant Client files to the PATH environment variable. Make sure this addition does not have a conflict with another existing Oracle path — if it does, either remove the existing value or move the new value to the front of the PATH variable.

We now have the SQL*Plus Instant Client ready to connect to remote databases.

4. Connect to a Database with SQL*Plus Instant Client via Oracle Net connection identifier

To connect to a database, first open a command prompt window.

Enter the Easy Connection identifier to connect to the database. For example, the Easy Connection identifier for the HR schema in an XE database, running on host sales-server, port 1521 is:

sqlplus hr/your_password@\"//sales-server:1521/XE\"

Once entered, SQL*Plus Instant Client will establish the database connection and you will see the SQL prompt.

You can now run your SQL commands through SQL*Plus Instant Client’s easy command-line interface.

Other than Easy Connection identifiers, Net Service Names can be used for making database connections via Instant Client. Net Service Names may come from sources such as LDAP or a tnsnames.ora file. For more details on connection options, see the user’s guide and reference.

Notes:

Remember that the error in the first screenshot (‘sqlplus’ is not recognized as an internal or external command) could be because of a misconfigured PATH — see step 3. Before you proceed to install SQL*Plus, verify that SQL*Plus is actually absent!

For more on SQL*Plus Instant Client, see the Oracle documentation: link. Also check out the advanced configuration tips in this neat tutorial at williamrobertson.net.

The post How to Install and Configure SQL*Plus Instant Client on Windows appeared first on Oratable.

When you have an enormous load of data to copy/merge from one table to another, you are probably concerned about:

• Performance: How fast can the program copy/merge all the data?
• Exception handling: How well does the program deal with errors in the data? Can it highlight bad data if present in the lot, at the same time allow the good data to pass successfully?

In older versions of Oracle, if you had to insert a million rows using INSERT…SELECT and even one of those rows had an error, the entire transaction would fail. For selective exception handling, you had no choice but to manage the transaction procedurally.

Oracle 10G R2+ has a way to fortify DML statements with a "LOG ERRORS INTO" clause, which can deal with errors pretty much like a PL/SQL exception handler would. Read onto know how DML error logging works.

DML Error Logging: Building Blocks

The LOG ERRORS INTO clause gets suffixed to your regular DML statement  this way:

<DML statement>
LOG ERRORS INTO<error_logging_table> (<tag>)
REJECT LIMIT <reject limit value>;

Here’s a closer look at the components of DML Error Logging.

1. DML statement

The DML statement could be an INSERT, UPDATE, DELETE or MERGE.

2. Error logging table

The error logging table is designed to hold the errorred rows during the DML operation. The error table may be created in any of the two ways:

• Manually via the regular CREATE TABLE command. This table must follow certain guidelines as described in the next section "Error Logging Table Format".
• Automatically via call to the DBMS_ERRLOG package::

exec dbms_errlog.create_error_log( ‘dml_target_table’, ‘error_logging_table’);

3. Tag

This is an optional value that goes into each row inserted into the error table. In a batch transaction, it helps to tag both the target DML table rows and the error logging table rows with a batch_id for grouping and cross-validation.

4. Reject limit value

An integer that defines the maximum number of errors allowed in the DML statement — if the error count exceeds the reject limit value, the DML statement terminates and all changes made are rolled back.

The default reject limit value is 0.

If you want the DML to go through regardless of the count of errors, set REJECT LIMIT UNLIMITED.

Error Logging Table Format

The DML error logging table should follow these specifications.

1. Mandatory columns

The table must contain these columns:

DBMS_ERRLOG takes care of including the mandatory columns in the error logging table, in the case of automatic error logging table creation.

When creating the error logging table manually, you need to ensure the mandatory columns are present. These columns can be in any order, but they must appear at the start of the table.

2. Optional columns:

The error logging table can optionally contain a set of columns to hold data from the row that caused the error. The column names of the error logging table must match the column names of the DML target table. If there’s a mismatch, the column is not logged/value is ignored.

If DBMS_ERRORLOG is used to create the error logging table automatically, all the columns from the DML target table get included in the error logging table. In case of a table with several columns, this may be an unnecessary space overhead: in this scenario it’s best to create your own error logging table trimmed to only the essential columns.

With your own error logging table, you can choose which columns to include: in general, the unique index columns are enough. Position them in the table after all the mandatory columns.

3. Error Logging Table Name:

When creating the error logging table automatically, you can specify its name in the DBMS_ERRLOG parameters.

If the error logging table name is unspecified, it is given a default name of this form:

ERR$_ || first 25 characters of <dml_target_table>

Example:
dml_target_table name: EMP
error_logging_table name: ERR$_EMP

When creating the error logging table manually, you can give it any name that follows the standard Oracle table naming guidelines.

Let’s get on with the DML error logging code in action.

Case Study: Copying huge data volume across tables

We’ll build on the problem statement from the bulk DML with FORALL example:

The tables:

• PEOPLE — Source table with columns (ID, NAME, STATUS) from which data is to be read.
• CUSTOMER — Target table with columns (ID, NAME) to which data is to be inserted.

Table CUSTOMER has a unique constraint on columm ID.

Table PEOPLE has no unique constraint, and some data may have IDs repeated.

The requirement:

Copy data from table PEOPLE to CUSTOMER, where the status of the data in PEOPLE is NEW.

Record the errors if they occur during copying. Given the ID repetitions in table PEOPLE, errors are expected due to unique constraint violation in table CUSTOMER.

After copying, update the status column in table PEOPLE: set the successfully copied rows to DONE and the failed rows to ERROR.

The data before copying:

SQL> -- Source DML table PEOPLE
SQL> select * from people;

  ID NAME       STATUS
---- ---------- --------
   1 Jon        NEW
   2 Cersei     NEW
   2 Khal       NEW
   3 Gregor     NEW
   3 Sansa      NEW
   4 Arya       NEW
   5 Eddard     NEW

7 rows selected.

SQL> -- Target DML table CUSTOMER
SQL> select * from customer;

no rows selected

DML Error Logging: Solution Approach

1. Create an error logging table.

2. INSERT INTO CUSTOMER…SELECT FROM PEOPLE, logging errors into the error logging table.

3. UPDATE PEOPLE status from NEW to DONE/ERROR conditionally with a searched case.

You might ask why we didn’t consider direct-path INSERT (that is, INSERT with /*+ APPEND */ hint), which could go much faster for high data volumes than conventional-path INSERT.

The reason is that with DML error logging, direct-path INSERT will fail when a unique constraint violation occurs, while a conventional-path INSERT will log these errors to the error logging table. Our expected error in this case is the unique constraint violation on table CUSTOMER, so we need to stick to direct-path INSERT…

…unless we are game to try this clever hack.

DML Error Logging in Action

[BEFORE] Data:

SQL> -- Source DML table PEOPLE
SQL> select * from people;

  ID NAME       STATUS
---- ---------- --------
   1 Jon        NEW
   2 Cersei     NEW
   2 Khal       NEW
   3 Gregor     NEW
   3 Sansa      NEW
   4 Arya       NEW
   5 Eddard     NEW

7 rows selected.

SQL> -- Target DML table CUSTOMER
SQL> select * from customer;

no rows selected

Note that of the seven rows of source data, two IDs (2 and 3) are repeated — this means the INSERT should work for only five of the source rows, and two rows should end up in error.

1. Create the error logging table for DML target table

SQL> -- Create the error logging table for DML target table
SQL> exec dbms_errlog.create_error_log( 'CUSTOMER' );

PL/SQL procedure successfully completed.

Since we used the defaults for DBMS_ERRLOG, the error logging table would be named ERR$_CUSTOMER.

2. Verify the structure of the error logging table

SQL> -- Verify the structure of the automatically
SQL> -- created error_logging_table for CUSTOMER
SQL> desc err$_customer
 Name                    Null?    Type
 ----------------------- -------- ---------------
 ORA_ERR_NUMBER$                  NUMBER
 ORA_ERR_MESG$                    VARCHAR2(2000)
 ORA_ERR_ROWID$                   ROWID
 ORA_ERR_OPTYP$                   VARCHAR2(2)
 ORA_ERR_TAG$                     VARCHAR2(2000)
 ID                               VARCHAR2(4000)
 NAME                             VARCHAR2(4000)
 

We see that the table has the mandatory columns ending in $, and the other columns (ID, NAME) from the DML target table CUSTOMER.

3. Run the script with DML Error Logging

This script inserts into CUSTOMER via select from PEOPLE where status = NEW. It then updates PEOPLE status to DONE or ERROR, depending on whether a corresponding row exists in the error logging table or not.

SQL> declare
  2    -- Id for the run. Not really necessary in this example
  3    -- but useful if DML error logging is part of a stored
  4    -- proc and called periodically via batch program
  5    batch_id  number := 999;
  6  begin
  7
  8    -- Insert into CUSTOMER, logging errors along the way
  9    insert into customer (id, name)
 10      select p.id, p.name
 11      from people p
 12      where status = 'NEW'
 13    log errors into err$_customer (batch_id)
 14    reject limit unlimited;
 15
 16    -- Update status in PEOPLE after copying data
 17    -- DONE if successful, ERROR if failed
 18    update people p
 19    set p.status = case when exists
 20      (select 1
 21       from err$_customer e
 22       -- identify the copied row
 23       where      p.id = e.id and p.name = e.name
 24       and ora_err_tag$ = batch_id)
 25       then 'ERROR'
 26       else 'DONE'
 27       end
 28    where status = 'NEW';
 29  end;
 30  /

PL/SQL procedure successfully completed.

The script ran successfully. Let’s check the errors if any in ERR$_CUSTOMER, and the state of the data in the two tables PEOPLE and CUSTOMER.

4. Check failed rows in the error_logging_table

SQL> -- Values in the error_logging_table
SQL> select ora_err_number$ number$
  2       , ora_err_mesg$ mesg$
  3       , ora_err_optyp$ optyp$
  4       , ora_err_tag$ tag$
  5       , id
  6       , name
  7  from   err$_customer;

NUMBER$ MESG$                        OPTYP$ TAG$ ID NAME
------- ---------------------------- ------ ---- -- -----
      1 ORA-00001: unique constraint I      999  2  Khal
         (HR.CUSTOMER_UK) violated

      1 ORA-00001: unique constraint I      999  3  Sansa
         (HR.CUSTOMER_UK) violated

As expected, two rows have failed  due to unique constraint violation.

[AFTER] Data:

• CUSTOMER has 5 new unique rows
• ERR$_CUSTOMER has 2 error rows with error details
• PEOPLE status correctly indicates which rows were copied successfully, which were not (five DONE, two ERROR)

SQL> -- Source DML table PEOPLE
SQL> select * from people;

  ID NAME       STATUS
---- ---------- --------
   1 Jon        DONE
   2 Cersei     DONE
   2 Khal       ERROR
   3 Gregor     DONE
   3 Sansa      ERROR
   4 Arya       DONE
   5 Eddard     DONE

7 rows selected.

SQL>
SQL> -- Target DML table CUSTOMER
SQL> select * from customer;

  ID NAME
---- ----------
   1 Jon
   2 Cersei
   3 Gregor
   4 Arya
   5 Eddard

Which to Use: DML Error Logging or FORALL…SAVE EXCEPTIONS?

When both DML Error Logging and FORALL…SAVE EXCEPTIONS give you performance/exception handling benefits for transactions with large data volume, which is the preferred approach?

The question doesn’t have an all-encompassing “this approach” answer – if it did, Oracle would probably not have two ways of doing the same thing. You would need to analyse your requirements/benchmark for both approaches.

DML Error Logging can be very fast if you can use direct-path load (INSERT /*+ APPEND */) and if there are no indexes on the target table. If you must use conventional-path load (as in the cases study above), you may want to evaluate the approach more carefully.

FORALL…SAVE EXCEPTIONS is faster than row-by-row processing, but might not be as fast as direct-path load with DML Error Logging.

Even if you can use direct-path load, there are factors other than speed to consider:

• Locking: direct-path load locks the object while it’s in action; bulk DML does not.
• Free space utilization: direct-path load does not utilise existing free space, which bulk DML does.
• Transaction management: direct-path load needs a commit/rollback right  after completion*; bulk DML does not and can support further processing in the same transaction.

*Example: try selecting from a table immediately after direct-path load, without commit/rollback. You get:

ORA-12838: cannot read/modify an object after modifying it in parallel

Summary

This article explains the features of DML Error Logging, with a working example. It also gives guidelines for making a choice between DML Error Logging and bulk DML with SAVE EXCEPTIONS.

For Further Reading

• AskTom: How cool is this
• Oracle documentation

The post DML Error Logging: Supersized DML Operations Made Easy appeared first on Oratable.