Thursday, June 26, 2014

1 + NULL is NULL

If you try to add a null to a number, the result is a null. You can see what I mean in the example below, which I tested in Oracle 10:

SQL> select 1 from dual
  2  /

         1
----------
         1

SQL> select nvl(null,'null') from dual
  2  /

NVL(NULL,'NULL')
----------------
null

SQL> select nvl(to_char(1+null),'null') from dual
  2  /

NVL(TO_CHAR(1+NULL),'NULL')
---------------------------
null

SQL>

Friday, June 20, 2014

MDSYS.SDO_COORD_AXES

This was tested on Oracle 11.2. If you need to look at the ORDER column in the above-mentioned table, there only seems to be one way to do it i.e. in upper case and surrounded by double quotes as shown below. I guess this is because ORDER is a reserved word:
 
SQL> desc mdsys.sdo_coord_axes
Name                       Null?    Type
-------------------------- -------- ------------------
COORD_SYS_ID               NOT NULL NUMBER(10)
COORD_AXIS_NAME_ID                  NUMBER(10)
COORD_AXIS_ORIENTATION              VARCHAR2(24)
COORD_AXIS_ABBREVIATION             VARCHAR2(24)
UOM_ID                              NUMBER(10)
ORDER                      NOT NULL NUMBER(5)
 
SQL> select max(order) from mdsys.sdo_coord_axes
  2  /
select max(order) from mdsys.sdo_coord_axes
           *
ERROR at line 1:
ORA-00936: missing expression
 
SQL> select max("order") from mdsys.sdo_coord_axes
  2  /
select max("order") from mdsys.sdo_coord_axes
           *
ERROR at line 1:
ORA-00904: "order": invalid identifier
 
SQL> select max(ORDER) from mdsys.sdo_coord_axes
  2  /
select max(ORDER) from mdsys.sdo_coord_axes
           *
ERROR at line 1:
ORA-00936: missing expression
 
SQL> select max("ORDER") from mdsys.sdo_coord_axes
  2  /
 
MAX("ORDER")
------------
           3
 
SQL>

Sunday, June 15, 2014

Integer Arithmetic

I was looking at the problem of the 3rd party application which is generating ORA-01426 errors when I found the following on My Oracle Support. According to Oracle, this issue affects version 11.2.0.4 and above but I tested it on Oracle 11.2.0.1. Since version 10g, Oracle has used integer arithmetic when it can. The largest integer result it can hold is not very long so you can easily get an ORA-01426:
 
SQL> declare
  2   z number;
  3  begin
  4   z := 654321 * -123456;
  5   dbms_output.put_line('z = '||z);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4
 
SQL>
 
If you add a digit after the decimal point in one of the operands, Oracle thinks that the number is not an integer and the calculation works correctly:
 
SQL> declare
  2   z number;
  3  begin
  4   z := 654321.0 * -123456;
  5   dbms_output.put_line('z = '||z);
  6  end;
  7  /
z = -80779853376
 
PL/SQL procedure successfully completed.
 
SQL>
 
It also works if the integer values are stored in variables:
 
SQL> declare
  2   x number;
  3   y number;
  4   z number;
  5  begin
  6   x := 654321;
  7   y := -123456;
  8   z := x * y;
  9   dbms_output.put_line('z = '||z);
10  end;
11  /
z = -80779853376
 
PL/SQL procedure successfully completed.
 
SQL>
 
Oracle do not regard this as a bug.

Somehow I doubt if this is the cause of my problem as it only seems to manifest itself with hard coded values but I will be bearing it in mind.

Several days later:

Kevan Gelling commented that  the calculation will also generate an ORA-01426 if the values are stored in variables defined as PLS_INTEGER. You can see what he means in the example below: 

SQL> declare
  2   x pls_integer;
  3   y pls_integer;
  4   z pls_integer;
  5  begin
  6   x := 654321;
  7   y := -123456;
  8   z := x * y;
  9   dbms_output.put_line('z = '||z);
10  end;
11  /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 8
 
SQL> 

This could be a useful line of investigation as several default schemas use PLS_INTEGER variables in the database concerned: 

SQL> l
  1  select owner, count(*)
  2  from dba_source
  3  where upper(text) like '%PLS_INTEGER%'
  4  group by owner
  5* order by 1
SQL> /
 
OWNER                            COUNT(*)
------------------------------ ----------
APEX_030200                           147
CTXSYS                                 17
EXFSYS                                  4
MDSYS                                   8
ORDSYS                                  2
SYS                                  1898
XDB                                   180
 
7 rows selected.
 
SQL>

Friday, June 13, 2014

DBMS_MONITOR.SESSION_TRACE_ENABLE

This happened on Oracle 11.2. I had a problem with a 3rd party application hosted in the UK with users in another country. The front-end application was failing regularly with ORA-01426. I needed to know which SQL was causing these errors. First I logged into a test database as user ORACLE:
 
SQL> show user
USER is "ORACLE"
SQL>
 
Then I started a trace of this session as follows:
 
SQL> show user
USER is "SYS"
SQL> select sid, serial# from v$session
  2  where username = 'ORACLE'
  3  /
 
       SID    SERIAL#
---------- ----------
       102       1354
 
SQL> exec dbms_monitor.session_trace_enable(102,1354);
 
PL/SQL procedure successfully completed.
 
SQL>
 
I ran some SQL to produce an ORA-01426 in the traced session:
 
SQL> show user
USER is "ORACLE"
SQL> select power(70,70) from dual
  2  /
select power(70,70) from dual
       *
ERROR at line 1:
ORA-01426: numeric overflow
 
SQL>
 
Then I stopped the trace:
 
SQL> show user
USER is "SYS"
SQL> exec dbms_monitor.session_trace_disable(102,1354);
 
PL/SQL procedure successfully completed.
 
SQL>
 
… but when I looked for the statement in the trace file, I could see no mention of the ORA-01426:
 
=====================
PARSING IN CURSOR #2 len=29 dep=0 uid=8354 oct=3 lid=8354 tim=31182397151149 hv=1978710958 ad='397d685b0' sqlid='0c4sm5duz1fxf'
select power(70,70) from dual
END OF STMT
PARSE #2:c=0,e=217,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1546270724,tim=31182397151146
EXEC #2:c=0,e=403,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1546270724,tim=31182397532682
WAIT #2: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=31182397532829
FETCH #2:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1546270724,tim=31182397533145
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)'
=====================
 
I had hoped that I would be able to trace the application, search the trace file(s) for the ORA-01426 and thus identify the failing SQL but it looks as if I will need to try something else. More to follow…

Monday, June 09, 2014

Read Only Tables

Oracle 11 introduced a new ALTER TABLE syntax, which allows you to make a table READ ONLY, so I decided to try it out. First I created a table:

SQL> create table table_list
  2  as select table_name from dba_tables
  3  /
 
Table created.

SQL>

... then I made it READ ONLY:
 
SQL> alter table table_list read only
  2  /
 
Table altered.

... so when I tried to update it, Oracle gave me an error:
 
SQL> update table_list
  2  set table_name = 'BLAH'
  3  /
update table_list
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table
"ORACLE"."TABLE_LIST" 

SQL>

I changed the table back to READ WRITE mode like this: 
 
SQL> alter table table_list read write
  2  /
 
Table altered.

SQL>

... then I was able to update it:
 
SQL> update table_list
  2  set table_name = 'BLAH'
  3  /
 
3081 rows updated.
 
SQL>

When you create a table, it is in READ WRITE mode by default. If you try to make it READ WRITE again, you get an error. Once a table is in READ ONLY mode, you get an error if you try to make it READ ONLY again. I checked this out as follows:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL> alter table andrew read write
  2  /
alter table andrew read write
*
ERROR at line 1:
ORA-14140: table ORACLE.ANDREW is already in
read/write mode

SQL> alter table andrew read only
  2  /

Table altered.

SQL> alter table andrew read only
  2  /
alter table andrew read only
*
ERROR at line 1:
ORA-14139: table ORACLE.ANDREW is already in read-only
mode

SQL>

Saturday, June 07, 2014

V$RECOVERY_FILE_DEST

The V$RECOVERY_FILE_DEST view shows details about the flash recovery area. In the example below, it is queried to see the maximum size. This is then increased from 4 gigabytes to 5 gigabytes and the view is queried again to see the change: 

ORCL /export/home/oracle > sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 18 16:08:48 2013
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> desc v$recovery_file_dest
Name                       Null?    Type
-------------------------- -------- ------------------
NAME                                VARCHAR2(513)
SPACE_LIMIT                         NUMBER
SPACE_USED                          NUMBER
SPACE_RECLAIMABLE                   NUMBER
NUMBER_OF_FILES                     NUMBER
 
SQL> select space_limit
  2  from v$recovery_file_dest
  3  /
 
SPACE_LIMIT
-----------
4294967296
 
SQL> alter system set db_recovery_file_dest_size = 5g
  2  /
 
System altered.
 
SQL> select space_limit
  2  from v$recovery_file_dest
  3  /
 
SPACE_LIMIT
-----------
5368709120
 
SQL>

If you click on the two images below to enlarge them and bring them into focus, you will see that in version 11.1, Oracle called it the flash recovery area:



... whereas in Oracle 11.2, it was called the fast recovery area:


Tuesday, June 03, 2014

Taking a Datafile Offline in NOARCHIVELOG Mode

I read some RMAN documentation which said that you could not take a datafile offline if the database was in NOARCHIVELOG mode. I don’t know much about taking datafiles offline but this seemed reasonable so I gave it a try in Oracle 12.1. First I checked the database’s LOG_MODE: 

SQL> select log_mode from v$database
  2  /
 
LOG_MODE
------------
NOARCHIVELOG
 
SQL>

Then I tried to take a datafile offline but this failed, as the documentation had said it would: 

SQL> alter database datafile
  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
  3  offline
  4  /
alter database datafile
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media
recovery enabled
 
SQL>
 
I read somewhere in the Oracle documentation that you could do this if the tablespace was READ ONLY. This also seemed reasonable so I tried it too: 

SQL> alter tablespace users read only
  2  /
 
Tablespace altered.
 
SQL> alter database datafile
  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
  3  offline
  4  /
 
Database altered.
 
SQL> 

Of course, once I had done this, I could not change the tablespace to READ WRITE mode again: 

SQL> l
  1* alter tablespace users read write
SQL> /
alter tablespace users read write
*
ERROR at line 1:
ORA-01135: file 6 accessed for DML/query is offline
ORA-01110: data file 6:
'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
 
SQL> 

… until I had brought the datafile ONLINE: 

SQL> alter database datafile
  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\USERS.DBF'
  3  online
  4  /
 
Database altered.
 
SQL> alter tablespace users read write
  2  /
 
Tablespace altered.
 
SQL>

Monday, June 02, 2014

SHUTDOWN TRANSACTIONAL

If you run this command, Oracle waits until all users have committed or rolled back any outstanding transactions before closing the database. To test this out in Oracle 12.1, I tried to close a database from a session with an outstanding transaction. This failed with an ORA-01097. Once I had committed the transaction, I was able to close the database successfully:

SQL> show user
USER is "SYS"
SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL> shutdown transactional
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
SQL> commit
  2  /
 
Commit complete.
 
SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 1720328192 bytes
Fixed Size                  2403496 bytes
Variable Size            1056965464 bytes
Database Buffers          654311424 bytes
Redo Buffers                6647808 bytes
Database mounted.
Database opened.
SQL>
 
For the next example, I logged in as user ANDREW, created a table, inserted a row but did not commit it:
 
SQL> show user
USER is "ANDREW"
SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL>
 
When user SYS tried to close the database, nothing happened:
 
SQL> show user
USER is "SYS"
SQL> shutdown transactional
 
After a while, user ANDREW rolled back his transaction:
 
SQL> show user
USER is "ANDREW"
SQL> create table tab1(col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values(1)
  2  /
 
1 row created.
 
SQL> rollback
  2  /
 
Rollback complete.
 
SQL>
 
… then the database closed down a few seconds later:
 
SQL> show user
USER is "SYS"
SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Sunday, June 01, 2014

Wrap Utility

This is an example using the wrap utility, which allows you to hide stored code. Software suppliers can use it to prevent customers stealing their PL/SQL. First I created a table:
 
SQL> create table name_list
  2  as select 'ANDREW' name from dual
  3  /
 
Table created.
 
SQL> select * from name_list
  2  /
 
NAME
------
ANDREW
 
SQL>
 
Then I created a procedure to add names to the table:
 
SQL> create or replace procedure add_name
  2   (new_name char) as
  3  begin
  4   insert into name_list values(new_name);
  5  end;
  6  /
 
Procedure created.
 
SQL>
 
... and I tested it as follows:
 
SQL> execute add_name('BRIAN');
 
PL/SQL procedure successfully completed.
 
SQL> select * from name_list
  2  /
 
NAME
------
ANDREW
BRIAN
 
SQL>
 
The procedure was easy to see in the database so anybody could have stolen it:
 
SQL> l
  1  select text from dba_source
  2  where name = 'ADD_NAME'
  3* order by line
SQL> /
 
TEXT
-------------------------------------------------------
procedure add_name
(new_name char) as
begin
insert into name_list values(new_name);
end;
 
SQL>
 
The wrap utility was stored here:
 
ORACLE11 > which wrap
/oracle/app/oracle/product/11.2.0/bin/wrap
ORACLE11 >
 
I ran it on the source code. To do this you supply the name of the file containing the code to be wrapped as the iname parameter and the name of the file to store the wrapped code as the oname parameter:
 
ORACLE11 > cat procedure.sql
create or replace procedure add_name
(new_name char) as
begin
insert into name_list values(new_name);
end;
/
ORACLE11 > wrap iname=procedure.sql \
> oname=wrapped_procedure.sql
 
PL/SQL Wrapper: Release 11.2.0.1.0- 64bit Production on Thu Nov 03 13:07:53 2011
 
Copyright (c) 1993, 2009, Oracle.  All rights reserved.
 
Processing procedure.sql to wrapped_procedure.sql
ORACLE11 >
 
This encrypted the source code as follows:
 
ORACLE11 > cat wrapped_procedure.sql
create or replace procedure add_name wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
5b 92
d6K98qP2M1tFArx52RBb3rXsyPgwg5nnm7+fMr2ywFxaWaH0cgxHLkMJUKXHstD+x9IyXLgz
uHQlw7h0i8DAMv7Shgml0pmfsp77Caj5nspECCJBr6ieOEoia+KvuEzsPHHiP9E8dKYY7AS4
 
/
ORACLE11 >
 
I compiled the wrapped source code and checked that it was still encrypted in the database:
 
SQL> @wrapped_procedure
 
Procedure created.
 
SQL> select text from dba_source
  2  where name = 'ADD_NAME'
  3  order by line
  4  /
 
TEXT
-------------------------------------------------------
procedure add_name wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
5b 92
d6K98qP2M1tFArx52RBb3rXsyPgwg5nnm7+fMr2ywFxaWaH0cgxHLkM
JUKXHstD+x9IyXLgz
uHQlw7h0i8DAMv7Shgml0pmfsp77Caj5nspECCJBr6ieOEoia+KvuEz
sPHHiP9E8dKYY7AS4
 
SQL>
 
Finally, I checked that the procedure still worked:
 
SQL> execute add_name('COLIN');
 
PL/SQL procedure successfully completed.
 
SQL> select * from name_list
  2  /
 
NAME
------
ANDREW
BRIAN
COLIN
 
SQL>
 
Looking elsewhere on the Internet there seem to be a few points to bear in mind when using wrap. I have not checked any of these myself:
  1. It will increase the size of your source code.
  2. If you have strict security on $ORACLE_HOME/bin, you may wish to install a copy of wrap in a shared area for your developers.
  3. You should use the correct version of wrap for your database. Otherwise, if Oracle has introduced some new feature, an out of date version of wrap will not recognise it.
  4. You should only wrap package bodies. You should leave the package headers alone as they can provide useful documentation (I'm not sure if I agree with this one).