MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘olap’ tag

PHP leveraging PL/SQL

with one comment

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.

Written by maclochlainn

December 22nd, 2010 at 11:53 pm

Posted in LAMP,OPAL,Oracle,PHP,pl/sql

Tagged with , , ,