Bug 445628

This was tested on an Oracle 9 database. Oracle logged this as bug 445628 in 1997. Then they closed it saying it was not a bug. Either way, it's interesting so here is a worked example. First create two schemas, one to create a stored procedure and another to own a table:

SQL> conn / as sysdba
Connected.
SQL> create user code_owner identified by code_owner
  2  /

User created.

SQL> grant create session, create procedure to code_owner
  2  /

Grant succeeded.

SQL> create user table_owner identified by table_owner
  2  default tablespace user_data
  3  quota 1m on user_data
  4  /

User created.

SQL> grant create session, create table to table_owner
  2  /

Grant succeeded.

SQL>

Now login as code_owner, create a stored procedure and allow table_owner to execute it:

SQL> conn code_owner/code_owner
Connected.
SQL> create procedure do_nothing as
  2  begin
  3  null;
  4  end;
  5  /

Procedure created.

SQL> grant execute on do_nothing to table_owner
  2  /

Grant succeeded.

SQL>

Now login as table_owner and execute code_owner's stored procedure:

SQL> conn table_owner/table_owner
Connected.
SQL> exec code_owner.do_nothing();

PL/SQL procedure successfully completed.

SQL>

So far so good but if table_owner creates a table with the same name as the schema whose code he wants to execute, that code stops working. I guess this is because Oracle thinks that he is trying to execute his own table:

SQL> create table code_owner (col1 number)
  2  /

Table created.

SQL> exec code_owner.do_nothing();
BEGIN code_owner.do_nothing(); END;

                 *
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00302: component 'DO_NOTHING' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>

The solution is to drop the table:

SQL> drop table code_owner
  2  /

Table dropped.

SQL>

Then the code starts to work again:

SQL> exec code_owner.do_nothing();

PL/SQL procedure successfully completed.

SQL>

Counting NULL Values

You should not use = NULL to check if something is NULL. You should use IS NULL instead. First find a table or view which has a column containing some null values and use the NVL function to count them:

SQL> select count(*) from dba_tab_comments
  2  where nvl(comments,'NULL') = 'NULL'
  3  /

  COUNT(*)
----------
      2354

SQL>

Count them again using IS NULL. The answer will be the same:

SQL> select count(*) from dba_tab_comments
  2  where comments is null
  3  /

  COUNT(*)
----------
      2354

SQL>

Finally, count them using = NULL. This will not find the null values:

SQL> select count(*) from dba_tab_comments
  2  where comments = null
  3  /

  COUNT(*)
----------
         0

SQL>

Division by Zero

This was tested on an Oracle 9 database. Division by zero is not allowed in SQL. If you try it, you get an ORA-01476:

SQL> SELECT 1/0 FROM DUAL
  2  /
SELECT 1/0 FROM DUAL
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL>

It is not allowed in PL/SQL either:

SQL> DECLARE
  2   QUOTIENT NUMBER;
  3  BEGIN
  4   QUOTIENT := 1 / 2;
  5   DBMS_OUTPUT.PUT_LINE('Quotient 1 = '||QUOTIENT);
  6   QUOTIENT := 1 / 0;
  7   DBMS_OUTPUT.PUT_LINE('Quotient 2 = '||QUOTIENT);
  8  END;
  9  /
Quotient 1 = .5
DECLARE
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 6

SQL>

Fortunately you can trap this error tidily as follows:

SQL> DECLARE
  2   QUOTIENT NUMBER;
  3  BEGIN
  4   QUOTIENT := 1 / 2;
  5   DBMS_OUTPUT.PUT_LINE('Quotient 1 = '||QUOTIENT);
  6   QUOTIENT := 1 / 0;
  7   DBMS_OUTPUT.PUT_LINE('Quotient 2 = '||QUOTIENT);
  8  EXCEPTION
  9   WHEN ZERO_DIVIDE THEN
 10   DBMS_OUTPUT.PUT_LINE('Division by zero not allowed');
 11  END;
 12  /
Quotient 1 = .5
Division by zero not allowed

PL/SQL procedure successfully completed.

SQL>

SUBSTR

This example was tested on an Oracle 9 database. Oracle provides a substr function, which allows you to extract part of a string of characters. Often, you will use this on a database column:

SQL> select username from dba_users
  2  where substr(username,1,3) = 'SYS'
  3  /

USERNAME
------------------------------
SYSTEM
SYS

SQL> 

This is equivalent to the following:

SQL> select username from dba_users
  2  where username like 'SYS%'
  3  /

USERNAME
------------------------------
SYSTEM
SYS

SQL> 

But for the remaining examples I will use substr on a fixed character string:

SQL> col substring format a9
SQL> select
  2  substr('International DBA',15,3) substring
  3  from dual
  4  /

SUBSTRING
---------
DBA

SQL> 

As you can see above, the column name or string is followed by 2 parameters, start position and length. Start position tells Oracle where to start in the string and length specifies how many characters to return. The first character in the string is counted as position 1. You can see this in the examples at the start of this post and in the SQL below:

SQL> select
  2  substr('International DBA',1,5) substring
  3  from dual
  4  /

SUBSTRING
---------
Inter

SQL> 

But the first character in the string can be numbered 0 instead:

SQL> select
  2  substr('International DBA',0,5) substring
  3  from dual
  4  /

SUBSTRING
---------
Inter

SQL> 

If the start position is negative, Oracle goes to the end of the string and works backwards to determine where to start from:

SQL> select
  2  substr('International DBA',-3,3) substring
  3  from dual
  4  /

SUBSTRING
---------
DBA

SQL>

But specifying a negative length does not cause the characters to be returned in reverse order:

SQL> select
  2  substr('International DBA',-1,-3) substring
  3  from dual
  4  /

SUBSTRING
---------


SQL>

That is because if length is less than 1, Oracle returns a null:

SQL> select
  2  nvl(substr('Andrew',1,0),'NULL') substring
  3  from dual
  4  /

SUBSTRING
---------
NULL

SQL> select
  2  nvl(substr('Andrew',1,-999),'NULL') substring
  3  from dual
  4  /

SUBSTRING
---------
NULL

SQL>

If you miss out the length, you get all the characters from the starting point to the end of the string:

SQL> select substr('Andrew',4) substring from dual
  2  /

SUBSTRING
---------
rew

SQL>

You can even use expressions for start position and length:

SQL> select
  2  substr('Great Britain',2*2,2*3) substring
  3  from dual
  4  /

SUBSTRING
---------
at Bri

SQL>

And floating point numbers are converted to integers first:

SQL> select substr('Andrew',2.5,3.7) from dual
  2  /

SUB
---
ndr

SQL>

Answer to Q3 from Vijay

To find the user who is taking up most CPU time, you need to look in V$SYSSTAT to get the number of the statistic in question e.g.

 

SQL> select statistic# from v$sysstat

  2  where name = 'CPU used by this session';

 

STATISTIC#

----------

        12

 

SQL>

 

The above step is important as the numbers have a habit of changing from one Oracle version to the next. Then you can get the SIDs of the sessions that have used most CPU since they logged in e.g.

 

  1  select * from

  2  (select sid, value/100 from v$sesstat

  3   where statistic# = 12

  4   order by 2 desc)

  5* where rownum <=10

SQL> /

 

       SID  VALUE/100

---------- ----------

        22     379.49

        15     310.84

        13     301.48

        33     287.93

        53     247.32

        79     176.91

        56     166.92

       153     161.46

        96     141.22

        36     136.64

 

10 rows selected.

 

SQL>

 

The values are in hundredths of a second so dividing them by 100 gives the results in seconds. If you want to get figures for users who are currently using most CPU, you will need to repeat the query after a while and just look at the users whose CPU usage has increased in the meantime. Once you have decided which SID you want to investigate you can find out who it is as follows:

 

  1  select sid, username from v$session

  2* where sid in (13, 15, 22)

SQL> /

 

       SID USERNAME

---------- ------------------------------

        13 ANDREW

        15 VIJAY

        22 FRED

 

SQL>