The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

  • php
    April 21, 2006

Calling Oracle Text's PL/SQL Procedures From PHP

Christopher Jones
Senior Principal Product Manager

A question was asked in the OTN PHP Forum  about how to call Oracle
Text's CTX_THES package
  from PHP.   Oracle Text is described:

Oracle Text uses standard SQL to index, search, and analyze text and documents stored in the Oracle database, in files, and on the web. Oracle Text can perform linguistic analysis on documents, as well as search text using a variety of strategies including keyword searching, context queries, Boolean operations, pattern matching, mixed thematic queries, HTML/XML section searching, and so on. It can render search results in various formats including unformatted text, HTML with term highlighting, and original document format. Oracle Text supports multiple languages and uses advanced relevance-ranking technology to improve search quality. Oracle Text also offers advanced features like classification, clustering, and support for information visualization metaphors.

To make it simpler to see what to do, let's set up an example package with a similar interface to CTX_THES.  This example ctx.sql just returns random data:

-- Package "SuppliedPkg" simulates Oracle Text's CTX_THES.
It has a procedure that returns a PL/SQL type.

create or replace package SuppliedPkg as
type SuppliedRec is record (
id number,
data varchar2(100)
type SuppliedTabType is table of SuppliedRec
index by binary_integer;
procedure SuppliedProc(p_p in out nocopy SuppliedTabType);
end SuppliedPkg;

create or replace package body SuppliedPkg as
procedure SuppliedProc(p_p in out nocopy SuppliedTabType) is
-- Create some random results
for i in 1..5 loop
p_p(i).id := i;
p_p(i).data := 'Random: ' || i ||
end loop;
end SuppliedProc;
end SuppliedPkg;

Run it in SQL*Plus like:

sqlplus hr/hr@//localhost/XE @ctx.sql

This is the "fixed" part of the problem, representing the pre-supplied functionality.  It seems impossible to call SuppliedProc() and return its data to PHP.

Since you can't change SuppliedProc(), you can create a helper function in PL/SQL to convert the PL/SQL type SuppliedTabType to a pair of SQL types. Myproc.sql is:

-- Create a wrapper procedure that calls the pre-supplied
-- SuppliedProc() and converts its output to SQL types.

create or replace type MyIdRec as varray(100) of number;
create or replace type MyDataRec as varray(100) of varchar2(100);

create or replace procedure MyProc
(p_id IN OUT MyIdRec, p_data IN OUT MyDataRec)
l_results SuppliedPkg.SuppliedTabType;

-- get results from existing procedure

-- copy to a type we can pass back to PHP
for i in 1..l_results.count loop
p_id(i) := l_results(i).id;
p_data(i) := l_results(i).data;
end loop;

end MyProc;

Use SQL*Plus to run this script too.  Now you can call MyProc() in ctx.php:


$conn = oci_connect("hr", "hr", "//localhost/XE");

$stmt = 'begin MyProc(:res_id, :res_data); end;';
$stid = oci_parse($conn, $stmt);
$res_id = oci_new_collection($conn, 'MYIDREC');
$res_data = oci_new_collection($conn, 'MYDATAREC');
oci_bind_by_name($stid, ':res_id', $res_id, -1, OCI_B_NTY);
oci_bind_by_name($stid, ':res_data', $res_data, -1, OCI_B_NTY);
oci_execute($stid, OCI_DEFAULT);

for ($i = 0; $i < $res_id->size(); $i++) {
$id = $res_id->getElem($i);
$data = $res_data->getElem($i);
echo "Id: $id, Data: $data<br>";

This allocates two collections and binds them as the parameters to MyProc().  After MyProc() has been called, the collection method getElem() is used to access each value returned.

The limitation of this example is that you have to set an upper bound on the MyIdRec and MyDataRec VARRAY sizes.  Simply replace both uses of VARRAY2(100) with TABLE to overcome this.

Join the discussion

Comments ( 2 )
  • Lori Levine Tuesday, May 2, 2006
    Hi Chris, I used your example as a workaround to display the contents of a sys.dbms_sql.varchar2s return variable. However, when it comes time to execute, I get a blank page. When I comment out the oci_execute command, all other commands (connect, parse, create collection, bind) work. Have you encountered this behavior before? TIA, Lori
  • Christopher Jones Wednesday, May 3, 2006
    A blank page could be anything. Can you post an example of what you are trying to do to http://www.oracle.com/technology/forums/php.html ?
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.