Execute Oracle procedure with one OUT parameter

Execute Oracle procedure with one OUT parameter - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: CodeIgniter 3.x (https://forum.codeigniter.com/forumdisplay.php?fid=17)
+--- Thread: Execute Oracle procedure with one OUT parameter (/showthread.php?tid=68045)

Execute Oracle procedure with one OUT parameter - zashishz - 05-15-2017

I am able to execute stored procedure in CI with IN params.
But Having problems using a procedure with one or more OUT params in oracle.

Example:

CREATE OR REPLACE PROCEDURE Sample (id IN NUMBER, title IN VARCHAR2, status_code OUT VARCHAR2 )
AS
   lv_status_code VARCHAR2 (500);
BEGIN
    lv_status_code :=
      'SUCCESS'
      || '|~|'
      || id
    || '|~|'
      || title;
   status_code := lv_status_code;
END;

Kindly let me know how i can execute this from codeigniter and to read output value.

RE: Execute Oracle procedure with one OUT parameter - donpwinston - 05-15-2017

try

$this->db->call_function('Sample', $id, $title, &$status_code);

You might have to rewrite your procedure into a function and return $status_code instead of using it as a param.

RE: Execute Oracle procedure with one OUT parameter - zashishz - 05-17-2017

(05-15-2017, 03:42 PM)donpwinston Wrote: try

$this->db->call_function('Sample', $id, $title, &$status_code);

You might have to rewrite your procedure into a function and return $status_code instead of using it as a param.

Hey donpwinston,

Thanks for reply i got below error while using &$status_code

Code:
Fatal error: Call-time pass-by-reference has been removed

Hence, i created function and returned the value.

$this->db->query("SELECT Sample($id, $title) FROM dual");

This works fine. Smile

But still executing procedure with OUT param is a mystery for me.

RE: Execute Oracle procedure with one OUT parameter - nortonex - 09-21-2017

Just use regular OCI functions:

Code:
        $var1 = 1;

        $var2 = 1;

        $var3 = 0;

        $stmt = oci_parse($this->db->conn_id,

        "begin 

            schema.procedure(:in1,:in2,:out1);

        end;"); 

        oci_bind_by_name($stmt, ':in1', $var1,-1,SQLT_INT);

        oci_bind_by_name($stmt, ':in2', $var2,-1,SQLT_INT);

        oci_bind_by_name($stmt, ':out1', $var3,99,SQLT_INT); // here you should enter max possible length of out parameter

        $r = oci_execute($stmt);                

        if ($r==true) {

            //use $var3 as return value of OUT parameter

        }