MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PHP PL/SQL Function Return

with 5 comments

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:

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

1
2
3
4
5
6
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.

Written by maclochlainn

May 28th, 2013 at 12:36 pm

5 Responses to 'PHP PL/SQL Function Return'

Subscribe to comments with RSS or TrackBack to 'PHP PL/SQL Function Return'.

  1. A side comment: For code that re-executes with different data values and doesn’t re-bind, it’s wise to specify a size for the in-bind too. This lets PHP allocate enough space in the case when a subsequent data value is longer than the first was.

    Chris Jones

    28 May 13 at 7:17 pm

  2. Great catch! I updated the post.

    maclochlainn

    29 May 13 at 10:48 am

  3. Dear Sir,

    I have started to read your blog and in my opinion at least the last two posts (PHP Function Return and PHP connection Class) are great examples of well written/ explained, working, useful samples.

    I am wondering how would you rewrite the “binding area” from the Connection Class (displayed below) in order to be able to handle/return multiple in/out parameters when calling Oracle stored units.

    // 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]);
        }

    This is a nice sample though in my opinion it misses the great generic approach of the Connection Class.

    Thank you very much in any situations,
    Marian

    Marian V.

    8 Jun 13 at 10:44 pm

  4. Marvin, You want me to write an example for a collection output? As an alternative, may I suggest an object table function returned through the TABLE function.

    maclochlainn

    9 Jun 13 at 1:27 am

  5. I question was not about writing an example to return a collection out, which one could implement just using the oci_bind_array_by_name instead of oci_bind_by_name version and add the extra required parameter for collection size. That would be one improvement to the generic class you have written – parse the binding array and use bind_by_name or bind_array_by_name.
    In this example you return the $msg_out which is hard coded and is simple to return it in the execute block. The idea was to make the previous example -connection class- more robust and rewriting the binding block to be able to handle multiple output parameters. Thank you. Marian

    Marian V.

    11 Jun 13 at 9:17 pm

Leave a Reply