call pipelined function

sgalaxy

Hi all,

how a pipelined function can be called and how can we get the results in php?

Is there a simple example of this?

I have searched but i didn't find anything.

Note: i use php 7.2

        oracle db 11g v2.

Thanks all,

Sim

Gaz in Oz

Simple working example:

$ cat pipe.sqldrop type pipe_t;drop type pipe_r;create type pipe_r as object (   id       NUMBER,   rand_str VARCHAR2(30));/create type pipe_t is table of pipe_r;/create or replace function pipe(search_str in VARCHAR2) return pipe_tpipelined as   cursor cur(search_str VARCHAR2) IS      WITH x AS (          SELECT level                                               id,                 dbms_random.STRING('X', 10)||TO_CHAR(level, 'fm09') rand_str          FROM   dual          WHERE  dbms_random.STRING('X', 10)||TO_CHAR(level, 'fm09') LIKE search_str          CONNECT BY level < 11      )      SELECT * FROM x;begin   for rec in cur (search_str) loop      pipe row(pipe_r(rec.id, rec.rand_str));   end loop;   return;end;/$ cat pipe.php<?php$db="xe";$un="gaz";$pw="gaz";$conn = oci_connect($un, $pw, $db);if (!$conn) {   $e = oci_error();   trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);}$stid = oci_parse($conn, "SELECT id, rand_str FROM table(pipe('%0_'))");oci_execute($stid);while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {   foreach ($row as $item) {      echo " " . ($item !== null ? htmlentities($item, ENT_QUOTES) : "Not found\n");   }   echo "\n";}oci_close($conn);?>$ php pipe.php 1 GZY5IOLWZW01 2 IHCFGS75W402 3 DH5LNX33UD03 4 SE7ZZA45MW04 5 5ZVCWY7Q8I05 6 UCT864VV8O06 7 TMIAYI262307 8 MFNDP920I008 9 8C411R89XZ09$

KayK

Hi Sim,

did you find this ?

https://oracle-base.com/articles/misc/pipelined-table-functions

Perhaps you tell us more about your requirements.
regards

Kay

sgalaxy

Hi Kayk,

i'm aware of declaration and use of pipelined functions in oracle db.

My original question is how to call and get the results of a pipelined function in PHP code...

Is there anywhere a simple call and use of a pipelined function in php code?

Thanks,

Sim

Christopher Jones-Oracle

See "Using a PIPELINED Function" on p206 of http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html

<?php$c = oci_connect("hr", "welcome", "localhost/XE");$s = oci_parse($c, "select * from table(myplpkg.mywrapper1())");oci_execute($s);oci_fetch_all($s, $res);var_dump($res);?>

Gaz in Oz

Simple working example:

$ cat pipe.sqldrop type pipe_t;drop type pipe_r;create type pipe_r as object (   id       NUMBER,   rand_str VARCHAR2(30));/create type pipe_t is table of pipe_r;/create or replace function pipe(search_str in VARCHAR2) return pipe_tpipelined as   cursor cur(search_str VARCHAR2) IS      WITH x AS (          SELECT level                                               id,                 dbms_random.STRING('X', 10)||TO_CHAR(level, 'fm09') rand_str          FROM   dual          WHERE  dbms_random.STRING('X', 10)||TO_CHAR(level, 'fm09') LIKE search_str          CONNECT BY level < 11      )      SELECT * FROM x;begin   for rec in cur (search_str) loop      pipe row(pipe_r(rec.id, rec.rand_str));   end loop;   return;end;/$ cat pipe.php<?php$db="xe";$un="gaz";$pw="gaz";$conn = oci_connect($un, $pw, $db);if (!$conn) {   $e = oci_error();   trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);}$stid = oci_parse($conn, "SELECT id, rand_str FROM table(pipe('%0_'))");oci_execute($stid);while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {   foreach ($row as $item) {      echo " " . ($item !== null ? htmlentities($item, ENT_QUOTES) : "Not found\n");   }   echo "\n";}oci_close($conn);?>$ php pipe.php 1 GZY5IOLWZW01 2 IHCFGS75W402 3 DH5LNX33UD03 4 SE7ZZA45MW04 5 5ZVCWY7Q8I05 6 UCT864VV8O06 7 TMIAYI262307 8 MFNDP920I008 9 8C411R89XZ09$