OCI8_driver and how to use a Stored FUNCTION/PROCEDURE in Oracle from PHP (CodeIgniter)

This code allows you to call for a stored functions/procedures from the packages in Oracle. Package in Oracle must has “grant execute on PACKAGE_NAME to WEBUSERNAME” privilege to the user data, which had been written in Database Connection configuration file “database.php“.

PHP function for PROCEDUREs (returns nothing):

public function call_stored_procedure($procedure, $params) { 
    $sql = "begin $procedure("; 
    foreach ($params as $param) { 
       if(isset($param['type']) && $param['type'] == "DATE"){ 
           $sql .= "to_date('" . $param['value'] . "','dd.mm.yyyy'),"; 
       } else { 
           $sql .= $param['name'] . ","; 
       } 
    } 
    $sql = trim($sql, ",") . "); end;"; 
    $this->stmt_id = FALSE; 
    $this->_set_stmt_id($sql); 
    foreach ($params as $param) { 
        if(!isset($param['type']) || $param['type'] != 'DATE') { 
            if(isset($param['size']) && isset($param['type'])) { 
                if(is_array($param['value'])){ 
                    oci_bind_array_by_name($this->stmt_id, $param['name'], $param['value'], $param['size'], -1, $param['type']); 
                } else { 
                    oci_bind_by_name($this->stmt_id, $param['name'], $param['value'], $param['size'], $param['type']); 
                } 
            } else { 
                oci_bind_by_name($this->stmt_id, $param['name'], $param['value']); 
            } 
        } 
    } 
    oci_execute($this->stmt_id); 
}

Simple example:

function __construct() {
     $this->load->database(); 
}

function testdb ($par) {
    return $this->db->stored_cursor_function('schema_name.package_name.test_procedure',[
        [
            'name' => ':v1',
            'value'=> $par
        ]
    ]); 
}

PHP function for FUNCTIONs (returns ref cursor or numeric/string value):

public function stored_cursor_function($function, $params, $size = null) { 

    $sql = "begin :res := $function("; 
    $val_str = ''; 

    foreach ($params as $param) { 

        if(isset($param['type']) && $param['type'] == "DATE"){ 
            $sql .= "to_date('" . $param['value'] . "','dd.mm.yyyy'),"; 
        } else { 
            $sql .= $param['name'] . ","; 
        } 

        if (is_array($param['value'])){ 
            $val_str .= $param['name'].'->'; 
            foreach($param['value'] as $item) $val_str .= $item.'; ';  
        } else { 
            $val_str .= $param['name'] . " -> ".$param['value'].' '; 
        } 
    } 

    $sql = trim($sql, ",") . "); end;"; 
    $this->stmt_id = FALSE; 
    $this->_set_stmt_id($sql); 
    $lobs = [];

    // @file_put_contents('logdb.php', array("\n",' time: ', date('d-m-Y H:i:s'),' -///- query: '. $sql, "\n", $val_str, "\n"), FILE_APPEND | LOCK_EX );

    foreach ($params as $param) { 

        if(!isset($param['type']) || $param['type'] != 'DATE') { 

            if(isset($param['type']) && in_array($param['type'], [OCI_B_CLOB, OCI_B_BLOB])) { 

                $lobs[] = oci_new_descriptor($this->conn_id, OCI_D_LOB); 
                oci_bind_by_name($this->stmt_id, $param['name'], end($lobs), -1, $param['type']); 
                end($lobs)->writeTemporary($param['value']); 

            } else if(isset($param['size']) && isset($param['type'])) { 

                if(is_array($param['value'])){ 
                     oci_bind_array_by_name($this->stmt_id, $param['name'], $param['value'], $param['size'], -1, $param['type']); 
                } else { 
                     oci_bind_by_name($this->stmt_id, $param['name'], $param['value'], $param['size'], -1, $param['type']); 
                } 

            } else { 
                oci_bind_by_name($this->stmt_id, $param['name'], $param['value']); 
            } 
        } 
    } 

    if($size == null) { 

        // ref cursor 
        $result = array(); 
        $cursor = $this->get_cursor(); 
        $smt = $this->stmt_id; 
        oci_bind_by_name($smt, 'res', $cursor, -1, OCI_B_CURSOR); 
        oci_execute($smt); 
        oci_execute($cursor); 

        while (($item = oci_fetch_array($cursor)) != false) { 
            $result[] = $item; 
        }  

    } else { 

        // value 
        $result = null; 
        $smt = $this->stmt_id; 
        oci_bind_by_name($smt, 'res', $result, $size); 
        oci_execute($smt); 
    } 

    foreach($lobs as $key => $lob) $lobs[$key]->close(); 

    return $result; 
}

Simple example:

In case of value, which could be returned from Stored Function as

FUNCTION getval(parm IN INTEGER) RETURN NUMBER;“:

function __construct() { 
   $this->load->database(); 
} 

function test_func_val($par){ 
    return $this->db->stored_cursor_function('schema_name.package_name.getval', [ 
        [ 
           'name'  => ':v1', 
           'value' => (int)$par, 
        ] 
    ] , 99999 // <-- without this you won't get a desired result
         // in case of REF CURSOR you need to erase this 3-rd argument (i.e. 99999)
    ); 
}