Logarithms

This example was tested on Oracle 11.2. You can display logarithms as follows. Select log(a,b) from dual where a is the base and b is the number you want the logarithm of:

SQL> select log(2,8) "Log base 2 of 8" from dual

  2  /

Log base 2 of 8

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

              3

SQL> select log(9,3) "Log base 9 of 3" from dual

  2  /

Log base 9 of 3

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

             .5

SQL> select log(1000000,100)

  2  "Log base 1000000 of 100" from dual

  3  /

Log base 1000000 of 100

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

             .333333333

SQL>

The base must be a positive number > 1:

SQL> select log(0,1) "Log base 0 of 1" from dual

  2  /

select log(0,1) "Log base 0 of 1" from dual

           *

ERROR at line 1:

ORA-01428: argument '0' is out of range

SQL>

The number must be positive:

SQL> select log(5,-3) "Log base 5 of -3" from dual

  2  /

select log(5,-3) "Log base 5 of -3" from dual

             *

ERROR at line 1:

ORA-01428: argument '-3' is out of range 

SQL>

To multiply 2 numbers, take the logarithm of each and add them together. Then raise the base to the power of the sum to get the answer:

SQL> select power(3,log(3,6)+log(3,7))

  2  "Should be 42" from dual

  3  /

Should be 42

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

          42

SQL>

ORA-01934

This example was tested on Oracle 11.2. It should be fairly obvious that you cannot grant a role to itself. You get an ORA-01934 if you try:

SQL> create role andrew1;

Role created.

SQL> grant andrew1 to andrew1;

grant andrew1 to andrew1

*

ERROR at line 1:

ORA-01934: circular role grant detected

SQL>

However, if you weren't paying attention, you might, for example, grant one role to a second role then grant that second role to a third role. If you later tried to grant the third role back to the first role, you would get another ORA-01934: 

SQL> create role andrew2;

Role created.

SQL> create role andrew3;

Role created.

SQL> grant andrew1 to andrew2;

Grant succeeded.

SQL> grant andrew2 to andrew3;

Grant succeeded.

SQL> grant andrew3 to andrew1;

grant andrew3 to andrew1

*

ERROR at line 1:

ORA-01934: circular role grant detected

SQL>

SET SCAN OFF and SET SCAN ON

This was tested on Oracle 11.2. It's not a good idea to use an ampersand in a PL/SQL comment. Oracle assumes it is the start of a substitution variable and prompts you to input a value:

SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands & how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /
Enter value for how:
old   3: -- Ampersands & how they cause problems in PL/SQL.
new   3: -- Ampersands  they cause problems in PL/SQL.

Procedure created.

SQL>

... and the comment entry is not stored correctly in the database:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands  they cause problems in PL/SQL.
null;
end;

SQL>

One way to deal with this is to get your developers to use the word and instead:

SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands and how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /

Procedure created.

SQL>

Then the comment is stored correctly:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands and how they cause problems in PL/SQL.
null;
end;

SQL>

If that is not possible, you can do set scan off before compiling the PL/SQL then Oracle does not treat the ampersand as the start of a substition variable. Before you do this, you need to be sure that your PL/SQL does not contain any genuine substitution variables:

SQL> set scan off
SQL> show scan
scan OFF
SQL> create or replace procedure do_nothing as
  2  begin
  3  -- Ampersands & how they cause problems in PL/SQL.
  4  null;
  5  end;
  6  /

Procedure created.

SQL>

... and again the comment is stored correctly:

SQL> select text from user_source
  2  where name = 'DO_NOTHING'
  3  /

TEXT
-------------------------------------------------------
procedure do_nothing as
begin
-- Ampersands & how they cause problems in PL/SQL.
null;
end;

SQL>

It's probably best to set scan on afterwards in case any SQL which follows includes a real substitution variable:

SQL> set scan on
SQL> show scan
scan ON
SQL>

ORA-00932

This was tested on Oracle 9 and Oracle 11.2. If you are searching for a piece of text, which you believe is in one of your views, you might try to do it like this:

SQL> select owner, view_name

  2  from dba_views

  3  where upper(text) like '%BLAH%'

  4  /

where upper(text) like '%BLAH%'

            *

ERROR at line 3:

ORA-00932: inconsistent datatypes: expected NUMBER got

LONG

SQL>

It fails because TEXT is a LONG column:

SQL> desc dba_views

Name                       Null?    Type

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

OWNER                      NOT NULL VARCHAR2(30)

VIEW_NAME                  NOT NULL VARCHAR2(30)

TEXT_LENGTH                         NUMBER

TEXT                                LONG

TYPE_TEXT_LENGTH                    NUMBER

TYPE_TEXT                           VARCHAR2(4000)

OID_TEXT_LENGTH                     NUMBER

OID_TEXT                            VARCHAR2(4000)

VIEW_TYPE_OWNER                     VARCHAR2(30)

VIEW_TYPE                           VARCHAR2(30)

SUPERVIEW_NAME                      VARCHAR2(30)

SQL>

One workaround is to SELECT the OWNER, VIEW_NAME and TEXT of all your views and SPOOL them to a file. Then you can search the spool file produced with vi or Notepad.

How to See the Value of a PL/SQL Bind Variable

You can see the value of a PL/SQL bind variable like this:

Add the following line to your database’s parameter file:

event='10046 trace name context forever, level 4'

Bounce the database.

Run some PL/SQL which uses a bind variable:

SQL> set serveroutput on

SQL> variable bind_variable varchar2(3)

SQL> exec :bind_variable := 'SYS'

PL/SQL procedure successfully completed.

SQL> declare

  2   counter number;

  3  begin

  4   select count(*) into counter

  5    from dba_tables

  6    where owner = :bind_variable;

  7   dbms_output.put_line('Tables owned by SYS = '||counter);

  8  end;

  9  /

Tables owned by SYS = 322

PL/SQL procedure successfully completed.

SQL>

Look at the appropriate lines in the trace file to see the value of the bind variable you have just used (click on your browser's zoom button if necessary as the text is quite small):

=====================

PARSING IN CURSOR #1 len=173 dep=0 uid=22 oct=47 lid=22 tim=46193203462144 hv=1143714290 ad='2b36288'

declare

counter number;

begin

select count(*) into counter

  from dba_tables

  where owner = :bind_variable;

dbms_output.put_line('Tables owned by SYS = '||counter);

end;

END OF STMT

PARSE #1:c=0,e=1024,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=46193203462144

BINDS #1:

bind 0: dty=1 mxl=32(03) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=32 offset=0

   bfp=1404b4520 bln=32 avl=03 flg=05

   value="SYS"

=====================

PARSING IN CURSOR #2 len=50 dep=1 uid=22 oct=3 lid=22 tim=46193203463168 hv=2267380128 ad='2ba90d0'

SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER = :B1

END OF STMT

PARSE #2:c=0,e=1024,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=2,tim=46193203463168

BINDS #2:

bind 0: dty=1 mxl=32(03) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0

   bfp=1404b48a0 bln=32 avl=03 flg=05

   value="SYS"

EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=2,tim=46193203463168

FETCH #2:c=50000,e=60416,p=0,cr=702,cu=0,mis=0,r=1,dep=1,og=2,tim=46193203523584

EXEC #1:c=50000,e=61440,p=0,cr=702,cu=0,mis=0,r=1,dep=0,og=2,tim=46193203523584

=====================

PL/SQL Loop Labels

This was tested on Oracle 11.2. PL/SQL loops can have labels, which must appear just before the LOOP statement. They must be undeclared identifiers enclosed by double angle brackets. If you wish, you can repeat the label at the end of the loop:

SQL> DECLARE

  2   X NUMBER;

  3   Y NUMBER;

  4  BEGIN

  5   X := 1;

  6   <<OUTER_LOOP>>

  7   LOOP

  8    Y := 1;

  9    <<INNER_LOOP>>

 10    LOOP

 11     DBMS_OUTPUT.PUT_LINE('X = '||X||' and Y = '||Y);

 12     Y := Y + 1;

 13     EXIT WHEN Y > 2;

 14    END LOOP INNER_LOOP;

 15    X := X + 1;

 16    EXIT WHEN X > 2;

 17   END LOOP OUTER_LOOP;

 18  END;

 19  /

X = 1 and Y = 1

X = 1 and Y = 2

X = 2 and Y = 1

X = 2 and Y = 2

PL/SQL procedure successfully completed.

SQL> DECLARE

  2   X NUMBER;

  3   Y NUMBER;

  4  BEGIN

  5   X := 1;

  6   <<OUTER_LOOP>>

  7   LOOP

  8    Y := 1;

  9    LOOP

 10     DBMS_OUTPUT.PUT_LINE('X = '||X||' and Y = '||Y);

 11     Y := Y + 1;

 12     EXIT WHEN Y > 2;

 13     EXIT OUTER_LOOP WHEN X + Y > 6;

 14    END LOOP;

 15   X := X + 1;

 16   END LOOP;

 17  END;

 18  /

X = 1 and Y = 1

X = 1 and Y = 2

X = 2 and Y = 1

X = 2 and Y = 2

X = 3 and Y = 1

X = 3 and Y = 2

X = 4 and Y = 1

X = 4 and Y = 2

X = 5 and Y = 1

PL/SQL procedure successfully completed.

SQL>

max_enabled_roles (Part 2)

This example was tested on Oracle 9. It shows the use of the max_enabled_roles initialisation parameter, which was deprecated in Oracle 10. For the purposes of the test, this database has max_enabled_roles set to 5:

SQL> select value from v$parameter

  2  where name = 'max_enabled_roles'

  3  /

VALUE

-----

5

SQL>

To test valid and invalid scenarios, we need 6 roles:

SQL> create role role1

  2  /

Role created.

SQL> create role role2

  2  /

Role created.

SQL> create role role3

  2  /

Role created.

SQL> create role role4

  2  /

Role created.

SQL> create role role5

  2  /

Role created.

SQL> create role role6

  2  /

Role created.

SQL>

Create a user and grant 5 of these roles to him:

SQL> grant create session,

  2  role1, role2, role3, role4, role5

  3  to andrew identified by reid

  4  /

Grant succeeded.

SQL>

When a user logs in, Oracle enables all roles which have been granted explicitly to him:

SQL> conn andrew/reid

Connected.

SQL>

A user can see his enabled roles in the SESSION_ROLES view:

SQL> select role from session_roles

  2  /

ROLE

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

ROLE1

ROLE2

ROLE3

ROLE4

ROLE5

SQL>

Now grant a 6th role to the user:

SQL> conn / as sysdba

Connected.

SQL> grant role6 to andrew

  2  /

Grant succeeded.

SQL>

This stops the user logging on as he would then have more than 5 enabled roles:

SQL> conn andrew/reid

ERROR:

ORA-01925: maximum of 5 enabled roles exceeded 

Warning: You are no longer connected to ORACLE.

SQL>

Now change the user so that he only has 4 enabled roles:

SQL> conn / as sysdba

Connected.

SQL> alter user andrew default role

  2  all except role5, role6

  3  /

User altered.

SQL>

This allows the user to login again:

SQL> conn andrew/reid

Connected.

SQL> select role from session_roles

  2  /

ROLE

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

ROLE1

ROLE2

ROLE3

ROLE4

SQL>

... but if he tries to enable all 6 roles, he gets an ORA-01925 again:

SQL> set role all

  2  /

set role all

*

ERROR at line 1:

ORA-01925: maximum of 5 enabled roles exceeded

SQL> set role role1, role2, role3, role4, role5, role6

  2  /

set role role1, role2, role3, role4, role5, role6

*

ERROR at line 1:

ORA-01925: maximum of 5 enabled roles exceeded

SQL>

... and his list of enabled roles remains unchanged:

SQL> select role from session_roles

  2  /

ROLE

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

ROLE1

ROLE2

ROLE3

ROLE4

SQL>

LAST_NUMBER Column in DBA_SEQUENCES

The LAST_NUMBER column in DBA_SEQUENCES shows you what a sequence's NEXTVAL would be if its cached values were lost. In the example below, a sequence is created with a CACHE of 13 values.

SQL> conn / as sysdba

Connected.

SQL> create sequence sequence1

  2  increment by 1

  3  start with 1

  4  cache 13

  5  /

Sequence created.

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

         1

SQL>

There are 13 values pre-calculated and stored in memory and the sequence increases by 1 each time its NEXTVAL is selected. The LAST_NUMBER is therefore 14:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         14

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

         2

SQL>

Selecting the sequence's NEXTVAL does not make the sequence add an extra number to the cache so the LAST_NUMBER is still 14 afterwards:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         14

SQL>

By flushing the shared pool, the sequence's cache is destroyed:

SQL> alter system flush shared_pool

  2  /

System altered.

SQL>

So the sequence's NEXTVAL is equal to LAST_NUMBER shown above:

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

        14

SQL>

... and a fresh cache of 13 terms is calculated so LAST_NUMBER is now 27:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         27

SQL>

A SHUTDOWN NORMAL does not remove the sequence's cached values:

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   76518176 bytes

Fixed Size                   733984 bytes

Variable Size              67108864 bytes

Database Buffers            8388608 bytes

Redo Buffers                 286720 bytes

Database mounted.

Database opened.

SQL>

So the NEXTVAL is just 1 greater than the previous term:

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

        15

SQL>

However, the SHUTDOWN NORMAL does seem to check that the sequence still has 13 cached values as its LAST_NUMBER goes up by 1:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         28

SQL>

A SHUTDOWN IMMEDIATE has the same effect:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   76518176 bytes

Fixed Size                   733984 bytes

Variable Size              67108864 bytes

Database Buffers            8388608 bytes

Redo Buffers                 286720 bytes

Database mounted.

Database opened.

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

        16

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         29

SQL>

However, a SHUTDOWN ABORT flushes the cache: 

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   76518176 bytes

Fixed Size                   733984 bytes

Variable Size              67108864 bytes

Database Buffers            8388608 bytes

Redo Buffers                 286720 bytes

Database mounted.

Database opened.

SQL>

The NEXTVAL goes up to the LAST_NUMBER value shown just above:

SQL> select sequence1.nextval from dual

  2  /

   NEXTVAL

----------

        29

SQL>

... and a fresh cache of terms is calculated for the sequence so its LAST_NUMBER goes up by 13 again:

SQL> select last_number from dba_sequences

  2  where sequence_name = 'SEQUENCE1'

  3  /

LAST_NUMBER

-----------

         42

SQL>