MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Sequence disallows order by

without comments

A call to a PRICE_S1 sequence in a query with an ORDER BY clause is disallowed, which typically occurs in an INSERT statement. Any attempt raises the following exception:

SELECT   price_s1.NEXTVAL AS price_id
                  *
ERROR at line 1:
ORA-02287: SEQUENCE NUMBER NOT allowed here

You need to remove the ORDER BY clause to eliminate the error.

Written by maclochlainn

June 15th, 2014 at 6:20 pm

MySQL Insert from Query

with 4 comments

While working with an error that my students surfaced in the Oracle Database 12c, I blogged about the limit of using a subquery in an Oracle INSERT statement, and I discovered something when retesting it in MySQL. It was a different limitation. I was also surprised when I didn’t find any mention of it through a Google search, but then I may just not have the right keywords.

The original test case uses a subquery to create a multiple row result set for an INSERT statement. Unfortunately, I discovered that you can’t always embed a UNION or UNION ALL inside a subquery. At least, you can’t when you call the subquery inside an INSERT statement. For example, let’s create a DESTINATION table and then we’ll try to insert records with a query that fabricates a result set.

-- Drop the destination table.
DROP TABLE IF EXISTS destination;
 
-- Create the destination table.
CREATE TABLE destination
( destination_id    INT UNSIGNED
, destination_name  VARCHAR(30));

Now let’s try the subquery:

1
2
3
4
INSERT INTO destination
( SELECT 1,'Sample1'
  UNION ALL
  SELECT 2,'Sample2' );

It raises the following error message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL
  SELECT 2,'Sample2' )' at line 3

I wasn’t sure if I missed something. It turned out that I did. While you can put a subquery inside an INSERT statement, you can’t embed it inside a set of parentheses when it contains a UNION ALL set operator. Other statements work with or without parentheses.

1
2
3
4
INSERT INTO destination
  SELECT 1,'Sample1'
  UNION ALL
  SELECT 2,'Sample2';

If somebody has any more to add, I look forward to reading it.

Written by maclochlainn

June 15th, 2014 at 12:19 am

Posted in MySQL,sql

Tagged with

SQL Insert from Query

with 5 comments

Sometimes my students find new errors that I’ve never seen. One student did that this week by including an ORDER BY clause in a subquery that feeds an INSERT statement. It raises an ORA-00907 exception, like:

ORA-00907: missing right parenthesis

You can’t include a subquery with an ORDER BY clause because it generates an error. The reason is simple. A subquery can’t perform a sort operation inside a subquery. Here’s a quick demonstration:

DROP TABLE destination;
CREATE TABLE destination
( destination_id    NUMBER
, destination_name  VARCHAR2(20));
 
INSERT INTO destination
(
  SELECT   1,'Sample1' FROM dual
  UNION ALL
  SELECT   2,'Sample2' FROM dual
  ORDER BY 1 DESC
);

If you remove the ORDER BY clause, the statement works without a problem. For example, here’s the working version:

INSERT INTO destination
(
  SELECT   1,'Sample1' FROM dual
  UNION ALL
  SELECT   2,'Sample2' FROM dual
);

Alternatively, you can include an ORDER BY clause when you remove the parentheses from around the subquery. This is an example:

INSERT INTO destination
  SELECT   1,'Sample1' FROM dual
  UNION ALL
  SELECT   2,'Sample2' FROM dual
  ORDER BY 1 DESC;

I hope this helps anybody who runs into the problem.

Written by maclochlainn

June 14th, 2014 at 10:40 pm

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

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