MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Hiding a Java Source

without comments

The ability to deploy Java inside the Oracle database led somebody to conclude that the source isn’t visible in the data catalog. Then, that person found that they were wrong because the Java source is visible when you use a DDL command to CREATE, REPLACE, and COMPILE the Java source. This post discloses how to find the Java source and how to prevent it from being stored in the data catalog.

You can verify that the Java class and source files exist with the following query:

1
2
3
4
5
6
7
8
COLUMN object_name FORMAT A20 HEADING "Object Name"
COLUMN object_type FORMAT A12 HEADING "Object Type"
COLUMN STATUS      FORMAT A14 HEADING "Object Status"
SELECT   object_name
,        object_type
,        STATUS
FROM     user_objects
WHERE    object_name = 'ReadFile';

It displays:

Object Name          Object Type  Object Status
-------------------- ------------ --------------
ReadFile             JAVA SOURCE  VALID
ReadFile             JAVA CLASS   VALID
 
2 rows selected.

Then, you can use the following query to discovery a Java library created by a SQL command:

1
2
3
4
5
6
COLUMN line FORMAT 9999 HEADING "Line|#"
COLUMN text FORMAT A66  HEADING "Text"
SELECT   line
,        text
FROM     user_source
WHERE    name = 'ReadFile';

It displays the following:

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

You can eliminate the source by compiling the Java library outside the database. Then, you use the loadjava utility to load the only the class file into the data catalog. The syntax would be the following command for an importer user in a video Pluggable Database (PDB):

loadjava -r -f -o -user importer/importer@video ReadFile.class

You should know that this syntax is disallowed by the loadjava utility, notwithstanding it’s found in the Oracle Database 12c documentation:

loadjava -r -f -o -user importer@video/importer ReadFile.class

You can verify that only the Java class file exists with the following query:

1
2
3
4
5
6
7
8
COLUMN object_name FORMAT A20 HEADING "Object Name"
COLUMN object_type FORMAT A12 HEADING "Object Type"
COLUMN STATUS      FORMAT A14 HEADING "Object Status"
SELECT   object_name
,        object_type
,        STATUS
FROM     user_objects
WHERE    object_name = 'ReadFile';

It displays:

Object Name          Object Type  Object Status
-------------------- ------------ --------------
ReadFile             JAVA CLASS   VALID
 
1 row selected.

Hope this helps those who want to hide the Java source files.

Written by maclochlainn

July 22nd, 2014 at 10:23 pm

Visual Studio Freedom

without comments

Some of my students want to learn to write C++ on a Windows OS. At some point, you simply surrender to the fact that many people prefer the Windows OS as a starting point. Traditionally, I’d recommend the Microsoft Visual Studio as their learning vehicle because it’s free to our students because of our relationship with Microsoft. It’s not free to graduates. When a graduate contacts me I recommend Code::Blocks. Just recently, one ask for more than a recommendation. He wanted instructions. These are the instructions.

Download and Install Code::Blocks

Here are the instructions to install and download Code::Blocks 13.12.

CodeBlocks_01

  1. You can download Download and Install Code::Blocks from the web site. Then, you can install Code::Blocks from download folder.

CodeBlocks_02

  1. You launch the codeblocks-12.12mingw-setup.exe file from the download folder.

CodeBlocks_03

  1. This is the first screen of the Code::Blocks wizard. Click the Next button to continue.

CodeBlocks_04

  1. This is the license agreement. Click the I Agree button to continue.

CodeBlocks_05

  1. This screen shows the components to install. The default has all of them checked. I recommend you keep the default. Click the Next button to continue.

CodeBlocks_06

  1. This screen lets you accept the default install location or to chose another install location. I recommend you keep the default install location. Click the Install button to continue.

CodeBlocks_07

  1. This screen shows you the extracting and deployment of files. Click the Next button to continue.

CodeBlocks_08

  1. This indicates that you’ve installed the product, and it now prompts you to run Code::Blocks programscreen shows you the extracting and deployment of files. Click the Yes button to continue.

CodeBlocks_09

  1. This indicates that the installer detects a GNU GCC Compiler. Click the OK button to continue.

CodeBlocks_10

  1. This acknowledges the completion of the installation. Click the OK button to continue.

CodeBlocks_9a

  1. This dialog tells you the installation has completed. Click the Next button to continue.

CodeBlocks_9b

  1. This dialog completes the installation. Click the Finish button to end the installation.

Configure Code::Blocks

CodeBlocks_11

  1. This is the main menu of Code::Blocks application. You need to click the Create a new project link to continue.

CodeBlocks_12

  1. You should choose a Console application for this example. Double click the Console application icon to continue.

CodeBlocks_13

  1. This dialog launches the Console page. Check the Skip this page next time and then click the Next button to continue.

CodeBlocks_14

  1. This dialog lets you choose whether you want to write a C and C++ program. Click the Next button to continue.

CodeBlocks_15

  1. This dialog lets you choose whether you want to write a C and C++ program. Click the Next button to continue.

CodeBlocks_16

  1. This dialog lets you choose the compiler, debug, and release configurations. Click the Finish button to continue.

Create and run a program in Code::Blocks

CodeBlocks_17

  1. This dialog gives you an empty project. Click the Sources item in the list of the Project tab.

CodeBlocks_18

  1. This dialog adds a main.cpp to the list of the Management console. Click the main.cpp item in the list of the Project tab.

CodeBlocks_19

  1. After clicking the main.cpp item in the Project list, the Code::Blocks IDE generates the content of the main.cpp file. Click the Green Arrow to run (compile and execute) the main.cpp file. The endl is a defined in std, and like a \n line terminator.

CodeBlocks_20

  1. A new file hasn’t been compiled (or built), which means you get the following dialog. It wants to know if you want to build the program. Click the Yes button to build the program.

CodeBlocks_21

  1. After making the file, the Build Log tells you whether the code compiles or not. Click the Run green arrow to run the program.

CodeBlocks_22

  1. After running a Console application, you’ll see the output of your program in a console window like the following. You can close it by clicking the console window’s close button.

CodeBlocks_23

  1. After executing the compiled file, the Build Log tells you whether the program compiles successfully or not.

As always, I hope this helps those who read it. Good luck learning to write C/C++ programs.

Written by maclochlainn

July 5th, 2014 at 1:48 am

Posted in C/C++ Programming

Tagged with

Fedora VMWare Upgrade

with 2 comments

When a new update of VMWare comes out, and it is time to upgrade VMWare Tools. Here’s an update on the instructions for upgrading VMWare Tools:

  1. Navigate to the VMWare Menu, choose Virtual Machine and in the drop down menu Install VMWare Tools. This will mount a virtual CD in the Oracle Unbreakable Linux virtual machine and it launches the following dialog box:
VMware962

  1. Open a terminal session by right clicking anywhere in the desktop, and then choose Open in Terminal from the context menu. You can then run the VMWare Toolkit by following these instructions as the root user:
cd /media/VMware\ Tools
cp VMwareTools-9.6.2-1688356.tar.gz /tmp
cd /tmp
gunzip VMwareTools-9.6.2-1688356.tar.gz
tar -xvf VMwareTools-9.6.2-1688356.tar
cd vmware-tools-distrib
sudo ./vmware-install.pl

The last step requires that you reply to a set of prompts. If you’d like to accept the default at one time, you can use the following command:

sudo ./vmware-install.pl --default

Written by maclochlainn

July 3rd, 2014 at 2:00 am

Posted in Fedora,Linux,VMWare

Tagged with , ,

External Table Access

without comments

I left to chance where students would attempt to place their external files in a Linux or Unix implementation. As frequently occurs, they choose a location in their student user’s home directory. Any attempt to read an external table based on a file in this type of directory fails because it’s not accessible by the Oracle user because the directory within the student user’s account isn’t reachable. You can’t simply chown a directory and the files in a directory.

The failure returns the following result:

SELECT COUNT(*) FROM transaction_upload
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /home/student/upload/transaction_upload.log

The reason isn’t readily visible to all, but a virtual directory must reference a physical directory owned by the oracle user and dba group. That also means the oracle user must own all directories from the logical mount point to the physical directory name.

Assuming you’re working in an Oracle Database 11g XE instance, you can create a valid upload directory by navigating to this directory:

/u01/app/oracle

Then, issue this command as the root user to create a new upload directory:

mkdir upload

Now you have the following directory:

/u01/app/oracle/upload

Assuming you’ve created the upload directory as the root user, the root user should issue the following two commands from the /u01/app/oracle directory:

chown -R oracle:dba upload
chmod -R 755 upload

Having made that change you should now be able to query the external file source, like a *.csv (comma-separated values) file. Hope this helps those trying to use external tables, which I subsequently wrote about for Toad World – External Tables.

Written by maclochlainn

June 19th, 2014 at 1:39 am

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 6 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 , ,