Calling Oracle Text's PL/SQL Procedures From PHP

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
begin
-- Create some random results
p_p.delete;
for i in 1..5 loop
p_p(i).id := i;
p_p(i).data := 'Random: ' || i ||
(1+ABS(MOD(dbms_random.random,100000)));
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)
as
l_results SuppliedPkg.SuppliedTabType;
begin

-- get results from existing procedure
SuppliedPkg.SuppliedProc(l_results);

-- copy to a type we can pass back to PHP
p_id.delete;
p_data.delete;
for i in 1..l_results.count loop
p_id.extend;
p_id(i) := l_results(i).id;
p_data.extend;
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:

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

Comments:

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

Posted by Lori Levine on May 02, 2006 at 07:07 AM PDT #

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 ?

Posted by Christopher Jones on May 02, 2006 at 07:45 PM PDT #

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
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
   
       
Today