Development

I’ve just released new version of my Simple Android Oracle Client.

New features:

• Supported Oracle versions: 11.2, 12.1, 12.2, 18, 19, 20.
• SQL Templates: now you can save and load own script templates
• Server output (dbms_output)
• Export results as JSON, CSV and HTML files (long tap on results)
• Copy results to the Clipboard as JSON or CSV

I use it just for basic troubleshooting and small fixes, but, please, let me know if you need anything else.
Screenshots:

• - Proof points, best practices, and guidelines for achieving peak performance for Oracle workloads with NVMe and Storage Class Memory.
• - Maintaining high availability through disasters with Oracle Extended RAC (demo)
• - Achieving amazing data reduction and storage efficiency for Oracle databases.

• Tree Calculations with Recursion (Recursive subquery factoring)
• Functions Defined Within SQL (Functions in the WITH clause)
• Answering Top-N Questions (Analytic ranking functions)
• Rolling Sums to Forecast Reaching Minimums (Analytic window clause, recursive subquery factoring, model clause)
• Merging Date Ranges (Row pattern matching MATCH_RECOGNIZE)
  

You may know that whenever you call PL/SQL functions from within SQL query, each query in the function is consistent to the SCN of its start and not to the SCN of parent query.

Simple example:

create table test as 
  select level a, level b from dual connect by level<=10;

create or replace function f1(a int) return int as
  res int;
begin
  select b into res 
  from test t 
  where t.a=f1.a;
  dbms_lock.sleep(1);
  return res;
end;
/

As you can see we created simple PL/SQL function that returns the result of the query select b from test where a=:input_var

But lets check what will it return if another session changes data in the table:

-- session 2:
begin
    for i in 1..30 loop
      update test set b=b+1;
      commit;
      dbms_lock.sleep(1);
    end loop;
end;
/
-- session 1:
SQL> select t.*, f1(a) func from test t;

         A          B       FUNC
---------- ---------- ----------
         1          1          1
         2          2          3
         3          3          5
         4          4          7
         5          5          9
         6          6         11
         7          7         13
         8          8         15
         9          9         17
        10         10         19

10 rows selected.

As you can see we got inconsistent results in the column FUNC, but we can easily fix it using OPERATORs:

CREATE OPERATOR f1_op
   BINDING (INT) 
   RETURN INT 
   USING F1;

Lets revert changes back and check our query with new operator now:

--session 1:
SQL> update test set b=a;

10 rows updated.

SQL> commit;

Commit complete.

-- session 2:
begin
    for i in 1..30 loop
      update test set b=b+1;
      commit;
      dbms_lock.sleep(1);
    end loop;
end;
/

-- session 1:
SQL> select t.*, f1(a) func, f1_op(a) op from test t;

         A          B       FUNC         OP
---------- ---------- ---------- ----------
         1          2          2          2
         2          3          5          3
         3          4          8          4
         4          5         11          5
         5          6         14          6
         6          7         17          7
         7          8         20          8
         8          9         23          9
         9         10         26         10
        10         11         29         11

10 rows selected.

As you can see values in the column OP are equal to the values of B, while, in turn, function F1 returns inconsistent values.

As of Oracle 19c OJPPD doesn’t support connect-by and TABLE():

OJPPD: OJPPD bypassed: query block contains START WITH/CONNECT BY.
OJPPD: OJPPD bypassed: View contains TABLE expression.

I forgot to share my files from presentations, so I’m going to keep them here:

• The innovations of technology and more specifically the Oracle Database, ORDS, and Oracle APEX. I want to thank all the developers and the people behind those products. They allow me to help other people with the tools they create and keep on learning about the new features that are released. 
• I want to thank the fantastic #orclapex (Oracle APEX) and Groundbreakers community. I believe we are a great example of how people help and support each other and are motivated to bring the technology further. Over time I got to know a lot of people, many I consider now friends.
• I want to thank you because you read this, show your appreciation and push me forward to share more. I'm passionate about the technology I use. I love helping people with my skill set of developing software and while I learn, share my knowledge through this blog. 

1. Distribute the traffic automatically over different machines. For example, when you use our APEX Office Print (AOP) Cloud you will actually hit our load balancer, behind the load balancer we have two to five different machines. It's not only to handle the large number of prints we get, but it also makes our lives easier when we want to upgrade without downtime. We upgrade one clone instance, and when done, new machines are brought online and old ones are shutdown. We patch our own service with zero downtime.
2. The Load Balancer has the SSL certificate and handles the HTTPS requests while the backend servers have HTTP.
3. On a Load Balancer, you have integrated health checks, so you can be warned when things go wrong, even when there's only one server behind the Load Balancer.