OPAL | MacLochlainns Weblog

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.

Six years ago, I wrote Oracle Database 10g Express Edition PHP Web Programming for the release of the express edition. It was a lot of fun to write because I enjoy the PHP programming language, but unfortunately sales didn’t measure up too well. That’s probably because the population of PHP developers working with Oracle was small.

Today it seems there are more PHP developers working with Oracle 11g. While the population of PHP community for Oracle 11g is still smaller than for MySQL, it continues to grow year-over-year.

The FREE Underground PHP and Oracle Manual can help those converting PHP to run in the Oracle Call Interface, which is the replacement for MySQLi Interface. Chris Jones (an Oracle Open Source Product Manager) and Alison Holloway (an Oracle Senior Product Manager) write and maintain this book. It’s a great place to start if you’re migrating to Oracle Database 11g from MySQL.

A student encountered a connection problem with a PHP application that failed to resolve to the database. The steps to validate this are to check the PHP credentials, which are in this older post. Once you’ve done that, you should do:

Check the tnsnames.ora file contents, they should have a hostname value not an IP address. When you’ve not set your machine hostname in Microsoft Windows host file (mine in the example is: McLaughlin7x64), Oracle opts for the IP address.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = McLaughlin7x64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xe)
    )
  )

Check if the Oracle TNS (Transparent Network Substrate) validates with this command:

tnsping xe

If the prior step fails, check to see if you’re listener is running and that it’s configuration file looks more or less like this sample. If it doesn’t, you should edit this listener.ora file and start the listener process through the services dialog.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = McLaughlin7x64)(PORT = 1521))
    )
  )

You should then be able to connect like this:

sqlplus username/password@xe

Hope this helps a few people.

Somebody wanted another example of how to leverage a true/false condition from a PL/SQL stored function in PHP. The first key is that you write the function as if you were using it in SQL not PL/SQL. That means you return a NUMBER data type not a PL/SQL-only BOOLEAN data type.

Here’s the schema-level PL/SQL function:

CREATE OR REPLACE FUNCTION like_boolean
( a NUMBER, b NUMBER ) RETURN NUMBER IS
 
  /* Declare default false return value. */
  lv_return_value NUMBER := 0;
 
BEGIN
 
  /* Compare numbers and return true for a match. */
  IF a = b THEN
    lv_return_value := 1;
  END IF;
 
  /* Return value. */
  RETURN lv_return_value;
 
END;
/

Here’s the PHP that leverages the PL/SQL in an if-statement on line #24:

<?php
  // Capture local variables when provided.
  $thingOne = (isset($_GET['thingOne'])) ? $_GET['thingOne'] : 1;
  $thingTwo = (isset($_GET['thingTwo'])) ? $_GET['thingTwo'] : 1;
 
  // Open a connection.
  if(!$c = oci_connect("student","student","localhost/xe"))
  {
    die;
  }
  else
  {
 
    // Parse a statement.  
    $s = oci_parse($c,"BEGIN
                         :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                       END;");
 
    // Bind input and output values to the statement.
    oci_bind_by_name($s,":returnValue",$returnValue);
    oci_bind_by_name($s,":thingOne",$thingOne);
    oci_bind_by_name($s,":thingTwo",$thingTwo);
 
    // Execute the statement.
    if (@oci_execute($s))
    {
      // Print lead in string.
      print "[".$thingOne."] and [".$thingTwo."] ";  
      if ($returnValue)
        print "are equal.<br />";
      else
        print "aren't equal.<br />";
    }
 
    // Clean up resources.
    oci_close($c);
  }
?>

If you run into a parsing error, which is infrequent now. You can wrap the multiple row PL/SQL anonymous block call with this function. It strips tabs and line returns. Alternatively, you can put all the lines of PL/SQL on a single line.

  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];
 
    // Return pre-parsed SQL statement.
    return $out;
  }

If you run into a parsing problem on Oracle XE 10g, you can wrap the PL/SQL call like the following. Alternatively, you can place the entire anonymous PL/SQL block on a single line without embedded tabs or return keys..

  $s = oci_parse($c,strip_special_characters(
                    "BEGIN
                       :returnValue := LIKE_BOOLEAN(:thingOne,:thingTwo);
                     END;"));

Hope that answers the question and helps some folks.

A few years ago I wrote a couple articles showing how to use PHP to connect to an Oracle Database 10g Express Edition instance. They’re still there on Oracle’s Technical Network but the source files are missing. It appears that Oracle may have migrated the articles to a new server but failed to migrate the source files.

Don’t forget that you’ll need to Install the Zend Community Server. Then, you need to create an IDMGMT1, IDMGMT2, and IDMGMT3 users and run the create_identity_db2.sql or create_identity_db3.sql seeding script for both database validation models. You’ll find links to the original articles, the source code in zip files. The newer version with CSS is the IDMGMT3 code.

The CSS sign-on form looks like this:

The portal page looks like this:

The add new user page looks like this:

The source files are as follows:

Database Authentication #1 – IdMgmt1.zip
Database Authentication #2 – IdMgmt2.zip
Database Authentication #3 – IdMgmt3.zip includes the CSS but works with the same write-up as Idmgmt2.

Hope this helps those who wanted the files.

I finally got around to writing that PHP Tutorial. It’s a bit large and takes about 10 seconds to load or longer depending on your connection and machine. It covers the basics from writing your first page to loops. It was too large for a blog post, so it’s a blog page. Click on the link if you’d like to check it out.

I plan others on functions, objects, and files. Then, I’ll get to tutorials against databases.

Naturally, suggestions are always welcome.

A Gauss posted a question on my from last year’s Utah Oracle User’s Group Training Days presentation. If I understood his question correctly, this should help him work with his legacy code. Honestly, as I wrote the example something Bryn Llewellyn said kept banging around in my head, “Just because we can, doesn’t mean we should.” He was speaking of writing poorly engineered code.

Sometimes, we don’t get the opportunity to re-factor existing code. That leaves us with writing wrappers that aren’t pretty or effective. A realization and preface to showing everyone how to accomplish these tasks, and perhaps a watch out warning if you choose this path. I suspect that there may be a better way but I don’t know their code tree.

Here’s the question, as I understand it. They’ve got a library function in PL/SQL that returns a system reference cursor and is principally consumed by an external Java program. This type of architecture is more or less an Adapter OOAD pattern that I wrote about here, over a year and a half ago. The question comes to how to you wrap this approach and make it work in PL/SQL natively too.

The answer depends on some earlier posts because I don’t have a great deal of time to write new examples. It uses a COMMON_LOOKUP table, which is more or less a bunch of small tables grouped into a big table for use in user interaction forms. That way the values don’t get lost in a large code base and are always consistently maintained. These types of tables exist in all major ERP and CRM applications.

The base code for the example is found here, where I discussed how you can effectively use object tables – collections of user-defined object types (Oracle 9iR2 forward if I remember correctly). You can grab the full code at the bottom of the page by clicking the Code Script widget to unfold the code. That code also depends on the Oracle Database 11g PL/SQL Programming downloadable code, which you can download by clicking the link to the zip file location.

Here are the steps to wrap a function that returns a PL/SQL reference cursor so that it can also return a PL/SQL associative array.

Create a package specification to hold all the components that are required to manage the process. Assuming that they may have anchored the system reference cursor to something other than a table like a shared cursor, which is a cumbersome implementation design. (I actually chose to exclude this from the book because it’s a stretch as a good coding practice. At least, it is from my perspective. Also, I couldn’t find an example in the Oracle documentation, which led me to believe they didn’t think it’s a great idea either or I could have glossed over it.) You should note that the PL/SQL RECORD, Associative Array (collection), and the REF CURSOR are defined in this package specification.

-- Create a package to hold the PL/SQL record structure.
CREATE OR REPLACE PACKAGE example IS
 
  -- Force cursors to be read as if empty every time.
  PRAGMA SERIALLY_REUSABLE;
 
  -- Package-level record structure that mimics SQL object type.
  TYPE common_lookup_record IS RECORD
  ( common_lookup_id      NUMBER
  , common_lookup_type    VARCHAR2(30)
  , common_lookup_meaning VARCHAR2(255));
 
  -- Package-level collection that mimics SQL object table.
  TYPE common_lookup_record_table IS TABLE OF common_lookup_record
  INDEX BY PLS_INTEGER;
 
  -- Cursor structure to support a strongly-typed reference cursor.
  CURSOR c IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup;
 
  -- Package-level strongly-typed system reference cursor.
  TYPE cursor_lookup IS REF CURSOR RETURN c%ROWTYPE;
 
END;
/

Write a function to return a strongly typed system reference cursor that’s anchored to a cursor defined in the package. This is fairly straightforward when the package specification is done right. You should notice right away that anchoring the original cursor in the package was a horrible practice because you must repeat it all again in the function. In my opinion, you shouldn’t anchor any system reference cursor explicitly to anything other than a table. The cursor could have used the generic weak cursor data type – SYS_REFCURSOR. Doing so, saves all the extra lines required by a potential shared cursor.

CREATE OR REPLACE FUNCTION get_common_lookup_cursor
( TABLE_NAME VARCHAR2, column_name VARCHAR2)
RETURN example.cursor_lookup IS
 
  -- Define a local variable of a strongly-typed reference cursor.
  lv_cursor EXAMPLE.CURSOR_LOOKUP;
 
BEGIN
 
  -- Open the cursor from a static cursor
  OPEN lv_cursor FOR
    SELECT common_lookup_id
    ,      common_lookup_type
    ,      common_lookup_meaning
    FROM   common_lookup
    WHERE  common_lookup_table = TABLE_NAME
    AND    common_lookup_column = column_name;
 
  -- Return the cursor handle.
  RETURN lv_cursor;
 
END;
/

Write a wrapper function that takes the reference cursor as a formal parameter and returns an Associative Array. You should note that this can’t be called from a SQL context. You must only use it in a PL/SQL context because system reference cursors are PL/SQL only data types.

CREATE OR REPLACE FUNCTION convert_common_lookup_cursor
( pv_cursor EXAMPLE.CURSOR_LOOKUP) 
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Local PL/SQL-only variable.
  out_record  EXAMPLE.COMMON_LOOKUP_RECORD;
  out_table   EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  LOOP
    FETCH pv_cursor INTO out_record;
    EXIT WHEN pv_cursor%NOTFOUND;
 
    -- Assign it one row at a time to an associative array.
    out_table(counter) := out_record;
 
    -- Increment the counter.
    counter := counter + 1;
 
  END LOOP;
 
  -- Return the record collection.
  RETURN out_table;
 
END;
/

You can test the program in an anonymous block, like the one below. It defines a local Associative Array variable and then assigns the system reference cursor through the wrapper.

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Define a local associative array.
  process_table  EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
  -- Print title block.
  dbms_output.put_line('Converting a SYS_REFCURSOR to TABLE');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Run the dynamic variables through the cursor generating function and then convert it.
  process_table := convert_common_lookup_cursor(get_common_lookup_cursor('ITEM','ITEM_TYPE'));
 
  -- Read the content of the Associative array.  
  FOR i IN 1..process_table.COUNT LOOP
    dbms_output.put('['||process_table(i).common_lookup_id||']');
    dbms_output.put('['||process_table(i).common_lookup_type||']');
    dbms_output.put_line('['||process_table(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

I hope this answers Gauss’s question. While writing it, I could envision another question that might pop-up. How do you convert an object table type to a PL/SQL context. It was an omission not to include it in that original post on object table types. Here’s how you wrap an object table type into a PL/SQL scope collection.

You might have guessed. It’s done with another wrapper function. At least this is the easiest way to convert the SQL data type to a PL/SQL data type that I see. If you’ve another approach, a better way, let us know.

CREATE OR REPLACE FUNCTION get_common_lookup_record_table
( TABLE_NAME  VARCHAR2
, column_name VARCHAR2 )
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   *
    FROM     TABLE(get_common_lookup_object_table(UPPER(table_name_in),UPPER(table_column_name_in)));
 
  -- A local PL/SQL-only collection variable.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  FOR i IN c(TABLE_NAME, column_name) LOOP
    list(counter) := i;
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_record_table;
/

You can then test this in an anonymous block, like so:

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Declare a local PL/SQL-only collection and assign the value from the function call.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
  BEGIN
 
  -- Print title block.
  dbms_output.put_line('Converting a SQL Collection to a PL/SQL Collection');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Assign wrapped SQL collection to a PL/SQL-only collection.
  list := get_common_lookup_record_table('ITEM','ITEM_TYPE');
 
  -- Call the record wrapper function.
  FOR i IN 1..list.COUNT LOOP
    dbms_output.put('['||list(i).common_lookup_id||']');
    dbms_output.put('['||list(i).common_lookup_type||']');
    dbms_output.put_line('['||list(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

As always, I hope this helps somebody without paying a fee for content. 😉

A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.

Part of the idea behind these examples is to show the basic structure while mimicking the \G option of MySQL. The \G (Go) displays results as a list of column names and values by row. Ever since I discovered that in MySQL, I’ve hoped Oracle would incorporate something similar in their product. While discussing my wish list, I’d also like Oracle to make the FROM dual optional (like MySQL does) when selecting a string or numeric literal. You can find an implementation here, that leverages an example from Tom Kyte.

You can click any of the titles to view the code, which isn’t needed when you don’t have JavaScript enabled or the RSS expands them for you.

Simple loop with local variables ↓

This simple loop example uses a static cursor and local variables that map to each column returned by the cursor. It uses the %TYPE to anchor local variables to the data dictionary.

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare local variables that are anchored to column data types.
  lv_title     item.item_title%TYPE;
  lv_subtitle  item.item_subtitle%TYPE;
  lv_rating    item.item_rating%TYPE;
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the three local variables.
    FETCH c
    INTO  lv_title
    ,     lv_subtitle
    ,     lv_rating;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variables on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||lv_title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||lv_subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||lv_rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

Simple loop with a local record structure variable ↓

This simple loop example uses a static cursor, a local record structure data type, and a local variable of the local record structure data type. The local record structure maps to the columns returned by the cursor. It uses explicit data types that match those of the table. You could also use the %TYPE to anchor the elements of the structure in the local data type, like the prior example. With explicit data types, you must modify the program when the definition of the table changes otherwise your program may fail at runtime.

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a local record data type, with explicit data types (you could use %TYPE here too).
  TYPE title_type IS RECORD
  ( title     VARCHAR2(60)
  , subtitle  VARCHAR2(60)
  , rating    VARCHAR2(8));
 
  -- Declare a local variable of the local record structure data type.
  item_record TITLE_TYPE;
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the local record structure variable.
    FETCH c
    INTO  item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

Simple loop with a local cursor structure variable ↓

This simple loop example uses a static cursor, a local variable that inherits its record structure from the local cursor. It does so by using the %ROWTYPE against the cursor. Often the %ROWTYPE is only applied when you return a structure that maps to the complete table definition. Sometimes I think using cursor_name%ROWTYPE is the only real purpose for shared cursors but I know that’s not really true.

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block.
  LOOP
 
    -- Fetch a row of the cursor and assign it to the local record structure variable.
    FETCH c
    INTO  item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

For loop with an implicit record structure variable ↓

This FOR loop example uses a static cursor. When a FOR loop uses a cursor it becomes a cursor FOR loop, and the iterator i becomes an implicit cursor record structure. You should note that this is a very compact program because a cursor FOR loop manages opening and closing the cursor, and handling the loop exit implicitly. Many programmers default to this approach whenever it fits because it is simple and easy to implement.

SET SERVEROUTPUT ON SIZE 1000000
 
DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
BEGIN
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start a cursor FOR loop block.
  FOR i IN c LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||i.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||i.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
END;
/

For loop without a declaration block ↓

This FOR loop example uses a static cursor. Like the prior example, this FOR loop uses a cursor but it is defined inside the actual FOR loop structure. That approach eliminates the need for the declaration block. It’s a nice feature that some may call a trick. As a rule, you should really avoid this style because formally defining your cursor is a good practice and improves code maintainability. Naturally, this is probably the most compact program because everything is managed implicitly including the cursor assignment to the loop structure.

SET SERVEROUTPUT ON SIZE 1000000
 
BEGIN
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start a cursor FOR loop block with the static cursor in the definition.
  FOR i IN (SELECT   i.item_title AS title
	        ,        i.item_subtitle AS subtitle
	        ,        i.item_rating AS rating
	        FROM     item i) LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||i.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||i.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||i.rating||']');
 
    -- Print an ending line.
   dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
END;
/

WHILE loop with a cursor guard on entry statement ↓

This WHILE loop example uses a static cursor, like the prior examples. Unlike the prior example, the WHILE loop is a guard on entry loop. The previous loops were guard on exit loops. This has much the same structure as the simple loop with a cursor record structure variable but differs on two key points.

Point one is that you must have two FETCH statements because the guard on entry condition checks whether any records are found in the cursor. The first FETCH statement checks whether at least a one row is returned. When true or false, it initializes the cursor attributes, like %FOUND. The second FETCH statement handles the second row returned to last row returned from the cursor.

Point two is that you don’t have an EXIT WHEN cursor_name%NOTFOUND inside the loop because the guard condition stops the loop when it fails to find at least one record.

DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Fetch the first record to put into context the cursor attributes, like %FOUND.
  FETCH c INTO item_record;
 
  -- Start the simple loop block with a guard on entry condition.
  WHILE (c%FOUND) LOOP
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
    dbms_output.put_line('----------------------------------------------------------------------');
 
    -- Fetch the second and subsequent rows of the cursor and assign it to a local variables.
    FETCH c
    INTO  item_record;
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

The WHILE loop as presented is complex because of the pre-loop FETCH statement, and the internal FETCH statement. It’s may appear better to convert it to a pseudo-infinite loop. You do that by setting the condition in a WHILE loop to a TRUE constant, like this example:

DECLARE
 
  -- Declare a static cursor.
  CURSOR c IS
    SELECT   i.item_title AS title
    ,        i.item_subtitle AS subtitle
    ,        i.item_rating AS rating
    FROM     item i;
 
  -- Declare a local variable of that inherits its structure from a local cursor.
  item_record c%ROWTYPE;
 
BEGIN
 
  -- Open the cursor.
  OPEN c;
 
  -- Print a starting line.
  dbms_output.put_line('----------------------------------------------------------------------');
 
  -- Start the simple loop block with a guard on entry condition.
  WHILE (TRUE) LOOP
 
    -- Fetch the record set into a user-defined variable.
    FETCH c INTO item_record;
 
    -- Exit when there aren't any more records in the cursor, without this you loop infinitely.
    EXIT WHEN c%NOTFOUND;
 
    -- Print the local variable elements on a single line each to mimic MySQL \G equivalent.
    dbms_output.put_line('ITEM.ITEM_TITLE    ['||item_record.title||']');
    dbms_output.put_line('ITEM.ITEM_SUBTITLE ['||item_record.subtitle||']');
    dbms_output.put_line('ITEM.ITEM_RATING   ['||item_record.rating||']');
 
    -- Print an ending line.
    dbms_output.put_line('----------------------------------------------------------------------');
 
  END LOOP;
 
  -- Close the cursor and release the resources.
  CLOSE c;
END;
/

The preceding example behaves much like a simple loop, and you have to ask what is the benefit of WHILE (TRUE) LOOP over LOOP. Generally, it appears that the WHILE loop syntax is slighly longer to type.

I’m sure this will help my students and hope it helps somebody else.

MacLochlainns Weblog

Archive for the ‘OPAL’ Category

PHP PL/SQL Function Return

PHP DB Connection Class

Zend 6 & Timezones

Free Oracle PHP Book

Troubleshooting ORA-12514

PHP leveraging PL/SQL

PHP Database Authentication

PHP Tutorial Available

Wrap a cursor function

Sample PL/SQL Cursor Loops

Recent Posts

Things Written About

Pages

Blogroll

Archives