MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Free Oracle NoSQL Book

with 7 comments

It’s seldom that Oracle makes an effort to give an Oracle Press book away for free, but they’ve done it. You can download the Getting Started with Oracle NoSQL Database book from Oracle Press for free.

Getting Started with Oracle NoSQL Database

OracleNoSQL

  1. Overview of Oracle NoSQL Database and Big Data
  2. Introducing Oracle NoSQL Database
  3. Oracle NoSQL Database Architecture
  4. Oracle NoSQL Database Installation and Configuration
  5. Getting Started with Oracle NoSQL Database Development
  6. Reading and Writing Data
  7. Advanced Programming Concepts: Avro Schemas and Bindings
  8. Capacity Planning and Sizing
  9. Advanced Topics

Just so you know, knowledge of Java and Hadoop are helpful. It’s a short 71 pages but a nice heads up and here’s what you do starter book.

If you’re opening in iBooks on an iPad, make sure you let the whole book download first as a PDF file. You may only get a partial PDF file when you don’t let it download completely to your browser. It isn’t the speediest download, notwithstanding its small size.

Written by maclochlainn

May 31st, 2014 at 9:34 am

Parsing DBMS_OUTPUT

without comments

Testing with DBMS_OUTPUT.PUT_LINE is always a bit of a problem when you have strings longer than 80 characters in length, which occurs more frequently with Oracle Database 12c. An example of managing output occurs when you want to print a string with embedded line breaks. My solution is the following parse_rows procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CREATE OR REPLACE PROCEDURE parse_rows
( pv_text  VARCHAR2 ) IS
 
  /* Declare parsing indexes. */
  lv_start     NUMBER := 1;
  lv_end       NUMBER := 1;
  lv_length    NUMBER;
 
BEGIN
 
  /* Assign an end value based on parsing line return or length. */
  IF INSTR(pv_text,CHR(10),lv_start) = 0 THEN
    lv_end := LENGTH(pv_text) + 1;
  ELSE
    lv_end := INSTR(pv_text,CHR(10),lv_start);
  END IF;
 
  /* Assign a length value to the parsed string. */
  lv_length := lv_end - lv_start;
 
  /* Print first line. */
  dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length));
 
  /* Print the rows of a multiple line string. */
  WHILE (lv_end < LENGTH(pv_text)) LOOP
 
    /* Assign a new start value. */
    lv_start := lv_end + 1;
 
    /* Assign a new end value. */
    IF INSTR(pv_text,CHR(10),lv_start + 1) = 0 THEN
      lv_end := LENGTH(pv_text) + 1;
    ELSE
      lv_end := INSTR(pv_text,CHR(10),lv_start + 1);
    END IF;
 
    /* Assign a new length. */
    lv_length := lv_end - lv_start;
 
    /* Print the individual rows. */
    dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length));
 
  END LOOP;
END;
/

Here’s the example of a test program for the procedure:

1
2
3
4
5
6
7
DECLARE
  /* Declare text. */
  lv_text  VARCHAR2(200) := 'This is too much'||CHR(10)||'information'||CHR(10)||'on one line.';
BEGIN
  parse_rows(lv_text);
END;
/

It prints the following:

1
2
3
This is too much
information
on one line.

Hope this benefits those looking for quick solution.

Written by maclochlainn

May 21st, 2014 at 12:35 am

Java File IO & NIO2

without comments

Java 6, or the java.io.* package which has been around a long time has known limits. There are some noted issues with the Java 6 java.io.* package, and why I wrote the FileIO.java file back in 2002. The FileIO.java class creates a series of static methods that let you read, write, copy, and move files both locally and across the network.

  • Many methods fail to throw exceptions
  • A number of simple methods are missing, like the copy and move methods
  • There isn’t any support for symbolic links
  • Many methods fail to scale with large files

The Java 7 introduces the java.nio.file.* package, and it does the following:

  • Works more consistently across platforms
  • Provides improved access to more file attributes
  • Provides improved exception handling
  • Supports non-native file systems

A student asked me to post the FileIO.java file. So, I posted it on the github.com site. I hope it helps simplifies the use of File IO operations. Admittedly, it’s easier to read and write a file with Java 7. The following provides a complete example class:

import java.awt.Dimension;
import java.awt.Font;
import java.io.*;
import java.nio.file.*;
import java.nio.charset.Charset;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
 
public class ReadWriteFile {
 
    // Constructor for objects of class ReadFile
    public ReadWriteFile() {}
 
  // Write a file.
  public static void writeFile(String fileName, String fileText) {
    // Declare a file path.
    Path p = Paths.get(fileName);
 
    // Write a file.
    try {
      BufferedWriter bw =
        Files.newBufferedWriter( p, Charset.forName("ISO-8859-1")
                                  , StandardOpenOption.CREATE, StandardOpenOption.APPEND );
      bw.write(fileText, 0, fileText.length());
      bw.close(); }
    catch(IOException ioe) {
      System.out.println("Error writing a file."); 
      System.exit(0); }}
 
  // Read a file.
  public static String readFile(String fileName) {
    // Declare a String to hold the file.
    String text = "", line = "";
 
    // Declare a file path.
    Path p = Paths.get(fileName);
 
    // Write a file.
    try {
      // Read the file.
      BufferedReader fileInput =
        Files.newBufferedReader(p,Charset.forName("ISO-8859-1"));
 
      // Read and process first line.
      line = fileInput.readLine();
      text = line + "\n";
 
      // Read and process second thru last lines.
      while (line != null) {
        line = fileInput.readLine();
        if (line != null)
          text += line + "\n"; }
 
      // Close the file input.
      fileInput.close(); }
    catch(EOFException eofe) {
        System.out.println("No more lines to read.");
        System.exit(0); }
    catch(IOException ioe) {
        System.out.println("Error reading file."); 
        System.exit(0); }
 
    // Return a String value.
    return text;
  }
 
  // Declare a static main to read a text file.
  public static void main(String[] args) {
    // Declare local object types.
    JOptionPane op = new JOptionPane();
    JScrollPane sp;
    JTextArea ta;
    Object[] options = {};
 
    // Instantiate a test class.
    ReadWriteFile rwf = new ReadWriteFile();
 
    // Text the readFile method.
    if (args.length > 0) {
      // Set the value and formatting of the text area.
      ta = new JTextArea(rwf.readFile(args[0]));
      ta.setFont(new Font(Font.SANS_SERIF,Font.PLAIN,14));
      ta.setLineWrap(true);
      ta.setWrapStyleWord(true);
 
      // Assign the text area to a scroll pane.
      sp = new JScrollPane(ta);
      sp.setPreferredSize(new Dimension(400,200));
 
      // Invoke a generic option pane.
      op.showOptionDialog(null, sp, "File Content", JOptionPane.DEFAULT_OPTION, JOptionPane.PLAIN_MESSAGE, null, options, null); }
 
    // Test the writeFile method.  
    if (args.length > 1) {
      rwf.writeFile(args[1],rwf.readFile(args[0])); }
  }
}

You can call it like this to read and write an output.txt file from any directory:

java ReadWriteFile Fellowship.txt output.txt

It will display a JOptionDialog like this:

JavaNIO2TextFile

Any suggestions or improvements are welcome.

Written by maclochlainn

May 18th, 2014 at 12:31 pm

Posted in Java,Java 7

Tagged with , ,

Oracle 12c VARCHAR2?

without comments

The Oracle Database 12c documentation says you can set the maximum size of a VARCHAR2 to 32,767 bytes. That’s true except when you’re trying to map a large Java string to a VARCHAR2. It fails when the physical size of the Java string is greater than 4,000 bytes with an ORA-01002 or fetch out of sequence error.

SELECT read_text_file('C:\Data\loader\Hobbit1.txt')
*
ERROR at line 1:
ORA-24345: A Truncation or null fetch error occurred
 
ERROR:
ORA-01002: fetch out of sequence

You need to grant privileges before you can test this code. You can grant privileges by connecting as the SYS user of a CDB (or non-multitenant database) or as the ADMIN user of a PDB with the AS SYSDBA clause. Then, you run the following command to grant external file access to the JVM inside Oracle Database 12c:

BEGIN
  DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
                            ,'SYS:java.io.FilePermission'
                            ,'C:\Data\Loader\Hobbit1.txt'
                            ,'read');
END;
/

The IMPORTER is a PDB user name, or a non-multitenant database user name. Please note that permissions must be granted on each physical file.

Here’s the code that raises the error when the external file is greater than 4,000 bytes:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ReadFile" AS
  // Java library imports.
  import java.io.File;
  import java.io.BufferedReader;
  import java.io.FileNotFoundException;
  import java.io.IOException;
  import java.io.FileReader;
  import java.security.AccessControlException;
 
  // Class definition.  
  public class ReadFile {
    // Define class variables.
    private static File file;
    private static FileReader inTextFile;
    private static BufferedReader inTextReader;
    private static StringBuffer output = new StringBuffer();
    private static String outLine, outText;
 
    // Define readText() method.
    public static String readText(String fromFile)
      throws AccessControlException, IOException {
      // Read file.
      try {
        // Initialize File.
        file = new File(fromFile);
 
        // Check for valid file.
        if (file.exists()) {
 
          // Assign file to a stream.          
          inTextFile = new FileReader(file);
          inTextReader = new BufferedReader(inTextFile);
 
          // Read character-by-character.
          while ((outLine = inTextReader.readLine()) != null) {
            output.append(outLine + "\n"); }
 
          // Assing the StringBuffer to a String.
          outText = output.toString();
 
          // Close File.
          inTextFile.close(); }
        else {
          outText = new String("Empty"); }}
      catch (IOException e) {
        outText = new String("");
        return outText; }
    return outText; }}
/

The PL/SQL wrapper should look like this:

1
2
3
4
5
CREATE OR REPLACE FUNCTION read_text_file
(from_file VARCHAR2) RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'ReadFile.readText(java.lang.String) return java.lang.String';
/

Then, you can query it like so:

SELECT read_text_file('C:\Data\loader\Hobbit1.txt')
FROM dual;

You can avoid the error by returning the Java file size as a CLOB data type with the following Java source code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ReadFile" AS
  // Java library imports.
  import java.io.File;
  import java.io.BufferedReader;
  import java.io.FileNotFoundException;
  import java.io.IOException;
  import java.io.FileReader;
  import java.security.AccessControlException;
  import java.sql.*;
  import oracle.sql.driver.*;
  import oracle.sql.*;
 
  // Class definition.  
  public class ReadFile {
    // Define class variables.
    private static int i;
    private static File file;
    private static FileReader inTextFile;
    private static BufferedReader inTextReader;
    private static StringBuffer output = new StringBuffer();
    private static String outLine, outText;
    private static CLOB outCLOB;
 
    // Define readText() method.
    public static oracle.sql.CLOB readText(String fromFile)
      throws AccessControlException, IOException, SQLException  {
      // Read file.
      try {
        // Initialize File.
        file = new File(fromFile);
 
        // Check for valid file.
        if (file.exists()) {
 
          // Assign file to a stream.          
          inTextFile = new FileReader(file);
          inTextReader = new BufferedReader(inTextFile);
 
          // Read character-by-character.
          while ((outLine = inTextReader.readLine()) != null) {
            output.append(outLine + "\n"); }
 
          // Assing the StringBuffer to a String.
          outText = output.toString();
 
          // Declare an Oracle connection.
          Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 
          // Transfer the String to CLOB.
          outCLOB = CLOB.createTemporary((oracle.jdbc.OracleConnectionWrapper) conn, true, CLOB.DURATION_SESSION);
          i = outCLOB.setString(1,outText);
 
          // Close File.
          inTextFile.close(); }
        else {
          i = outCLOB.setString(1,"Empty"); }}
      catch (IOException e) {
        i = outCLOB.setString(1,"");
        return outCLOB; }
    return outCLOB; }}
/

The wrapper changes to return a CLOB and map a java.sql.CLOB as the return type of the Java library. This command works:

1
2
3
4
5
CREATE OR REPLACE FUNCTION read_clob_file
(from_file VARCHAR2) RETURN CLOB IS
LANGUAGE JAVA
NAME 'ReadFile.readText(java.lang.String) return oracle.sql.CLOB';
/

You now query the PL/SQL wrapper with this type of effective SQL*Plus command-line command:

COLUMN atext FORMAT A60 HEADING "Text"
COLUMN asize FORMAT 99,999 HEADING "Size"
 
SELECT   read_clob_file('C:\Data\loader\Hobbit1.txt') AS AText
,        LENGTH(read_clob_file('C:\Data\loader\Hobbit1.txt')) AS ASize
FROM dual;

As always, I hope this helps somebody.

Written by maclochlainn

May 7th, 2014 at 3:03 am

Toad Freeware Page

without comments

While I posted how to install Toad for MySQL Freeware five years ago, I’m always surprised how few people know about it there and consistently updated and improved. You can download Toad for MySQL Freeware or Toad Freeware for Oracle, SQL Server, Sybase, or IBM DB2 at this web site.

MySQLToadHome

You can also download Toad Data Modeler Freeware Edition. Just two notes, while Toad for Oracle Freeware is an MSI file, Toad for MySQL Freeware is a zip file and limited to only a Windows install.

Written by maclochlainn

April 30th, 2014 at 1:46 am

A/UX, NeXTSTEP, & OS X

with 5 comments

One thing that gets tedious in the IT community and Oracle community is the penchant for Windows only solutions. While Microsoft does an excellent job in certain domains, I remain a loyal Apple customer. By the way, you can install Oracle Client software on Mac OS X and run SQL Developer against any Oracle Database server. You can even run MySQL Workbench and MySQL server natively on the Mac OS X platform, which creates a robust development platform and gives you more testing options with the MySQL monitor (the client software).

Notwithstanding, some Windows users appear to malign Apple and the Mac OS X on compatibility, but they don’t understand that it’s a derivative of the Research Unix, through BSD (Berkeley Software Distribution). This Unix lineage chart illustrates it well:

Screen Shot 2014-04-18 at 3.49.39 PM

I’m probably loyal to Apple because in the early 1990′s I worked on Mac OS 6, Mac OS 7, A/UX, NeXTSTEP, and AIX/6000 (Version 3) while working at APL (American President Lines) in Oakland, California. Back then, my desktop was a pricey Macintosh Quadra 950 and today I work on a pricey Mac Pro desktop. The Mac Pro lets me use VMware virtualize development environments for Oracle Linux, Red Hat Enterprise Linux, Fedora, and as you might guess Windows 7/8. My question to those dyed in the wool Microsoft users is simple, why would you choose a single user OS like Windows over a multi-user OS like Mac OS X?

Written by maclochlainn

April 18th, 2014 at 4:28 pm

Best UTL_FILE Practice

with 3 comments

In a post a couple days ago, I promised to provide a best practice approach to reading external files with the UTL_FILE package. My first assumption is that you’re reading unstructured data because structured data is best read by external tables because external tables can read data much faster with the PARALLEL option.

My second assumption is that you’re you don’t know how to use or choose not to use the DBMS_LOB package; specifically, the loadclobfromfile and loadblobfromfile procedures. By the way, Chapter 10 of the Oracle Database 12c PL/SQL Programming has several complete examples using the DBMS_LOB package. My third assumption is that you’d like the external file packaged as a whole, which means you want it returned as a CLOB or BLOB data type.

Here’s a generic function that achieves that and avoids the nonsense with closing the file in the exception handler, or worse yet, wrapping it in another PL/SQL schema-level function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
CREATE OR REPLACE FUNCTION read_file_to_clob
( pv_location   VARCHAR2
, pv_file_name  VARCHAR2 ) RETURN CLOB IS 
 
  /* Declare local input variables. */
  lv_location      VARCHAR2(60);
  lv_file_name     VARCHAR2(40);
 
  /* Declare a file reference pointer and buffer. */
  lv_file    UTL_FILE.FILE_TYPE;  -- File reference
  lv_line    VARCHAR2(32767);     -- Reading buffer
 
  /* Declare local sizing variables. */
  lv_file_size  NUMBER;
  lv_line_size  NUMBER;
  lv_read_size  NUMBER :=0;
 
    /* Declare local file attribute data. */
  lv_file_exists  BOOLEAN := FALSE;
  lv_block_size   BINARY_INTEGER;
 
  /* Declare a control variable and return CLOB variable. */
  lv_enable  BOOLEAN := FALSE;
  lv_return  CLOB;
BEGIN
  /* Declare local input variables. */
  lv_location  := pv_location;
  lv_file_name := pv_file_name;
 
  /* Check for open file and close when open. */
  IF utl_file.is_open(lv_file) THEN
    utl_file.fclose(lv_file);
  END IF;
 
  /* Read the file attributes to get the physical size. */
  utl_file.fgetattr( location    => lv_location
                   , filename    => lv_file_name
                   , fexists     => lv_file_exists
                   , file_length => lv_file_size
                   , block_size  => lv_block_size ); 
 
  /* Open only files that exist. */
  IF lv_file_exists THEN
 
    /* Create a temporary CLOB in memory. */
    dbms_lob.createtemporary(lv_return, FALSE, dbms_lob.CALL);
 
    /* Open the file for read-only of 32,767 byte lines. */
    lv_file := utl_file.fopen( location     => lv_location
                             , filename     => lv_file_name
                             , open_mode    => 'R'
                             , max_linesize => 32767);
 
 
    /* Read all lines of a text file. */
    WHILE (lv_read_size < lv_file_size) LOOP
      /* Read a line of text until the eof marker. */
      utl_file.get_line( file   => lv_file
                       , buffer => lv_line );
 
      /* Add the line terminator or 2 bytes to its length. */
      lv_line := NVL(lv_line,'')||CHR(10);
      lv_read_size := lv_read_size
                    + LENGTH(NVL(lv_line,CHR(10))) + 2;
 
      /* Write to an empty CLOB or append to an existing CLOB. */
      IF NOT lv_enable THEN
        /* Write to the temporary CLOB variable. */
        dbms_lob.WRITE( lv_return, LENGTH(lv_line), 1, lv_line);
 
        /* Set the control variable. */
        lv_enable := TRUE;
      ELSE
        /* Append to the temporary CLOB variable. */
        dbms_lob.writeappend( lv_return, LENGTH(lv_line),lv_line);
      END IF;
    END LOOP;
 
    /* Close the file. */
    utl_file.fclose(lv_file);
  END IF;
 
  /* This line is never reached. */
  RETURN lv_return;
EXCEPTION
  WHEN OTHERS THEN
    utl_file.fclose(lv_file);
    RAISE NO_DATA_FOUND;
END;
/

You can test the function with the following:

SET LONG 100000
SET PAGESIZE 999
 
SELECT read_file_to_clob('SomeVirtualDirectory','TextFile.txt') AS "Output"
FROM   dual;

If anybody has suggestions for improvements, please pass them along. As always, I hope this helps other developers.

Written by maclochlainn

April 16th, 2014 at 3:07 am

Using UTL_FILE Package

with 5 comments

Sometimes I’m surprised. Today, the surprise came when somebody pointed to a potential error in another author’s book. The person who asked the question had to send me a screen shot before I believed it.

The author’s code encounters the following error because the code was designed to loop through a multiple line file, and the code called the UTL_FILE.FOPEN procedure with three instead of four parameters. While it works with only three parameters when the strings are less than or equal to 1,024 (thanks Gary), it throws read errors when a string exceeds the default. You use the fourth parameter when your string exceeds the default length of 1,024.

DECLARE
*
ERROR at line 1:
ORA-29284: file READ error
ORA-06512: at "SYS.UTL_FILE", line 106
ORA-06512: at "SYS.UTL_FILE", line 746
ORA-06512: at line 26

As mentioned, you fix the problem by using the fourth parameter like the call on lines 15 through 18 below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DECLARE
  /* Declare local input variables. */
  lv_location      VARCHAR2(60) := 'C:\Data\Direct';
  lv_file_name     VARCHAR2(40) := 'TextFile.txt';
 
  /* Declare a file reference pointer and buffer. */
  lv_file     UTL_FILE.FILE_TYPE;  -- File reference
  lv_line     VARCHAR2(32767);     -- Reading buffer
 
BEGIN
 
  /* Check for open file and close when open. */
  IF utl_file.is_open(lv_file) THEN
    utl_file.fclose(lv_file);
  END IF;
 
  /* Open the file for read-only of 32,767 lines of text.
     The fourth parameter is required when you want to use
     the GET_LINE procedure to read a file line-by-line. */
  lv_file := utl_file.fopen( location     => lv_location
                           , filename     => lv_file_name
                           , open_mode    => 'R'
                           , max_linesize => 32767);
 
  /* Read all lines of a text file. */
  LOOP
    /* Read a line of text, when the eof marker is found 
       the get_line procedure raises a NO_DATA_FOUND 
       error, which is why there's no explicit loop. */
    utl_file.get_line( file   => lv_file
                     , buffer => lv_line );
 
    /* Print the line of text or a line return because
       UTL_FILE doesn't read line returns. */
    dbms_output.put_line(NVL(lv_line,CHR(10)));
 
  END LOOP;
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    utl_file.fclose(lv_file);
END;
/

While the foregoing is traditional and uses a physical directory path from the local server’s operating system, you can use a virtual directory from Oracle Database 10g forward. This physical directory is also defined in the utl_file_dir parameter of the database server. A virtual directory hides the physical directory from the application software, which simplifies physical file maintenance when you need to move the files.

3
  lv_location      VARCHAR2(60) := 'DirectVirtualDirectory';

The preceding example works, and I’ll put one out converting the external to CLOB later in the week.

Written by maclochlainn

April 13th, 2014 at 11:46 pm

DBMS_JAVA Privilege Error?

without comments

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read');
  6  END;
  7  /

After you grant privileges to read, write, and delete files with Oracle’s dbms_java package, you must disconnect any active session to enjoy the new privileges. If you forget to disconnect and re-connect an active session, you get the following error message:

FROM     TABLE(list_files(get_directory_path('STAGE')))
               *
ERROR at line 2:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
java.security.AccessControlException: Directory permissions restricted.

The problem and fix are simple. The permissions aren’t in effect until after you disconnect and reconnect your active sessions. I put this there because when I Google’d it, there wasn’t an answer already.

While I’m on the topic of privilege errors, that aren’t really errors, I thought it would be helpful to qualify how the delete permission works because I couldn’t find it anywhere in the Oracle documentation (if somebody finds it let make a comment that enriches the discussion). Unlike the read and write permissions, the delete permission requires permissions on specific files.

If you only grant permission on like:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read,write,delete');
  6  END;
  7  /

You would get this message when trying to delete an external file:

BEGIN
*
ERROR at line 1:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
c:\DATA\log\item_import.log READ) has NOT been GRANTED TO IMPORTER. The PL/SQL
TO GRANT this IS dbms_java.grant_permission( 'IMPORTER',
'SYS:java.io.FilePermission', 'c:\data\log\item_import.log', 'read' )
ORA-06512: at "IMPORTER.DELETE_FILE", line 1
ORA-06512: at line 2

It requires that you grant permissions on individual files to have the privilege of deleting them from within Oracle. That grant would look like the following:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload\item_import.log'
  5                               ,'read,write,delete');
  6  END;
  7  /

As always, I hope this helps other developers.

Written by maclochlainn

March 29th, 2014 at 1:59 pm

Hidden DBMS_JAVA Nuance

without comments

It always happens when I’m in a hurry. Yes, I ran into one of those pesky little features with Oracle’s DBMS_JAVA package. While I try to write entries with proper GeSHi case semantics, like everyone else I tend to write PL/SQL initially in lowercase. That led me to the discovery of this wonderful error message:

BEGIN
*
ERROR at line 1:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
oracle.aurora.vm.IdNotFoundException: importer : USER OR ROLE id does NOT exist
ORA-06512: at "SYS.DBMS_JAVA", line 705
ORA-06512: at line 2

The problem was simple, while not published, you must enter the user/schema name in uppercase text when calling DBMS_JAVA.GRANT_PERMISSION procedure. Here’s a proper example:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read');
  6  END;
  7  /

If you change the IMPORTER string to lower or mixed case, you raise the aforementioned error. Quite an interesting tidbit that I missed from Oracle Database 10g until Oracle Database 12c. Hope this helps you avoid wondering what’s happening when your line 2 looks like this:

  2    DBMS_JAVA.GRANT_PERMISSION('Importer'

As always, I hope this helps others. If you like more on writing Java inside the Oracle database you can check this older post.

Written by maclochlainn

March 21st, 2014 at 12:44 am