My 4 Oracle dreams

Oracle introduces some fixes and new features in each release, but there are still problems 

1. Oracle does not support transactional DDL: a transaction is considered closed when a CREATE, DROP, RENAME or ALTER command is executed, a hidden COMMIT is executed. If the transaction contains DML commands, Oracle commits the transaction as a whole, and then commits the DDL command as a separate transaction. This would be especially convenient for particularly complex DDL installation scripts

2.Problems with handling NULL in SQ

Note that NULL values ​​are not comparable to each other or to other non-NULL values... Oracle should treat the use of NULL in conditions after the WHERE clause of comparison operators as an error, any conditions like a = NULL, a < NULL.

SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME = NULL;

SELECT * FROM HR.EMPLOYEES WHERE LAST_NAME <> NULL;

Such queries never return anything, and people with less experience are not aware of it. In my opinion, an error should be generated: invalid use of operators with NULL, use any of operators IS NULL or IS NOT NULL

Empty string '' should be forbidden, because it is de facto a NULL value.

SELECT NVL('', 'I am NULL')  FROM DUAL;

Result: I am NULL

3,CLOB handling is the same as VARCHAR2 handling. 

The idea is to have built-in functions instead of functions in package DBMS_LOB. 

• LENGTH instead of DBMS_LOB.GETLEGTH
• SUBSTR instead of DBMS_LOB.SUBSTR 
• INSTR instead of DBMS_LOB.INSTR 
• To have the = and <> operators work like DBMS_LOB.COMPARE 

4.To have a built-in function to convert the first 4000 characters of type LONG to VARCHAR2 .

LONG and LONG RAW are both deprecated. Yet they still exist in the data dictionary and legacy systems. For this reason, it is still quite common to see questions in Oracle forums about querying and manipulating LONGs. These questions are prompted because the LONG datatype is extremely inflexible and is subject to a number of restrictions.

Mortgage repayments calculator in Oracle SQL - power of the MODEL clause

The post was inspired by late-night discussions about the sense of using mortgages. In the first approach, it was supposed to be a PL/SQL package, but I decided to make my life easier and wrote a query displaying the loan repayment plan for fixed and decreasing installments.

The query can be easily parameterized (parameters are dark green) by changing:

• loan amount 
• repayment period 
• annual interest rate 

The following assumptions were made: 

• the interest rate during loan repayment period is fixed
•  installments are payable in advance from the current day every month 
• we assume that the interest rate is the same within each month 

The query below, with a bit of imagination, can be expanded in an interesting way, e.g. by assuming different interest rates in subsequent years.

SELECT d + 1 AS "Repayments number",
          REPAYMENT_DATE AS "Repayments date",        
          ROUND (DECL_REPAYMENTS_DEBT_PART, 2) AS "Declining repayments debt part ",
          ROUND (DECL_REPAYMENTS_INTEREST_PART, 2) AS "Declining repayments interest part",
          ROUND (DECL_REPAYMENTS_DEBT_PART + DECL_REPAYMENTS_INTEREST_PART, 2) AS "Declining repayment amount",
          ROUND (DECL_REPAYMENTS_BALANCE, 2) AS "Declining repayment balance",
          ROUND (FIXED_REPAYMENTS_DEBT_PART, 2) AS "Fixed payments debt part",
          ROUND (FIXED_REPAYMENTS_INTEREST_PART, 2) AS "Fixed repayments interest part",
          ROUND (FIXED_REPAYMENTS_AMOUNT, 2) AS "Fixed repayments amount",
          ROUND (FIXED_REPAYMENTS_BALANCE, 2) AS "Fixed repayments balance"
     FROM (SELECT 1 FROM DUAL)
   MODEL
      DIMENSION BY (0 d)
      MEASURES (600000 AMOUNT_OF_CREDIT, 6 BANK_RATE, 240 NUMBER_REPAYMENTS,
      TRUNC (SYSDATE) REPAYMENT_DATE,
             0 DECL_REPAYMENTS_BALANCE,
             0 DECL_REPAYMENTS_DEBT_PART,
             0 DECL_REPAYMENTS_INTEREST_PART,            
             0 FIXED_REPAYMENTS_AMOUNT,
             0 FIXED_REPAYMENTS_DEBT_PART,
             0 FIXED_REPAYMENTS_INTEREST_PART,
             0 FIXED_REPAYMENTS_BALANCE
             )
      RULES
         ITERATE (10000) UNTIL (ITERATION_NUMBER = NUMBER_REPAYMENTS[0] -1)
         (              
         NUMBER_REPAYMENTS [ITERATION_NUMBER ] =  
               NVL (NUMBER_REPAYMENTS[ITERATION_NUMBER - 1],                                     NUMBER_REPAYMENTS[0]),
          BANK_RATE [ITERATION_NUMBER ] =
               NVL (BANK_RATE[ITERATION_NUMBER - 1], BANK_RATE[0]/1200),
         DECL_REPAYMENTS_DEBT_PART [ITERATION_NUMBER ] =
               AMOUNT_OF_CREDIT[0] / NUMBER_REPAYMENTS[0],
         REPAYMENT_DATE [ITERATION_NUMBER ] =
                ADD_MONTHS (REPAYMENT_DATE[0], ITERATION_NUMBER ),
         DECL_REPAYMENTS_BALANCE [ITERATION_NUMBER ] =
                 NVL (DECL_REPAYMENTS_BALANCE[ITERATION_NUMBER - 1],
                      AMOUNT_OF_CREDIT[0])
               - NVL (DECL_REPAYMENTS_DEBT_PART[ITERATION_NUMBER ], 0),
         DECL_REPAYMENTS_INTEREST_PART [ITERATION_NUMBER ] =
                 DECL_REPAYMENTS_BALANCE[ITERATION_NUMBER ]
               * BANK_RATE[ITERATION_NUMBER ]  ,                                       
         FIXED_REPAYMENTS_DEBT_PART [ITERATION_NUMBER ]              
         = AMOUNT_OF_CREDIT[0] *BANK_RATE [ITERATION_NUMBER ] *POWER( 1+BANK_RATE [ITERATION_NUMBER ], ITERATION_NUMBER )/
          ( POWER( 1+BANK_RATE [ITERATION_NUMBER ],          NUMBER_REPAYMENTS[ITERATION_NUMBER ]) -1  ),
           FIXED_REPAYMENTS_BALANCE [ITERATION_NUMBER ] =
                 NVL (FIXED_REPAYMENTS_BALANCE[ITERATION_NUMBER - 1],
                      AMOUNT_OF_CREDIT[0])
               - FIXED_REPAYMENTS_DEBT_PART[ITERATION_NUMBER ],
           FIXED_REPAYMENTS_INTEREST_PART [ITERATION_NUMBER ] =
                 FIXED_REPAYMENTS_BALANCE[ITERATION_NUMBER ]
               * BANK_RATE[ITERATION_NUMBER ],
           FIXED_REPAYMENTS_AMOUNT[ITERATION_NUMBER ] = FIXED_REPAYMENTS_DEBT_PART[ITERATION_NUMBER ] + FIXED_REPAYMENTS_INTEREST_PART[ITERATION_NUMBER ]       
          )

Calculating Euler's number in Oracle SQL

 

I use the MODEL clause again to calculate the Euler's number.
Euler proved that e is the sum of the infinite series e = 1/0!  +1/1!  +1/2! +1/3!  +1/4! ........
There is one dimension - the iteration number.
The measures are:

• E_EXP -the Euler's number calculated from the EXP function
• FACTORIAL - value of the factorial function
• E_COMPUTED - our estimate of the number e
• DIFF -the difference between EXP(1) and the current estimate

My proposal is to change the precision in the stop condition

- the second parameter of the POWER function: 

UNTIL (DIFF[ITERATION_NUMBER] < POWER(10, -30))

----------------------------------------------------------- 

SELECT
       d              "Iteration number",
       FACTORIAL      "Factorial",
       E_EXP          "Euler's number from EXP",
       E_COMPUTED     "Euler's number calculated",
       DIFF           "Difference"
  FROM (SELECT 1 d FROM DUAL)
MODEL
    DIMENSION BY (0 d)
    MEASURES (CAST (0  AS NUMBER) AS E_EXP,
              CAST (1 AS NUMBER) AS FACTORIAL,
              CAST (1 AS NUMBER) AS E_COMPUTED,
              CAST (0 AS NUMBER) AS DIFF)
    RULES
    ITERATE (1000) UNTIL (DIFF[ITERATION_NUMBER] < POWER(10, -30))
    (
        FACTORIAL [ITERATION_NUMBER] =
            CASE
                WHEN ITERATION_NUMBER=0  THEN 1
                ELSE FACTORIAL[ITERATION_NUMBER- 1] * (ITERATION_NUMBER)
            END,
        E_EXP [ITERATION_NUMBER] =  
          CASE
            WHEN ITERATION_NUMBER= 0 THEN    
                EXP(1)
            WHEN ITERATION_NUMBER> 0 THEN
                E_EXP[ITERATION_NUMBER-1]
            END,
        E_COMPUTED [ITERATION_NUMBER] =
            CASE ITERATION_NUMBER
                WHEN 0 THEN 1
                ELSE
                      E_COMPUTED[ITERATION_NUMBER- 1]
                    + 1 / FACTORIAL[ITERATION_NUMBER]
            END,
        DIFF [ITERATION_NUMBER] =
            ABS (E_COMPUTED[ITERATION_NUMBER] - E_EXP[ITERATION_NUMBER]))

Recursive algorithms in SQL

Possible?? Yes, just like the model clause..  

Below, the two queries contain algorithms written in a recursive version:

•   Fibonacci sequence 
•   Factorial
•   Euclid's algorithm for determining the greatest common divisor (GCD)

Two techniques are presented:  

• iterative models - are perfect for implementing unary recursive functions, and their notation is almost intuitive. Unfortunately, with a larger number of parameters, problems arise with cyclical cell enumeration 
• non-iterative models - formulas are a bit more complicated, but multi-argument functions can be handled
  

SELECT                                                    
      d  , f AS FIBBONACCI,
      S AS FACTORIAL
  FROM   (SELECT   0 d  FROM DUAL)
MODEL
   DIMENSION BY (0 d)
   MEASURES (0 f, 0 s)
   RULES
      ITERATE (100) UNTIL (ITERATION_NUMBER = 50)
       (f [ITERATION_NUMBER] =
                            CASE ITERATION_NUMBER
                                WHEN 0 THEN 0
                                WHEN 1 THEN 1
                              ELSE
                                f[ITERATION_NUMBER - 2] + f[ITERATION_NUMBER - 1]
                            END,
      s [ITERATION_NUMBER] =
                  CASE ITERATION_NUMBER
                    WHEN 0 THEN 1
                    ELSE           
                    ITERATION_NUMBER * s[ITERATION_NUMBER - 1]
                  END)


The example below can be simplified a bit :)

SELECT   L1, L2, GCD
  FROM   (SELECT   L1, l2
            FROM   (    SELECT   LEVEL - 1 L1
                          FROM   DUAL
                    CONNECT BY   LEVEL <= 20),
                   (    SELECT   LEVEL - 1 L2
                          FROM   DUAL
                          CONNECT BY   LEVEL <= 20))
MODEL
   DIMENSION BY (L1, L2)
   MEASURES (0 GCD)
   RULES SEQUENTIAL ORDER
   (GCD [ANY, ANY] =
            CASE
               WHEN CV (l2) = 0  AND CV (L1) =0
               THEN
                  1
               WHEN CV (l2) = 0  OR CV (L1) =0
               THEN
                  GREATEST(CV (l1), CV(l2))
               WHEN CV (l2) <= CV (l1)
               THEN
                  GCD[CV (l2), MOD (CV (l1), CV (l2))]
            END,
      GCD[ANY, ANY] =
            CASE
               WHEN CV (l2) > CV (l1) THEN
                        GCD[CV (l2), CV (l1)]
               ELSE GCD[CV (l1), CV (l2)]
            END)