Package Initialization – Changes between ORACLE 11g and ORACLE 12c

The package initialization block

When a session references a package item, the package is instantiated and initialized. Initializing means:

  • assigning values to public constants (constants defined in the package specification or body outside of packaged procedures and functions)
  • assigning values to public variables if values are specified in variable declaration
  • execute the initial block of the package body  
CREATE OR REPLACE PACKAGE test_init IS
   PROCEDURE show_values;
END test_init;
/

CREATE OR REPLACE PACKAGE BODY test_init IS
   g_before_error VARCHAR2(30) := 'NOT INITIALIZED';
   g_after_error  VARCHAR2(30) := 'NOT INITIALIZED';
   g_test         VARCHAR2(5);

   PROCEDURE show_values IS
   BEGIN
      sys.dbms_output.put_line('Before : ' || g_before_error);
      sys.dbms_output.put_line('After  : ' || g_after_error);
   END show_values;

-- INIT BLOCK
BEGIN
   g_before_error := 'INITIALIZED';
   g_test         := 'This will raise an error';
   g_after_error  := 'INITIALIZED';
END test_init;
/

What happens if the initialization block raises an error?

Now basically you get an error – as we would expect.

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So far so good – but now, a significant thing has changed between ORACLE 11 and ORACLE 12.


What happens if we call the show_values procedure again?

ORACLE 12 (same in 18)

BEGIN
   test_init.show_values();
END;
/

Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TEST.TEST_INIT", line 13
ORA-06512: at line 2
06502. 00000 - "PL/SQL: numeric or value error%s"

So in ORACLE 12 the initialization block is executed again. This indicates, that the package was not instantiated due to the error in the initialization block. 

ORACLE 11

BEGIN
   test_init.show_values();
END;
/

PL/SQL procedure successfully completed.

Before : INITIALIZED
After  : NOT INITIALIZED

In ORACLE 11 the packages has been instantiated even if the initialization run into an error. So a second call will not run the initialization again…

As you can see the second global variable (g_after_error) has not been changed by the initialization block whereas the first was.

 


What does this mean?

In my opinion the current (12c/18c) behaviour is much more consistent…but this change in behaviour can (and will) affect applications that did not care too much on whether the initialization run into an error in Oracle 11g – as a secondary call to a package item that does not depend on a correct initialization of the package worked..

 

 

 

ORACLE 18c – Qualified Expressions

Tags

, ,

Qualified expressions improve readability and maintainability of our PL/SQL code. 

While initialising nested tables was quite easy in the past, initialising associative arrays was quite a pain, if it was not done using a query. 

The following examples show how qualified expressions simplify the coding using an associative array, where the key is the name of a city and the content is the population.

Before 18c

DECLARE
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
   city_population city_population_type;
BEGIN
   <<Init>>
   BEGIN
      city_population('Zürich')   := 400000;
      city_population('Genf')     := 195000;
      city_population('Basel')    := 175000;
      city_population('Bern')     := 140000;
      city_population('Lausanne') := 135000;
   END Init;
 
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

18c using qualified expression

DECLARE   
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);   
   city_population city_population_type;
BEGIN   
   <<Init>>   
   BEGIN       
      city_population := city_population_type('Zürich'   => 400000
                                             ,'Genf'     => 195000
                                             ,'Basel'    => 175000
                                             ,'Bern'     => 140000
                                             ,'Lausanne' => 135000);
   END Init;      
  
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

Qualified expression can also be used when working with record structures (and of course also when record structures are part of a associative array).


Record structures with qualified expressions

DECLARE
   TYPE emp_type IS RECORD(empno    emp.empno%type
                          ,ename    emp.ename%type
                          ,job      emp.job%type
                          ,mgr      emp.mgr%type 
                          ,hiredate emp.hiredate%type
                          ,sal      emp.sal%type
                          ,comm     emp.comm%type 
                          ,deptno   emp.deptno%type);

   TYPE emp_tab_type IS TABLE OF emp_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_type(ename => 'Smith'
                                          , Job => 'Clerk')
                          ,7782 => emp_type(ename => 'Clark'
                                          , Job => 'Manager'
                                          , Sal => 3000)
                          ,7902 => emp_type(ename => 'Ford'
                                          , Sal => 3000
                                          , Deptno => 20));
 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Now…normally we would make use of %ROWTYPE when we need a record structure that reflects a table row…

DECLARE
   TYPE emp_tab_type IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => ?(ename => 'Smith'
                                   , Job => 'Clerk')
                          ,7782 => ?(ename => 'Clark'
                                   , Job => 'Manager'
                                   , Sal => 3000)
                          ,7902 => ?(ename => 'Ford'
                                   , Sal => 3000
                                   , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Hmm….what “constructor” should we use if the associative array holds a rowtype? But…perhaps a subtype could help.

DECLARE
   SUBTYPE emp_row_type IS emp%ROWTYPE;
   TYPE emp_tab_type IS TABLE OF emp_row_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_row_type(ename => 'Smith'
                                              , Job => 'Clerk')
                          ,7782 => emp_row_type(ename => 'Clark'
                                              , Job => 'Manager'
                                              , Sal => 3000)
                          ,7902 => emp_row_type(ename => 'Ford'
                                              , Sal => 3000
                                              , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

ORA-06550: line 6, column 36: 
PLS-00222: no function with name 'EMP_ROW_TYPE' exists in this scope

Too bad…that would have been nice too.


Conclusion

Qualified expression will help us to write code that is more readable and easier. It’s a nice new feature that I will use for sure.

And perhaps, someday, the %ROWTYPE thing will be possible too.

 

 

 

ORACLE 18c – ALTER SEQUENCE RESTART

Tags

,

How many times have we seen the request to “set back” or “set forward” a sequence to its original value. Reasons for doing this have been e.g. having a sequence, that start every time period (e.g. month) new or a heavy import has been done and the sequence should now be set to the max-value+1 of the rows inserted to the table.

There have been various ways to solve this kind of issues.

  • To set the sequence back normally we dropped and recreated it (and hopefully did not forget the grants).
  • To set the sequence to a new high value, we just built a loop or changed the stepwidth to get the sequence to the desired value.

All those workarounds are no longer needed with ORACLE 18c as there is a new option that we can use to handle both cases.


Test 1:

After having consumed 500 number from the sequence, we would like to set it back to it’s original value (1).

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
      1

So this works as expected.


Test 2:

After having consumed 500 number from the sequence, we would like to set it forward to 1500.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
   1500

Excellent….


Test 3:

I expect the ALTER SEQUENCE statement to commit open transactions…but just to be sure.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

CREATE TABLE t (x NUMBER);

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501

INSERT INTO t VALUES (1);
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

ROLLBACK;

SELECT *
 FROM t;

X
-
1

ok, would have been nice if not, but it’s what was expected.


Conclusion

Still this is a nice feature available in ORACLE 18c but not in the documentation (SQL 

There you will find:

To restart the sequence at a different number, you must drop and re-create it.

and

You will also miss the RESTART option in the syntax diagram of the ALTER SEQUENCE command…

MERGE – DELETE

Or:  “why we should read the manual from time to time…and not blindly follow our intuition…”.

Basically I thought that I know the MERGE command by heart. So, no big deal to write a sql statement that handles changes in the source table and moves them to a target table.

Even removing rows from target, when they are logically deleted in the source should not be a problem as there is an update delete clause in the merge command. So everything went fine…but somehow it did not work the way I expected the merge command to work.


Test case:

CREATE TABLE empl (empno     NUMBER
                  ,ename     VARCHAR(30 CHAR)
                  ,hiredate  DATE
                  ,leavedate DATE)
/

CREATE TABLE empl_current (empno  NUMBER
                          ,ename  VARCHAR2(30 CHAR))
/

INSERT INTO empl VALUES (1,'MUELLER',DATE '2017-01-01', NULL);
INSERT INTO empl VALUES (2,'MEIER'  ,DATE '2017-02-01', NULL);
INSERT INTO empl VALUES (3,'HUBER'  ,DATE '2017-03-01', NULL);
INSERT INTO empl VALUES (4,'HOFER'  ,DATE '2017-04-01', NULL);
INSERT INTO empl VALUES (5,'SCHMIDT',DATE '2017-05-01', DATE '2017-05-31');

COMMIT;

Step 1:

Write a merge that transfers all employees from empl to empl_current if they have not left the company

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
              FROM empl
             WHERE leavedate IS NULL
                OR leavedate > SYSDATE) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename;

Checking empl_current should now show employees 1 to 4 but not 5 as 5 has left the company already.

SELECT * FROM empl_current; -- should show 1 - 4

EMPNO  ENAME
------ ---------------------
     1 MUELLER
     2 MEIER
     3 HUBER
     4 HOFER

Ok, all fine so far.


Step 2:

Now doing some updates on the source table and check whether the MERGE does what it should do.

UPDATE empl
   SET ename = 'Meier'
 WHERE empno = 2; 

UPDATE empl
   SET leavedate = DATE '2017-06-30'
 WHERE empno = 3; 

COMMIT;

So the previous merge would now handle the changes on employee 2 but not the one of employee 3 as I do not have a delete clause yet.

So I need to add a delete clause to the merge command.

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl
             WHERE leavedate IS NULL
                OR leavedate > SYSDATE) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL
                              AND leavedate <= sysdate;

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier     ---> changed 
     3 HUBER     ---> still here (?)
     4 HOFER

Now, the reason for HUBER (3) still being here is, that it cannot be deleted, as it is not a match as the row is not part of the source dataset due to the filter I applied….


Step 3:

The source data set should also contain rows, that should not be inserted but eventually deleted.

So changing the source filter.

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl             
           --  WHERE leavedate IS NULL    /* remove this line */
           --     OR leavedate > SYSDATE  /* and this too */
           ) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL
                               AND leavedate <= sysdate;


SELECT * FROM empl_current; 


EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER
     5 SCHMIDT   ---> ???

Ok, Huber has gone but we have Schmidt now in the target table despite the fact that he has left the company…so what we need to add  is a filter on the “NOT MATCHED” insert statement to not add rows that we are not (or no longer) interested in…


Step 4:

Improve the filter on the insert statement in the “MATCHED” case. So no rows get inserted that we are not interested in.

MERGE INTO empl_current tar     
     USING (SELECT empno
                  ,ename
                  ,leavedate
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                       WHERE leavedate IS NULL
                          OR leavedate > SYSDATE
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE leavedate IS NOT NULL 
                              AND leavedate <= SYSDATE;

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER

This is, where I placed a trap…

I came this far with my first attempt … but I added an trap by just following my intuition and not reading the manual.

The reason for getting trapped was, that I asked myself the question:

“Why should I update a row, that I am going to delete anyway?” I think UPDATE and DELETE should exclude each other so we are only updating when not deleting and only deleting when not updating….

Thefore my MERGE looked like this:

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                       WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                       WHERE delete_flag = 'N'
                      DELETE WHERE delete_flag = 'Y';

I added a delete flag, which made it easier to insert only unmatched rows that have not yet been deleted and it was also not to complicated to distinguish between update an delete too.

So add another testcase to get rid of  empno 4…

UPDATE empl
   SET leavedate = DATE '2017-08-31'
 WHERE empno = 4; 

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                      WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                       WHERE delete_flag = 'N'
                      DELETE WHERE delete_flag = 'Y';

SELECT * FROM empl_current;                       

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier
     4 HOFER  --- ????

Hmmm…

So, why is employee 4 still in the target table? I checked my statement over and over again and finally had a look to the documentation which says:

ORACLE Database SQL Language Reference

merge_update_clause

The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.

Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SETWHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

 

That was somehow surprising…but, after removing the WHERE clause on the update, everything worked again…

MERGE INTO empl_current tar
     USING (SELECT empno
                  ,ename
                  ,CASE WHEN leavedate <= SYSDATE
                        THEN 'Y'
                        ELSE 'N'
                   END AS delete_flag
              FROM empl) src ON (tar.empno = src.empno)
WHEN NOT MATCHED THEN INSERT (empno    ,ename)
                      VALUES (src.empno, src.ename)
                      WHERE delete_flag = 'N'
WHEN     MATCHED THEN UPDATE SET tar.ename = src.ename
                      DELETE WHERE delete_flag = 'Y';

SELECT * FROM empl_current; 

EMPNO ENAME
------ ---------------------
     1 MUELLER
     2 Meier

Conclusion

Testing is everything … from time to time you stumle upon things you thought you know by heart … and then you will be thankful for the great documentation job oracle did.

 

ORACLE 12.2 – External Table Enhancements – Overriding Parameters

Another cool feature of 12.2 is the possibility to override defined parametrs of an external table when accessing it. The following properties may be overwritten:

  • default directory
  • location (file-name)
  • access parameters like badfile, logfile and discardfile
  • reject limit

This enables us to change e.g. the name of the file to be read without having to do an ALTER TABLE statement (and therefore doing an impicit commit). Furthermore the modification done this way is effective for the current statement only.

So imagine we have an external table defined like this:

CREATE TABLE employee_import (    
   department_id  NUMBER(2,0)  
  ,first_name     VARCHAR2(20 BYTE)  
  ,last_name      VARCHAR2(20 BYTE)  
  ,email          VARCHAR2(25 BYTE)  
  ,phone_number   VARCHAR2(20 BYTE)  
  ,hire_date      DATE
  ,job_id         VARCHAR2(10 BYTE)
  ,salary         NUMBER(8,2)  
  ,commission_pct NUMBER(2,2)
) 
ORGANIZATION EXTERNAL (    
   TYPE ORACLE_LOADER   
   DEFAULT DIRECTORY LTROT_VM_DIR   
   ACCESS PARAMETERS (       
      RECORDS DELIMITED BY NEWLINE      
      LOGFILE  LTROT_VM_DIR:'emp20_wrong.log'      
      BADFILE  LTROT_VM_DIR:'emp20_wrong.bad'      
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL (         
         department_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date date 'DD.MM.YYYY'
        ,job_id
        ,salary
        ,commission_pct
      )
   )
   LOCATION('emp20.txt')
)
REJECT LIMIT UNLIMITED ;

Now, if we would like to access a different os file (having the same structure) than “emp20.txt” we can easily achieve this with the following query:

SELECT *
 FROM employee_import 
 EXTERNAL MODIFY (LOCATION ('em10.txt'));

The parameters that can be changed this way are:

  • Default Directory
  • Location
  • Access Parameters
  • Reject Limit

If the external is partitioned only table level (not partition level) clauses may be overwritten.


Use-Case

Now, looking at the list of parameters that can be changed it should be possible to change the name of the bad-/log-file when loading the emp-os file and afterwards having an external table where i can read the bad-/log-file passing the names that i used before….

Something like this:

Generic External Table to read log/bad files

CREATE TABLE generic_file (
    message VARCHAR2(4000 BYTE)

ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
   DEFAULT DIRECTORY LTROT_VM_DIR
   ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      NOLOGFILE
      NOBADFILE
      NODISCARDFILE
      FIELDS (
         message CHAR(4000)
      )
   )
   LOCATION('emp20.bad')
)
REJECT LIMIT UNLIMITED;

So when I read the emp os-file I alter the name of the bad and the log file using the external modify clause:

SELECT * 
  FROM employee_import
EXTERNAL MODIFY (
   ACCESS PARAMETERS (
      BADFILE 'abadfile.bad'
      LOGFILE 'alogfile.log'));

And after that I should be able to access the bad-/log-file using my generic external table.

SELECT * 
  FROM generic_file 
EXTERNAL MODIFY (
   LOCATION('abadfile.bad'));

Unfortunately I cannot see any data, as the log-/bad-files are locked and empty (Linux Environment) so I need to commit first to release the locks. Afterwards I can read the log-file as well as the bad-file.

SELECT * 
 FROM generic_file 
EXTERNAL MODIFY (
 LOCATION('alogfile.log'))
 WHERE message IS NOT NULL;

MESSAGE
---------------------------------------------------------------------------
 LOG file opened at 07/26/17 07:25:18
Field Definitions for table EMPLOYEE_IMPORT
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Rows with all null fields are accepted
 Fields in Data Source:
 DEPARTMENT_ID CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 FIRST_NAME CHAR (255)
 Terminated by ","
 Enclosed by """ and """
 Trim whitespace same as SQL Loader
 LAST_NAME CHAR (255)
 Terminated by ","
...

Conclusion

Nice!

NULLIF – A tiny, powerfull and underrated SQL Function

Tags

,

NULLIF has been introducde quite a while ago (Oracle 9) but still this function is not well known to the community.

SYNTAX : NULLIF(expr1,expr2)

Nullif compares expr1 and expr2. If they are equal, then NULL is returned otherwise expr1. We cannot specify the literal NULL for expr1.

So where can this function be used?

Eliminiate Division by Zero:

WITH sales (prod, thisyear, lastyear)
        AS (SELECT 'Monitor' ,  50, 25 FROM dual UNION ALL
            SELECT 'Keyboard', 125,  0 FROM dual UNION ALL
            SELECT 'Mouse'   ,  35, 40 FROM dual UNION ALL 
            SELECT 'Desktop' ,   0, 25 FROM dual UNION ALL
            SELECT 'Laptop'  ,  10,  3 FROM dual)
SELECT prod, thisYear, lastYear
     , ROUND(thisYear * 100 / NULLIF(lastYear,0),2) AS SalesPerc
  FROM sales
/

PROD       THISYEAR   LASTYEAR  SALESPERC
-------- ---------- ---------- ----------
Monitor          50         25        200
Keyboard        125          0
Mouse            35         40       87.5
Desktop           0         25          0
Laptop           10          3     333.33

 

Remove not needed concatenation seperators:

Add first letter of the middle name followed by a dot if a middle name exists.

WITH pers (firstname, middle, lastname)
       AS (SELECT 'Kurt'  ,'Heinrich','Meier' FROM dual UNION ALL
           SELECT 'Hubert',NULL      ,'Huber' FROM dual)
SELECT firstname || NULLIF(' '||SUBSTR(middle,1,1) || '.',' .') 
    || ' ' || lastname AS fullname
  FROM pers
/    

FULLNAME
---------------
Kurt H. Meier
Hubert Huber

 


Suppress values above (or below) a given margin:

Do not show salaries greater/equal 3000.

SELECT ename, NULLIF(sal,GREATEST(sal,3000)) AS sal
  FROM emp
/    

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT
KING
TURNER           1500
ADAMS            1100
JAMES             950
FORD
MILLER           1300

 

As you can see there are multiple opportunities to make use of this smart little function.

DBMS_APPLICATION_INFO

Ever asked yourselves

  • What progress has my batch process done so far? 
  • Which iteration is currently processed?
  • How much longer will the job run?
  • What step is the batch process currently processing?

These and other questions could be easily answered if more developers made use of the DBMS_APPLICATION_INFO package. 

DBMS_APPLICATION_INFO may add information to V$SESSION and V$SESSION_LONGOPS views. The package is writing its data to memory and therefore the overhead produced by using this package is insignificant.

 


SET_MODULE

When a batch is started it should subscribe itself using the SET_MODULE procedure of DBMS_APPLICATION_INFO. Along with the name of the module the initial action is registered.

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Module(module_name => 'MyBatch'
                                       ,action_name => 'Initialize');
   ...
END;
/

The name of the module is limited to 48 bytes (longer values will be truncated), the name of the action to 32 bytes.


SET_ACTION

This procedure is used to provide the current state of the module that has been registered using the Set_Module procedure. 

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Action(action_name => 'Closing Orders');
   ...
END;
/

SET_CLIENT_INFO

The “Client Information” may be used to provide additional information.

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Action(action_name => 'Closing Orders');
   SYS.DBMS_APPLICATION_INFO.Set_Client_Info(Client_Info => 'Iteration 3');
 ...
END;
/

The client_info is limited to 64 bytes.

Querying V$SESSION View

V$SESSION may be queried from a different session to see what the batch process is doing.

select sid, serial#, username, module, action, client_info
 from v$session
 where username = 'TEST'
/

  SID SERIAL# USERNAME MODULE         ACTION          CLIENT_INFO 
----- ------- -------- -------------- --------------- --------------
   38    6914 TEST     SQL Developer 
  269   51268 TEST     SQL Developer  Closing Orders  Iteration 3

SET_SESSION_LONGOPS

This procedure call is reflected in the V$SESSION_LONGOPS view and may be used to provide information on the progress of a task.

DECLARE
   l_rindex     BINARY_INTEGER;
   l_slno       BINARY_INTEGER;
   l_iterations PLS_INTEGER := 10;
BEGIN
   l_rindex := SYS.DBMS_APPLICATION_INFO.set_session_longops_nohint;

   FOR i in 1..l_iterations
   LOOP
      SYS.DBMS_APPLICATION_INFO.set_session_longops(rindex      => l_rindex
                                                   ,slno        => l_slno
                                                   ,op_name     => 'TEST'
                                                   ,sofar       => i
                                                   ,totalwork   => l_iterations
                                                   ,units       => 'Iterations'
                                                   );
      SYS.DBMS_LOCK.Sleep(3);
   END LOOP;
END;
/

 

Querying the longops view:

SELECT opname
      ,sofar
      ,totalwork
      ,units
  FROM v$session_longops
/

OPNAME       SOFAR TOTALWORK  UNITS 
------- ---------- ---------- --------------------------------
TEST             2         10 Iterations

Conclusion

If you have not yet used DBMS_APPLICATION_INFO in any of your processes it is worth looking at this package. The information provided when using this package for long running processes might keep you away from killing a process just because you *think* it should have ended long time ago but you have no idea how far the process is….

 

ORACLE 12.2 – Enhanced Whitelist (ACCESSIBLE BY)

With Version 12.1 ORACLE provided a new way to determine that a program unit may only be called by other program units which are part of a whilelist. This new feature was implemented using the ACCESSIBLE BY clause. ACCESSIBLE BY was declared on top level (e.g. package specification).

ORACLE 12.2 provides a higher granularity for the whitelist. It is now possible to define the access on a packaged procedure/function too.

Code Example:

CREATE OR REPLACE PACKAGE emp_tapi IS
   TYPE emp_tapi_rect IS RECORD (ename    emp.ename%TYPE
                               , comm     emp.comm%TYPE
                               , hiredate emp.hiredate%TYPE
                               , empno    emp.empno%TYPE
                               , mgr      emp.mgr%TYPE
                               , job      emp.job%TYPE
                               , deptno   emp.deptno%TYPE
                               , sal      emp.sal%TYPE);

   TYPE emp_tapi_tabt IS TABLE OF emp_tapi_rect;

-- ========================================================================
-- insert
-- ========================================================================
   PROCEDURE ins (emp_tapi_rec IN OUT NOCOPY emp_tapi_rect)
      ACCESSIBLE BY (PACKAGE emp_maint_up);

-- ========================================================================
-- update
-- ========================================================================
   PROCEDURE upd (emp_tapi_rec IN emp_tapi_rect) 
      ACCESSIBLE BY (PACKAGE emp_maint_up);

-- ========================================================================
-- delete
-- ========================================================================
   PROCEDURE del (p_empno IN emp.empno%type)
      ACCESSIBLE BY (TRIGGER emp_io_d_t);
END emp_tapi;
/

CREATE OR REPLACE PACKAGE BODY emp_tapi IS
-- ========================================================================
-- insert
-- ========================================================================
   PROCEDURE ins (emp_tapi_rec IN OUT NOCOPY emp_tapi_rect)
      ACCESSIBLE BY (PACKAGE emp_maint_up)
   IS
   BEGIN
      ...

As you can see, ACCESSIBLE BY clause has to be specified in specification and body, if we want to define the whitelist on subprograms. 


Conclusion

The ACCESSIBLE BY clause is very useful (e.g. refactoring) and the higher granularity will help to make it even better.

ORACLE 12.2 – Coverage Pragma and DBMS_PLSQL_CODE_COVERAGE

The new package DBMS_PLSQL_CODE_COVERAGE helps use to collect data on how well our testcases exercise our code.

Basic block coverage

The DBMS_PLSQL_CODE_COVERAGE package collects data at the basic block level. A basic block is a segment of code with no branches, the basic block has a single entry and a single exit point.

Preparation

The code coverage is collected in 3 tables (DBMSPCC_RUNS, DBMSPCC_UNITS, DBMSPCC_BLOCKS). Those tables may be created using the create_coverage_tables procedure of dbms_plsql_code_coverage. The procedure has a single boolean parameter (force_it) which would drop/create the tables if they already exist. The default value of the parameter is false. If this parameter remains false and the tables alreday exist, the execution of the procedure will raise an exception (dbms_plsql_code_coverage.coverage_error).

Run the code coverage analysis

To run the code coverage analysis we call the start_coverage function which accepts a run_comment to be able to identify your analysis run later and returns a unique run_id. Stoping the analysis is done using the stop_coverage procedure which is parameterless. 

DECLARE
   l_run_id pls_integer;
BEGIN
   l_run_id := dbms_plsql_code_coverage.start_coverage('COVERAGETEST1');
   coveragetest1(100);
   sys.dbms_plsql_code_coverage.stop_coverage();
END;
/

Analyse the code coverage results

The data collected by the code coverage package may be analysed using a simple query joining the 3 tables (DBMSPCC_RUNS, DBMPSPCC_UNITS, DBMSPCC_BLOCKS).

SELECT r.run_comment
      ,u.owner as object_owner
      ,u.name as object_name
      ,u.type as object_type
      ,ROUND((COUNT(CASE b.covered WHEN 1 THEN 1 ELSE NULL END) 
            + COUNT(CASE WHEN     b.covered = 0 
                              AND b.not_feasible = 1 THEN 1 ELSE NULL END)) 
         / NULLIF(COUNT(*),0) * 100,2) AS pct_covered
  FROM dbmspcc_runs   r
  JOIN dbmspcc_units  u ON (u.run_id = r.run_id)
  JOIN dbmspcc_blocks b ON (    b.object_id = u.object_id
                            AND b.run_id = r.run_id)
 GROUP BY r.run_comment
         ,u.owner
         ,u.name
         ,u.type;

RUN_COMMENT    OBJECT_OWNER  OBJECT_NAME    OBJECT_TYPE  PCT_COVERED
-------------  ------------  -------------  -----------  -----------
COVERAGETEST1  TEST          COVERAGETEST1  PROCEDURE           37.5

Now, if you have a closer look to the query, you may see that there are not only covered and uncovered blocks in the DBMSPCC_BLOCKS table but also some marked as “not feasible”. 


NOT FEASIBLE PRAGMA

If we want to exclude certain blocks from the code analysis, we may mark them using a new pragma as not feasible.

 1 CREATE OR REPLACE PROCEDURE coveragetest2(in_param IN INTEGER) AS 
 2    l_res integer;
 3 BEGIN
 4    l_res := in_param mod 2;
 5 
 6    -- mark a range of blocks as not feasible
 7   PRAGMA COVERAGE ('NOT_FEASIBLE_START');
 8   IF l_res = 0 THEN
 9      sys.dbms_output.put_line(in_param || ' is even');
10   ELSE
11      sys.dbms_output.put_line(in_param || ' is odd');
12   END IF;
13   PRAGMA COVERAGE ('NOT_FEASIBLE_END');
14
15   IF l_res = 0 THEN 
16      sys.dbms_output.put_line(in_param || ' is even');
17   ELSIF l_res = 1 THEN 
18      sys.dbms_output.put_line(in_param || ' is odd');
19   ELSIF l_res IS NULL THEN
20      sys.dbms_output.put_line(in_param || ' is null');
21   ELSE
22      PRAGMA COVERAGE ('NOT_FEASIBLE');
23      sys.dbms_output.put_line(in_param || ' is unknown');
24   end if;
25   sys.DBMS_OUTPUT.PUT_LINE('That''s all');
26 END coveragetest2;
/

So what happens if we do a code analysis of this procedure, which has “not feasible” blocks in it?

SELECT block, line, col, covered, not_feasible
 FROM dbmspcc_blocks 
WHERE run_id = (SELECT MAX(run_id) 
 FROM dbmspcc_runs) 
 ORDER BY LINE;


     BLOCK       LINE        COL    COVERED NOT_FEASIBLE
---------- ---------- ---------- ---------- ------------
         1          1          1          1            1
         2          9          6          1            1
         3         11          6          0            1
         4         16          7          1            0
         6         17         16          0            0
         7         18          7          0            0
         8         19         10          0            0
         9         20          7          0            0
        10         22          7          0            1
         5         25          4          1            0

10 rows selected. 

Code coverage is also recorded if a block or a set of blocks are set to “not feasible”. But still this pragma may be used not only to mark “unreachable” code but also to mark code we do not care to cover during tests (which i would not call good practice).

ORACLE 12.2 -Auto List Partitioning

Another new feature in the partitioning aerea is the automatic list partitioning. Like with the interval partitioning that came with ORACLE 11.1 the automatic list partitioning creates new partitions as they are required.

The following lines show an example of automatic list partitioning:

Example:

-- =============================================================================
-- Create an automatic list partitioned table
-- =============================================================================
CREATE TABLE employees_part (
   employee_id     NUMBER(4)
  ,first_name      VARCHAR2(30)
  ,last_name       VARCHAR2(30)
  ,email           VARCHAR2(30)
  ,phone_number    VARCHAR2(20)
  ,hire_date       DATE
  ,salary          NUMBER(6,0)
  ,commission_pct  NUMBER(3,2)
  ,manager_id      NUMBER(4)
  ,department_id   NUMBER(4)
)
PARTITION BY LIST (department_id) AUTOMATIC (
   PARTITION P_10 VALUES (10)
);

SELECT partition_name, high_value, partition_position
  FROM all_tab_partitions 
 WHERE table_name = 'EMPLOYEES_PART';

PARTITION_NAME       HIGH_VALUE           PARTITION_POSITION
-------------------- -------------------- ------------------
P_10                 10                                    1


Adding new values to the partitioned table:

In a first step I try to create a list partitioned table having a global and a local index. 

INSERT INTO employees_part (employee_id
                          , first_name
                          , last_name
                          , email
                          , phone_number
                          , hire_date
                          , salary
                          , commission_pct
                          , manager_id
                          , department_id)
SELECT employee_id
     , first_name
     , last_name
     , email
     , phone_number
     , hire_date
     , salary
     , commission_pct
     , manager_id
     , department_id
 FROM employees;

107 rows inserted.

SELECT partition_name, high_value, partition_position
  FROM all_tab_partitions 
 WHERE table_name = 'EMPLOYEES_PART';

PARTITION_NAME       HIGH_VALUE           PARTITION_POSITION
-------------------- -------------------- ------------------
P_10                 10                                    1
SYS_P2161            90                                    2
SYS_P2162            60                                    3
SYS_P2163            100                                   4
SYS_P2164            30                                    5
SYS_P2165            50                                    6
SYS_P2166            80                                    7
SYS_P2167            NULL                                  8
SYS_P2168            20                                    9
SYS_P2169            40                                   10
SYS_P2170            70                                   11
SYS_P2171            110                                  12

How to find out whether a partitioned table is set to automatic?

The information whether a table is set to automatic is stored in the …_PART_TABLES dictionary views.

SELECT table_name, partitioning_type, autolist 
  FROM user_part_tables;

TABLE_NAME      PARTITIONING_TYPE  AUTOLIST
--------------- ------------------ --------
EMPLOYEES_PART  LIST               YES

Conclusion:

The new automatic list partitioning feature dispenses us from the duty to either have to manually (or programatically) create new partitions for new values or having a default partition to store all values that are not covered by the existing partitions. 

This burdon was not that big…but it is still a useful simplification.