Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
call pipelined function
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
Best Answers
-
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);?>
-
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$
Answers
-
Hi Sim,
did you find this ?
https://oracle-base.com/articles/misc/pipelined-table-functions
Perhaps you tell us more about your requirements.
regardsKay
-
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
-
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);?>
-
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$