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)

 

Obsługa XML - część 3

Cały czas  działamy na tabeli  TBL_XML - opisanej tutaj. Analizowany XML  zawiera powtarzający się element pozycja, a w ramach tego elementu zagnieżdżone są pola:

• nazwa_waluty
• przelicznik
• kod_waluty
• kurs sredni

 
    dolar amerykański

1 USD 4,2396

Poznamy teraz konstrukcji, która w w elegancki sposób będzie pokazywać elementy i atrybuty z powtarzających się elementów

SELECT   EXTRACTVALUE( x.cxml, '//data_publikacji') data_pubblikacji ,
   EXTRACTVALUE( x.cxml, '//numer_tabeli') nr_tabeli ,
  xt.*
FROM   TBL_XML x,
       XMLTABLE('//pozycja'
         PASSING x.cxml --nazwa_kolumny XML
         COLUMNS
           NAZWA_WALUTY     VARCHAR2(30 CHAR)  PATH 'nazwa_waluty',
           KOD_WALUTY     VARCHAR2(3 CHAR)  PATH '//*/kod_waluty',
           PRZELICZNIK    VARCHAR2(5 CHAR) PATH '//przelicznik',
           KURS_DZIENNY    VARCHAR2(10 CHAR) PATH 'kurs_sredni'
         ) xt;
  
Wynik: Zauważmy - wszystkie pola sa tekstowe !!!!

Jak powyższa konstrukcja działa?

1.  Tworzona jest w pamięci tabela aliasowana przez xt
2. Każdy wiersz tabeli xt to powtarzający się element  pozycja, opisujący parametry waluty
3. W nawiasie po słowie kluczowym XMLTABLE podaje się ścieżkę do analizowanego elementu
4. Po słowie kluczowym PASSING podaje się nazwę pola zawierającego XML
5. Po słowie kluczowym COLUMNS podaje się  definicje i mapowania kolumn. Mapowanie występuje po słowie kluczowym PATH  i może być, jak powyżej, wykonane na różne sposoby
6. Wyrażenie xt.* zwraca kolumny zdefiniowane w mapowaniu

Uwagi:

1. W sekcji COLUMNS  najlepiej jest robić mapowania na typ znakowy lub na typ numeryczny(ale tylko dla liczb całkowitych). W powyższym przykładzie  mapowanie typ NUMBER(4) zadziałałoby dla pola PRZELICZNIK.  Dla liczb zmiennopozycyjnych (jak pole w  powyższym przypadku KURS_DZIENNY) i dat, automatyczna konwersja jest źródłem problemów.  Właściwą konwersję najlepiej przeprowadzić  w głównym zapytaniu, a nie na poziomie mapowania  
2. Jeśli zdefiniujemy  typ o niewystarczającej długości, to podczas mapowania teksty zostaną obcięte
3. Jedli w danym elemencie nie wystąpi ścieżka z mapowania, wtedy dla odpowiadającej temu mapowaniu kolumny  zwracana jest wartość NULL
4. W przypadku zmiany kolumny lub dodania nowych pól, modyfikacja istniejącego zapytania jest banalna