EllisLab text mark
Advanced Search
     
Using CI with Oracle Stored Procedures
Posted: 22 July 2009 11:05 AM
Joined: 2009-07-22
5 posts

I have been trying to get CI+Oracle Stored Procedures working.

The issue i currently have is the corruption of input data during oci binding:

Below is a snippet from:
MODEL

function testProc($dat)
{
        $params 
= array(
                        array(
'name'=>':p_num''value'=>$dat'type'=>SQLT_NUM'length'=>-1),
                        array(
'name'=>':p_out''value'=>&$res2'type'=>OCI_B_INT'length'=>-1)
                        );
                               
        
$this->db->stored_procedure('SPSB_PKG','TEST',$params);


ORACLE STORED PROCEDURE

PROCEDURE test(p_num NUMBER,
                 
p_out OUT NUMBER
  AS
  
BEGIN
    p_out 
:= p_num;
  
END test

 

When i call this function from the Controller passing in a value(eg. 4), when i check the returned value in variable $res2 i get varying values like 220189512,220189160,-0.008212.

Any help with how to bind oracle datatypes to php will be appreciated.

Thanks.

 
Posted: 23 July 2009 12:54 AM   [ # 1 ]   [ Rating: 0 ]
Joined: 2009-07-22
5 posts

I finally figured out the problem. I had to modify the oci8_driver.php file.

Original form:

function _execute($sql)
    
{
        
// oracle must parse the query before it is run. All of the actions with
        // the query are based on the statement id returned by ociparse
        
$this->stmt_id FALSE;
        
$this->_set_stmt_id($sql);
        
ocisetprefetch($this->stmt_id1000);
        return @
ociexecute($this->stmt_id$this->_commit);
    
}

function stored_procedure($package$procedure$params)
    
{
        
if ($package == '' OR $procedure == '' OR ! is_array($params))
        
{
            
if ($this->db_debug)
            
{
                log_message
('error''Invalid query: '.$package.'.'.$procedure);
                return 
$this->display_error('db_invalid_query');
            
}
            
return FALSE;
        
}
        
        
// build the query string
        
$sql "begin $package.$procedure(";

        
$have_cursor FALSE;
        foreach(
$params as $param)
        
{
            $sql 
.= $param['name'",";
            
            if (
array_key_exists('type'$param) && ($param['type'== OCI_B_CURSOR))
            
{
                $have_cursor 
TRUE;
            
}
        }
        $sql 
trim($sql",") . "); end;";
                
        
$this->stmt_id FALSE;
        
$this->_set_stmt_id($sql);
        
$this->_bind_params($params);
        
$this->query($sqlFALSE$have_cursor);
    

The stored_procedure() function parses the stored procedure sql [_set_stmt_id($sql)] and binds variables using this ociparse object [_bind_params($params)].

A subsequent call to query($sql, FALSE, $have_cursor) internally calls _execute($sql) which nullifies the stmt_id and attempts to reparse the sql string without the necessary bind variables.

Based on other modifications to these files recommended in other posts on this forum, the modified files should look like this

oci_driver.php

//bind variable declared for class
var $bind FALSE;

    function 
_execute($sql)
    
{
        
// oracle must parse the query before it is run. All of the actions with
        // the query are based on the statement id returned by ociparse
        
$this->stmt_id FALSE;
        
$this->_set_stmt_id($sql);
        
//begin modification
        
if ( ! $this->stmt_id)
        
{
            $e 
oci_error($this->stmt_id);
            
log_message('error'$e['message']);
            return 
FALSE;
        
}

        
if($this->binds !== FALSE)
        
{
            $this
->_bind_params($this->binds);
        
}
        
        ocisetprefetch
($this->stmt_id1000);
        
//return @ociexecute($this->stmt_id, $this->_commit);
        
$exec_worked ociexecute($this->stmt_id$this->_commit);
        if (
$exec_worked === FALSE// if ociexecute failed, grab the oracle error message and log it
            
$e oci_error($this->stmt_id);
            
log_message('error'$e['message']);
        
}
        
return $exec_worked;
        
//end modification
    
}

function _set_stmt_id($sql)
    
{
        
//if ( ! is_resource($this->stmt_id))
        //{
        //    $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
        //}
        
$this->stmt_id ociparse($this->conn_id$this->_prep_query($sql));
    
}

function stored_procedure($package$procedure$params)
    
{
        
if ($package == '' OR $procedure == '' OR ! is_array($params))
        
{
            
if ($this->db_debug)
            
{
                log_message
('error''Invalid query: '.$package.'.'.$procedure);
                return 
$this->display_error('db_invalid_query');
            
}
            
return FALSE;
        
}
        
        
// build the query string
        
$sql "begin $package.$procedure(";

        
$have_cursor FALSE;
        foreach(
$params as $param)
        
{
            $sql 
.= $param['name'",";
            
            if (
array_key_exists('type'$param) && ($param['type'== OCI_B_CURSOR))
            
{
                $have_cursor 
TRUE;
            
}
        }
        $sql 
trim($sql",") . "); end;";        
        
$this->stmt_id FALSE;
        
        
//begin modification
        
$this->binds FALSE;
        
//end modification
        
        
$this->_set_stmt_id($sql);
        
//$this->_bind_params($params);
        
$this->query($sql$params$have_cursor);
    

The DB_driver.php file is also modified thus:

function query($sql$binds FALSE$return_object TRUE)
    
{
        
if ($sql == '')
        
{
            
if ($this->db_debug)
            
{
                log_message
('error''Invalid query: '.$sql);
                return 
$this->display_error('db_invalid_query');
            
}
            
return FALSE;
        
}

        
...
        ...
        
// Compile binds if needed
        
if ($binds !== FALSE)
        
{
            
//begin modification
            //$sql = $this->compile_binds($sql, $binds);
            
            
if ($this->dbdriver == 'oci8')
            
{
                $this
->binds $binds;
            
}
            
else
            
{
                $sql 
$this->compile_binds($sql$binds);
            
}
            
//end modification
        
}
        
...
        ...
    

The modifications also allow the use of bind variables in oracle queries instead of the substitution of values in ‘?’ placeholders, as shown below:

function getAll()
    
{
        $sql 
'SELECT * FROM user_info WHERE id = :P_ID and login_id = :P_LOGIN';
        
$params = array(
                        array(
'name'=>':P_ID''value'=>1'type'=>SQLT_CHR'length'=>-1),
                        array(
'name'=>':P_LOGIN''value'=>'ade''type'=>SQLT_CHR'length'=>-1)
                        );
        
$query $this->db->query($sql,$params);
        return 
$query->result_array();
     
 
Posted: 04 August 2009 04:02 PM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2009-07-30
2 posts

Thanks for good work,  I follow up your suggestion, there is no errors but still data is not inserting on table.  Trying to insert records through html form on table.

I am using Oracle 11g, codeigniter 1.7.1 and PHP 5.1.2.

Thanks,

 Signature 

Nobody can go back and start a new beggining, but anyone can start today and make a new ending.

 
Posted: 04 August 2009 08:01 PM   [ # 3 ]   [ Rating: 0 ]
Joined: 2009-07-22
5 posts

What exactly r u tryin’ to do?

I have some slight modification to query function(DB_driver.php)

function query($sql$binds FALSE$return_object TRUE)
    
{
        
if ($sql == '')
        
{
            
if ($this->db_debug)
            
{
                log_message
('error''Invalid query: '.$sql);
                return 
$this->display_error('db_invalid_query');
            
}
            
return FALSE;
        
}
...
...
        
// Compile binds if needed
        
$this->binds $binds;
        if (
$binds !== FALSE)
        
{
            
//begin modification
            //$sql = $this->compile_binds($sql, $binds);
            
            
if ($this->dbdriver == 'oci8')
            
{
                $this
->binds $binds;
            
}
            
else
            
{
                $sql 
$this->compile_binds($sql$binds);
            
}
            
//end modification
        
}

...
... 

You can PM for more help

 
Posted: 28 October 2009 10:24 AM   [ # 4 ]   [ Rating: 0 ]
Joined: 2009-10-28
4 posts

I had to modify all drivers but still probem with cursors.

My code in model:

  public function GetUsers(){

$cursor = $this->db->get_cursor();
$this->db->stored_procedure("jile","get_roles" ,array
(array
('name' => ':cRoles',
'value' => $cursor,
'type' => OCI_B_CURSOR,
'length' => -1)
)
);
}

displays:

ORA-24374: in file oci8/oci8_result.php, Line Number: 154

 
Posted: 10 September 2010 10:26 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2010-07-20
32 posts

Hi,

I’m trying to learn oracle, and I’m just using a sample database. seems like selecting table and views are easy but when calling procedures I got a bit of a problem…

Is there an easier way to call an Oracle stored Procedure like using the default active record methods?

or should I really have to modify the driver?

seems like the $package is required here right this means I have to create the package? and all I got right now is a procedure…

anyway I would like to call this one:

create or replace
PROCEDURE add_job_history
  
(  p_emp_id          job_history.employee_id%type
   
p_start_date      job_history.start_date%type
   
p_end_date        job_history.end_date%type
   
p_job_id          job_history.job_id%type
   
p_department_id   job_history.department_id%type
   
)
IS
BEGIN
  INSERT INTO job_history 
(employee_idstart_dateend_date,
                           
job_iddepartment_id)
    
VALUES(p_emp_idp_start_datep_end_datep_job_idp_department_id);
END add_job_history
 Signature 

I am real nowhere man, living in my nowhere land, doing all my nowhere plans for nobody…

SELECT 
    horny_girls 
AS fubu
FROM
    bar
WHERE
    body LIKE 
"barbie" 
 
Posted: 11 September 2010 04:10 AM   [ # 6 ]   [ Rating: 0 ]
Joined: 2010-09-11
2 posts

Thanks for good work,  I follow up your suggestion, there is no errors but still data is not inserting on table.  Trying to insert records through html form on table.

I am using Oracle 11g, codeigniter 1.7.1 and PHP 5.1.2.

Thanks,
mbt shoes ,mbt outlet,discount mbt shoes,cheap mbt shoes,mbt shoes sale

 
Posted: 25 May 2011 11:56 AM   [ # 7 ]   [ Rating: 0 ]
Joined: 2011-05-25
1 posts

didn’t help me at all, actually. i’m still stuck with this thing. i need to call not a query, but a procedure. it still outputs error on this line:

$exec_worked ociexecute($this->stmt_id$this->_commit); 

it doesn’t recognize function “ociexecute”.
what to do? my head’s about to explode. and my boss’ too.