MacLochlainns Weblog

I saw a post on the forum and fielded a question from my students on how you can write a database trigger that uses Java for the programming logic. I provided two approaches in this blog page. One lets Java raise the exception, which becomes an unhandled exception in SQL. The other implements the library as a function, and uses an IF statement to raise an exception – with RAISE_APPLICATION_ERROR.

I’m partial to the second approach but think the Fusion middleware may yet present a better option in the future. You should take a peak at the oracle.dss.util.TypeNotSupportedException.

A while back, I demonstrated how you can read a file directory from SQL. Here’s how you can delete an external file referenced by a BFILE column. There’s only one problem with this level of the architecture, there’s no rollback. However, it does let you delete the external file when you want to delete the column value.

You can find the blog page here …

A twist or quirk, I’m not sure. I was working on a internal Java library to delete external files when I encountered an ORA-29532. The error said that I needed to grant read file permissions but it really required read, write, and delete permissions. Worse yet, they’d already been granted.

Then, I remembered that this is the instance that I built before assigning a hostname in the C:\WINDOWS\system32\drivers\etc\hosts file. I’d blogged about it earlier, describing how to drop and recreate the Enterprise Manager. I had a hunch, and it worked.

I ran the following:

BEGIN
  DBMS_JAVA.GRANT_PERMISSION('SYSTEM'
                             ,'SYS:java.net.SocketPermission'
                             ,'<strong>hostname</strong><em></em>:1521'
                             ,'connect,resolve');
END;
/

Then, I reconnected as the test user and everything worked fine. The full error received:

BEGIN delete_file(get_canonical_local_bfilename(:locator));
*
ERROR AT line 1:
ORA-29532: JAVA call terminated BY uncaught JAVA EXCEPTION:
JAVA.security.AccessControlException: the Permission (JAVA.io.FilePermission
C:\JavaDev\BFileFramework\HarryPotter1.png read) has NOT been granted TO PLSQL.
The PL/SQL TO grant this IS
dbms_java.grant_permission( 'PLSQL',
'SYS:java.io.FilePermission', 'C:\JavaDev\BFileFramework\HarryPotter1.png','read' )
ORA-06512: AT "PLSQL.DELETE_FILE", line 1
ORA-06512: AT line 1

You can get the full Java stack trace by two steps:

1. Call the DBMS_JAVA.SET_OUTPUT(2000000);
2. Set SERVEROUTPUT ON