MacLochlainns Weblog

This answers “How you connect PHP programs to an Oracle 12c multitenant database. This shows you how to connect your PHP programs to a user-defined Container Database (CDB) and Pluggable Database (PDB). It presupposes you know how to provision a PDB, and configure your Oracle listener.ora and tnsnames.ora files.

CDB Connection:

This assumes you already created a user-defined c##plsql CDB user, and granted an appropriate role or set of privileges to the user. Assuming the demonstration database Oracle TNS Service Name of orcl, you would test your connection with this script:

PDB Connection:

This assumes you already created a user-defined videodb PDB, and video user in the PDB, and granted an appropriate role or set of privileges to the video user. Assuming the user-defined videodb PDB uses an Oracle TNS Service Name of videodb, you would test your connection with this script:

Line 3 above uses the TNS Service Name from the tnsnames.ora file, which is also the SID Name from the listener.ora file after the slash that follows the localhost. That’s the only trick you should need.

You should note that because the tnsnames.ora file uses a video service name, the connection from the command line differs:

sqlplus video@video/video

Hope this helps those trying to sort it out.

The dbms_comparison package isn’t deployed when you provision a pluggable databases (PDBs) in Oracle 12c. It appears to be a simple omission. At least, it let me manually compiled the dbms_comparison package with this syntax:

@?/rdbms/admin/dbmscmp.sql
@?/rdbms/admin/prvtcmp.plb

However, when I ran the code against the PDB it failed. The same code worked against a container database (CDB). It struck me as odd. The error stack wasn’t too useful, as you can see below:

1
2
3
4
5
6
7
8
9
10

BEGIN
*
ERROR at line 1:
ORA-06564: object "SYS"."COMPARE_NAME" does NOT exist
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 569
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 602
ORA-06512: at "SYS.DBMS_CMP_INT", line 394
ORA-01403: no DATA found
ORA-06512: at "SYS.DBMS_COMPARISON", line 764
ORA-06512: at line 2

My test was using two copies of a table with differences between column values. Both were deployed in the same CDB or PDB. That meant it was either a missing table or a problem with my database link. Here’s the statement that caused the failure:

7
8
9
10
11
12

  dbms_comparison.create_comparison(comparison_name      => 'COMPARE_NAME'
                                   , schema_name         => 'video'
                                   , object_name         => 'MEMBER#1'
                                   , dblink_name         => 'loopbackpdb'
                                   , remote_schema_name  => 'video'
                                   , remote_object_name  => 'MEMBER#2');

Unfortunately, there wasn’t any notable difference between the two database links. Playing around with it, I discovered the problem. While you don’t have to enclose your case sensitive password in double quotes for a CDB database link, you do need to enclose the password with double quotes in a PDB database link.

This database link fixed the problem:

1
2
3

CREATE DATABASE LINK loopbackpdb
  CONNECT TO video IDENTIFIED BY "Video1"
  USING 'video';

The delimiting double quotes on line 2 fixed the problem. Hopefully, this helps somebody who runs into it too. Any way, according to this June 2013 Oracle White Paper it would appear as a bug because it’s an inconsistent behavior between a CDB and PDB.

PHP namespaces are new to PHP 5.3 (2012), but PHP class files have been around since PHP 5 was introduced. However, a couple students had problems creating working code from the many fragments published else where. Here’s my attempt to qualify it in a single post, running Zend Server Community Edition 6 and Oracle Database 11g.

The first thing you need to understand is a namespace. Namespaces exist to disambiguate (tell the difference between) class files that share the same name. After all, there are only so many obvious things to call class files. 😉 You can put classes, interfaces, functions, and constants in namespaces.

Let’s say you qualify your namespace as:

namespace Oracle\Db;

You would make that the first thing in a PHP file, and shouldn’t include any HTML. You would then use a require(), require_once(), include(), or include_once() to add the class to a PHP file that uses the namespace qualified file. Then, you would construct a new instance of your PHP class. Together, these two steps would look like this:

  require_once('Db.php');
  $db = new \Oracle\Db\Db("Test Example","Author");

Notice the back slash in front of the Oracle namespace, and then you provide the namespace qualified file name (minus the file extension) and the class name. Since the namespace qualified file name and class name are the same, you see the double Db.

Here is a basic (starter) Oracle database connection class file, which you should store as Db.php in the Apache’s htdocs\Oracle directory:

<?php
/* Declare a namespace, available from PHP 5.3 forward. */
namespace Oracle\Db;
 
/* Create a Database Connection class. */
class Db {
 
  /* Declare class variables. */
  protected $conn = null;
  protected $stmt = null;
  protected $prefetch = 100;
 
  /* Declare the default construction function. */
  function __construct($module, $cid) {
 
    // Construct a connection and suppress errors and warnings.    
    $this->conn = @oci_connect(SCHEMA, PASSWD, TNS_ID, CHARSET);
 
    // Check for a connection, and process the work.
    if (!$this->conn) {
      // Assign Oracle error message.
      $msg = oci_error();
 
      /* The \ preceding Exception is necessary because of the
         introduction of namespaces in PHP 5.3. Without it, the
         program would attempt to call \Oracle\Exception rather
         than our little runtime example. */
      throw new \Exception('Cannot connect to database: '.$msg['message']);
    }
 
    /* Set Oracle Call Interface parameters.
     * =========================================================
     *  - The oci_set_client_info() function replaces a call
     *    to the DBMS_APPLICATION_INFO package, and much more
     *    efficient.
     *  - The oci_set_module_name() function allows you to 
     *    register the function name that calls the Db class.
     *  - The oci_set_client_identifier() function and you 
     *    use it with DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE,
     *    which can be enabled with a call to the 
     *    DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE.     
     * =========================================================
     */
    oci_set_client_info($this->conn, "Administrator");
    oci_set_module_name($this->conn, $module);
    oci_set_client_identifier($this->conn, $cid);
  }
 
  /* Declare execute function. */  
  public function execute($sql, $action, $bindvars = array()) {
 
    // Parse statement.
    $this->stmt = oci_parse($this->conn, $sql);
 
    // Check for a prefetch value greater than zero.
    if ($this->prefetch >= 0) {
      oci_set_prefetch($this->stmt, $this->prefetch);
    }
 
    // Read the list of bind variables and bind them.
    foreach ($bindvars as $bv) {
      oci_bind_by_name($this->stmt, $bv[0], $bv[1], $bv[2]);
    }
 
    // Set the action name for Oracle tracing and execute statement.
    oci_set_action($this->conn, $action);
 
    // Set to auto commit.
    oci_execute($this->stmt);
  }
 
  /* Declare function that fetches all. */
  public function execFetchAll($sql, $action, $bindvars = array()) {
    $this->execute($sql, $action, $bindvars);
    oci_fetch_all($this->stmt, $res, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
 
    // Free statement resources.
    $this->stmt = null;
    return($res);
  }
 
  /* Declare the default destructor function. */
  function __destruct() {
    if ($this->stmt)
      oci_free_statement($this->stmt);
    if ($this->conn)
      oci_close($this->conn);
  }
}
?>

Here is a credential file for Oracle, where the network SID is orcl (change orcl to xe when using the Oracle Express Edition):

1
2
3
4
5
6
7

<?php
  // Connection variables.
  define('SCHEMA',"student");
  define('PASSWD',"student");
  define('TNS_ID',"localhost/orcl");
  define('CHARSET',"AL32UTF8");
?>

If you do not know your the character set of your database, you can find it by logging in as the SYSTEM user, and running this query:

SELECT VALUE$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

Here’s the test program for the database connection class, save it as TestDb.php in your Apache’s htdocs\Oracle directory:

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

<?php
  // Require once the namespace identified class and credentials files.
  require_once('Db.php');
  require_once('credentials.php');
 
   // Process the input parameter, which REALLY should be through a $_POST argument.
  (isset($_GET['last_name'])) ? $input = $_GET['last_name'] : $input = '';
 
  /* Establish new connection.
   * ======================================================
   *  The namespace (PHP 5.3) is set in Db.php as follows:
   *    namespace Oracle\Db;
   *
   *  The namespace syntax needs to qualify the following
   *  when you call it:
   *  - A \ (back slash) before the namespace.
   *  - The file name but not the file extension.
   *  - The class name from the Db.php file.
   */
  $db = new \Oracle\Db\Db("Test Example","Author");
 
  // Assign query.
  $sql = "SELECT * FROM contact c WHERE c.last_name = :bv";
 
  // Assign fetch to a result array.
  $result = $db->execFetchAll($sql, "Query Example", array(array(":bv", $input, -1)));
 
  // Open table and provide headers.
  print "<table border='1'>\n";
  print "<tr><th>First Name</th><th>Last Name</th></tr>\n";
 
  // Iterate through the rows.
  foreach ($result as $row) {
    $fname = htmlspecialchars($row['FIRST_NAME'], ENT_NOQUOTES, 'UTF-8');
    $lname = htmlspecialchars($row['LAST_NAME'], ENT_NOQUOTES, 'UTF-8');
    print "<tr><td>$fname</td><td>$lname</td></tr>\n";
  }
 
  // Close the table.
  print "</table>";
?>

If you get the call to the namespace wrong, you’ll get a strange set of errors. Just make sure you understand the differences between declaring a namespace and calling a namespace.

You test the database connection class with the following URL on your localhost (substitute a server name if it’s not a development environment), provided you’ve created a table contact with a row where the last_name equals 'Sweeney':

http://localhost/Oracle/TestDb.php?last_name=Sweeney

The following creates and seeds the contact table:

CREATE TABLE contact
( contact_id  NUMBER
, first_name  VARCHAR2(10)
, last_name   VARCHAR2(10));
INSERT INTO contact VALUES (1,'Meghan','Sweeney');
INSERT INTO contact VALUES (2,'Matthew','Sweeney');
INSERT INTO contact VALUES (3,'Ian','Sweeney');

One of my old students and lab assistants stopped by to show his fiancée the BYU-Idaho campus. It was a long trip since he lives in Macao, China.

He kindly brought me a copy of my Oracle Database 11g and MySQL 5.6 Developer Handbook in simplified Chinese. He’s holding it in the photo.

That makes three books translated into Chinese, which made my day. It’ll be interesting to see if the new MySQL Workbench: Data Modeling & Development book gets translated into Chinese too. Oddly, I never hear about this from the publisher first.

The cover emphasized only the Dolphin, not the Oracle logo material. It made me wonder, how many MySQL users there might be in China. If anybody from China catches the post, it would be great to hear about the MySQL Community in China.

Likewise, if anybody in China catches the post and reads the book, please let me know if you liked it. 😉 Naturally, let me know if you found any problems with it too. By the way, I keep an errata for the book here.

Two things contributed to this post. One was a student question about the difference between the MAX_VALUE of a sequence and the actual sequence value. The other was a comment on an earlier post addressing an NDS approach to resetting sequences.

The student wanted to understand why there were gaps in the sequence, since they created it with the default values, like this:

CREATE SEQUENCE sequence_name;

A default sequence in an Oracle database sets the cache value to 20, which means you get gaps every time a new set is allocated. This Ask Tom question and answer holds that information.

The blog comment was on an NDS statement post. I wasn’t quite sure what the comment wanted because there really wasn’t a question or a complete code example. Having demonstrated how to dynamically drop and recreate a new sequence without a gap in a comment reply, I thought that was adequate.

Having pointed the student to the Ask Tom column and my answer, he wanted to know how to create a stored procedure to reset sequences dynamically. It took me a couple weeks to get back to this but here’s the procedure. The tricky element of the procedure is the “no binding values allowed” restriction placed on NDS statements that process DDL statements.

The procedure uses two local procedures and two local functinons. The local find_sequence finds a sequence name in the schema, and find_next_sequence function returns the next value. The local create_sequence and drop_sequence procedures respectively isolate the logic for dynamic drops and creates of the sequence.

Warning: The restriction with this design assumes that the table name and sequence name are linked by using the table name and an _ID suffix.

-- Declare an autonomous procedure to drop sequences.
CREATE OR REPLACE PROCEDURE reset_sequence
( pv_table_name     VARCHAR2
, pv_sequence_name  VARCHAR2
, pv_cache_value    NUMBER DEFAULT 0 ) IS
 
  -- Declares stubs to remove forward reference limits.
  PROCEDURE create_sequence ( pv_sequence_name  VARCHAR2, pv_cache_value  NUMBER );
  PROCEDURE drop_sequence ( pv_sequence_name  VARCHAR2 );
  FUNCTION find_sequence ( pv_sequence_name  VARCHAR2 ) RETURN VARCHAR2;
  FUNCTION find_next_sequence ( pv_table_name  VARCHAR2 ) RETURN VARCHAR2;
 
  -- Drops sequence.
  PROCEDURE drop_sequence
  ( pv_sequence_name  VARCHAR2 ) IS
    -- Declare local variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_name  VARCHAR2(30);
  BEGIN
 
    /*  Conditionally drop any sequence using a local function to find a valid
        sequence name before attempting to drop it. */  
    IF find_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)) > 0 THEN  
 
      /* Dynamically drop sequence, which requires concatenating the sanitized 
         sequence name because you can't bind values on a DDL statement. */
      lv_statement := 'DROP sequence '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name);
 
      -- Execute statement immediately.
      EXECUTE IMMEDIATE lv_statement;
 
    END IF;      
  END drop_sequence;
 
    -- Drops sequence.
  PROCEDURE create_sequence
  ( pv_sequence_name  VARCHAR2
  , pv_cache_value    NUMBER ) IS
    -- Declare local variable(s).
    lv_statement      VARCHAR2(200);
    lv_next_sequence  NUMBER;
  BEGIN
 
    -- Assign the next sequence value if one exists.
    lv_next_sequence := find_next_sequence(pv_table_name);
 
    -- Check whether a cache value has been provided.
    IF pv_cache_value > 0 THEN
 
      /* Dynamically create a sequence statement with a new start value that is one
         greater than the highest value in the table that uses the sequence. */
      lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10)
                   ||       'MINVALUE 1 NOMAXVALUE INCREMENT BY 1'||CHR(10)
                   ||       'START WITH '||lv_next_sequence||' CACHE '||pv_cache_value||' NOORDER NOCYCLE';
 
    ELSE
 
      /* Dynamically create a sequence statement with a new start value that is one
         greater than the highest value in the table that uses the sequence. */
      lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10)
                   ||       ' MINVALUE 1 NOMAXVALUE'||CHR(10)
                   ||       'INCREMENT BY 1 START WITH '||lv_next_sequence||' NOCACHE NOORDER NOCYCLE';
 
    END IF;
 
    -- Execute create sequence statement.
    EXECUTE IMMEDIATE lv_statement;
 
  END create_sequence;
 
  -- Finds whether a sequence exists.
  FUNCTION find_sequence
  ( pv_sequence_name  VARCHAR2 ) RETURN VARCHAR2 IS
    -- Declare local return variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_name  VARCHAR2(30);
    lv_return_value   NUMBER := 0;
 
    -- Declare local system reference cursor.
    lv_cursor  SYS_REFCURSOR;
 
  BEGIN
    -- Dynamically find sequence.
    lv_statement := 'SELECT   sequence_name'||CHR(10)
                 || 'FROM     user_sequences'||CHR(10)
                 || 'WHERE    sequence_name = :seq_name';
 
    OPEN lv_cursor FOR lv_statement USING DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name);
    FETCH lv_cursor INTO lv_sequence_name;
    CLOSE lv_cursor;
 
    -- Convert valid sequence name to positive integer, which represents truth.
    lv_return_value := LENGTH(lv_sequence_name);
 
    -- Return value.
    RETURN lv_return_value;
  EXCEPTION
    -- Reached when DBMS_ASSERT check fails.
    WHEN OTHERS THEN
      RETURN lv_return_value;
  END find_sequence;
 
  -- Finds the next sequence value.
  FUNCTION find_next_sequence
  ( pv_table_name  VARCHAR2 ) RETURN VARCHAR2 IS
    -- Declare local return variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_value  NUMBER;
    lv_return_value    NUMBER := 1;
 
    -- Declare local system reference cursor.
    lv_cursor  SYS_REFCURSOR;
 
  BEGIN
    -- Dynamically find sequence.
    lv_statement := 'SELECT   MAX('||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name)||'_ID) + 1'||CHR(10)
                 || 'FROM    '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name);
 
    OPEN lv_cursor FOR lv_statement;
    FETCH lv_cursor INTO lv_sequence_value;
    CLOSE lv_cursor;
 
    -- Assign the return value from the NDS statement to a local return variable.
    lv_return_value := lv_sequence_value;
 
    -- Return value.
    RETURN lv_return_value;
  EXCEPTION
    -- Reached when DBMS_ASSERT check fails.
    WHEN OTHERS THEN
      RETURN lv_return_value;
  END find_next_sequence;
 
  -- Set precompiler instruction to run in a discrete process.
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 
  -- Conditionally drop sequence.
  drop_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name));
 
  -- Create sequence.
  create_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name), pv_cache_value);
 
END;
/

You can test this procedure with the following script, which includes COMMIT statements. Those are requires because the stand alone procedure runs independently of the current session as an autonomous procedure.

-- Conditionally drop existing tables and sequences.
BEGIN
  FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('SAMPLE','SAMPLE_S')) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
      dbms_output.put_line(i.object_name);
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;   
      dbms_output.put_line(i.object_name);
    END IF;
  END LOOP;
END;
/
 
-- Create test table.
CREATE TABLE sample
(sample_id  NUMBER);
 
-- Create test table.
CREATE SEQUENCE sample_s;
 
-- Insert a seeding row.
INSERT INTO sample VALUES (10);
 
-- Commit the values because the procedure is autonomous.
COMMIT;
 
-- Reset the sequence value.
EXECUTE reset_sequence('SAMPLE','SAMPLE_S');
 
-- Insert the next sequence value.
INSERT INTO sample VALUES (sample_s.nextval);
 
-- Commit the values because the procedure is autonomous.
COMMIT;
 
-- Query the result to ensure the numbers are sequential.
SELECT * FROM sample;
 
EXECUTE reset_sequence('SAMPLE','SAMPLE_S',500);
 
-- Insert the next sequence value.
INSERT INTO sample VALUES (sample_s.nextval);
 
-- Query the result to ensure the numbers are sequential.
SELECT * FROM sample;

Hope this helps anybody interested in automating the process of resetting a START WITH value of an Oracle sequence.

Why should you use stored programs? Great question, here’s my little insight into a situation that I heard about in a large organization.

A very large organization is having a technology argument. In someway, like politics, half-truth drives this type of discussion. This company has hundreds of databases and they’re about half SQL Server and Oracle. The argument (half-truth) states that using T-SQL or PL/SQL yields “spaghetti” code!

It seems like an old argument from my perspective. After all, I’ve been working with T-SQL and PL/SQL for a long time. Spaghetti code exists in every language when unskilled programmers solve problems but the point here is one of software architecture, and an attempt to malign stored programming in general. Let’s examine the merit of the argument against stored programs.

First of all, the argument against stored programs is simply not true. SQL DML statements, like the INSERT, UPDATE, and DELETE statements should maintain ACID compliant interactions with a single table in a database. Unfortunately, the same statements create anomalies (errors) in a poorly designed database.

Stored programs provide the ability to perform ACID compliant interactions across a series of tables in a database. They may also hide database design errors and protect the data from corruption. The same can’t be said for Java or C# developers. Java and C# developers frequently fail to see database design errors or they overlook them as inconsequential. This type of behavior results in corrupt data.

It typically raises cost, errors, and overall application complexity when key logic migrates outside the database. If you’re asking why, that’s great. Here are my thoughts on why:

1. Making a Java or C# programmer responsible for managing the transaction scope across multiple tables in a database is not trivial. It requires a Java programmer that truly has mastered SQL. As a rule, it means a programmer writes many more lines of logic in their code because they don’t understand how to use SQL. It often eliminates joins from being performed in the database where they would considerably outperform external language operations.
2. Identifying bottlenecks and poor usage of data becomes much more complex for DBAs because small queries that avoid joins don’t appear problematic inside the database. DBAs don’t look at the execution or scope of transactions running outside of the database and you generally are left with anecdotal customer complaints about the inefficiency of the application. Therefore, you have diminished accountability.
3. Developing a library of stored procedures (and functions) ensures the integrity of transaction management. It also provides a series of published interfaces to developers writing the application logic. The published interface provides a modular interface, and lets developers focus on delivering quality applications without worrying about the database design. It lowers costs and increases quality by focusing developers on their strengths rather than trying to make them generalists. That having been said, it should never mask a poorly designed database!
4. Service level agreements are critical metrics in any organization because they compel efficiency. If you mix the logic of the database and the application layer together, you can’t hold the development team responsible for the interface or batch processing metrics because they’ll always “blame” the database. Likewise, you can’t hold the database team responsible for performance when their metrics will only show trivial DML statement processing. Moreover, the DBA team will always show you that it’s not their fault because they’ve got metrics!
5. Removing transaction controls from the database server generally means you increase the analysis and design costs. That’s because few developers have deep understanding of a non-database programming language and the database. Likewise, input from DBAs is marginalized because the solution that makes sense is disallowed by design fiat. Systems designed in this type of disparate way often evolve into extremely awkward application models.

Interestingly, the effective use of T-SQL or PL/SQL often identifies, isolates, and manages issues in poorly designed database models. That’s because they focus on the integrity of transactions across tables and leverage native database features. They also act like CSS files, effectively avoiding the use of inline style or embedded SQL and transaction control statements.

Let’s face this fact; any person who writes something like “spaghetti” code in the original context is poorly informed. They’re typically trying to sidestep blame for an existing bad application design or drive a change of platform without cost justification.

My take on this argument is two fold. Technologists in the organization may want to dump what they have and play with something else; or business and IT management may want to sidestep the wrath of angry users by blaming their failure on technology instead of how they didn’t design, manage, or deliver it.

Oh, wait … isn’t that last paragraph the reason for the existence of pre-package software? 😉 Don’t hesitate to chime in, after all it’s just my off-the-cuff opinion.

Somebody posed a question about starting and stopping the database from within the OEM tool as the SYSTEM rather than the SYS user. There’s actually a simple trick to do that, you connect to OEM as the SYSTEM user but when you startup or shutdown the database, you reconnect for those tasks as the SYS user.

You can use the Windows services (services.msc) to start or stop the OEM service. Alternatively, you can do it at the command-line. Instructions for starting and stopping OEM from the command-line are found in this older post.

I put together these screen shots from the point where the DBConsole and database are up and running. That means they show you how to shutdown the database before re-starting it.

1. Navigate to the DB Console home page and click the Shutdown button.

2. After clicking the Shutdown button, you’re prompted for an authorized host operating system credentials and database credentials.

3. You enter the host operating system user and password, and SYS user and password (which is often the same as the SYSTEM password). Click the OK button after entering the host and database credentials.

4. After validating credentials, you get a confirmation page. Click the Yes button.

5. Since a shutdown can take more than a few moments, you get the following screen. Click the Refresh button when you want to check whether the database is shutdown.

6. The Refresh button shows the following webpage. You can click the Startup button to restart the database.

7. Clicking the Startup button re-prompts you for the same host and database credentials as used during the shutdown operation. Click the OK button to continue.

8. After validating credentials, you get a confirmation page. Click the Yes button.

9. After validating credentials and starting the database, you are prompted for the database credential to sign on to OEM.

10. After validating credentials and starting the database, you are prompted for the database credential to sign on to OEM. Enter the credentials and click the Login button to connect to OEM.

Hopefully, this answers the questions and helps those who are curious to see the screens before trying the process.

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.

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.

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

2. 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.
3. 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.