Forum Stats

  • 3,733,838 Users
  • 2,246,829 Discussions
  • 7,856,890 Comments

Discussions

call pipelined function

sgalaxy
sgalaxy Member Posts: 5,687 Bronze Trophy
edited December 2018 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,776 Bronze Crown
    edited December 2018 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,638 Bronze Crown
    edited December 2018

    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,687 Bronze Trophy
    edited December 2018

    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,630 Employee
    edited December 2018

    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,776 Bronze Crown
    edited December 2018 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
Sign In or Register to comment.