MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for August, 2012

SQL Developer batch file?

with 3 comments

It’s amazing to me that there is yet another outcome from installing Oracle Database 11g on Windows 7. This one installs without an error but then raises the following error message dialog when you try to launch SQL Developer from the menu. Naturally, it seems to depend on having more than one Java JVM installed when you run the Oracle Universal Installer (OUI) without setting the %JAVA_HOME% environment variable.

Text of error message from image:

Windows is searching for SQLSERVER.BAT. To located the file yourself, click Browse.

While the installation release notes (E10842-02) that download or ship with the product don’t mention the %JAVA_HOME% or %DEV_TOOLS% environment variables, it appears you should set them before the installation. You can discover this by double clicking on the SQL Developer menu option, and then choose Properties. You should see the following in the dialog.

Windows 7 searches for the sqldeveloper.bat file when the OUI installation fails to configure the SetJavaHome parameter in the sqldeveloper.conf file. OUI does configure the SetJavaHome parameter when OUI resolves the JVM address.

You should put this in the Target field:

C:\app\<user_name>\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloperW.exe

Although, that may be a simplifying assumption of what causes the problem, I didn’t go through all the Oracle Universal Installer (OUI) background tasks to discover the actual code element. Also, I didn’t take the time to retest by setting those parameters. I solved this problem by replacing the sqldeveloper.bat value with a sqldeveloperw.exe value in the menu link, applying the change, and saving it. If you’ve a better solution, please share it.

The configuration file is found here:

C:\> app\<user_name>\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf

While you can provide a sqldeveloper.bat file, it’s much simpler to simply put the JVM physical address in the sqldeveloper.conf file. That’s what I did to solve the problem and what I think the development team expects.

A valid SetJavaHome value points to a 32-bi JVM, and in my testing environment that is:

SetJavaHome C:\Program Files (x86)\Java\jdk1.6.0_34

Another post on this topic can be found here. As always, I hope this saves somebody time working with the Oracle product stack.

Written by maclochlainn

August 26th, 2012 at 4:39 pm

What’s ORACLE_UNQNAME?

with 55 comments

It started with a three part question: What’s the URL to run the DB Console for Oracle Database 11gR2 on Windows 7, and what’s the ORACLE_UNQNAME and why isn’t it defined by the installation. The first part is easy (shown further below), but the second and third parts were more involved.

The ORACLE_UNQNAME is an operating system environment variable that holds the database’s unique name value. You can find it with the following query as the SYSTEM user (through SQL*Plus):

SELECT name, db_unique_name FROM v$database;

By the way, it’s not set as a Windows environment variable by default. You would need to do that manually (an example of setting an environment variable is here). The Oracle Universal Installer (OUI) actually used it to configure the already running DB Console service (with a successful installation). Once there, it didn’t need to set it as a system-level environment variable.

You may be wondering what generated the question if there’s already a configured service. You encounter the error when dropping down to the command line. First, you verify that the ports are running with this command:

C:\> netstat -an | findstr /C:1158
  TCP    0.0.0.0:1158           0.0.0.0:0              LISTENING

While this blog discusses the hard way to determine whether the DB Console is running, you can simply open the Windows Services to see whether the DB Console is running. Likewise, if you know the URL, enter it in your browser. Assuming you don’t know how to do either or are just a gluten for the command line, the rest of this post is important.

You can see your Windows services by typing services.msc in the Start->Run Command field. That way you don’t need to navigate the various links that differ between Windows releases.

Many know that you can check the status of the running DB Console with the emctl utility at the command line. It lets you find the URL that you should enter for the DB Console in a browser. This knowledge is where users encounter the problem with %ORACLE_UNQNAME% environment variable ($ORACLE_UNQNAME on Linux or Unix).

For example, running the following command raises an error that instructs you to set the %ORACLE_UNQNAME% environment variable. Although, it leaves many wondering what’s the right value to enter.

C:\> emctl status dbconsole
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

If you object to using the Windows services to start and stop the OEM tool, you can do it at the command-line like the status example above. Having set the environment variables, you can start the DB console with this command-line syntax:

C:\> emctl start dbconsole

Having set the environment variables, you can stop the DB console with this command-line syntax:

C:\> emctl stop dbconsole

It’s not hard to find this information when you know how. While the error message complains about one environment variable, there are actually two environment values you need to set. They are: %ORACLE_HOSTNAME% and %ORACLE_UNQNAME%.

You can find them by navigating to the %ORACLE_HOME%\oc4j\j2ee\ folder (or directory). The file name of the DB Console file tells you the values for these environment variables because they’re embedded in the file’s name. A snapshot from Windows Explorer shows them both.

You can set these environment variables as shown below in command shell of Windows (Linux or Unix users should use terminal), and then successfully run emctl from the command line.

C:\>set ORACLE_HOSTNAME=localhost
 
C:\>set ORACLE_UNQNAME=orcl
 
C:\>emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory C:\app\McLaughlinM\product\11.2.0\dbhome_1/localhost_orcl/sysman/log

If you’re using Linux or Unix, the export commands differ. You can check this other post for those. They under step 8 in that post.

You then enter the following URL in a browser to use the newly installed DB Console:

https://localhost:1158/em

The browser will prompt you with a security warning like the following:

Click the Add Exception button and you’ll see the following Windows dialog.

Having granted the exception, you arrive at the following credential web page. Connect as the SYSDBA using the SYS user’s account when you require extraordinary privileges. Doing so, shows a security risk in the console. You should connect as the SYSTEM user with NORMAL access generally, as shown below.

The following home page shows after your credentials are validated.

Hope that helps those trying to sort out running the DB Console and finding the magic %ORACLE_UNQNAME% value. Check this other blog post for instructions to reconfigure OEM.

Written by maclochlainn

August 23rd, 2012 at 10:32 pm

Verifying a Socket w/Perl

with one comment

Using a lowercase hostname is typical but I got sloppy on a Windows 7 installation, after all Windows is case insensitive, and I used a mixed case hostname. It raised an interesting error when installing Oracle Database 11g Release 2.

Enterprise manager configuration failed due to the following error –

Failed to allocate port(s) in the specified range(s) for the following process(es): JMS
[5540-5559], RMI [5520-5539], Database Control [5500-5519], EM Agent [3938] | [1830-1849]

Refer to the log file at C:\app\McLaughlinM\cfgtoollogs\dbca\orcl\emConfig.log for more details.

You can retry configuring this database with Enterprise Manager later by manually running C:\app\McLaughlinM\product\11.2.0\dbhome_1\bin\emca script.

After verifying the ports were available, it required testing the ability to form a socket. The quickest way to do that was installing ActiveState Perl and test the socket.

Server-side Perl code (server.pl):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# Import socket library.
use IO::Socket;
 
# Create new socket.
my $sock = new IO::Socket::INET(
                 LocalAddr => 'mclaughlinmysql',
                 LocalPort => '5500',
                 Proto => 'tcp',
                 Listen => 5500,
                 Reuse => 5500);
 
# Kill the program when socket not created.
die "Could not create socket: $!\n" unless $sock;
 
# Set socket to listen for incoming request and loop while waiting.
my $new_sock = $sock->accept();
while(<$new_sock>) {
print $_; }
 
# Close the socket.
close($sock);

Server-side Perl code (client.pl):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Import socket library.
use IO::Socket;
 
# Create new socket.
my $sock = new IO::Socket::INET(
                 PeerAddr => 'mclaughlinmysql',
                 PeerPort => '5500',
                 Proto => 'tcp');
 
# Kill the program when socket not created.
die "Could not create socket: $!\n" unless $sock;
 
# Send string to socket.
print $sock "Hello there!\n";
close($sock);

These scripts help you check connectivity on a port. Run the server first in one command shell and the client second in another command shell. Then, the server-side program prints the “Hello There!” message sent from the client-side program.

You run the server with the following:

perl server.pl

and the client with this:

perl client.pl

Hope they help you verify viability through server ports.

Written by maclochlainn

August 23rd, 2012 at 12:35 am

MySQL Workbench Scripts

with 2 comments

It’s always interesting when somebody asks why they got an error message, and especially sweet when you’re working on something related that lets you answer the question. They were using MySQL Workbench and wanted to know why they couldn’t open a SQL script file by clicking on the Scripting menu option.

As I explained to the individual who asked, you should always click the Edit SQL Script link in the SQL Development section of the MySQL Workbench home page to work on SQL scripts. The Scripting menu option supports Python and Lua plug-ins development and scripts.

They did the following initially, which led down the rabbit warren and left them stumped because they don’t know anything about Python or Lua. This is provided to those who choose to experiment with this advanced feature of MySQL Workbench.

That presents you with a chooser dialog and it lets you pick any type of file. (You may wonder, as I did, why they didn’t restrict it to .py and .lua file extensions, which would preclude opening a .sql file. I actually logged an enhancement request to see if the development team may agree with me.) You get the following message when you choose something other than a Python or Lua script. You can click on any of the reduced size screen shots to enlarge them and make them readable.

As you may note, the dialog says the activity is unsupported by provides no cancellation button. Click the OK button and the unsupported file is loaded into a tab that is useless. All you can do is click to close the tab and dismiss the window.

After you dismiss (by clicking the x) the non-editable .sql file, you need to click on the Open Script file icon shown below.

This chooser really should open where the default is for the MySQL Workbench application script files but it doesn’t. It opens in the last accessed directory. You need to navigate to where your Python or Lua scripts are stored, which is the following directory on Windows:

C:\Users\<user_name>\AppData\Roaming\MySQL\Workbench\scripts

Please note that on a Windows system you can’t chose this directory option because it’s protected. You must enter the navigation bar and type it. Then, you should see any scripts that you saved from within MySQL Workbench.

The ReadFile.py below contains a rather simplistic and static program that reads a file and prints it to console (it’s small and fits in the screen). Obviously, it dispenses with a bunch to keep it small but check a Python website or book for the right way to manage a try block and handle exceptions.

Here’s the ReadFile.py file shown in the preceding and next screen shots. For those new to Python, watch out because tabs aren’t equivalent to spaces. I made a change in the script below to display the trailing semicolon because one of my students asked about it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# ReadFile.py
# Written in MySQL Workbench 5.2.41
 
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Parse string to avoid reading line return.
    if not line[len(line) - 1:len(line)] == ";":
      print(line[0:len(line) - 1])
    else:
      print(line)
    if not line:
        break

Life’s funny, and you can never please everyone. The latest question, “Why did I choose to use substrings when suppressing line returns from the print() function is easier?” Simple answer because the approach differs between Python 2.7 and 3.0 and I didn’t want this post to have a lot of Python nuance.

Python 2.7 (compatible with MySQL Workbench 5.2):

1
2
3
4
5
6
7
8
9
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line),
    if not line:
        print
        break

Python 3.0 (not-compatible with MySQL Workbench 5.2)

You should take note that both version require a print statement on line #8. Line #6 above shows that Python 2.7 uses a comma to suppress the line return, and below line #6 shows Python 3 requires you set end equal to an empty string. Line #8 below also has a set of empty parentheses, which works in Python 3.x but not in Python 2.7. Python 2.7 would print the parentheses unless you put an empty string inside of them, like a print('') statement.

1
2
3
4
5
6
7
8
9
import os
f = open("c:\\Data\\MySQL\\query.sql",'rU')
while True:
    line = f.readline()
    # Suppress line return.
    print(line, end = '')
    if not line:
        print()
        break

Hopefully, everyone concurs the parsing was simpler than explaining all these Python nuances. Although, it’s nice somebody was so curious.

If your script complies with the Python 2.7 rules (that’s what is deployed in MySQL Workbench), click the lighting bolt and your code will run and display the results. That’s shown in the last screen shot.

If you’re interesting in developing plug-ins, check this summary page or this nice example of executing a query to text. Although, rumor has it that certain features may mature over the next year …

Naturally, I hope this helps those experimenting but personally it’s a cool advanced feature of the MySQL Workbench.

Written by maclochlainn

August 18th, 2012 at 4:57 pm

SQL Developer JVM Fix

with 5 comments

It’s amazing the moving parts in Oracle Database 11g, and the ones that I don’t use after installation for a while often reach out to teach me something new. That’s what happened when I went to launch SQL Developer on my Windows 7 Professional 64-bit installation.

I got the message Unable to find a Java Virtual Machine, as shown in the image below. It really means unable to find a 32-bit Java 5 or Java 6 (1.6.0_4+) SDK home. Although the installation appeared fine, it pointed the SQL Developer configuration file to an incompatible 64-bit Java 7 SDK.

You fix this error by following these steps:

  1. Open the sqldeveloper.conf file and check the SetJavaHome parameter value. You find the sqldeveloper.conf file in the following directory:
%ORACLE_HOME\sqldeveloper\sqldeveloper\bin
  1. Install the 32-bit Java 6 (1.6.0_4+) SDK on the operating system if not installed already. You can check whether it’s installed by looking for it in the Program Files (x86) folder.
  2. Change the value of the SetJavaHome parameter to point to the new 32-bit Java 6 home directory (or folder). The following change to line 18 in the sqldeveloper.conf file should fix it on your installation (provided that’s your version of the JVM).
18
SetJavaHome C:\Program Files (x86)\Java\jdk1.6.0_34

Hope this helps those who run into the same issue.

Written by maclochlainn

August 15th, 2012 at 6:01 pm

Oracle and Java Tutorial

without comments

I’m posting this because of a question raised against this older post on how to configure the %CLASSPATH% to find the ojdbc6.jar file. This is the lab file I use in my Database 1 class to expose students to the moving parts of writing Java programs against the Oracle database. That’s why I choose to use a CLOB data type, which requires Oracle’s DBMS_LOB package and wrapping stored procedures.

If you want the same content for MySQL, here’s the link. The full program in either blog entry is available by clicking on the fold/unfold Java Source Code Program widget at the bottom of the respective posts.

This demonstrates how to create an Java infrastructure for reading and writing large text files to an Oracle database. The example provides:

  • A FileIO.jar library that lets you enter Oracle connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
  • A ojdbc6.jar file, which is Oracle’s library for JDBC communication with the Oracle Databases.

The steps to compiling and testing this code are qualified below:

  1. Download and install the Java Software Development Kit (JSDK) for Java 6.
  2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
  3. Download and position the ojdbc6.jar and FileIO.jar files in the JavaTest directory.
  4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:
set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\ojdbc6.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

  1. Copy the WriteReadCLOB.java code from the bottom of this posting and also put it into the JavaTest directory.
  2. Compile the WriteReadCLOB.java source code with the javac utility, as shown below:
javac WriteReadCLOB.java

After you compile it, you should run it as follows:

java WriteReadCLOB
  1. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
  2. When it runs, you’ll see the following tabbed JOptionPane.

You need to enter the following values before clicking the OK button:

  • Host: The hostname of your machine.
  • Port: The port that the Oracle Listener is running on (the default value is 1521).
  • Database: The Oracle TNS Alias, which is orcl for the full database sample database or xe for the Oracle Database 10g Express Edition.
  • UserID: The user (schema) name where you’ve created an ITEM table.
  • Password: The password for the user’s account.

In the JFileChooser, select a file to upload to the database.

You should see what you uploaded displayed in a JFrame.

Written by maclochlainn

August 1st, 2012 at 12:25 pm