Oracle 11g | MacLochlainns Weblog

I’ve worked with every release of Microsoft Excel, and I know it takes effort to keep up to date with certain releases. Clearly, the Data Analysis eXpression (DAX) Language introduced in Excel 2010 went unnoticed by many, which was sad. DAX is truly a powerful extension to the analytical and modeling approaches in Microsoft Excel.

I’d like to recommend Microsoft Excel 2013 Building Data Models with PowerPivot to those who haven’t learned how to use DAX in Excel 2010, 2011, or 2013. DAX works with tables but if you don’t use tables, I guess you can skip DAX because you must have infinite time to produce marginal analytical outcomes (tongue in cheek humor). However, if you’re like most folks, you want a book to get you up-to-speed quickly, and that’s what this book will do for you.

Just one caveat if you’re using an Oracle or MySQL database, use the prepackaged analytic functions before you download the data set. You should always pre-select data before applying analytics in Excel. Remember the more refined the data model you start with the easier it is to structure analytical tools to leverage the data model. While DAX is powerful, it doesn’t replace the speed and query optimized behaviors of effective Oracle or MySQL queries.

Raja asked a question but unfortunately, I was buried in the final aspects of the write of the new Oracle Database 12c PL/SQL Programming book. He wanted to know how to pass an object type as an OUT-only mode parameter from a procedure.

That’s a great question, and it’s actually simple once you understand the difference between Oracle object types and other data types. Oracle object types must always be initiated before you use them, which means you must initialize any OUT-only mode parameters at the top of your execution section, like this:

CREATE OR REPLACE PROCEDURE reset_troll
( pv_troll OUT TROLL_OBJECT ) IS
  /* Troll default name. */
  lv_troll_name  VARCHAR2(20) := 'Bert';
BEGIN
  /* Initialize the incoming parameter by allocating memory to it. */
  pv_troll := troll_object();
 
  /* Set the name to something other than the 'Tom' default value. */
  pv_troll.set_troll(lv_troll_name);
END reset_troll;
/

Line 7 shows you the trick, initialize the incoming parameter because there isn’t an incoming parameter for an OUT-only mode parameter. The calling parameter to an OUT-only mode parameter is only a reference where PL/SQL will copy the internal object reference. While the calling parameter has been initialized, the reference to the call parameter’s object is where the internal object will be copied. The local program must first ensure a new memory location for a new instance of the object type before it can act on or return an object instance to the external reference. More or less, the internal object is copied to the calling object instance’s memory location when the procedure completes its execution.

Here’s the source code for the troll_object object type and body:

CREATE OR REPLACE TYPE troll_object IS OBJECT
( troll VARCHAR2(20)
, CONSTRUCTOR FUNCTION troll_object
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION troll_object
  ( troll VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_troll RETURN VARCHAR2
, MEMBER PROCEDURE set_troll (troll VARCHAR2)
, MEMBER FUNCTION to_string RETURN VARCHAR2)
INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE TYPE BODY troll_object IS
  /* Default no-argument constructor. */
  CONSTRUCTOR FUNCTION troll_object RETURN SELF AS RESULT IS
    troll TROLL_OBJECT := troll_object('Tom');
  BEGIN
    SELF := troll;
    RETURN;
  END troll_object;
  /* Single argument constructor. */
  CONSTRUCTOR FUNCTION troll_object (troll VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    SELF.troll := troll;
    RETURN;
  END troll_object;
  /* A getter function. */
  MEMBER FUNCTION get_troll RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.troll;
  END get_troll;
  /* A setter procedure. */
  MEMBER PROCEDURE set_troll (troll VARCHAR2) IS
  BEGIN
    SELF.troll := troll;
  END set_troll;
  /* A function that returns the formatted object type's contents. */
  MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Hello '||SELF.troll;
  END to_string;
END;
/

You can test the reset_troll procedure with the following anonymous block:

/* Enable printing from a PL/SQL block. */
SET SERVEROUTPUT ON SIZE UNLIMITED
/* Anonymous testing block. */
DECLARE
  lv_troll  TROLL_OBJECT := troll_object('Bill');
BEGIN
  dbms_output.put_line('--------------------');
  /* Prints 'Hello William' */
  dbms_output.put_line(lv_troll.to_string());
  dbms_output.put_line('--------------------');
  reset_troll(lv_troll);
  /* Prints 'Hello Bert' */
  dbms_output.put_line(lv_troll.to_string());
  dbms_output.put_line('--------------------');
END;
/

If you remark out line 7 from the reset_troll procedure, you’d raise the following exception by the call on line 10 because the local object hasn’t been instantiated (given life). It means there’s no memory location allocated for the instantiated (instance of an object type).

--------------------
Hello Bill
--------------------
DECLARE
*
ERROR at line 1:
ORA-30625: method dispatch ON NULL SELF argument IS disallowed
ORA-06512: at "VIDEO.RESET_TROLL", line 10
ORA-06512: at line 8

Hope this helps those trying to solve the same problem.

Somebody wanted an example of how to capture the return value of a PL/SQL function in a PHP script running against the Oracle Database. The trick is embedding the call inside an anonymous block program unit, like a prior example that I posted on handling an OUT mode variable in a PL/SQL Procedure.

Here’s the sample PHP code:

<?php
  // Attempt to connect to your database.
  $c = @oci_connect("student", "student", "localhost/orcl");
  if (!$c) {
    print "Sorry! The connection to the database failed. Please try again later.";
    die();
  }
  else {
    // Initialize incoming message whether or not parameter sent.
    $msg_in = (isset($_GET['msg'])) ? $_GET['msg'] : "Cat got your keyboard?";
 
    // Set the call statement, like a SQL statement.
    $sql = "BEGIN :b := echo(:a); END;";
 
    // Prepare the statement and bind the two strings.
    $stmt = oci_parse($c,$sql);
 
    // Bind local variables into PHP statement, you MUST size OUT only variables.
    oci_bind_by_name($stmt, ":a", $msg_in);
    oci_bind_by_name($stmt, ":b", $msg_out, 80, SQLT_CHR);
 
    // Execute it and print success or failure message.
    if (oci_execute($stmt)) {
      print $msg_out;
    }
    else {
      print "Sorry, I can't do that Dave...";
    }
 
    // Free resources.
    oci_free_statement($stmt);
    oci_close($c);
  }
?>

As noted by Chris, you should size input parameters too. It could be qualified as a best practice when code re-executes with different values without rebinding.

It depends on this echo function:

CREATE OR REPLACE FUNCTION echo
( message IN     VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  RETURN 'Message ['||message||'] received.';
END;
/

Hope that this is what they needed, or you’re looking for as an answer to a search.

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);
  }
}
?>

<?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):

<?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:

<?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');

Just went through all my PHP testing against a fresh instance of Oracle with Zend Server Community Edition 6, and found these warnings, guess that’s pretty clean for the Oracle part of the installation. I didn’t notice it before because generally I do most of my PHP development against a MySQL database. I should have been configuring the php.ini file routinely, as qualified in this PHP forum discussion.

Warning: oci_set_client_info(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 47
Warning: oci_set_module_name(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 48
Warning: oci_set_action(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 69

Turns out Zend 6 doesn’t automatically set the [Date] elements in the php.ini file, which is required for the oci_set_client_info(), oci_set_module_name(), and oci_set_action() functions of the OCI. You can find the php.ini file in the C:\Program Files (x86)\Zend\ZendServer\etc folder on Windows:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
;date.timezone =
 
; http://php.net/date.default-latitude
;date.default_latitude = 31.7667
 
; http://php.net/date.default-longitude
;date.default_longitude = 35.2333
 
; http://php.net/date.sunrise-zenith
;date.sunrise_zenith = 90.583333
 
; http://php.net/date.sunset-zenith
;date.sunset_zenith = 90.583333

You can find the values for date.timezone here. Update the date.timezone as follows:

date.timezone = America/Denver

Then, reboot the Zend Server, and it fixes the warning messages.

Somebody asked me why they can’t query the DBMS_TYPES.TYPECODE_OBJECT value because they get an ORA-06553 error. Their query attempt is:

SELECT   dbms_types.typecode_object
FROM     dual;

Naturally, it raises the following exception:

SELECT   dbms_types.typecode_object
         *
ERROR at line 1:
ORA-06553: PLS-221: 'TYPECODE_OBJECT' IS NOT a PROCEDURE OR IS undefined

The explanation is very simple. It’s a package scoped variable and in Oracle 11g only accessible in a PL/SQL block. Here’s an anonymous block that would print the value to the console:

BEGIN
  dbms_output.put_line(dbms_types.typecode_object);
END;
/

Hope that helps those trying to discover what a package variable’s value is.

Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an ORA-22979 is raised is one of those, and the error is typically:

ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF

This error occurs on an INSERT statement if you follow the example from the Oracle 11gR2 Object-Relational Developer’s Guide, which also has various slightly modified examples in a couple PL/SQL books. It also happens on an UPDATE statement to populate REF values.

The conflict is typically between the uniqueness of the reference and an attempt to make a non-reference column of the object type a primary key constrained column and embedded object view. The source of the conflict is the OBJECT IDENTIFIER IS PRIMARY KEY associated with a primary key in the Oracle documentation. The two goals are mutually exclusive; only the reference or non-reference column can be the object identifier. Unfortunately, Oracle documentation contains both examples in different places without making any effective cross reference.

If you want to make a column of an object type a primary key for an object table (that is a table that uses an object type to define its structure) and the object view (the content of the embedded object type), you can’t include the OBJECT IDENTIFIER IS PRIMARY KEY clause when you want to populate the REF column of the object type. Here’s an example that uses a column of the object type as a primary key and leaves the REF column empty:

-- Create the BASE_T type, or specification for IDL.
CREATE OR REPLACE
  TYPE base_t IS OBJECT
  ( obj_id    NUMBER
  , obj_name  VARCHAR2(30)
  , obj_ref   REF base_t)
  NOT FINAL;
/

You can then create a table like the following:

CREATE TABLE base OF base_t
( obj_id CONSTRAINT base_pk PRIMARY KEY )
  OBJECT IDENTIFIER IS PRIMARY KEY;

Let’s insert some rows to test for ourselves that this fails when you try to assign references:

INSERT INTO base VALUES (base_t(1, 'Dwalin',NULL));
INSERT INTO base VALUES (base_t(2, 'Borfur',NULL));
INSERT INTO base VALUES (base_t(3, 'Gloin',NULL));
INSERT INTO base VALUES (base_t(4, 'Kili',NULL));
INSERT INTO base VALUES (base_t(5, 'Fili',NULL));

The following UPDATE statement attempts to assign references, but fails as shown below:

UPDATE   base b
SET      obj_ref = REF(b);

The UPDATE fails as shown:

UPDATE   base b
         *
ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF

The simple fix redefines the object table by removing the OBJ_ID column as an object identifier and primary key value. You do that by removing the OBJECT IDENTIFIER IS PRIMARY KEY clause because the column of the object type can be a primary key for the table without being an object view identifier. After you make the change, you can successfully update the table with object references. Object identifiers or references are unique and serve the same purpose of a primary key for the object view, and at the same time they can’t both exist.

CREATE TABLE base OF base_t

Inserting the same rows, you can now update the table to provide valid object references. Let’s experiment with how they work because that’s also not as clear as I’d like in the Oracle documentation.

The next statement creates a CHILD table that holds a reference to the BASE (or parent) table and another instance of the same BASE_T object type:

CREATE TABLE child
( child_id   NUMBER  CONSTRAINT child_pk PRIMARY KEY
, base_ref   REF base_t SCOPE IS base
, child      base_t);

The INSERT statement can’t use a VALUES clause because we MUST capture the reference (or in this case primary key) from the BASE (or parent) table. An INSERT statement with a query does the trick:

INSERT INTO child 
SELECT 1, obj_ref, base_t(1, 'Gimli',NULL)
FROM   base b
WHERE  b.obj_name = 'Gloin';

You should note that the reference for the CHILD table’s CHILD column isn’t set but is likewise not required for the example to work.

Now, lets perform an standard INNER JOIN (equijoin) between the two tables by using the references as primary and foreign keys. Please note the trick is referring to the table and column of the BASE (or parent) table and the table, column, and embedded OBJ_REF of the CHILD table.

COLUMN father FORMAT A10
COLUMN son    FORMAT A10
SELECT   b.obj_name AS "Father"
,        c.child.obj_name AS "Son"
FROM     base b INNER JOIN child c ON b.obj_ref = c.base_ref.obj_ref;

You get the following results:

Father     Son
---------- ----------
Gloin      Gimli

You can make a view of this table with either of these syntaxes:

CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID DEFAULT AS
SELECT * FROM base;

or,

CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID (obj_id) AS
SELECT * FROM base;

Hope it helps anybody trying it. Personally, I think it’s better to use collections of object types, but that’s much bigger discussion that I’ll save for the Oracle Database 12c PL/SQL Programming book that I’m writing.

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps (a two-minute tech-tip).

Basically, you create a user-defined data type, or structure:

CREATE OR REPLACE TYPE item_structure IS OBJECT
( id      NUMBER
, lookup  VARCHAR2(30));
/

Then, you create a list (an Oracle table) of the structure, like:

1 2	CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure; /

Lastly, you create an object table function, like:

CREATE OR REPLACE FUNCTION get_item_types RETURN item_lookup IS
 
  -- Declare a variable that uses the record structure.
  lv_counter      PLS_INTEGER := 1;
 
  -- Declare a variable that uses the record structure.
  lv_lookup_table  ITEM_LOOKUP := item_lookup();
 
  -- Declare static cursor structure.
  CURSOR c IS
    SELECT   cl.common_lookup_id AS lookup_id
    ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
    FROM     common_lookup cl
    WHERE    cl.common_lookup_table = 'ITEM'
    AND      cl.common_lookup_column = 'ITEM_TYPE'
    AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
    ORDER BY cl.common_lookup_meaning;
 
BEGIN
 
  FOR i IN c LOOP
    lv_lookup_table.EXTEND;
 
    /* The assignment pattern for a SQL collection is incompatible with
       the cursor return type, and you must construct an instance of the
       object type before assigning it to a collection. */
    lv_lookup_table(lv_counter) := item_structure( i.lookup_id
                                                 , i.lookup_meaning );
 
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /* Call an autonomous function or procedure here! It would allow you to
     capture who queried what and when; and acts like a pseudo trigger for
     queries. */
 
  RETURN lv_lookup_table;
END;
/

Now you can embed the object table function in a view, like this:

1
2
3

CREATE OR REPLACE VIEW item_lookup_view AS
  SELECT *
  FROM   TABLE(get_item_types);

Why not simply use an embedded query in the view, like the following?

SQL> CREATE OR REPLACE VIEW normal_view AS
  2    SELECT   cl.common_lookup_id AS lookup_id
  3      ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
  4      FROM     common_lookup cl
  5      WHERE    cl.common_lookup_table = 'ITEM'
  6      AND      cl.common_lookup_column = 'ITEM_TYPE'
  7      AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
  8      ORDER BY cl.common_lookup_meaning;

My guess is that it was too easy but who knows, maybe they found a real need. The only need that I see occurs when you’re enforcing something like HIPPA and you want to capture unauthorized queries along with who performed them.

Naturally, I hope this helps those looking to resolve syntax errors when they have a need to do the more complex solution.

It’s funny but Oracle doesn’t want you to enter a trivial password, and about every week I get asked what the standards are for Oracle Database passwords. That’s funny too because it’s in the documentation, the one most experienced and new users never read – Oracle Database Installation Guide (available by platform, the link is for the Windows platform).

Anyway, let me quote the rules:

Oracle recommends that the password you specify:

Contains at least one lowercase letter.
Contains at least one uppercase letter.
Contains at least one digit.
Is at least 8 characters in length.
Uses the database character set which can include the underscore (_), dollar ($), and pound sign (#) character.
If (the password) contains special characters, including beginning the password with a number or symbol, then enclose the password with double-quotation marks.
Should not be an actual word.

Likewise, you can’t use the old educational passwords:

The SYS account password cannot be change_on_install (case-insensitive).
The SYSTEM account password cannot be manager (case-insensitive).
The SYSMAN account password cannot be sysman (case-insensitive).
The DBSNMP account password cannot be dbsnmp (case-insensitive).
If you choose to use the same password for all the accounts, then that password cannot be change_on_install, manager, sysman, or dbsnmp (case-insensitive).

Hope this helps, and by the way reading the documentation never hurts too much! 🙂

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact tables. The internal lookup uses the customers name from the contact table to find the membership account information in the member table.

SELECT   m.account_number
,        m.member_type         -- A fk to common_lookup table.
,        m.credit_card_number
,        m.credit_card_type    -- A fk to common_lookup table.
,        c.first_name
,        c.middle_name
,        c.last_name
,        c.contact_type        -- A fk to common_lookup table.
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

It returns the results on the left, while a set of joins against the common_lookup table returns the results on the right (both use the \G in SQL Monitor to display the data vertically).

The member_type, credit_card_type, and contact_type columns in the data set on the left hold foreign key values. They’re copies of values found in the primary key column of the common_lookup table. You have the option of using these values to connect the data through a join or through function calls. A join requires three copies of the common_lookup table and yields the data displayed on the right above. The query to get the meaningful business information from the common_lookup table is:

SELECT   m.account_number
,        cl1.common_lookup_meaning
,        m.credit_card_number
,        cl2.common_lookup_meaning
,        c.first_name
,        c.middle_name
,        c.last_name
,        cl3.common_lookup_meaning
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id INNER JOIN common_lookup cl1
ON       cl1.common_lookup_id = m.member_type INNER JOIN common_lookup cl2
ON       cl2.common_lookup_id = m.credit_card_type INNER JOIN common_lookup cl3
ON       cl3.common_lookup_id = c.contact_type
WHERE    c.first_name = 'Harry'
AND      c.middle_name = 'James'
AND      c.last_name = 'Potter'\G

The data returned from any query is symmetrical, which means all columns return the same number of rows. The results of the preceding query are the business results. Although, they’re not what you’d want to display in a web form that presents the ability to change values, like the member, credit card, or contact types. You need to get that information by using the foreign key as a parameter to a function call, and in this case three function calls. One for each of the foreign keys from the original query results. The result is an asymmetric collection of data would look like the following conceptually with Lily Luna Potter as the customer (note the green row is the symmetrical return set from the preceding query):

The f(x) represents a generic function call where the x substitutes for the foreign key value as a lookup key to the primary key value of the common_lookup table. The function in this case is a query that returns a two column list. One column holds the primary key value of the (common_lookup_id) and the other holds the customer facing description (from the common_lookup_meaning column).

These steps describe the process:

Use the natural key (the user name) to find the data in the contact table.
Use the member_id foreign key column in the contact table to link to the same name column primary key in the member table by joining the two rows.
Use the foreign keys in the new row (combining columns from the contact and member tables) as call parameters to a PHP function that returns all possible foreign key values and their business descriptions in a web form.

The vertical choices displayed above map to OPTION tag elements of an HTML SELECT tag. The blue highlighted value contains an instruction, Select Type, in the display of an HTML OPTION tag, and it holds a null as the value of the VALUE attribute for the OPTION tag. The other displayed rows are the possible values. The green highlighted value is the currently selected value and the yellow highlighted values are alternate possibilities for an end-user to select. The logic for that process is in the PHP get_common_lookup function below.

Having discussed the structure of the data and problem, we’ll present the result in a dynamically constructed web form below. The display form shows a member account with customer information.

You should note that the primary and foreign keys aren’t displayed because they’re irrelevant to the business process. Primary and foreign keys only serve to support relationships when we use surrogate keys as the primary key of a table. Only the meaningful information from the common_lookup table are displayed in the preceding form. Behind the web form, the primary and foreign key values are critical to maintaining anomaly free table data.

Each of the drop downs contains the full selection of possibilities from the common_lookup table, and an end-user could choose a new value by clicking on any of the drop down fields. For example, the following displays the selection of a type of credit card:

The user can click on the drop down, and then navigate from the selection to a new selection. Assuming we don’t change anything, submitting the form should transmit the foreign key column values. The following shows that’s exactly what it does:

As you can see from the screen shot it works easily. Below is the PHP code for a MySQL credentials file and the get_lookup function. The function lets you find an existing value or returns a set of unique values for you to choose from.

You should rename the following credentials.php file as MySQLCredentials.inc for it to work in the file below it.

Assuming you’ve implemented the credentials.php file as the MySQLCredentials.inc file, you can now implement the following file. The get_common_lookup function returns a <SELECT> tag with a list embedded of <OPTION> tags with values; one <OPTION> tag is selected when the foreign key matches a valid primary key value in the common_lookup table; and no <OPTION> tag is selected when the foreign key doesn’t match a a valid primary key value in the common_lookup table. The last possibility means a user must choose a new valid value for the foreign key column when the foreign key column is constrained as a mandatory or not null column.

The code for the web form is a bit more complex, as shown below. It contains three separate calls to the modified get_common_lookup function (on lines 104, 111, and 126). Each call to the get_common_lookup function selects the list of possible values and highlights the value associated with the foreign key value.

Here’s the web form code. You should note that it only returns a single row of data from the query by using a natural key from the contact table.

<html>
<header>
<title>Select Option Sample</title>
<style type="text/css">
  /* Class tag element styles. */
  .box {border:1px solid;padding:0px;width:392px;background-color:silver;}
  .bottomBox {border-left:1px solid;border-right:1px solid;border-bottom:1px solid;padding:5px;width:380px;background-color:silver;}
  .middleBox {border:1px solid;padding:5px;width:380px;background-color:silver;}
  .topBox {border-left:1px solid;border-right:1px solid;border-top:1px solid;padding:5px;width:380px;background-color:silver;}
  .button {margin:5px;background-color:lightblue;font-weight:bold;align:right;}
  .clear {clear:left;}
  .dropDown {min-width:50px;display:block;float:left;text-align:left;color:black;}
  .formDownLabel {width:90px;display:block;float:left;margin:5px;text-align:right;vertical-align:bottom;color:black;}
  .formAcrossLabel {width:80px;display:block;float:left;padding-bottom:0px;margin:5px 5px 0px;text-align:left;vertical-align:bottom;font-style:italic;font-size:90%;color:black;}
  .formInput {min-width:150px;margin:5px;text-align:left;}
  .formShortInput {width:80px;margin:5px;text-align:left;}
  .title1 {margin-left:0px;font-weight:bold;font-style:italic;font-size:125%;color:black;}
  .title2 {margin-left:5px;font-weight:bold;font-style:italic;font-size:105%;color:black;}
</style>
</header>
<body>
<?php
 
  // Include libraries.
  include_once("MySQLCredentials.inc");
  include_once("library.inc");
 
  // Define a HTML page string.
  $out = '';
 
  // Declare input variables.
  $first_name = (isset($_GET['first_name'])) ? $_GET['first_name'] : $first_name = "Harry";
  $middle_name = (isset($_GET['middle_name'])) ? $_GET['middle_name'] : $middle_name = "James";
  $last_name = (isset($_GET['last_name'])) ? $_GET['last_name'] : $last_name = "Potter";
 
  // Declare output variables.
  $member_account_number = null;
  $credit_card_number = null;
 
  // Declare lookup input and output (initialized as nulls to suppress warnings) variables.  
  $member_table = 'member';
  $member_type = 'member_type';
  $member_type_id = null;
  $credit_card_type = 'credit_card_type';
  $credit_card_type_id = null;
  $contact_table = 'contact';
  $contact_type = 'contact_type';
  $contact_type_id = null;
 
  // Assign credentials to connection.
  $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE);
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
    // Initial statement.
    $stmt = $mysqli->stmt_init();
 
    // Declare a static query.
    $sql = "SELECT   m.account_number\n"
         . ",        m.member_type\n"
         . ",        m.credit_card_number\n"
         . ",        m.credit_card_type\n"
         . ",        c.first_name\n"
         . ",        c.middle_name\n"
         . ",        c.last_name\n"
         . ",        c.contact_type\n"
         . "FROM     member m INNER JOIN contact c\n"
         . "ON       m.member_id = c.member_id\n"
         . "WHERE    c.first_name = ?\n"
         . "AND      c.middle_name = ?\n"
         . "AND      c.last_name = ?\n";
 
    // Prepare statement.
    if ($stmt->prepare($sql)) {
      $stmt->bind_param("sss",$first_name,$middle_name,$last_name); } 
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }
    else {   
      // This query only returns one row, and an empty block follows the while logic.
      $stmt->bind_result($member_account_number, $member_type_id, $credit_card_number, $credit_card_type_id, $first_name, $middle_name, $last_name, $contact_type_id);
      while ($stmt->fetch()) {}
    }
  }
 
  // Print the query form.
  $out .= '<form method="post" name="myForm" action="submitItemType.php">';
  $out .= '<label class="title1">Membership Account Information</label><br />';
  $out .= '<div class="box">';
  $out .= '<div class="topBox">';
  $out .= '<label class="formDownLabel">Account #</label><input class="formInput" type="text" value="'.$member_account_number.'" />';
  $out .= '<select name="member_type" size="1" onChange="change(this.form.member_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($member_table, $member_type, $member_type_id);
 
  $out .= '</select><br />';
  $out .= '<label class="formDownLabel">Credit Card #</label><input class="formInput" type="text" value="'.$credit_card_number.'" />';
  $out .= '<select name="credit_card_type" size="1" onChange="change(this.form.credit_card_type)">';
 
  // Get dynamic credit card type lookup string fragment.
  $out .= get_common_lookup($member_table, $credit_card_type, $credit_card_type_id);
 
  // Print the closing HTML table tag.
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="middleBox">';
  $out .= '<label class="formDownLabel">&nbsp;</label>';
  $out .= '<label class="formAcrossLabel">First Name</label><label class="formAcrossLabel">Middle Name</label><label class="formAcrossLabel">Last Name</label><br class="clear" />';
  $out .= '<label class="formDownLabel">Customer</label><input class="formShortInput" type="text" value="'.$first_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$middle_name.'" />';
  $out .= '<input class="formShortInput" type="text" value="'.$last_name.'" /><br />';
  $out .= '<label class="formDownLabel">Type</label>';
  $out .= '<select style="margin:5px" name="contact_type" size="1" onChange="change(this.form.contact_type)">';
 
  // Get dynamic membership type lookup string fragment.
  $out .= get_common_lookup($contact_table, $contact_type, $contact_type_id);
 
  $out .= '</select><br />';
  $out .= '</div>';
  $out .= '<div class="bottomBox">';
  $out .= '<input class="button" style="margin-left:300px" name="submit" type="submit" value="Submit">';
  $out .= '</div>';
  $out .= '</form>';
  $out .= '</body>';
  $out .= '</html>';
 
  print $out;
?>
</body>
</html>

<html> <header> <title>Select Option Sample</title> <style type="text/css"> /* Class tag element styles. */ .box {border:1px solid;padding:0px;width:392px;background-color:silver;} .bottomBox {border-left:1px solid;border-right:1px solid;border-bottom:1px solid;padding:5px;width:380px;background-color:silver;} .middleBox {border:1px solid;padding:5px;width:380px;background-color:silver;} .topBox {border-left:1px solid;border-right:1px solid;border-top:1px solid;padding:5px;width:380px;background-color:silver;} .button {margin:5px;background-color:lightblue;font-weight:bold;align:right;} .clear {clear:left;} .dropDown {min-width:50px;display:block;float:left;text-align:left;color:black;} .formDownLabel {width:90px;display:block;float:left;margin:5px;text-align:right;vertical-align:bottom;color:black;} .formAcrossLabel {width:80px;display:block;float:left;padding-bottom:0px;margin:5px 5px 0px;text-align:left;vertical-align:bottom;font-style:italic;font-size:90%;color:black;} .formInput {min-width:150px;margin:5px;text-align:left;} .formShortInput {width:80px;margin:5px;text-align:left;} .title1 {margin-left:0px;font-weight:bold;font-style:italic;font-size:125%;color:black;} .title2 {margin-left:5px;font-weight:bold;font-style:italic;font-size:105%;color:black;} </style> </header> <body> <?php // Include libraries. include_once("MySQLCredentials.inc"); include_once("library.inc"); // Define a HTML page string. $out = ''; // Declare input variables. $first_name = (isset($_GET['first_name'])) ? $_GET['first_name'] : $first_name = "Harry"; $middle_name = (isset($_GET['middle_name'])) ? $_GET['middle_name'] : $middle_name = "James"; $last_name = (isset($_GET['last_name'])) ? $_GET['last_name'] : $last_name = "Potter"; // Declare output variables. $member_account_number = null; $credit_card_number = null; // Declare lookup input and output (initialized as nulls to suppress warnings) variables. $member_table = 'member'; $member_type = 'member_type'; $member_type_id = null; $credit_card_type = 'credit_card_type'; $credit_card_type_id = null; $contact_table = 'contact'; $contact_type = 'contact_type'; $contact_type_id = null; // Assign credentials to connection. $mysqli = new mysqli(HOSTNAME, USERNAME, PASSWORD, DATABASE); // Check for connection error and print message. if ($mysqli->connect_errno) { print $mysqli->connect_error."<br />"; print "Connection not established ...<br />"; } else { // Initial statement. $stmt = $mysqli->stmt_init(); // Declare a static query. $sql = "SELECT m.account_number\n" . ", m.member_type\n" . ", m.credit_card_number\n" . ", m.credit_card_type\n" . ", c.first_name\n" . ", c.middle_name\n" . ", c.last_name\n" . ", c.contact_type\n" . "FROM member m INNER JOIN contact c\n" . "ON m.member_id = c.member_id\n" . "WHERE c.first_name = ?\n" . "AND c.middle_name = ?\n" . "AND c.last_name = ?\n"; // Prepare statement. if ($stmt->prepare($sql)) { $stmt->bind_param("sss",$first_name,$middle_name,$last_name); } // Attempt query and exit with failure before processing. if (!$stmt->execute()) { // Print failure to resolve query message. print $mysqli->error."<br />"; print "Failed to resolve query ...<br />"; } else { // This query only returns one row, and an empty block follows the while logic. $stmt->bind_result($member_account_number, $member_type_id, $credit_card_number, $credit_card_type_id, $first_name, $middle_name, $last_name, $contact_type_id); while ($stmt->fetch()) {} } } // Print the query form. $out .= '<form method="post" name="myForm" action="submitItemType.php">'; $out .= '<label class="title1">Membership Account Information</label><br />'; $out .= '<div class="box">'; $out .= '<div class="topBox">'; $out .= '<label class="formDownLabel">Account #</label><input class="formInput" type="text" value="'.$member_account_number.'" />'; $out .= '<select name="member_type" size="1" onChange="change(this.form.member_type)">'; // Get dynamic membership type lookup string fragment. $out .= get_common_lookup($member_table, $member_type, $member_type_id); $out .= '</select><br />'; $out .= '<label class="formDownLabel">Credit Card #</label><input class="formInput" type="text" value="'.$credit_card_number.'" />'; $out .= '<select name="credit_card_type" size="1" onChange="change(this.form.credit_card_type)">'; // Get dynamic credit card type lookup string fragment. $out .= get_common_lookup($member_table, $credit_card_type, $credit_card_type_id); // Print the closing HTML table tag. $out .= '</select><br />'; $out .= '</div>'; $out .= '<div class="middleBox">'; $out .= '<label class="formDownLabel"> </label>'; $out .= '<label class="formAcrossLabel">First Name</label><label class="formAcrossLabel">Middle Name</label><label class="formAcrossLabel">Last Name</label><br class="clear" />'; $out .= '<label class="formDownLabel">Customer</label><input class="formShortInput" type="text" value="'.$first_name.'" />'; $out .= '<input class="formShortInput" type="text" value="'.$middle_name.'" />'; $out .= '<input class="formShortInput" type="text" value="'.$last_name.'" /><br />'; $out .= '<label class="formDownLabel">Type</label>'; $out .= '<select style="margin:5px" name="contact_type" size="1" onChange="change(this.form.contact_type)">'; // Get dynamic membership type lookup string fragment. $out .= get_common_lookup($contact_table, $contact_type, $contact_type_id); $out .= '</select><br />'; $out .= '</div>'; $out .= '<div class="bottomBox">'; $out .= '<input class="button" style="margin-left:300px" name="submit" type="submit" value="Submit">'; $out .= '</div>'; $out .= '</form>'; $out .= '</body>'; $out .= '</html>'; print $out; ?> </body> </html>

The submit button simply runs another web page that prints the actual values for the drop down selections. Here’s the code to print that:

<html>
<head>
</head>
<body>
<?php
  // Print the surrogate key values for the common_lookup_id column.
  print "MEMBER_TYPE -> [".$_POST['member_type']."]<br />";
  print "CREDIT_CARD_TYPE -> [".$_POST['credit_card_type']."]<br />";
  print "CONTACT_TYPE -> [".$_POST['contact_type']."]<br />";
?>
</body>
</html>

I hope this helps those who are interested in sorting how to implement a common_lookup table.

MacLochlainns Weblog

Archive for the ‘Oracle 11g’ Category

Excel PowerPivot & DAX

OUT Parameter Trick

PHP PL/SQL Function Return

PHP DB Connection Class

Zend 6 & Timezones

Finding DBMS_TYPES value?

Conflict between identifiers

Object Table Function View

Oracle Passwords

Common Lookup Tables 2

Recent Posts

Things Written About

Pages

Blogroll

Archives