Execute Oracle procedure with one OUT parameter

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.

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.

Simpler is always better

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. 

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

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
        }