MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

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

Posted in Java,Microsoft Windows 7,MySQL Connect,Oracle,Oracle 12c,pl/sql,sql

«
»