To b:\ or not to B:\

Recently, I ran into a small issue with Java permissions. Starting point is the DirList Java procedure to list the content of an OS directory. Here is the code to get started (executed as SCOTT):

CREATE global TEMPORARY TABLE DIR_LIST ( filename VARCHAR2(255) ) ON
 COMMIT
  DELETE rows
/

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DirList"
AS
 import java.io.*;
 import java.sql.*;
 public class DirList { 
  public static void getList(String directory) throws SQLException { 
    File path = NEW File( directory );
    String[]
    list = path.list();
    String element;
    for(INT i = 0; i < list.length;  i++) { 
      element = list[i];
      #sql { INSERT INTO DIR_LIST (FILENAME) VALUES (:element) };
    } 
  } 
 }
/

CREATE OR REPLACE PROCEDURE get_dir_list ( p_directory IN VARCHAR2 ) 
  AS language java name 'DirList.getList( java.lang.String )'; 
/

Don't grant the role JAVAUSERPRIV but use a more granular option. Grant read permission to SCOTT on the d: drive:

EXECUTE dbms_java.grant_permission( 'SCOTT', 'java.io.FilePermission','d:\','read' );

Everything is in place now, time to run the procedure:

EXECUTE get_dir_list('D:\');
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: 
the Permission (java.io.FilePermission D:\ read) 
has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(SCOTT|PolicyTableProxy(SCOTT))
ORA-06512: at "SCOTT.GET_DIR_LIST", line 0
ORA-06512: at line 2

An error occurred, perhaps the directory did not exists? Executing a dir D:\ at the command prompt on the database server lists the files. The command prompt is not case sensitive, executing a dir d:\ returns the same listing. Perhaps but DBMS_JAVA.GRANT_PERMISSION is case sensitive?

EXECUTE get_dir_list( 'd:\' );
PL/SQL procedure successfully completed

SELECT * FROM DIR_LIST
/
FILENAME
--------------------------------------------------------------------------------
oracle
RECYCLER
System Volume Information

Returning into clause and post statement triggers

If you are using RETURNING INTO clauses with DML statements and you are also using (post statement) triggers then you should be aware of the following caveat where the values of the RETURNING INTO do not match the column values.

To show this behavior, set up a small test case first. A table t1 with one column c1 with an after insert statement trigger, modifying the c1 column.

SQL> CREATE TABLE t1(c1 VARCHAR2(10));

Table created.

SQL>
SQL> CREATE TRIGGER t_ais_t1 AFTER INSERT ON t1
2  BEGIN
3    UPDATE t1
4    SET c1 = 'TRIGGER';
5  END;
6  /

Trigger created.

Insert a row in t1 and return the new value of c1 into bind variable c1.

SQL> VARIABLE c1 VARCHAR2(10)
SQL>
SQL> INSERT INTO t1(c1)VALUES('SQLPLUS') RETURNING c1 INTO :c1
2  /

1 row created.

And inspect the contents of the table and the value of the bind variable.

SQL>
SQL> SELECT c1 "column",
2    :c1 "bind"
3  FROM t1
4  /

column     bind
---------- ----------
TRIGGER    SQLPLUS

1 row selected.

As expected the value of column c1 is TRIGGER. The value is the bind variable is SQLPLUS. This value is set before the after statement trigger starts.

Although this code is not production code, you might notice this behavior in production code as well: I discovered this behavior hidden in a mutating table workaround.

Enumeration types in PL/SQL?

In the book Oracle PL/SQL for DBAs the definition of the type BOOLEAN is shown:

SELECT text
  FROM all_source
 WHERE owner = 'SYS'
   AND type = 'PACKAGE'
   AND name = 'STANDARD' 
   AND line <5
/

TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
-------------------------------------------------------------
package STANDARD AUTHID CURRENT_USER is              

  /********** Types and subtypes, do not reorder **********/ 
  type BOOLEAN is (FALSE, TRUE);

4 rows selected.

Have you seen that kind of type definition before? I did, it looks like a Delphi enumeration type when you replace the is with = . Let's define our own type.

create package TRAFFIC is 
  type LIGHT_T is (RED, YELLOW, GREEN);
end;
/

Warning: Package created with compilation errors.

SHOW ERR

LINE/COL   TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
-------------------------------------------------------------
2,19       PLS-00505: User Defined Types may only be defined 
           as PLSQL Tables or Records                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
2,3        PL/SQL: Declaration ignored                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      

2 rows selected

Perhaps this is an enhancement request for Oracle 11g release 2?