• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Execute Oracle procedure with one OUT parameter

#1
Question 
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 NUMBERtitle IN VARCHAR2status_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.
Reply

#2
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
Reply

#3
(05-15-2017, 04: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.
Reply

#4
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
        }
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2017 MyBB Group.