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>

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>

ORA-25153

If you try to sort anything other than a small amount of data, and the sort has to use disk space, you may see the following error message:

 

SQL> select a.table_name, b.table_name

  2  from dba_tables a, dba_tables b

  3  order by 1;

from dba_tables a, dba_tables b

     *

ERROR at line 2:

ORA-25153: Temporary Tablespace is Empty

 

SQL>

 

This will happen if you:

 

(1)    Copy the datafiles from a source to a target database.

(2)    Recreate the target database’s control file.

(3)    Forget to add a file to its temporary tablespace.

 

You can diagnose this fault as follows. First you have to find the name(s) of the target database’s temporary tablespace(s). In this example there is only one:

 

SQL> select distinct temporary_tablespace

  2  from dba_users

  3  /

 

TEMPORARY_TABLESPACE

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

TEMP

 

SQL>

 

Then you have to see if it contains any temp files:

 

SQL> select file_name from dba_temp_files

  2  where tablespace_name = 'TEMP'

  3  /

 

no rows selected

 

SQL>

 

If it doesn’t, you have to add one. On this occasion, there was still an old tempfile so I was able to reuse it:

 

  1  alter tablespace temp add tempfile

  2  'test10/andrew/temp_files/temp01.dbf'

  3* reuse

SQL> /

 

Tablespace altered.

 

SQL>

 

Then the sort should work:

 

SQL> col table_name format a25

SQL> l

  1  select a.table_name, b.table_name

  2  from dba_tables a, dba_tables b

  3* order by 1

SQL> /

 

TABLE_NAME                TABLE_NAME

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

ACCESS$                   AQ$_ALERT_QT_H

ACCESS$                   WRI$_DBU_FEATURE_USAGE

ACCESS$                   WRI$_ALERT_HISTORY

ACCESS$                   AQ$_ALERT_QT_T

ACCESS$                   WRH$_FILESTATXS_BL

ACCESS$                   AQ$_ALERT_QT_G

ACCESS$                   AQ$_ALERT_QT_I

ACCESS$                   WRH$_FILESTATXS

ACCESS$                   WRH$_WAITSTAT

ACCESS$                   WRH$_TEMPSTATXS

ACCESS$                   WRH$_SQLSTAT

Etc

Mutating Triggers

This post gives an introduction to mutating triggers and was tested on an Oracle 9 database. It contains points which I do not fully understand yet. When I do, I will come back and update it. First, create a table and add some data to it:

 

SQL> CREATE TABLE andrews_table

  2   (employee VARCHAR2(10))

  3  /

 

Table created.

 

SQL> INSERT INTO andrews_table

  2   VALUES ('SMITH')

  3  /

 

1 row created.

 

SQL> INSERT INTO andrews_table

  2   VALUES ('JONES')

  3  /

 

1 row created.

 

SQL> SELECT * FROM andrews_table

  2  /

 

EMPLOYEE

----------

SMITH

JONES

 

Now create a mutating trigger. It fires for each row while the table is being modified so Oracle does not know what value to assign to employee:

 

SQL> CREATE TRIGGER andrews_trigger

  2   AFTER UPDATE ON andrews_table

  3   FOR EACH ROW

  4   BEGIN

  5   UPDATE andrews_table

  6   SET employee = 'BLOGGS';

  7   END;

  8  /

 

Trigger created.

 

SQL> UPDATE andrews_table

  2   SET employee = 'BROWN'

  3  /

UPDATE andrews_table

       *

ERROR at line 1:

ORA-04091: table SYSTEM.ANDREWS_TABLE is

mutating, trigger/function may not see it

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

 

SQL>

 

So when you look at the data, it has not changed:

 

SQL> SELECT * FROM andrews_table

  2  /

 

EMPLOYEE

----------

SMITH

JONES

 

SQL>

 

If you remove FOR EACH ROW, the trigger appears to fire after the update and sets up an infinite loop until Oracle notices what has happened:

 

SQL> CREATE OR REPLACE TRIGGER andrews_trigger

  2   AFTER UPDATE ON andrews_table

  3   BEGIN

  4   UPDATE andrews_table

  5   SET employee = 'GREEN';

  6   END;

  7  /

 

Trigger created.

 

SQL> UPDATE andrews_table

  2   SET employee = 'BLACK'

  3  /

UPDATE andrews_table

       *

ERROR at line 1:

ORA-00036: maximum number of recursive SQL levels

(50) exceeded

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 2

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

ORA-06512: at "SYSTEM.A

 

SQL>

 

And again, the data has not changed:

 

SQL> SELECT * FROM andrews_table

  2  /

 

EMPLOYEE

----------

SMITH

JONES

 

SQL>

 

Even if the trigger only reads the table, Oracle still says it is mutating:

 

SQL> CREATE OR REPLACE TRIGGER andrews_trigger

  2   AFTER UPDATE ON andrews_table

  3   FOR EACH ROW

  4   DECLARE counter NUMBER;

  5   BEGIN

  6   SELECT COUNT(1) INTO counter FROM andrews_table;

  7   END;

  8  /

 

Trigger created.

 

SQL> UPDATE andrews_table

  2   SET employee = 'WHITE'

  3  /

UPDATE andrews_table

       *

ERROR at line 1:

ORA-04091: table SYSTEM.ANDREWS_TABLE is

mutating, trigger/function may not see it

ORA-06512: at "SYSTEM.ANDREWS_TRIGGER", line 3

ORA-04088: error during execution of trigger

'SYSTEM.ANDREWS_TRIGGER'

 

SQL>

 

If you run oerr ora 04091 on the server, the output makes it clear that this is supposed to happen:

 

TEST9 > oerr ora 04091

04091, 00000, "table %s.%s is mutating, trigger/function may not see it"

// *Cause: A trigger (or a user defined plsql function that is referenced in

//         this statement) attempted to look at (or modify) a table that was

//         in the middle of being modified by the statement which fired it.

// *Action: Rewrite the trigger (or function) so it does not read that table.

TEST9 >

 

And the data still has not changed:

 

SQL> SELECT * FROM andrews_table

  2  /

 

EMPLOYEE

----------

SMITH

JONES

 

SQL>

 

But if the trigger only reads the table and you remove FOR EACH ROW, Oracle fires the trigger once after the update, you do not set up an infinite loop and the update works:

 

SQL> CREATE OR REPLACE TRIGGER andrews_trigger

  2   AFTER UPDATE ON andrews_table

  3   DECLARE counter NUMBER;

  4   BEGIN

  5   SELECT COUNT(1) INTO counter FROM andrews_table;

  6   END;

  7  /

 

Trigger created.

 

SQL> UPDATE andrews_table

  2   SET employee = 'KING'

  3  /

 

2 rows updated.

 

SQL>

 

And finally, the data has changed:

 

SQL> SELECT * FROM andrews_table;

 

EMPLOYEE

----------

KING

KING

 

SQL>

Sequences (Part 2)

Go to part 1 

In this example, sequence numbers are added to a table retrospectively. First create a table and add some data to it:

SQL> create table my_table

  2  (first_name varchar2(10))

  3  /

 

Table created.

 

SQL> insert into my_table values ('Andrew')

  2  /

 

1 row created.

 

SQL> insert into my_table values ('Brian')

  2  /

 

1 row created.

 

SQL> insert into my_table values ('Colin')

  2  /

 

1 row created.

 

SQL> insert into my_table values ('Desmond')

  2  /

 

1 row created.

SQL>
  

Then add an extra column to hold the sequence number:
  

SQL> alter table my_table add

  2  (sequence_no number)

  3  /

 

Table altered.

SQL>
  

Create the sequence:
  

SQL> create sequence my_sequence start with 1

  2  /

 

Sequence created.

SQL>
  

And finally, add the sequence numbers with a simple update statement:
  

SQL> update my_table set sequence_no =

  2  my_sequence.nextval

  3  /

 

4 rows updated.

 

SQL> select * from my_table

  2  /

 

FIRST_NAME SEQUENCE_NO

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

Andrew               1

Brian                2

Colin                3

Desmond              4

 

SQL>