MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for May, 2014

Free Oracle NoSQL Book

with 9 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