CODEWITHSUNDEEP
phporacle-database
Ina Prisacaru
Ina Prisacaru

Reputation: 11

how to call in php a function from Oracle that returns a large xml

$sql = <<<SQL
declare 
x xmltype;
x1 number;
x2 varchar2(4000);
x4 varchar2(4000);
x5 number;
x6 varchar2(4000);
a long;
begin
:x := ws_circus.get_stations(318461,'ro',0,'autogara',:x5,x6);
end;
SQL;

this is my function

            $statement=oci_parse($connection['link'] , $sql);
            oci_bind_by_name($statement, ':x', $a ,-1);???????????????
            oci_bind_by_name($statement, ':x5', $x5);
            oci_execute($statement);

how to call a function from Oracle that returns a large xml

Upvotes: 1

Views: 191

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10496

Check out the techniques in the XML chapter of The Underground PHP and Oracle Manual. You will need to create an anonymous block of PL/SQL or some other code to convert the XML to a LOB using XMLTYPE.GETCLOBVAL(). Then you can use LOB functions in PHP OCI8.

For example

drop table mytab;
create table mytab (id number, data xmltype);
insert into mytab (id, data) values (1, '<something>mydata</something>');

create or replace function myf(p_id number) return xmltype as
  loc xmltype;
begin
  select data into loc from mytab where id = p_id;
  return loc;
end;
/

create or replace function myf_wrapper(p_id number) return clob as
begin
  return myf(p_id).getclobval();
end;
/

You can use the wrapper function myf_wrapper or an anonymous block like:

<?php

$c = oci_connect('cj', 'cj', 'localhost/orclpdb');

$bd = oci_new_descriptor($c, OCI_D_LOB);
$s = oci_parse($c, "begin :bv := myf_wrapper(1); end;");
oci_bind_by_name($s, ":bv", $bd, -1, OCI_B_CLOB);
oci_execute($s);

echo htmlentities($bd->load()) . "\n";
$bd->free();

$bd = oci_new_descriptor($c, OCI_D_LOB);
$s = oci_parse($c, "begin :bv := myf(1).getclobval(); end;");
oci_bind_by_name($s, ":bv", $bd, -1, OCI_B_CLOB);
oci_execute($s);

echo htmlentities($bd->load()) . "\n";
$bd->free();

?>

Upvotes: 1

Related Questions