• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: PL/SQL

Best way to loop over a dense nested table

24 Monday Jun 2019

Posted by troroblog in ORACLE, PL/SQL, Tipps and Tricks

≈ Leave a comment

Tags

ORACLE, PL/SQL

“What is the best way to process a dense nested table?” seems to be a simple question with an quite simple answer…

Looping First to Last

One way to implement this kind of loop is making use of the collection methods first and last to write code like:

begin
   for i in myArray.first() .. myArray.last() loop
      ... do whatever you need to do
   end loop;
end;

This works as long as the array is initialized and not empty.

If the array is not initialized you get a

ORA-06531: Reference to uninitialized collection

exception. This because the first/last methods can only be used on an initialized collection. If the collection is initilized but empty, the exception thrown is:

ORA-06502: PL/SQL: numeric or value error

the reason for this error is, that on an empty array first and last return null as the result and this is not feasible for a numeric for loop.

So the correct implemetation using the first/last version of a loop would be:

begin
   if myArray is not null
      and myArray is not empty
   then
      for i in myArray.first() .. myArray.last() loop
         ... do whatever you need to do
      end loop;
   end if;
end;

but…we can do better (and shorter).


Looping 1 to count

A slightly different approach is to use 1 as the lower and count as the upper border. If count is called on an empty collection we get 0 as result which has the effect that the loop body is never executed. But as with last/first we get an error when count is used on a collection that has not been initialized. 

So the solution using 1..count would look like this:

begin
   if myArray is not null
   then
      for i 1 .. myArray.count() loop
         ... do whatever you need to do
      end loop;
   end if;
end;

but again…we can do better (and shorter).


Looping using cardinality

Instead of using count to get the number of entries of a nested table we can also use the cardinality function. The advantage of cardinality is that this function can be called on not initialized collections too. The result of cardinality in this case would be null. Null however is not a valid value for a loop border so the cardinality call has to be surrounded by a nvl/coalesce function.

begin
   for i 1 .. nvl(cardinality(myArray),0) loop
      ... do whatever you need to do
   end loop;
end;

This is the shortes way to loop over a collection and it is a version that handles not initialized and empty collections too.

ORACLE 18c – Qualified Expressions

21 Wednesday Feb 2018

Posted by troroblog in ORACLE, ORACLE 18c, PL/SQL

≈ Leave a comment

Tags

New Feature, ORACLE, PL/SQL

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 12.2 – deprecated pragma for PL/SQL units

02 Sunday Oct 2016

Posted by troroblog in Uncategorized

≈ Leave a comment

Tags

Compiler Error, Compiler Warning, ORACLE 12c Release 2, PL/SQL, PRAGMA

With ORACLE 12.2 it is possible to mark program units as deprecated along with a message using a new pragma. The message will show up when the deprecated program unit is referenced and the referencing program unit is compiled (and compiler warnings are enabled).

This enables us to gently replace code by advising the user to move to an alternative program unit.

 


EXAMPLE: Standalond procedure

ALTER SESSION SET PLSQL_WARNINGS = 'Enable:All'
/

CREATE OR REPLACE PROCEDURE p IS
   PRAGMA deprecate(p,'p is deprecated please use p2 instead.');
BEGIN
   sys.dbms_output.put_line('p');
END p;
/

Warning(2,4): PLW-06019: entity P is deprecated

When compiling a deprecated program unit we get an PLW-06019 warning.


EXAMPLE: Packaged procedure

CREATE OR REPLACE PACKAGE pkg IS
   PROCEDURE p;
     PRAGMA deprecate(p,'pkg.p is deprecated please use pkg.p2 instead.');
 
   PROCEDURE p2;
END;
/

Warning(2,4): PLW-06019: entity P is deprecated

CREATE OR REPLACE PACKAGE BODY pkg IS
   PROCEDURE p IS
   BEGIN
      sys.dbms_output.put_line('pkg.p');
   END p;

   PROCEDURE p2 IS
   BEGIN
      sys.dbms_output.put_line('pkg.p2');
   END p2;
END pkg;
/

The pragma specification for packaged program units has to be placed in the specification.

 


Referencing a deprecated program unit

So, what happens if you are using pkg.p or p?

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Warning(3,4): PLW-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Warning(6,8): PLW-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

A different warning (PLW-06020 instead of PLW-06019) shows up including the message we placed in the pragma.


Forcing PLW-06020 to be an error

If we would like to force the user of pkg.p/p to move to pkg.p2/p2 we could now easily defined PLW-06020 to be a compiler error.

ALTER SESSION SET PLSQL_WARNINGS = 'ERROR:6020'
/

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Error(8,4): PLS-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Error(11,8): PLS-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

So the q procedure does not compile anymore.


Conclusion

The deprecated pragma is a good way to let others know that a program unit should not be used anymore…if:

  • compiler warnings are enable
  • compiler warnings are looked at

The possibility to turn a warning into an error is also helpful to force the replacement of deprecated program units (in dev environment).

The problem I see is, that not all deprecated program units have the same expiry date and therefor setting a warning type to error for this case is somehow not sufficient. What I really would like to have is a third (optional) parameter in the pragma specification where I could specify by which date THIS deprecation turns into an error.

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...