Password_Verify_Function (Part 1)

This example was tested on Oracle 10 on Linux. It shows you how to create a simple password verify function (PVF). First connect as the SYS user because a PVF must be owned by SYS:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> 

Create a profile to use the PVF:

SQL> create profile andrews_profile
  2  limit password_life_time 60
  3  /

Profile created.

SQL>

Try to give my new profile a PVF called my_verify_function. This fails as it has not been created yet:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /
alter profile andrews_profile limit
*
ERROR at line 1:
ORA-07443: function MY_VERIFY_FUNCTION not found

SQL>

Create a function with the required name. A PVF must have the parameters shown. It should be obvious what this PVF is supposed to do:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8    return false;
  9  else
 10    return true;
 11  end if;
 12  end my_verify_function;
 13  /

Function created.

SQL>

Change the new profile to use this PVF:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /

Profile altered.

SQL>

Create a user and assign it the new profile:

SQL> create user andrew identified by reid1
  2  profile andrews_profile
  3  /

User created.

SQL>

Change the user’s password. The new value is passed to the PVF and rejected:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-28003: password verification for the specified
password failed

SQL>

The error message was not very helpful so change the PVF accordingly:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8   raise_application_error
  9   (-20000, 'Password < 4 characters long');
 10  else
 11   return true;
 12  end if;
 13  end my_verify_function;
 14  /

Function created.

SQL>

Try to change the password again. This time the reason for rejection is clear:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-20000: Password < 4 characters long

SQL>

Fix the problem and try again:

SQL> alter user andrew identified by reid2
  2  /

User altered.

SQL>

Constraints (Part 7)

Go to part 6 

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)

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.

Go to part 7 

Constraints (Part 5)

Go to part 4 

This example, tested on an Oracle 9 database, starts to look at foreign key constraints. First create a department table. Each row has a department number and description:

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

Table created.

SQL> 

Next, create an employee table. Each row has an employee number, name and department number. A foreign key constraint checks that the department number is already present in the department table:

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  /
references dept(dept_no))
                *
ERROR at line 6:
ORA-02270: no matching unique or primary key for this
column-list

SQL>

That fails because the department number in the department table does not have to be unique. Add a constraint to ensure it is then try again:

SQL> alter table dept add constraint dept_no_pk
  2  unique(dept_no)
  3  /

Table altered.

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  foreign key (emp_dept_no)
  7  references dept(dept_no))
  8  /

Table created.

SQL>

So far so good. Now see what happens if you try to drop the department table. Note that both tables are empty at this point:

SQL> drop table dept
  2  /
drop table dept
          *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by
foreign keys

SQL> 

That fails too because the department table is now required to validate changes to the employee table. Try to add a row to the employee table:

SQL> insert into emp values ('E001','Andrew','D001')
  2  /
insert into emp values ('E001','Andrew','D001')
*
ERROR at line 1:
ORA-02291: integrity constraint (ORACLE.FK_DEPT_NO)
violated - parent key not found

SQL>

The insert is rejected because the department number has not been added to the department table yet. Add a couple of entries in the department table (including the one needed for the employee row above) and have another go:  

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

1 row created.

SQL> insert into dept values ('D002','Sales')
  2  /

1 row created.

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

1 row created.

SQL>

That worked. Now try to delete rows from the department table:

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT
D002 Sales

SQL> delete dept
  2  /
delete dept
*
ERROR at line 1:
ORA-02292: integrity constraint (ORACLE.FK_DEPT_NO)
violated - child record found

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT
D002 Sales

SQL>

That didn't work either because D001 already has one employee in the emp table. Note that D002 was not deleted either, even though it has no employees. Finally, drop both tables. If you do the emp table first, there is no problem:

SQL> drop table emp
  2  /

Table dropped.

SQL> drop table dept
  2  /

Table dropped.

SQL>

Go to part 6 

Global Temporary Tables (Part 2)

You can use a global temporary table (GTT) simultaneously in multiple sessions. Each of these sessions can delete, insert, select and update data in the GTT but other sessions cannot see that data. To put it in other words, each session can only see its own data. This example demonstrates this and also looks more closely at ORA-14452. It uses two sessions: session 1 (in red) and session 2 (in blue) which run concurrently. First create a GTT, which retains data after a commit statement, in session 1:

SQL> prompt Starting session 1
Starting session 1
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> create global temporary table gtt1
  2  (session_name varchar2(12))
  3  on commit preserve rows
  4  /

Table created.

 SQL>

Add a single row to the GTT, do a commit then check that the row is still there:

SQL> insert into gtt1 values('Session 1')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from gtt1
  2  /

SESSION_NAME
------------
Session 1

SQL>

Check the GTT's object_id for future reference reference:

SQL> select object_id  from dba_objects
  2  where object_name = 'GTT1'
  3  /

 OBJECT_ID
----------
    235348

SQL> accept stop prompt 'Go to session 2'
Go to session 2

Now go to session 2 as instructed by the SQL prompt. Check that you can see the GTT and that it has the same object_id:

SQL> prompt Starting session 2
Starting session 2
SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> desc gtt1
Name                       Null?    Type
-------------------------- -------- ------------------
SESSION_NAME                        VARCHAR2(12)

SQL> select object_id from dba_objects
  2  where object_name = 'GTT1'
  3  /

 OBJECT_ID
----------
    235348

SQL>

Confirm that you cannot see the data from session 1, insert a row for session 2 and check that you can see it:

SQL> select * from gtt1
  2  /

no rows selected

SQL> insert into gtt1 values('Session 2')
  2  /

1 row created.

SQL> select * from gtt1
  2  /

SESSION_NAME
------------
Session 2

SQL>

Truncate the GTT. Check that the row belonging to session 2 has gone. We will check later to see that the row from session 1 has not been affected:

SQL> truncate table gtt1
  2  /

Table truncated.

SQL> select * from gtt1
  2  /

no rows selected

SQL>

Try to drop the table. This fails as session 1 is still using it:
  

SQL> drop table gtt1

  2  /

drop table gtt1

          *

ERROR at line 1:

ORA-14452: attempt to create, alter or drop an index

on temporary table already in use

SQL> prompt Go back to session 1

Go back to session 1

Now go back to session 1 as instructed. Check that the truncate, which session 2 did, has not removed the row for session 1:

SQL> prompt Resuming session 1

Resuming session 1

SQL> select * from gtt1

  2  /

SESSION_NAME

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

Session 1

SQL>

Try to drop the GTT again as session 1 is the only one using it:

SQL> drop table gtt1

  2  /

drop table gtt1

          *

ERROR at line 1:

ORA-14452: attempt to create, alter or drop an index

on temporary table already in use

SQL>

That failed so look up the error message at the UNIX prompt (sorry the font is so small):

TEST9 > oerr ora 14452

14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"

// *Cause:  An attempt was made to create, alter or drop an index on temporary

//          table which is already in use.

// *Action: All the sessions using the session-specific temporary table have

//          to truncate table and all the transactions using transaction

//          specific temporary table have to end their transactions.

TEST9 >

Here is the key point relating to this example: 

sessions using the session-specific temporary table have to truncate table

Session 2 has already done a truncate so we need to stay in session 1 and do a truncate there:

SQL> truncate table gtt1

  2  /

Table truncated.

SQL>

Now we can drop the GTT:

SQL> drop table gtt1

  2  /

Table dropped.

SQL>

Global Temporary Tables (Part 1)

You can use these to store data temporarily. I had never used them before and decided to try them out. I hit a problem straight away:

SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> create global temporary table gtt
  2  as select * from dba_synonyms
  3  where 1 = 2
  4  on commit delete rows
  5  /
on commit delete rows
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

SQL>

I couldn't find an example in my documentation using CTAS (create table as select) with a global temporary  table. I had a look on the Internet and it seems that several people have already had the same problem. The correct syntax is as follows:

SQL> create global temporary table gtt
  2  on commit delete rows
  3  as select * from dba_synonyms
  4  where 1 = 2
  5  /

Table created.

SQL> desc gtt
Name                       Null?    Type
-------------------------- -------- ------------------
OWNER                      NOT NULL VARCHAR2(30)
SYNONYM_NAME               NOT NULL VARCHAR2(30)
TABLE_OWNER                         VARCHAR2(30)
TABLE_NAME                 NOT NULL VARCHAR2(30)
DB_LINK                             VARCHAR2(128)

SQL>

As the name suggests, you can use these tables for temporary storage. I'm not sure why you might want to save the contents of dba_synonyms but it's only an example:

SQL> insert into gtt select * from dba_synonyms
  2  /

12401 rows created.

SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
    12401

SQL>

If you specify on commit delete rows when you create the temporary table, the rows disappear when you do a commit, as you might expect:

SQL> commit;

Commit complete.

SQL> select count(*) from gtt
  2  /

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

SQL>

The same thing happens after an implied commit:

SQL> insert into gtt select * from dba_synonyms
  2  /

12401 rows created.

SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
    12401

SQL> grant select on gtt to system
  2  /

Grant succeeded.

SQL> select count(*) from gtt
  2  /

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

SQL>

To stop this happening, you have to specify on commit preserve rows instead when you create the table:

SQL> drop table gtt
  2  /

Table dropped.

SQL> create global temporary table gtt
  2  on commit preserve rows
  3  as select * from dba_synonyms
  4  where 1 = 2
  5  /

Table created.

SQL> insert into gtt select * from dba_synonyms
  2  /

12401 rows created.

SQL>

Oracle allows you to use commit work instead of commit to keep in line with standard SQL syntax. Both statements work in the same way:

SQL> commit work;

Commit complete.

SQL>

This time the rows remain in the table:

SQL> select count(*) from gtt
  2  /

  COUNT(*)
----------
     12401

SQL>

And they will stay there for the duration of the session (unless you delete them on purpose). So you cannot drop the table at this point:

SQL> drop table gtt
  2  /
drop table gtt
          *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index
on temporary table already in use

SQL>

However, if you end the session by logging into a new one:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL>

Then log back in as the original user, the table is there but the contents have gone. (It is the table's contents which are temporary, not the table itself.)

SQL> conn /
Connected.
SQL> show user
USER is "ORACLE"
SQL> select count(*) from gtt
  2  /

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

SQL>

And now you can drop the table:

SQL> drop table gtt
  2  /

Table dropped.

SQL>