• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Category Archives: PL/SQL

Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions

25 Monday May 2020

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

≈ Leave a comment

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.


The first time I hit this kind of code I was a little bit confused … it never came to my mind to do something like redefining named exceptions like NO_DATA_FOUND or TOO_MANY_ROWS or even OTHERS.

But after many years I became pretty hardened when it comes to what to expect during a code review.

My own NO_DATA_FOUND Exception

So guess what this peace of code does…

declare
   L_Variable number;
   no_data_found EXCEPTION;
begin
   select empno
     into l_variable
     from scott.emp
    where ename = 'TYRION LENNISTER';
exception
    when no_data_found
       then sys.dbms_output.put_line('Tyrion does not exist');
end;

It ends with raising a no_data_found exception upon the caller…

Error report -
ORA-01403: no data found
ORA-06512: at line 5
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

Hold a minute…why does it raise an exception? There is an exception handler that should take care of no_data_found …
well yes … but …
That is a user defined exception which is different from the named exception that was automatically raised by Oracle. It shares the same name but it is different.

Now how about this?

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      no_data_found EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
    exception
       when no_data_found
          then sys.dbms_output.put_line('Tyrion does not exist');
    end inner;
exception
   when no_data_found
      then sys.dbms_output.put_line('Yes, Tyrion really does not exist');
end outer;

This time we get the message

Yes, Tyrion really does not exist

because in the outer block the no_data_found exception declared in the inner block is not known and therefore we fall back to Oracles named exception NO_DATA_FOUND which is handled in the outer block.


My own OTHERS

How about an user defined others exception?

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when no_data_found 
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

I put the others handler ahead of the no_data_found just to make sure it is “my” others (you cannot do that with the named others) and …

Error report -
ORA-06550: line 14, column 7:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

So obviously that others was not mine but the one of oracle. Next try:

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when inner.others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when no_data_found 
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

This time I got no error

Hello, what did you expect

So I passed the others exception handler (my own) and fell into the no_data_found handler. The same happens when you write your code like this:

<<outer>>
begin
   <<inner>>
   declare
      L_Variable number;
      others EXCEPTION;
   begin
      select empno
        into l_variable
        from scott.emp
       where ename = 'TYRION LENNISTER';
   exception
      when inner.others 
         then sys.dbms_output.put_line('Tyrion does not exist');
      when others
         then sys.dbms_output.put_line('Hello, what did you expect');
   end inner;
exception
   when others 
      then sys.dbms_output.put_line('Yes Tyrion really does not exist');
end outer;

But, honestly …

This is really confusing and in my eyes it is absolutely needless to confuse our colleagues by redefining named exceptions. So get rid of that!


Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.

Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns

24 Sunday May 2020

Posted by troroblog in Antipattern, ORACLE, PL/SQL, SQL

≈ Leave a comment

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.


Another sign of programmers laziness is the wide usage of “select *”. This antipattern can be found in inline views, database views, cursors …

The only column needed in the following example is the employee_id, so why do we read the whole row?

This is a waste for many reasons:

  • expensive columns like CLOB/BLOB/XMLTYPE
  • chained rows in the table
  • maybe we could benefit of an index-only access if we pared the query down to the minimum
  • storage that is needed to hold the row

Which Columns are really needed?

For this PL/SQL block we would have to return employee_id only from the implicit cursor.

begin
   for EmpRec in (select *
                    from employees
                   where salary < l_minSalary) loop
      raise_salary(in_employee_id => EmpRec.employee_id);
   end loop;
end;

In the following example the only relevant columns needed as result of the inline view on GR_BRCT_STWE (which has 39 columns) are GR_LNR_1 to join GR_STAMM and GR_LNR_2 to filter against a bind variable (which could be done in the inline view anyway) .

select count(*)
  from (select *
          from GR_BRCT_STWE
         where USERBK_NR = :2) verb
  join GR_STAMM mfh on (    verb.GR_LNR_1 = mfh.GR_LNR
                        and mfh.GR_TYP_CD = 3 
                        and mfh.USERBK_NR = :2)
  join GR_GBDE mfh_gbde on (    mfh_gbde.GR_LNR = mfh.GR_LNR
                            and mfh_gbde.USERBK_NR = :2)
 where verb.GR_LNR_2 = :1
  and mfh_gbde.WHG_ANZ is not null;

If we have to use most but not all of the columns of a table it might be cumbersome to write down all the columns, but nowadays we have great support by IDEs like SQL Developer.

SQL Developer may help you…

If you write down a select * from table you will discover a yellow wavy line underneath the asterisk. Hovering your mouse pointer over this line will enable you to apply the complete column list, which may be arranged afterwards.


Efficient coding? Sorry, I call it laziness.

Yes it may be more work to specify exactly which columns you need, but not doing so is not a sign of efficiency but only of laziness.


Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.

Antipatterns SQL & PL/SQL – Substitute NULL with empty string

24 Sunday May 2020

Posted by troroblog in Antipattern, ORACLE, PL/SQL, SQL

≈ Leave a comment

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.


One of the worst things that may be seen often in PL/SQL code is using an empty string as a substitution of a NULL value. There are places where it technically works and therefore is simply ugly and others where it is just wrong.


Ugly NULL substitutions

The ugly usages are those where an empty string is used as an assignment to a variable or a parameter. Even if this is not technically wrong it may be a reason why inexperienced colleagues may end substituting NULL by empty string not only in assignements but also when it comes to conditions.

So ugly NULL substitutions look like this:

...
   L_Variable := '';
   F_GetTableValues('P1' ,'P2' ,'' ,P3 ,'P4');
...
   
select decode(a.Column,'',0,a.OtherColumn)
   from table a;

...
   procedure OpenForm (P_UserId      in varchar2
                      ,P_StartUpArgs in varchar2 default ''
                      ,P_Commit      in number default 0);
...

Wrong NULL substitutions

Substituting NULL by an empty string within a condition is not only ugly, it is just wrong and it will not lead to the expected results. The reason therefore is, that this kind of conditions will not work with the IS / IS NOT keyword (as needed for NULL comparison) but with an equal/unequal operator.

Obviously is is not really unknown, that comparing with an empty is string is kind of problematic, this might be the reason why we can often find things like:

if L_Variable = '' or L_Variable is null then
...
end if;

if L_Variable <> '' or L_Variable is not null then
...
end if;

An example using the HR sample locations table

select city, country_id, state_province
  from locations
 where state_province = '';

no rows selected


select city, country_id, state_province
  from locations
 where state_province is null;

CITY       COUNTRY_ID  STATE_PROVINCE
---------  ----------  --------------
Roma       IT
Venice     IT
Hiroshima  JP
Beijing    CN
Singapore  SG
London     UK


select city, country_id, state_province
  from locations
 where state_province <> '';

no rows selected

Why substitute?

Maybe two single quotes are shorter than the keyword NULL (2 vs 4 letters) but as soon as we think about adding an additional or clause we should really get rid of the NULL substitutions.

And this please not only at the places where the substitutions is wrong but also where it is only ugly…


Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.

Antipattern SQL & PL/SQL – Using the LIKE operator even if searching for equality.

23 Saturday May 2020

Posted by troroblog in Antipattern, ORACLE, PL/SQL, SQL

≈ Leave a comment

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.


I guess that queries like the following may be found throughout every application.

select *
  from emp
 where ename like 'SCOTT';

select table_name, comments
  from dictionary
 where table_name like 'ALL_TABLES';

Those queries are confusing, error-prone and sometimes even wrong.


Confusing

If we find a LIKE expression in a query, the expectations would be to see some wildcards in the condition. Not having any wildcards in the query leads to a certain suspicion.

Error-Prone

When using the like operator instead of an equal sign we have to make sure that every wildcard character that should not be used as a wildcard is escaped. The most common wild card is the percent sign (%) … the one that is often missed is the underscore.

So basically this query is not only searching for an object called “ALL_TABLES” but also for something starting with “ALL” having any charcater at position 4 and ending with “TABLES”.

select table_name, comments
  from dictionary
 where table_name like 'ALL_TABLES';

To correct this query we need to escape the underscore

select table_name, comments
  from dictionary
 where table_name like 'ALL\_TABLES' escape '\';

or even better use an equal instead of the like operator.


Same same but different

Do comparisons without wildcard always return the same result regardless of using the LIKE operator or the equal sign?

Well….no, they dont.
There are not many cases where the CHAR datatype is still used, but nevertheless it is important to know, how this datatype behaves when it comes to equal comparisons.

Blank-Padded comparison semantics (Oracle – SQL Language Reference)

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

Nonpadded comparison semantics (Oracle – SQL Language Reference)

With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.

Example for blank-padded comparison

As you can see it does not matter how many spaces we add to the search string Hello as the database type is a CHAR and a blank-padded comparison is used.

create table test_char (x char(10));

insert into test_char (x) values ('Hello');

select *
from test_char
where x = 'Hello';

X
----------------
Hello

select *
from test_char
where x = 'Hello ';

X
----------------
Hello

However using LIKE for the comparison changes everything. A like comparison will never use the blank-padding semantic and therefore we are not able to find a row.

select *
from test_char
where x like 'Hello';

no rows selected

What is the datatype of a string literal?

According to the documentation (ORACLE SQL Language Reference) a string (or text or character) literal has the CHAR datatype and therefore also is compared using the blank-padding semantic.


Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.

Antipatterns SQL & PL/SQL – Existance Check

23 Saturday May 2020

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

≈ 2 Comments

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.

The first antipattern is the “check existance by doing a complete count” pattern.


How do we check whether a particular value exists in a table?

E.g. we would like to know whether there is a person in UK or DE which earns at least 80% of the maximum salary defined by the job. If we found that kind of rows, we would have to call a given procedure.

An anti-pattern that can be often found to solve this kind of question, is reading way to many rows to take your decision.

...
   select count(*)
     into L_Counter
     from EMPLOYEES   e
     join JOBS        j on (j.JOB_ID        = e.JOB_ID)
     join DEPARTMENTS d on (d.DEPARTMENT_ID = e.DEPARTMENT_ID)
     join LOCATIONS   l on (l.LOCATION_ID   = d.LOCATION_ID)
    where e.SALARY > (j.MAX_SALARY * .8)
      and l.COUNTRY_ID in ('DE','UK');

   if L_Counter > 0 then
      procedure1();
   end if;
...

To reduce the number of rows to be read, we should add a rownum filter to stop searching as soon as we found a hit.

...
   select count(*)
     into L_Counter
     from EMPLOYEES   e
     join JOBS        j on (j.JOB_ID        = e.JOB_ID)
     join DEPARTMENTS d on (d.DEPARTMENT_ID = e.DEPARTMENT_ID)
     join LOCATIONS   l on (l.LOCATION_ID   = d.LOCATION_ID)
    where e.SALARY > (j.MAX_SALARY * .8)
      and l.COUNTRY_ID in ('DE','UK')
      and rownum = 1;

if L_Counter > 0 then
   procedure1();
end if;
...

A different approach would be to use a subquery to answer the question connecting it to dual using an EXIST clause. One of the main differences to the count/rownum approach is, that this kind of query may raise a no_data_found exception and we therefore do not have to check a counter variable. We can rely on a “normal continuation” if a row was found. If no row exists an exception will be raised.

...
   begin
      select 1
        into L_Counter
        from dual
       where exists (select 'Employee with > 80% salary in UK/DE found'
                       from EMPLOYEES   e
                       join JOBS        j on (j.JOB_ID        = e.JOB_ID)
                       join DEPARTMENTS d on (d.DEPARTMENT_ID = e.DEPARTMENT_ID)
                       join LOCATIONS   l on (l.LOCATION_ID   = d.LOCATION_ID)
                      where e.SALARY > (j.MAX_SALARY * .8)
                        and l.COUNTRY_ID in ('DE','UK'))

      procedure1();
   exception
      when no_data_found then
         ...
   end;
...


To update or not to update?

Nowadays the question whether to insert or update a row can easily be solved using the merge command. However I came accross situations where the insert had to be done using an API whilst the update was done directly.

Situations like that are another place where you can find the “check existance by count”-antipattern.

...
   select count(*)
     into L_Counter
     from EMPLOYEES   e
    where e.EMPLOYEE_ID = in_emprec.employee_id;

   if L_Counter > 0 then
      update EMPLOYEES e
         set ...
       where e.EMPLOYEE_ID = in_emprec.employee_id;
   else
      InsertEmployee(in_emprec => in_emprec);
   end if;
...

This solution has two problems:

  1. In case of an update we need to process the employees table twice (first for the count, second for the update)
  2. We ignore the fact, that between the count and the follow-up action the situation could change due to data manipulations done by other sessions.

As a solution for the insert or update question (if no merge is possible) my suggestion would be to try the update and do the API call if the update command ended in updating 0 rows.

...
   update EMPLOYEES e
      set ...
    where e.EMPLOYEE_ID = in_emprec.employee_id;
   
   if SQL%ROWCOUNT = 0 then
      InsertEmployee(in_emprec => in_emprec);
   end if;
...

Or (even better) thinking about handling update/insert in the API using a merge command.


Testing nested tables for existence of an element

A way of testing existance of elements I see quite often is the “SQL way”.  The collection is scanned with a SQL doing a count(*) (without stop key). Maybe this has been an evolutionary way a couple of years ago, but today …

...
   select count(*)
     into L_Count
     from table(L_NestedTable)
    where column_value = L_Value;
 
    if (L_COUNT > 0) then
...

Today the easiest way is to use MEMBER OF to check for existens of an certain element in a nested table.

...
   if L_value member of L_NestedTable then
...

Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.

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.

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

16 Wednesday May 2018

Posted by troroblog in ORACLE, ORACLE 11g Release 2, ORACLE 12c Release 1, ORACLE 12c Release 2, ORACLE 18c, PL/SQL

≈ Leave a comment

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

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.

 

 

 

DBMS_APPLICATION_INFO

25 Tuesday Apr 2017

Posted by troroblog in ORACLE, PL/SQL, Underestimated Features

≈ Leave a comment

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)

25 Tuesday Apr 2017

Posted by troroblog in ORACLE, ORACLE 12c Release 2, PL/SQL

≈ Leave a comment

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.

← Older posts

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.