Database upgrade apex.oracle.com

Read my blog post Database upgrade apex.oracle.com.

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

APEX 3.1 On XE

On 29 Februari 2008, Oracle released Application Express 3.1 . The page Oracle Database XE and Application Express 3.1 provides upgrade instructions .

APEX_GLOBAL_ARRAYS

When you are working with global package arrays (apex_application.g_f01 ... apex_application.g_f50) in Oracle Application Express (APEX), the following package procedure apex_global_arrays can help in reducing development time especially time spent in the bugs and features department.

PACKAGE apex_global_arrays
IS
PROCEDURE print(maxlength  NUMBER DEFAULT 20);
END;

The procedure print prints a HTML table with the contents of the arrays only if the page is run in debug mode. The parameter maxlength truncates the value of each table cell at the given number. You can call the procedure in a separate On Submit application process. In this way the table is rendered even when succeeding On Submit processes fail. Define once, execute everywhere. And of course, you can call it in page process as well.

A picture says more than a thousand words. Below is an example of an updateable report based on the emp table.

After pressing submit, the following output is shown. Compare the g_f01 array with the checkboxes above. The g_f01 array is not sparse like the g_f08 (commission) array. Also, when an array value does not exists, no table cell is printed, i.e. g_f05(9); King does not have a manager.

The source can be found here.

Notes

• Due to the name change of HTMLDB into APEX the public synonyms apex_application and htmldb_application point both to the wwv_flow package where the associative arrays are declared. The global package arrays are also known as referencing arrays in APEX documentation or associative arrays in the PL/SQL language.

• On apex.oracle.com you have to enter the debug mode after the page is submitted in order to render the table. On Oracle XE, you have to enter debug mode before submitting the page.

• You can install APEX_GLOBAL_ARRAYS in the parsing schema or in a separate schema. You can find the parsing schema of your application on application definition page. When you choose for a separate schema you have to grant execute right to the parsing schema and create a (public) synonym for APEX_GLOBAL_ARRAYS.

Introducing RuleGen

I've been working with CDM Ruleframe for a few years now. Recently I've attended a presentation about another framework focusing on business rules called RuleGen.

RuleGen is a framework written in PL/SQL that generates  code to maintain data integrity constraints. Right now RuleGen implements table constraints, i.e. at most one president allowed in EMP,  and database constraints, i.e. every department has at least two employees. Enforcing a data integrity constraint is done in two steps. The first step is about administering the affected rows of a transaction (inserts, updates and deletes). The second step is validating the constraint against the affected rows. If the constraint is violated an exception is raised.  You can also say the first step is about WHEN  the constraint is validated and the second step is HOW the constraint is validated.

There are switches to influence the runtime behavior of RuleGen like the execution model: stop on the first constraint violation or continue after the first constraint violation in order to collect a list of constraint violations (like the message stack in CDM Ruleframe). It is also possible to defer checking (in contrary to immediate checking). 

A difference between CDM Ruleframe and RuleGen is the relationship with Oracle Designer. RuleGen is not integrated with Oracle Designer where CDM Ruleframe is. The definition (remember HOW and WHEN) of data integrity constraints is either done with SQL*Plus or with a small APEX application. Another  difference between RuleGen and CDM Ruleframe is there is no PL/SQL coding required with RuleGen. The definition of data integrity constraints is done in SQL queries completely.

In my opinion, the functionality of RuleGen looks very promising. Keep an eye on it!

Tagged!

 Gareth Roberts tagged me. Thanks for the invitation. And here are 8 things you did not know about me.

1. My nickname Jornica is derived from Jorrit Nijssen. However five characters is often not enough for an username. By adding some extra random chosen characters ca it is long enough...
2. I maintain another non Oracle related blog as well: Etc.
3. Keywords of my favorite holidays: sea, sun and hills. Did I mention volcanoes?
4. My roots are in the southern part of the Netherlands; I like music from artists like De Janse Bagge Bend, Gé Reinders and Rowwen Hèze.
5. I like to watch fire engine movies on You Tube with my son.
6. My family believes I'm a good cook.
7. My favorite Linux distribution is homemade Linux From Scratch. It's about building your own Linux system from source code.
8. I use Lifehacker as a source for new software: Windows tools and utilities.

And now, who's next (sorry 3 out of 8).

• Patch
• Ome-b (dutch)
• Software Engineer Thoughts