MacLochlainns Weblog

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

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

10
11
12
13

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

Hope that answers the question and helps some folks.

Back in June, I considered replacing my iPhone with an HTC Evo.. I didn’t do it then for two reasons. The battery life of the Evo was too short to make it worthwhile and the cost wasn’t too different. My son Joseph’s passionate sentiments for everything Apple and his membership on the family plan helped dissuade me.

When I dropped my son from the family program, AT&T took all my accumulated minutes away. Truly, it was not a customer centric moment. At the end of the day, my bill went up 15% relative to the cost of half of the family plan.

Last summer, I didn’t have an iPad nor did I foresee buying a first generation one. That was then, but I caved as summer went on and I considered writing an eBook. Notwithstanding my January comments on the iPad, I bought one in late July. It’s features made my iPhone a little jealous and me a little less enticed by the iPhone. The only thing my iPhone could do that it couldn’t was connect to the AT&T network.

Today, I bid my iPhone adieu. I made the move to economize from AT&T’s rates to Sprint. I went with the LG Optimus S. You can read a review if you’re interested about it here. The LG Optimus S is a nice mid-range Android 2.2 smart phone that cost $49.

I suppose that one of my kids will get the iPhone now. The 16 GB unit can serve as an iPod or WiFi device for email and music. These truly have become ubiquitous devices, and we owe Apple a debt for that! By the way, I love the iPad and won’t lament its lack of a camera when the next generation arrives with the new year. 😉

The REST OF THE STORY, as of February 2012, is:

1. I couldn’t live on the Android. It was an awkward interface after the iPhone.
2. I couldn’t return to AT&T, the coverage where I’m at is horrible and their prices are absurd.
3. Sprint sells the iPhone and the coverage and price work.

My return to the iPhone and recommendation for a case is in this blog post.

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:

1. Database Authentication #1 – IdMgmt1.zip
2. Database Authentication #2 – IdMgmt2.zip
3. 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.

We had a discussion today about how you can manage legacy code that you can’t change. For example, how do you access a PL/SQL function in PHP that returns a PL/SQL table of record structures? PL/SQL tables, index-by tables, or associate arrays are one in the same dependent on the release documentation. They’ve been available since Oracle 7.3 (roughly 15+ years).

You’ve a handful of solutions but I think the best is to wrap it in a Pipelined Table function (more on that in this older post). Here’s an example of such a package, wrapper function, and PHP program calling the wrapper function (command-line only PHP sample code).

Let’s say you have the following type of legacy package specification and body:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

-- Create the package specification.
CREATE OR REPLACE PACKAGE lib IS
  /* Define a record structure. */
  TYPE movie_title_record IS RECORD
  ( title    VARCHAR2(60)
  , subtitle VARCHAR2(60));
 
  /* Define an associative array of a package record structure. */
  TYPE movie_title_table IS TABLE OF movie_title_record
  INDEX BY BINARY_INTEGER;
 
  /* Define a prototype of a package function. */
  FUNCTION get_movie
  ( pv_title VARCHAR2 ) RETURN lib.movie_title_table;
 
END lib;
/
 
-- Create the package body.
CREATE OR REPLACE PACKAGE BODY lib IS
 
  /* Implement the package function. */
  FUNCTION get_movie
  ( pv_title VARCHAR2 ) RETURN lib.movie_title_table IS
 
    /* Declare a counter variable. */
    lv_counter INTEGER := 1;
 
    /* Declare an instance of the package nested table and initialize it. */
    lv_table   LIB.MOVIE_TITLE_TABLE := lib.movie_title_table();
 
    /* Define a parameterized cursor to read values from the ITEM table. */  
    CURSOR c ( cv_partial_title VARCHAR2 ) IS
      SELECT   i.item_title
      ,        i.item_subtitle
      FROM     item i
      WHERE    i.item_title LIKE '%'||cv_partial_title||'%';
 
  BEGIN
 
    /* Read the contents of the parameterized cursor. */
    FOR i IN c (pv_title) LOOP
 
      /* Extend space, assign values from the cursor to the record structure
         of the nested table, and increment counter. */
      lv_table.EXTEND;
      lv_table(lv_counter) := i;
      lv_counter := lv_counter + 1;
 
    END LOOP;
 
    /* Return PL/SQL-scope nested table. */
    RETURN lv_table;
 
  END get_movie;
 
END lib;
/

You can wrap the lib package’s get_movie function with a schema-level function provided you convert the older associative array to a PL/SQL-scope nested table. You can do that in two steps. The first requires that you create a wrapper package specification, like the following example. The second step requires you to write a conversion wrapper function, shown later.

The table is dependent on the named record structure from the lib, and as such the packages are now tightly coupled. This is not uncommon when you can’t fix a vendors legacy code set.

1
2
3
4
5
6
7

CREATE OR REPLACE PACKAGE wlib IS
 
  /* Define a nested table of a package record structure. */
  TYPE movie_title_table IS TABLE OF lib.movie_title_record;
 
END wlib;
/

The wrapper function also converts the Oracle Database 7.3 forward data type to an Oracle Database 8.0.3 data type, and then pipes it into a SQL aggregate table. SQL aggregate tables are valid call parameters in the SQL-context. The TABLE function converts the collection of record structures into an inline view or derived table, as you’ll see a little farther along.

You should note that the return type of this function differs from the original package-level get_movie function. The former uses an associative array defined in the lib, while the latter uses a nested table defined in the wlib package.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE OR REPLACE FUNCTION get_movie
( pv_title VARCHAR2 ) RETURN wlib.movie_title_table
PIPELINED IS
 
  /* Define a PL/SQL-scope associative array (Available Oracle 7.3). */
  lv_table_source LIB.MOVIE_TITLE_TABLE;
 
  /* Define a PL/SQL-scope nested table (Available Oracle 8.0.3). */
  lv_table_target WLIB.MOVIE_TITLE_TABLE := wlib.movie_title_table();
 
BEGIN
 
  /* Assign the results of a PL/SQL-scope call to the package function. */
  lv_table_source := lib.get_movie(dbms_assert.simple_sql_name(pv_title));
 
  /* Read the contents of the PL/SQL-scope nested table into a PIPE ROW. */    
  FOR i IN 1..lv_table_source.COUNT LOOP
    lv_table_target.EXTEND;
    lv_table_target(i) := lv_table_source(i);
    PIPE ROW(lv_table_target(i));
  END LOOP;
 
END;
/

You can test this exclusively in SQL*Plus with the following formatting and query. The TABLE function translates the returned array into an inline view or derived table for processing.

-- Format columns for display with SQL*Plus.
COLUMN title    FORMAT A20 HEADING "Movie Title"
COLUMN subtitle FORMAT A20 HEADING "Movie Subtilte"
 
-- Select the contents of the schema-level function in a SQL-context.
SELECT *
FROM   TABLE(get_movie('Star'));

If you’re using my sample code from the Oracle Database 11g PL/SQL Programming book, you should see:

Movie Title          Movie Subtilte
-------------------- --------------------
Star Wars I          Phantom Menace
Star Wars II         Attack of the Clones
Star Wars II         Attack of the Clones
Star Wars III        Revenge of the Sith

The following is a simple command-line PHP program that calls the wrapper function. It calls the wrapper function, which calls the lib.get_movie() function, and it converts the PL/SQL data type from an associative array (Oracle 7.3+ data type) to a nested table (Oracle 8.0.3+ data type). The nested table is defined in the wlib library, which supplements rather than replaces the original lib library.

The last thing that the wrapper function does is transform the associative array result into a nested table before placing it in the pipe (this process is known as a Pipelined Table function). Only nested table and varray data types may be piped into a SQL aggregate table. Then, the external programming language can manage the output as if it were a query.

Here’s the PHP program:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

<?php
  // Connect to the database.
  if ($c = oci_connect("student","student","localhost/orcl"))
  {
    // Parsing a statement into a digestable SQL statement.
    $s = oci_parse($c,"SELECT * FROM TABLE(get_movie('Star'))");
 
  // Execute the parsed statement.
  oci_execute($s,OCI_DEFAULT);
 
  // Store control variable for the number of columns returned by the statement.
  $columns = oci_num_fields($s);
 
  // Find the number of columns, loop through them, and write their column name metadata.
  for ($i = 1; $i <= $columns; $i++) {
 
    // Print the column names, also known as field names.
    print oci_field_name($s,$i);
 
    // Define a variable.
    $line;
 
    /* Check whether a variable is declared and pad it.
     *   The numeric literal is for my convenience because the maximum size
     *   of possible returns is known. In a real situation, you'd use the 
     *   following str_pad() call:
     *
     *   str_pad($temp,oci_field_size($s,$i),"-") */
    if (!isset($line)) 
      $line .= str_pad($temp,15,"-");
    else
      $line .= " ".str_pad($temp,20,"-");
 
    /* One of the values requires a double tab to line up, otherwise this
       practice is unwise. */    
    if ($i < $columns)
      print "\t\t"; }
 
  // Print line return for the header and a line to mimic Oracle SQL*Plus output.
  print "\n";
  print $line .= "\n";
 
  // Process row-by-row data returned when data is returned.
  while (oci_fetch($s))
  {
    // Process column-by-column data returned for each row.
    for ($i = 1; $i <= $columns; $i++) {
      print oci_result($s,$i);
 
    if ($i < $columns) print "\t"; }
 
      // Print line return for the row of data returned.
    print "\n";
  }
 
  // Release resources.
  oci_close($c);
 
  // Explicitly free any resources.
  oci_free_statement($s);
  oci_free_cursor($c);
  }
?>

Assuming you call this callOracle.php, you can call it from the command-line with this syntax:

php callOracle.php

It prints, like it would in SQL*Plus:

TITLE           SUBTITLE
--------------- --------------------
Star Wars I     Phantom Menace
Star Wars II    Attack of the Clones
Star Wars II    Attack of the Clones
Star Wars III   Revenge of the Sith

Hope that helps those working with legacy Oracle code.

Somebody asked if you really have to run the MySQL Configuration Wizard when you want to shut out network connections. The answer is no.

If you want to secure the database server to perform maintenance, you can comment out the port assignment line in the [mysqld] section and add the following in the same section:

# The TCP/IP Port the MySQL Server will listen on
# port=3306

# Instruct it to skip networking and enable named pipes.
skip-networking
enable-named-pipe
 
# Define the Pipe the MySQL Server will use.
socket=mysql

This allows only users on the local system to connect to the database. You can test it by running the following PHP program as a command-line process form the server.

1
2
3
4
5
6
7
8
9
10
11
12

<?php
  // Attempt to connect to your database.
  $c = @mysqli_connect("localhost", "username", "password", "somedb");
  if (!$c) {
    print "Sorry! The connection to the database failed.";
    die();
  }
  else {
    // Initialize a statement in the scope of the connection.
    print "Congrats! You've connected to a MySQL database!";
  }
?>

You call a command-line PHP program like this:

php phpConnect.php

It would fail when you call it from the Apache web server’s htdocs folder because network communication across TCP/IP is closed. Only local sockets are available across the mysql pipe. There’s no magic to the pipe name of mysql but it’s the default pipe name convention.

My data warehousing class goes through traditional design methods, like Kimball. Then, we explore XML databases. We use the Community License of the MarkLogic 4.2 Server, which is the engine of O’Reilly’s Safari online.

You can find the installation, configuration, and client-tool installation/configuration of the MarkLogic Server in the following blog pages:

1. Installation, License, and first-use configuration
2. Configuration of a Forest, HTTP Server, and WebDAV
3. CQ client software

You can find the list of potential function here on the developer’s site for MarkLogic.