Forum Stats

  • 3,757,165 Users
  • 2,251,204 Discussions
  • 7,869,748 Comments

Discussions

call pipelined function

sgalaxy
sgalaxy Member Posts: 5,691 Bronze Trophy
edited Dec 12, 2018 12:22AM in PHP

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

Christopher Jones-Oracle

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Dec 10, 2018 7:48AM Accepted Answer

    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$
    sgalaxyChristopher Jones-Oracle

Answers

  • KayK
    KayK Member Posts: 1,678 Bronze Crown
    edited Dec 5, 2018 7:03AM

    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
    sgalaxy Member Posts: 5,691 Bronze Trophy
    edited Dec 5, 2018 8:28AM

    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
    Christopher Jones-Oracle Member Posts: 1,638 Employee
    edited Dec 9, 2018 8:39PM

    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);?>
    sgalaxy
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Dec 10, 2018 7:48AM Accepted Answer

    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$
    sgalaxyChristopher Jones-Oracle