Oracle SQL
  • LICENSE

Monthly Archives: May 2013

A couple of well-known but often forgotten things for PL/SQL developers

Posted on May 28, 2013 by Sayan Malakshinov Posted in oracle, PL/SQL, troubleshooting 3,620 Page views 2 Comments

1. Don’t forget always add NO_DATA_FOUND exception handling, when you doing “select into” in code which can be called as from PL/SQL, as from SQL.

A little example:
Suppose we need to create a function, which would call some procedure:

create or replace procedure p_nested as
  a int;
begin
  select 1 into a from dual where 1=0;
end;
/
create or replace function f_no_data_found return varchar2 as
begin
  p_nested;
  return 'ok';
end;
/

When we call this function in PL/SQL, it will raise NO_DATA_FOUND and we will see it:

SQL> exec dbms_output.put_line(f_no_data_found);
BEGIN dbms_output.put_line(f_no_data_found); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "XTENDER.P_NESTED", line 4
ORA-06512: at "XTENDER.F_NO_DATA_FOUND", line 3
ORA-06512: at line 1

But it doesn’t when we call it in SQL, because it’s normal for SQL: it’s just like a result of scalar subquery that returns nothing – NULL:

SQL> set null "NUL"
SQL> col ndf format a10
SQL> select f_no_data_found ndf from dual;

NDF
----------
NUL

1 row selected.

So if you want the function to behave the same way in PL/SQL and SQL, just add exception handling with reraising another exception or just return null.
It must be at the level of reflexes – “select into” → “exception when no_data_found”
Otherwise, later, when code become a big and difficult, you can get unstable hidden error.

2. Exceptions raised in a declaration section or in default parameters assigning will never be handled in exception section of the same level

Let’s take a look at a very simple example:

An example of exception in default parameter assigning

[sourcecode language=”sql”]
SQL> create or replace function f_value_error return int is
2 begin
3 raise value_error;
4 return 1;
5 end;
6 /

Function created.

SQL> create or replace function f(i int:=f_value_error) return varchar2 is
2 begin
3 return ‘ok’;
4 exception when others then
5 return dbms_utility.format_error_backtrace;
6 end;
7 /

Function created.

SQL> set serverout on;
SQL> begin
2 dbms_output.put_line(‘From f: ‘||chr(10)||f);
3 dbms_output.put_line(‘****************************’);
4 exception when others then
5 dbms_output.put_line(‘****************************’);
6 dbms_output.put_line(‘From higher level:’||chr(10)||dbms_utility.format_error_backtrace);
7 dbms_output.put_line(‘****************************’);
8 end;
9 /
****************************
From higher level:
ORA-06512: at "XTENDER.F_VALUE_ERROR", line 3
ORA-06512: at line 2

****************************

PL/SQL procedure successfully completed.
[/sourcecode]

[collapse]

As you can see, there are two problems:
1. an exception was handled at higher level
2. the error backtrace does not show the call of the function “F”.

If the exception was caused in the declaration, we would see the “correct” backtrace, but exception would be still handled at higher level only:

In the declaration

[sourcecode language=”sql” highlight=”25″]
SQL> create or replace function f(i int:=null) return varchar2 is
2 l_i int:=nvl(i,f_value_error);
3 begin
4 return ‘ok’;
5 exception when others then
6 return dbms_utility.format_error_backtrace;
7 end;
8 /

Function created.

SQL> set serverout on;
SQL> begin
2 dbms_output.put_line(‘From f: ‘||chr(10)||f);
3 dbms_output.put_line(‘****************************’);
4 exception when others then
5 dbms_output.put_line(‘****************************’);
6 dbms_output.put_line(‘From higher level:’||chr(10)||dbms_utility.format_error_backtrace);
7 dbms_output.put_line(‘****************************’);
8 end;
9 /
****************************
From higher level:
ORA-06512: at "XTENDER.F_VALUE_ERROR", line 3
ORA-06512: at "XTENDER.F", line 2
ORA-06512: at line 2

****************************

PL/SQL procedure successfully completed.
[/sourcecode]

[collapse]

Sometimes it’s not so dangerous, but last week I was investigating a complex case for this reason: one function when called in SQL throws strange exception, but in PL/SQL it works fine.
The exception was:

SQL> select PKG1.F(1,0,0,1275) from dual; 
select PKG1.F(1,0,0,1275) from dual 
       * 
ERROR at line 1: 
ORA-06553: PLS-801: internal error [1401]

And the function has many functions calls in default parameters initialization, so I couldn’t even find out which one contains a root problem.

oracle pl/sql troubleshooting

SQL*Plus tips #6: Colorizing output

Posted on May 22, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 6,075 Page views 4 Comments

If you have seen a colored scripts like a fish from “Session Snapper v.4” by Tanel Poder or OraLatencyMap by Luca Canali, you may be also want to colorize your scripts.
I’ve created the script for this purposes with a set of predefined substitution variables.
Just download colors.sql and try this script:

@colors.sql;
prompt ::: &_C_RED ***  TEST PASSED  *** &_C_RESET :::
prompt ::: &_C_RED *** &_C_BLINK TEST PASSED &_C_BLINK_OFF *** &_C_RESET :::

You will get something like this:
sqltips6
All these variables have a prefix “_C” and “_CB” for background colors.

Do not forget to close text attributes, for example: &_C_BOLD; bold text &_C_BOLD_OFF; enables “bold” attribute and disables it after “bold text”.

Full list of the predefined variables:

Predefined variables

Description Variable
_C_RESET Reset formatting – Turn off all attributes
_C_BOLD
_C_BOLD_OFF
Set bright mode on/off
_C_UNDERLINE
_C_UNDERLINE_OFF
Set underline mode on/off
_C_BLINK
_C_BLINK_OFF
Set blink mode on/off
_C_REVERSE
_C_REVERSE_OFF
Exchange foreground and background colors
_C_HIDE
_C_HIDE_OFF
Hide text (foreground color would be the same as background)
_C_BLACK
_C_RED
_C_GREEN
_C_YELLOW
_C_BLUE
_C_MAGENTA
_C_CYAN
_C_WHITE
_C_DEFAULT
Font colors
_CB_BLACK
_CB_RED
_CB_GREEN
_CB_YELLOW
_CB_BLUE
_CB_MAGENTA
_CB_CYAN
_CB_WHITE
_CB_DEFAULT
Background colors

[collapse]

In addition, I want to show a simple example of printing histograms.
We can print a simple histogram using the following query:

-- loading colors variables:
@inc/colors;
-- set max length of bar:
def _max_length=80;
-- columns formatting:
col bar format a&_max_length;
-- clear screen:
prompt &_CLS
with t as (-- it's just a test values for example:
            select level id
                 , round(dbms_random.value(1,100)) val
            from dual
            connect by level<=10
          )
select t.*
      -- bar length is just " (value / max_value) * max_length" in symbols:
      ,floor( val * &_max_length / max(val)over()
            ) as bar_length
      -- generating of bar:
      ,lpad( chr(176)
            ,ceil(val * &_max_length / max(val)over())
            ,chr(192)
           ) as bar
from t;
sqltips6-1

And now we can colorize it:

Colorized script

[sourcecode language="sql"]
-- loading colors variables:
@inc/colors;
-- set max length of bar:
def _max_length=100;
-- column formatting
col bar format a&amp;amp;_max_length;
-- clear screen:
prompt &amp;amp;_CLS

-- test query which prints histogram(or may be simply bars?):
with t as (-- it's just a test values for example:
            select level id
                 , round(dbms_random.value(1,100)) val
            from dual
            connect by level&amp;lt;=10
          )
select
       id
      ,val
      , case
           when pct &amp;gt;= 0.9 then '&amp;amp;_C_RED'
           when pct &amp;lt;= 0.4 then '&amp;amp;_C_GREEN'
           else '&amp;amp;_C_YELLOW'
        end
        -- string generation:
      ||lpad( chr(192)
             ,ceil(pct * &amp;amp;_max_length)-9 -- color - 5 chars and reset - 4
             ,chr(192)
            )
      ||'&amp;amp;_C_RESET'
       as bar
from (
     select
        t.*
       ,val / max(val)over() as pct -- as a percentage of max value:
     from t
     ) t2
/
[/sourcecode]

[collapse]
sqltips6-2
SQL*Plus
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro Alumni

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • Oracle Telegram Bot
  • Partition Pruning and Global Indexes
  • Interval Search: Part 4. Dynamic Range Segmentation – interval quantization
  • Interval Search Series: Simplified, Advanced, and Custom Solutions
  • Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Popular posts

Recent Comments

  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Optimizing Date Range Queries – Part 1
  • Oracle SQL | Interval Search Series: Simplified, Advanced, and Custom Solutions on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 2. Dynamic Range Segmentation – Simplified on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin
©Sayan Malakshinov. Oracle SQL