ORA-01427

This was tested on Oracle 11.2. I created a small table as shown below:

 

SQL> select * from emp

  2  order by emp_name, year

  3  /

 

EMP_NAME   YEAR     SALARY

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

ANGUS      2000      15000

ANGUS      2001      16000

BORIS      2000      15000

BORIS      2001      15500

COLIN      2000      15500

COLIN      2001      16000

 

6 rows selected.

 

SQL>

 

I wanted to see who earned the same as Angus each year and thought I could do it like this. It failed with an ORA-01427 as the subquery returned two rows, not one, and Oracle did not know which one to compare against:

 

SQL> select *

  2  from emp

  3  where emp_name != 'ANGUS'

  4  and salary =

  5  (select salary from emp

  6   where emp_name = 'ANGUS')

  7  order by emp_name, year

  8  /

(select salary from emp

*

ERROR at line 5:

ORA-01427: single-row subquery returns more than one

row

 

SQL>

 

I changed the SQL to use a correlated subquery instead. This compared the results year by year and, as Angus only had one salary each year, it ran successfully:

 

SQL> select *

  2  from emp x

  3  where emp_name != 'ANGUS'

  4  and salary =

  5  (select salary from emp

  6   where emp_name = 'ANGUS'

  7   and year = x.year)

  8  order by emp_name, year

  9  /

 

EMP_NAME   YEAR     SALARY

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

BORIS      2000      15000

COLIN      2001      16000

 

SQL>

PL/SQL Runs on the Server, not on the Client

I read in a book that PL/SQL runs on the server, not the client so I decided to check this out. I started a Command prompt on my Windows PC and used a SQL*Plus session to connect to a database on a UNIX server. Then I kicked off the loop below:

 

SQL> ed

Wrote file afiedt.buf

 

  1  declare

  2   a number;

  3  begin

  4   for b in 1..1000000000

  5    loop

  6    a := a + 1;

  7    end loop;

  8* end;

SQL> /

 

After it had been running for a while, I looked at the CPU usage on the Windows PC but it was hardly showing any at all (as usual, click on the image to enlarge it and bring it into focus):

The server, on the other hand, was showing over 95% of the CPU was being used for this one task so it seems that the book was right: 

Oracle 9: ps aux|more

USER            PID %CPU %MEM   VSZ  RSS TTY      S    STARTED         TIME COMMAND

oracle       503287 95.4  0.5  299M 4.7M ??       R    18:34:17     4:52.64 oracleLIVDPT1 (LOCAL=NO)

ORA-00026

This was tested on Oracle 9. When you use the ALTER SYSTEM KILL SESSION command, you must supply the SID and SERIAL# from an entry in V$SESSION. You get an ORA-00026 if you do not provide one: 

SQL> alter system kill session

  2  /

alter system kill session

                        *

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

The SID and SERIAL# are both numbers so you also get an ORA-00026 if you give non numeric values:

 

SQL> alter system kill session 'A,B'

  2  /

alter system kill session 'A,B'

*

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

… or values which do not come from V$SESSION:

 

SQL> select count(*) from v$session

  2  where sid = 123

  3  and serial# = 456

  4  /

 

  COUNT(*)

----------

         0

 

SQL> alter system kill session '123,456'

  2  /

alter system kill session '123,456'

*

ERROR at line 1:

ORA-00026: missing or invalid session ID

 

SQL>

 

However, if the values are valid, the command should work:

 

SQL> select sid, serial# from v$session

  2  where username = 'ANDREW'

  3  /

 

       SID    SERIAL#

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

        13         34

 

SQL> alter system kill session '13,34'

  2  /

 

System altered.

 

SQL>

Constraints (Part 7) - On Delete Set Null

This example was tested on Oracle 9. It creates another foreign key constraint. First create emp and dept tables as before. Note the on delete set null clause when the employee table is created. We will see what it does later: 

SQL> create table dept
  2  (dept_no  varchar2(4) unique,
  3  dept_desc varchar2(10))
  4  /

Table created.

SQL> create table emp
  2  (emp_no     varchar2(4),
  3  emp_name    varchar2(10),
  4  emp_dept_no varchar2(4)
  5  constraint fk_dept_no
  6  references dept(dept_no)
  7  on delete set null)
  8  /

Table created.

SQL>

And create a department with one employee as in earlier examples: 

SQL> insert into dept values ('D001','IT')
  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew     D001

SQL> 

Delete the IT department and see what happens to its one employee:

SQL> delete dept
  2  /

1 row deleted.

SQL> select * from dept
  2  /

no rows selected

SQL> 

The employee is still in the emp table but his department number has been set to null. As with the on delete cascade clause in the previous example, you need to be really sure that this is what you want to do:

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew

SQL>

Rollback the transaction, set the employee's department number to be not null, then try again:

SQL> rollback;

Rollback complete.

SQL> alter table emp modify emp_dept_no not null
  2  /

Table altered.

SQL>

As you might expect, the delete dept statement fails because the employee's department number can no longer be set to null:

SQL> delete dept
  2  /
delete dept
      *
ERROR at line 1:
ORA-01407: cannot update
("ORACLE"."EMP"."EMP_DEPT_NO") to NULL

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew     D001

SQL>

Constraints (Part 6) - On Delete Cascade

Go to part 5

There is more than one way to create a foreign key constraint. This example, tested on an Oracle 9 database, uses the on delete cascade clause. First create a department table:  

SQL> create table dept

  2  (dept_no  varchar2(4) unique,

  3  dept_desc varchar2(10))

  4  /

Table created.

SQL>

Next create an employee table using the on delete cascade clause. We will see what this is for later:

SQL> create table emp

  2  (emp_no     varchar2(4),

  3  emp_name    varchar2(10),

  4  emp_dept_no varchar2(4)

  5  constraint fk_dept_no

  6  references dept(dept_no)

  7  on delete cascade)

  8  /

Table created.

SQL> 

Set up an IT department with one employee: 

SQL> insert into dept values ('D001','IT')

  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')

  2  /

1 row created.

SQL> select * from dept

  2  /

DEPT DEPT_DESC

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

D001 IT

SQL> select * from emp

  2  /

EMP_ EMP_NAME  EMP_DEPT_NO

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

E001 Andrew    D001

SQL>

Now delete the IT department from the dept table. The on delete cascade clause means that any employee(s) in this department will be deleted from the emp table without warning:

SQL> delete dept

  2  /

1 row deleted.

SQL> select * from dept

  2  /

no rows selected

SQL> select * from emp

  2  /

no rows selected

SQL>

Later on, i.e. when it is too late to rollback the transaction, you may decide that the IT department was removed in error. If you then reinstate it in the dept table, you will somehow need to find the names of the employees who work there and add them to the emp table again.