MacLochlainns Weblog

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;
/

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;
/

This is too much
information
on one line.

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])); }
  }
}

java ReadWriteFile Fellowship.txt output.txt

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

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

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; }}
/

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';
/

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

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; }}
/

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';
/

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;