%ROWTYPE

This was tested on Oracle 11.2. You can use %ROWTYPE to declare a record with the same structure as a row from a database table:

SQL> DECLARE

  2   OBJECT_DETAIL DBA_OBJECTS%ROWTYPE;

  3  BEGIN

  4   SELECT *

  5    INTO OBJECT_DETAIL

  6    FROM DBA_OBJECTS

  7    WHERE OBJECT_ID =

  8    (SELECT MIN(OBJECT_ID) FROM DBA_OBJECTS);

  9   DBMS_OUTPUT.PUT_LINE

10   ('OWNER = '||OBJECT_DETAIL.OWNER);

11   DBMS_OUTPUT.PUT_LINE

12   ('OBJECT_NAME = '||OBJECT_DETAIL.OBJECT_NAME);

13   DBMS_OUTPUT.PUT_LINE

14   ('OBJECT_ID = '||OBJECT_DETAIL.OBJECT_ID);

15  END;

16  /

OWNER = SYS

OBJECT_NAME = C_OBJ#

OBJECT_ID = 2

PL/SQL procedure successfully completed.

SQL>

%TYPE

This example was tested on Oracle 11.2. It shows how you can use %TYPE to create a variable like another variable. In this example, a variable called MAX_OBJ_ID is defined like OBJECT_ID from DBA_OBJECTS. Then SELECT INTO is used to select the maximum value of OBJECT_ID into it and the result is displayed:

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2   MAX_OBJ_ID DBA_OBJECTS.OBJECT_ID%TYPE;

  3  BEGIN

  4   SELECT MAX(OBJECT_ID)

  5    INTO MAX_OBJ_ID

  6    FROM DBA_OBJECTS;

  7   DBMS_OUTPUT.PUT_LINE('MAX_OBJ_ID = '||MAX_OBJ_ID);

  8  END;

  9  /

MAX_OBJ_ID = 132369

PL/SQL procedure successfully completed.

SQL>

Finally, the same result is obtained in SQL*Plus:

SQL> SELECT MAX(OBJECT_ID) FROM DBA_OBJECTS

  2  /

MAX(OBJECT_ID)

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

        132369

SQL>

Sequences (Part 4)

This was tested on Oracle 11.2. If you create a sequence and tell it to cycle, it goes back to the start again as soon as it reaches its maximum value:

SQL> create sequence andrew

  2  cache 2

  3  cycle

  4  maxvalue 3

  5  /

Sequence created.

SQL> select andrew.nextval from dual

  2  /

   NEXTVAL

----------

         1

SQL> select andrew.nextval from dual

  2  /

   NEXTVAL

----------

         2

SQL> select andrew.nextval from dual

  2  /

   NEXTVAL

----------

         3

SQL> select andrew.nextval from dual

  2  /

   NEXTVAL

----------

         1

SQL>

If you tell the sequence not to cycle and it reaches its maximum value, you get an ORA-08004 when you try to select the next term:

SQL> alter sequence andrew nocycle

  2  /

Sequence altered.

SQL> select andrew.nextval from dual

  2  /

   NEXTVAL

----------

         2

SQL> select andrew.nextval from dual

  2  /

   NEXTVAL

----------

         3

SQL> select andrew.nextval from dual

  2  /

select andrew.nextval from dual

*

ERROR at line 1:

ORA-08004: sequence ANDREW.NEXTVAL exceeds MAXVALUE

and cannot be instantiated

SQL>

Error Handling in SQL*Plus

If you want to know if there has been an error in a SQL*Plus session, you need to do this explicitly. Here is a UNIX shell script:

ORACLE 11 > cat error1.ksh

sqlplus / << failure

select to_number('A') from dual

/

exit

failure

echo "Return code after failure = $?"

ORACLE 11 >

When you run it, there is a failure in the SQL*Plus session but UNIX is not aware of this:

ORACLE 11 > ./error1.ksh

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:41:54 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>   2  select to_number('A') from dual

                 *

ERROR at line 1:

ORA-01722: invalid number

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Return code after failure = 0

ORACLE 11 >

If you wish to tell UNIX there has been an error, you should add whenever sqlerror exit failure to the SQL*Plus session:

ORACLE 11 > cat error2.ksh

sqlplus / << failure

whenever sqlerror exit failure

select to_number('A') from dual

/

exit

failure

echo "Return code after failure = $?"

ORACLE 11 >

Then the return code will be non zero so UNIX will know about the error:

ORACLE 11 > ./error2.ksh

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:45:44 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL>   2  select to_number('A') from dual

                 *

ERROR at line 1:

ORA-01722: invalid number

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Return code after failure = 1

ORACLE 11 >

Grant Any Object Privilege

This example was tested on Oracle 11.2. It demonstrates the grant any object privilege system privilege. First create three users:

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

User created.

SQL> grant create session, create table

  2  to andrew

  3  /

Grant succeeded.

SQL> create user fred

  2  identified by bloggs

  3  /

User created.

SQL> grant create session, grant any object privilege

  2  to fred

  3  /

Grant succeeded.

SQL> create user nosey

  2  identified by parker

  3  /

User created.

SQL> grant create session to nosey

  2  /

Grant succeeded.

SQL>

Andrew Reid has won a lot of money on the lottery. He creates a table to record his bank balance but wants to keep it secret:

SQL> conn andrew/reid

Connected.

SQL> create table bank_account

  2  as select 1234567 balance

  3  from dual

  4  /

Table created.

SQL> select balance from bank_account

  2  /

   BALANCE

----------

   1234567

SQL>

Nosey Parker hears a rumour about the lottery win and tries to look at Andrew's bank account:

SQL> conn nosey/parker

Connected.

SQL> select balance from andrew.bank_account

  2  /

select balance from andrew.bank_account

                           *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Nosey knows that Fred Bloggs has the grant any object privilege system privilege and asks him to help. Fred grants select privilege on Andrew's bank_account table to Nosey: 

SQL> conn fred/bloggs

Connected.

SQL> grant select on andrew.bank_account to nosey

  2  /

Grant succeeded.

SQL>

Nosey can then see how much money Andrew has:

SQL> conn nosey/parker

Connected.

SQL> select balance from andrew.bank_account

  2  /

   BALANCE

----------

   1234567

SQL>

You might think that Fred would appear in dba_tab_privs as the grantor of this privilege as he ran the grant statement but you would be wrong. Andrew appears as the grantor instead: 

SQL> conn / as sysdba

Connected.

SQL> select grantor from dba_tab_privs

  2  where grantee = 'NOSEY'

  3  and owner = 'ANDREW'

  4  and table_name = 'BANK_ACCOUNT'

  5  and privilege = 'SELECT'

  6  /

GRANTOR

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

ANDREW

SQL>

How NOT to Install Oracle Software

Once upon a time there was a DBA who always used the Oracle Universal Installer (OUI) to install Oracle software. Then a manager asked him to install it by copying an Oracle home from one place to another using operating system commands. The DBA said this was a bad idea but was told to do it anyway as it had never caused any problems in the past. The new Oracle home worked OK for some time until the company hit an Oracle bug. Oracle knew about this bug and there was a patch for it. However, the company were unable to apply it as OPatch (Oracle's patching utility) would not work. Oracle could not help them either as the installation method used was unsupported. After that the company always used the OUI to install Oracle software and OPatch worked happily ever after.

I think this was the bug which caused the problem. It was bug 4192148, also known as bug 4604896. It affected Oracle 9.2.0.7.0. The following SQL statement creates a view. Line 2 contains a select a.* and the comma separating it from the bytes column is at the start of line 3:

SQL> create or replace view object_sizes as

  2  select a.*

  3  , bytes

  4  from user_objects a, user_segments b

  5  where a.object_name = b.segment_name

  6  /

View created.

SQL>

You can select object_name, which is at the start of select a.*:

SQL> select object_name from object_sizes

  2  where rownum < 6

  3  /

OBJECT_NAME

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

TOT_READ_WRITES

PACSTAT1

PACSTAT2

LOCK_TYPES

MFSESS_IO

SQL>

... and you can select secondary, which is at the end:

SQL> select secondary from object_sizes

  2  where rownum < 6

  3  /

S

-

N

N

N

N

N

SQL>

... but if you try to select bytes, you get an ORA-00936:

SQL> select bytes from object_sizes

  2  where rownum < 6

  3  /

select bytes from object_sizes

                  *

ERROR at line 1:

ORA-00936: missing expression

SQL>

That's because the view is not stored correctly. There is a comma at the end of the 4th line after a."SECONDARY". There is another comma at the start of the 5th line before bytes but there is nothing between them:

SQL> set long 4000

SQL> select text from user_views

  2  where view_name = 'OBJECT_SIZES'

  3  /

TEXT

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

select a."OBJECT_NAME",a."SUBOBJECT_NAME",a."OBJECT_ID"

,a."DATA_OBJECT_ID",a."OBJECT_TYPE",a."CREATED",a."LAST

_DDL_TIME",a."TIMESTAMP",a."STATUS",a."TEMPORARY",a."GE

NERATED",a."SECONDARY",

, bytes

from user_objects a, user_segments b

where a.object_name = b.segment_name

SQL>

You cannot recompile the view either:

SQL> alter view object_sizes compile

  2  /

alter view object_sizes compile

                              *

ERROR at line 1:

ORA-00936: missing expression

SQL>

Assuming you have used the OUI to install the Oracle software, you can apply the appropriate patch (I can no longer remember the number but I guess it must still be on My Oracle Support). If not, you can rewrite the view by putting the comma on the same line as the select a.* then everything works correctly:

SQL> create or replace view object_sizes as

  2  select a.*,

  3  bytes

  4  from user_objects a, user_segments b

  5  where a.object_name = b.segment_name

  6  /

View created.

SQL> select object_name from object_sizes

  2  where rownum < 6

  3  /

OBJECT_NAME

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

TOT_READ_WRITES

PACSTAT1

PACSTAT2

LOCK_TYPES

MFSESS_IO

SQL> select secondary from object_sizes

  2  where rownum < 6

  3  /

S

-

N

N

N

N

N

SQL> select bytes from object_sizes

  2  where rownum < 6

  3  /

     BYTES

----------

     16384

     40960

     32768

     49152

     16384

SQL> set long 4000

SQL> select text from user_views

  2  where view_name = 'OBJECT_SIZES'

  3  /

TEXT

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

select a."OBJECT_NAME",a."SUBOBJECT_NAME",a."OBJECT_ID"

,a."DATA_OBJECT_ID",a."OBJECT_TYPE",a."CREATED",a."LAST

_DDL_TIME",a."TIMESTAMP",a."STATUS",a."TEMPORARY",a."GE

NERATED",a."SECONDARY",

bytes

from user_objects a, user_segments b

where a.object_name = b.segment_name

SQL> alter view object_sizes compile

  2  /

View altered.

SQL>

SYSTIMESTAMP

This is how you extract the microseconds portion of the timestamp. It was tested on Oracle 9:

SQL> col systimestamp format a40

SQL> col microseconds format a12

SQL> select systimestamp,

  2  to_char(systimestamp,'ff') microseconds

  3  from dual

  4  /

SYSTIMESTAMP                             MICROSECONDS

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

20-JUN-12 12.17.11.790899 +01:00         790899

SQL>

ORA-00990

This was tested on Oracle 11. You can grant roles and system privileges in the same statement. But if you want to include object privileges, you need to use a new GRANT statement:

SQL> grant dba, alter system,

  2  execute on sys.dbms_lock

  3  to andrew

  4  /

grant dba, alter system,

      *

ERROR at line 1:

ORA-00990: missing or invalid privilege

SQL> grant dba, alter system

  2  to andrew

  3  /

Grant succeeded.

SQL> grant execute on sys.dbms_lock

  2  to andrew

  3  /

Grant succeeded.

SQL>

ORA-02020

Tested on Oracle 9. I reconfigured several database links then ran a test script to check them all:

SQL> conn andrew/reid

Connected.

SQL> select * from dual@link1

  2  /

D

-

X

SQL> select * from dual@link2

  2  /

D

-

X

SQL> select * from dual@link3

  2  /

D

-

X

SQL> select * from dual@link4

  2  /

D

-

X

SQL> select * from dual@link5

  2  /

select * from dual@link5

                   *

ERROR at line 1:

ORA-02020: too many database links in use

SQL>

The 5th query failed. This was because the value of the open_links parameter was too low:

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'open_links'

  3  /

VALUE

----------

4

SQL>

Once you have run a query down a database link, it is retained until you do a commit or rollback. In the example below, a commit after the 1^st query allows the 5^th to work. Changing the value of the open_links parameter is also an option. I will try to look at this in a future post:

SQL> conn andrew/reid

Connected.

SQL> select * from dual@link1

  2  /

D

-

X

SQL> commit

  2  /

Commit complete.

SQL> select * from dual@link2

  2  /

D

-

X

SQL> select * from dual@link3

  2  /

D

-

X

SQL> select * from dual@link4

  2  /

D

-

X

SQL> select * from dual@link5

  2  /

D

-

X

SQL>