Example #1
2
 public function readCursor($storedProcedure, $binds)
 {
     //
     // This function needs two parameters:
     //
     // $storedProcedure - the name of the stored procedure to call a chamar. Ex:
     //  my_schema.my_package.my_proc(:param)
     //
     // $binds - receives an array of associative arrays with: parameter names,
     // values and sizes
     //
     // WARNING: The first parameter must be consistent with the second one
     $conn = oci_connect('SECMAN', 'SECMAN', '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.10.24)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cisqa)))');
     if ($conn) {
         // Create the statement and bind the variables (parameter, value, size)
         $stid = oci_parse($conn, 'begin :cursor := ' . $storedProcedure . '; end;');
         foreach ($binds as $variable) {
             oci_bind_by_name($stid, $variable["parameter"], $variable["value"], $variable["size"]);
         }
         // Create the cursor and bind it
         $p_cursor = oci_new_cursor($conn);
         oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR);
         // Execute the Statement and fetch the data
         oci_execute($stid);
         oci_execute($p_cursor, OCI_DEFAULT);
         oci_fetch_all($p_cursor, $data, null, null, OCI_FETCHSTATEMENT_BY_ROW);
         // Return the data
         return $data;
     }
 }
Example #2
1
 public function get($id)
 {
     require_once 'DataBase.php';
     $data = new DataBase();
     $data->open();
     $sql = "BEGIN GETRUTACONDUCTOR(:p_id, :rc); END;";
     $sent = oci_parse($data->getConn(), $sql);
     $refcur = oci_new_cursor($data->getConn());
     $this->id = $id;
     oci_bind_by_name($sent, ':p_id', $this->id);
     oci_bind_by_name($sent, ':rc', $refcur, -1, OCI_B_CURSOR);
     if (!oci_execute($sent)) {
         return false;
     }
     if (!oci_execute($refcur)) {
         return false;
     }
     while (($row = oci_fetch_array($refcur, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
         $this->ruta = $row['ID_RUTA'];
         $this->conductor = $row['ID_CONDUCTOR'];
         $this->kilometraje = $row['KILOMETRAJE'];
         $this->descripcion = $row['DESCRIPCION'];
     }
     oci_free_statement($refcur);
     $data->free($sent);
     $data->close();
     return true;
 }
Example #3
0
 /**
  * Constructor.
  *
  * Receive a database adapter and try to allocate a new OCI cursor.
  *
  * @param  Zend_Db_Adapter_Oracle $db
  * @throws Zend_Db_Cursor_Exception
  */
 public function __construct(Zend_Db_Adapter_Oracle $db)
 {
     // Get the OCI connection resource and a cursor
     $conn = $db->getConnection();
     $cursor = oci_new_cursor($conn);
     // Check if the cursor is a valid resource
     if (!is_resource($cursor)) {
         require_once 'Zend/Db/Cursor/Exception.php';
         throw new Zend_Db_Cursor_Exception('Can\'t allocate a cursor.');
     }
     // Setup $_cursor property
     $this->_cursor = $cursor;
 }
Example #4
0
 /**
  * @param Connection $connection
  * @param null       $paging
  * @param null       $resource
  */
 public function __construct(Connection $connection, $paging = null, &$resource = null)
 {
     parent::__construct($connection, $paging, $resource);
     if (isset($resource)) {
         if (is_resource($resource)) {
             $this->resource = $resource;
         } else {
             $this->resource = oci_new_cursor($this->connection->resource);
             $trace = debug_backtrace();
             trigger_error('Invalid input via __construct(): value is not a valid resource for ("this->resource' . '") in ' . $trace[0]['file'] . ' on line ' . $trace[0]['line'], E_USER_NOTICE);
         }
     } else {
         $this->resource = oci_new_cursor($this->connection->resource);
     }
 }
Example #5
0
function DBExecSP($connection, &$cursor, $sql, $params = array(), $return = true, $mode = 1)
{
    // Ejecuta un store procedure..
    // 0 = NO commit..
    // 1 = Autocommit..
    global $dbError;
    $commit = OCI_COMMIT_ON_SUCCESS;
    switch ($mode) {
        case 0:
            $commit = OCI_DEFAULT;
            break;
        case 1:
            $commit = OCI_COMMIT_ON_SUCCESS;
            break;
    }
    $cursor = oci_new_cursor($connection);
    $stmt = oci_parse($connection, $sql);
    foreach ($params as $key => $val) {
        oci_bind_by_name($stmt, $key, htmlspecialcharsDecodeUpper($params[$key]));
    }
    if ($return) {
        oci_bind_by_name($stmt, "data", $cursor, -1, OCI_B_CURSOR);
    }
    $error = !oci_execute($stmt, $commit);
    $error = ($error or !oci_execute($cursor, $commit));
    if ($error) {
        $dbError = oci_error($stmt);
        if (isset($dbError["offset"])) {
            DBRollback($connection);
            // Hago un rollback por si el query con el error venía sin transacción..
            saveSqlError($connection, $dbError["message"], $sql, $params);
            EscribirLogTxt1('Error DBExecSP sql', $sql);
            EscribirLogTxt1('Error DBExecSP params', implode(",", $params));
            EscribirLogTxt1('Error DBExecSP e ', $error . " ---- " . $dbError["message"]);
            throw new Exception("Error inesperado. [" . date("d/m/Y H:i:s") . "]");
        }
    }
    return $stmt;
}
 public function Cursor($stored_proc, $bind)
 {
     if (!is_resource($this->conn_handle)) {
         return false;
     }
     $sql = "begin {$stored_proc}(:{$bind}); end;";
     $curs = oci_new_cursor($this->conn_handle);
     $stmt = oci_parse($this->conn_handle, $sql);
     oci_bind_by_name($stmt, $bind, $curs, -1, OCI_B_CURSOR);
     oci_execute($stmt);
     oci_execute($curs);
     $this->FreeStatement($stmt);
     return $curs;
 }
Example #7
0
 /**
  * Binds a parameter to the specified variable name.
  *
  * @param mixed $parameter Name the parameter, either integer or string.
  * @param mixed $variable  Reference to PHP variable containing the value.
  * @param mixed $type      OPTIONAL Datatype of SQL parameter.
  * @param mixed $length    OPTIONAL Length of SQL parameter.
  * @param mixed $options   OPTIONAL Other options.
  * @return bool
  * @throws Zend_Db_Statement_Exception
  */
 protected function _bindParam($parameter, &$variable, $type = null, $length = null, $options = null)
 {
     $valueLob = '';
     if ($variable instanceof ZendT_Type_Date) {
         $variable = $variable->getValueToDb();
         $variable = substr(str_replace('T', ' ', $variable), 0, 19);
     } else {
         if ($variable instanceof ZendT_Type_Number) {
             $variable = $variable->getValueToDb();
             $type = SQLT_FLT;
         } else {
             if ($variable instanceof ZendT_Type_Clob) {
                 $valueLob = $variable->getValueToDb();
                 $variable = oci_new_descriptor($this->_adapter->getConnection(), OCI_D_LOB);
                 $type = OCI_B_CLOB;
                 $length = -1;
             } else {
                 if ($variable instanceof ZendT_Type_Blob) {
                     $valueLob = $variable->getValueToDb();
                     $variable = oci_new_descriptor($this->_adapter->getConnection(), OCI_D_LOB);
                     $type = OCI_B_BLOB;
                     $length = -1;
                 }
             }
         }
     }
     if ($variable instanceof ZendT_Type) {
         $variable = $variable->getValueToDb();
     }
     // default value
     if ($type === NULL || $type === Zend_Db::PARAM_STR) {
         $type = SQLT_CHR;
     }
     if ($type == Zend_Db::PARAM_STMT) {
         $type = OCI_B_CURSOR;
         $variable = @oci_new_cursor($this->_adapter->getConnection());
         if (empty($variable)) {
             /**
              * @see Zend_Db_Adapter_Oracle_Exception
              */
             require_once 'Zend/Db/Statement/Oracle/Exception.php';
             $error = array("code" => "", "message" => "Error to create oracle cursor");
             throw new Zend_Db_Statement_Oracle_Exception(oci_error($variable));
         }
     }
     // default value
     if ($length === NULL) {
         $length = -1;
     }
     $retval = @oci_bind_by_name($this->_stmt, $parameter, $variable, $length, $type);
     if ($valueLob) {
         $variable->WriteTemporary($valueLob);
     }
     if ($retval === false) {
         /**
          * @see Zend_Db_Adapter_Oracle_Exception
          */
         require_once 'Zend/Db/Statement/Oracle/Exception.php';
         throw new Zend_Db_Statement_Oracle_Exception(oci_error($this->_stmt));
     }
     return true;
 }
 /**
  * Binds a parameter to the specified variable name
  * @param string $parameter
  * @param mixed $variable
  * @param int $data_type
  * @param int $length
  * @param array $driver_options
  * @return bool
  */
 public function bindParam($parameter, &$variable, $data_type = PDO::PARAM_STR, $length = 4000, $driver_options = null)
 {
     if (strpos($parameter, ':') === false) {
         $parameter = ':' . $parameter;
     }
     if (stripos($this->queryString, $parameter) === false) {
         return true;
     }
     $isOutputParameter = $this->checkBitFlag($data_type, PDO::PARAM_INPUT_OUTPUT);
     $data_type = $this->removeBitFlag($data_type, PDO::PARAM_INPUT_OUTPUT);
     $ociParamType = $this->pdo2OciParamConst($data_type);
     if ($ociParamType === SQLT_CHR) {
         $variable = (string) $variable;
     }
     if (is_array($variable)) {
         // TODO Не съм сигурен, дали ще се използва някога
         $res = @oci_bind_array_by_name($this->stmt, $parameter, $variable, count($variable), $length, $ociParamType);
         $this->checkError($res);
     } else {
         // Cursor
         if ($ociParamType == OCI_B_CURSOR) {
             $statementType = @oci_statement_type($this->stmt);
             $this->checkError($statementType);
             if (!in_array($statementType, array('BEGIN', 'DECLARE'))) {
                 throw new Exception('Bind cursor only in BEGIN or DECLARE statement');
             }
             $this->_cursor = @oci_new_cursor($this->ociPdoAdapter->getOciConnection());
             $res = $this->_cursor;
             $this->checkError($res);
             $res = @oci_bind_by_name($this->stmt, $parameter, $this->_cursor, -1, $ociParamType);
             $this->checkError($res);
         } elseif ($lob_desc = $this->oci_lob_desc($ociParamType)) {
             $this->_lobs[$this->_lobsCount]['type'] = $ociParamType;
             $this->_lobs[$this->_lobsCount]['lob'] = @oci_new_descriptor($this->ociPdoAdapter->getOciConnection(), $lob_desc);
             $res = $this->_lobs[$this->_lobsCount]['lob'];
             $this->checkError($res);
             $res = @oci_bind_by_name($this->stmt, $parameter, $this->_lobs[$this->_lobsCount]['lob'], -1, $ociParamType);
             $this->checkError($res);
             if (!$isOutputParameter) {
                 if (is_resource($variable) && get_resource_type($variable) === 'stream') {
                     $this->_lobs[$this->_lobsCount]['var'] = '';
                     $res = @$this->_lobs[$this->_lobsCount]['lob']->writeTemporary($this->_lobs[$this->_lobsCount]['var'], $ociParamType == SQLT_BLOB ? OCI_TEMP_BLOB : OCI_TEMP_CLOB);
                     $this->checkError($res);
                     $buffer = 8192;
                     while (!feof($variable)) {
                         $res = @$this->_lobs[$this->_lobsCount]['lob']->write(fread($variable, $buffer));
                         $this->checkError($res);
                         $res = @$this->_lobs[$this->_lobsCount]['lob']->flush();
                         $this->checkError($res);
                     }
                 } else {
                     $variable = (string) $variable;
                     $this->_lobs[$this->_lobsCount]['var'] =& $variable;
                     $res = @$this->_lobs[$this->_lobsCount]['lob']->writeTemporary($this->_lobs[$this->_lobsCount]['var'], $ociParamType == SQLT_BLOB ? OCI_TEMP_BLOB : OCI_TEMP_CLOB);
                     $this->checkError($res);
                     $res = @$this->_lobs[$this->_lobsCount]['lob']->flush();
                     $this->checkError($res);
                 }
             } else {
                 $this->_lobs[$this->_lobsCount]['var'] =& $variable;
             }
             $this->_lobs[$this->_lobsCount]['input'] = !$isOutputParameter;
             $this->_lobsCount++;
         } else {
             $res = @oci_bind_by_name($this->stmt, $parameter, $variable, $length, $ociParamType);
             $this->checkError($res);
         }
     }
     return $res;
 }
    $res = "BEGIN CPI.PKG_INDICADORES_PERF.PRC_PERFORMANCE_SC1_CALC(:CURSOR,:USUARIO); END;";
    $res1 = oci_parse($c, $res);
    //  Bind the input parameter
    $refcur = oci_new_cursor($c);
    oci_bind_by_name($res1, ':CURSOR', $refcur, -1, OCI_B_CURSOR);
    oci_bind_by_name($res1, ':USUARIO', $USUARIO, -1);
    oci_execute($res1);
    oci_execute($refcur);
}
/* LOGICA PARA BUSCAR POR SU_PAQ_IND - Sucursales */
if ($TABLAS == 'SU_PAQ_IND') {
    // Obtengo los porcentajes mediante cursores
    $res = "BEGIN CPI.PKG_INDICADORES_PERF.PRC_PERFORMANCE_SU_CALC(:CURSOR,:USUARIO); END;";
    $res1 = oci_parse($c, $res);
    //  Bind the input parameter
    $refcur = oci_new_cursor($c);
    oci_bind_by_name($res1, ':CURSOR', $refcur, -1, OCI_B_CURSOR);
    oci_bind_by_name($res1, ':USUARIO', $USUARIO, -1);
    oci_execute($res1);
    oci_execute($refcur);
}
echo "<div class='container'>\t\n\t</div>\n\t<div class='container'>\n\t\t<div class='sixteen columns'>\n\t\t\t<div class='field'>\n\t\t\t\t<input type=\"button\" id=\"boton_descargar_control\" onclick=\"performanceProceso()\" value=\"Descargar (Excel)\"/>\n\t\t\t</div>\n\t\t</div>\n\t\t<div class='sixteen columns'>\n\t\t\t\t<h3>Pre-Carga entre el {$FE_ESTADO_DESDE} y el {$FE_ESTADO_HASTA}</h3>\n\t\t</div>\n\t</div>";
// Empiezo a escribir los datos
echo "<div class='sixteen columns'>";
echo "<h3><a href=\"performanceProceso.php\">Volver Atrás</a></h3>";
echo "<table class='standard-table'>";
echo "<tr><th>Descripcion</th><th>Cantidad</th><th>Porcentaje</th><th>Ver mas</th></tr>";
$i = 0;
while ($row = oci_fetch_array($refcur, OCI_ASSOC + OCI_RETURN_NULLS)) {
    if (strpos($row['FUSS_CD_TITULO'], 'Control ') !== false) {
        echo "<td><span class=\"control\">" . $row['FUSS_CD_TITULO'] . "</span></td>\n";
Example #10
0
 function _set_curs_id($name = '')
 {
     $this->curs_id[$name] = oci_new_cursor($this->conn_id);
     return $this->curs_id[$name];
 }
Example #11
0
 public static function callCursorFunction($procedure, $values = array())
 {
     $database = SQLDatabase::getInstance();
     $sql = '';
     $keys = array_keys($values);
     $p_cursor = oci_new_cursor($database);
     if (sizeof($values) > 0) {
         $sql = 'BEGIN :callFunctionRes := ' . $procedure . '(' . implode(',', $keys) . '); END;';
     } else {
         $sql = 'BEGIN :callFunctionRes := ' . $procedure . '; END;';
     }
     $statement = oci_parse($database, $sql);
     oci_bind_by_name($statement, ':callFunctionRes', $p_cursor, -1, OCI_B_CURSOR);
     foreach ($keys as $key) {
         oci_bind_by_name($statement, $key, $values[$key], 512);
     }
     if (@(!oci_execute($statement))) {
         $errors = oci_error($statement);
         return array('success' => false, 'data' => 'Error : ' . $errors['code'] . ' => ' . $errors['message'], 'params' => $values);
     }
     oci_execute($p_cursor);
     $result = array();
     oci_fetch_all($p_cursor, $result, null, null, OCI_FETCHSTATEMENT_BY_ROW);
     return array('success' => true, 'data' => $result, 'params' => $values);
 }
Example #12
0
 public function actionTry()
 {
     if ($_POST != '') {
         Yii::app()->oracle->sessStart();
         $conn = Yii::app()->oracle->getOracleConnection();
         $session_id = $_POST['session_id'];
         $user_id = $_POST['user_id'];
         $dataset = $_POST['dataset'];
         $complex = $_POST['complex'];
         /*$session_id = '200136441';
           $user_id = '200000081';
           $dataset = '162';
           $complex = '300';*/
         /*$session_id = $_SESSION['client_session_id'];
           $user_id = $_SESSION['user_id'];
           $dataset = $_SESSION['dataset'];
           $complex = $_SESSION['complex'];*/
         //start SidebarTree packs
         $notifications_curs = oci_new_cursor($conn);
         $rows = array();
         $notification_obj = oci_parse($conn, "begin comm.SET_CLIENT_INFO(:user_id,:dataset,:complex, :session_id); :p2:=DOB_CURSORS.counter(); end;");
         oci_bind_by_name($notification_obj, ":p2", $notifications_curs, -1, OCI_B_CURSOR);
         oci_bind_by_name($notification_obj, ":user_id", $user_id);
         oci_bind_by_name($notification_obj, ":dataset", $dataset);
         oci_bind_by_name($notification_obj, ":complex", $complex);
         oci_bind_by_name($notification_obj, ":session_id", $session_id);
         oci_execute($notification_obj);
         oci_execute($notifications_curs);
         while ($row = oci_fetch_array($notifications_curs, OCI_ASSOC)) {
             $rows[] = $row;
         }
         echo json_encode($rows);
         /*$array = array();
           Yii::app()->oracle->setClientInfo($conn, true, ":p1:=DOB_CURSORS.counter()", $array);*/
     }
 }
 /**
  * Lance une procédure stockées sur la connextion courante
  * @param string $pProcedure la procédure a lancer
  * @param array $pParams un tableau de paramètre à donner à la procédure
  *  le tableau est de la forme $pParams['nom'] = array ('type'=>, 'length'), 'in'=>, ''
  * @return array un tableau de résultat avec array['results'] = résultats,
  *    array['params']['nomParam'] = valeur
  */
 public function doProcedure($pProcedure, $pParams)
 {
     CopixLog::log($pProcedure . var_export($pParams, true), 'query', CopixLog::INFORMATION);
     //Préparation de la requête
     $stmt = @ociparse($this->_ct, $pProcedure);
     if ($stmt === false) {
         throw new CopixDBException('[CopixDB] Impossible de préparer la procédure ' . $pProcedure);
     }
     //On analyse les paramètres
     $arVariablesName = array();
     $arVariables = array();
     foreach ($pParams as $name => $param) {
         $variableName = substr($name, 1);
         if (!is_array($param)) {
             ${$variableName} = $param;
             if (!OCIBindByName($stmt, $name, ${$variableName}, 255)) {
                 throw new Exception("[CopixDB] Impossible de rapprocher '{$name}' avec '" . ${$variableName} . "' taille " . $arVariables[$variableName]['maxlength'] . " type " . $this->_convertQueryParam($arVariables[$variableName]['type']));
             }
             $arVariables[$variableName]['type'] = 'AUTO';
             $arVariables[$variableName]['value'] = $param;
         } else {
             if (!isset(${$variableName})) {
                 ${$variableName} = isset($param['value']) ? $param['value'] : null;
             }
             $arVariables[$variableName] = $param;
             if (!isset($arVariables[$variableName]['type'])) {
                 $arVariables[$variableName]['type'] = CopixDBQueryParam::DB_AUTO;
             }
             if (!isset($arVariables[$variableName]['maxlength'])) {
                 $arVariables[$variableName]['maxlength'] = -1;
             }
             if ($arVariables[$variableName]['type'] === CopixDBQueryParam::DB_CURSOR) {
                 ${$variableName} = oci_new_cursor($this->_ct);
             }
             if (!OCIBindByName($stmt, $name, ${$variableName}, $arVariables[$variableName]['maxlength'], $this->_convertQueryParam($arVariables[$variableName]['type']))) {
                 oci_free_statement($stmt);
                 throw new CopixDBException("[CopixDB] Impossible de rapprocher '{$name}' avec '" . ${$variableName} . "' taille " . $arVariables[$variableName]['maxlength'] . " type " . $this->_convertQueryParam($arVariables[$variableName]['type']));
             }
         }
     }
     //on exécute la requête
     if (!ociexecute($stmt, OCI_DEFAULT)) {
         $statementErrors = oci_error($stmt);
         oci_free_statement($stmt);
         throw new CopixDBException('[CopixDB] Impossible d\'exécuter la procédure ' . $pProcedure . ' - ' . var_dump($statementErrors) . ' avec les variables ' . var_dump($arVariables));
     }
     //analyse des résultats
     foreach ($arVariables as $name => $value) {
         //Si c'est un curseur
         if ($value['type'] === CopixDBQueryParam::DB_CURSOR) {
             if (!@ociexecute(${$name})) {
                 oci_free_statement(${$name});
                 oci_free_statement($stmt);
                 throw new CopixDBException("Impossible de récupérer l'ensemble de résultat de la variable {$name}");
             }
             $toReturn[':' . $name] = array();
             while ($r = oci_fetch_object(${$name})) {
                 $toReturn[':' . $name][] = $r;
             }
             oci_free_statement(${$name});
         } else {
             $toReturn[':' . $name] = ${$name};
         }
     }
     //On commit si le mode est autocommit
     if ($this->_autoCommitMode == self::OCI_AUTO_COMMIT) {
         $this->commit();
     }
     oci_free_statement($stmt);
     CopixLog::log('Terminé', 'Procedure');
     return $toReturn;
 }
Example #14
0
 public function setClientInfo($conn, $cursor, $sql, $array)
 {
     $userid = $_SESSION['user_id'];
     $dataset = $_SESSION['dataset'];
     $complex = $_SESSION['complex'];
     $session_id = $_SESSION['client_session_id'];
     /*   */
     if ($userid != '') {
         $_SESSION['activity'] = time();
         $_SESSION['time'] = time();
         Yii::app()->oracle->sessStart();
         unset($_COOKIE['PHPSESSID']);
         setcookie('PHPSESSID', null, time() - 86400, "/");
         setcookie('PHPSESSID', session_id(), time() + 86400, "/");
     }
     /*   */
     $stid = oci_parse($conn, "begin comm.SET_CLIENT_INFO(:userid,:dataset,:complex, :session_id); {$sql}; end;");
     if (!$stid) {
         $e = oci_error($conn);
         //trigger_error(htmlentities($e['message']), E_USER_ERROR);
         Yii::app()->oracle->addLogs($e, $userid);
         exit;
     }
     if ($cursor == true) {
         $rows = array();
         $curs = oci_new_cursor($conn);
         oci_bind_by_name($stid, ":p1", $curs, -1, OCI_B_CURSOR);
     } else {
         oci_bind_by_name($stid, ":p1", $val, 200);
     }
     oci_bind_by_name($stid, ":userid", $userid);
     oci_bind_by_name($stid, ":dataset", $dataset);
     oci_bind_by_name($stid, ":complex", $complex);
     oci_bind_by_name($stid, ":session_id", $session_id);
     if (!empty($array)) {
         foreach ($array as $key => $value) {
             oci_bind_by_name($stid, "{$key}", $value, -1, SQLT_CHR);
         }
     }
     $r = oci_execute($stid);
     if (!$r) {
         $e = oci_error($stid);
         Yii::app()->oracle->addLogs($e, $userid);
         exit;
     }
     if ($cursor == true) {
         $c = oci_execute($curs);
         if (!$c) {
             $e = oci_error($curs);
             Yii::app()->oracle->addLogs($e, $userid);
             exit;
         }
         while ($row = oci_fetch_array($curs, OCI_ASSOC)) {
             $rows[] = $row;
         }
         echo json_encode($rows);
     } else {
         echo json_encode($val);
     }
 }
Example #15
0
 function manual()
 {
     $conn = oci_connect('SECMAN', 'SECMAN', '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.10.24)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cisqa)))');
     if (!$conn) {
         $m = oci_error();
         trigger_error(htmlentities($m['message']), E_USER_ERROR);
     }
     /*
     		$sql = 'call BILL52.PKG_INFOAGENDA.CARI(:no_agenda,:prm_policy_no,:prm_date_of_loss,:prm_policy_details,:prm_success)';
     		$prm_policy_no = 'LCURSOR1';
     		$prm_date_of_loss = 'LCURSOR2';
     
     
     		if (!$conn) {
     			$e = oci_error();
     			trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     		}
     
     		$stmt = oci_parse($conn, $sql);
     		if (!$stmt)
     			print "Error parsing SQL";
     
     		$prm_policy_details = oci_new_cursor($conn);
     		$prm_success = '';
     
     		oci_bind_by_name($stmt, ':no_agenda', '553000511308190147') 
     		or die('Error binding string');
     
     		oci_bind_by_name($stmt, ':prm_policy_no', $prm_policy_no, -1, OCI_B_CURSOR) 
     		or die('Error binding string');
     
     		oci_bind_by_name($stmt, ':prm_date_of_loss', $prm_date_of_loss, -1, OCI_B_CURSOR) 
     		or die('Error binding string');
     
     		oci_bind_by_name($stmt, ':prm_policy_details', $prm_policy_details, -1, OCI_B_CURSOR) 
     		or die('Error binding cursor');
     
     		oci_bind_by_name($stmt, ':prm_success', $prm_success) 
     		or die('Error binding string');
     
     				// Execute Statement
     		$execute_return = oci_execute($stmt);
     		if (!$execute_return)
     			print "Error Execution Stored Procedure";
     
     				//execute the CURSORS (this is one of the weird things about ref cursors
     				// w/ Oracle-- they must get EXECUTED
     
     		oci_execute($prm_policy_details);
     
     		print "<pre>";
     		print "Returned parameters<br/>\"";
     		print_r($prm_policy_details);
     		print "\"<br/>";
     		print "Sucess Code:" . $prm_success . "<br/>";
     		print "</pre>"; */
     //You must asign before.
     $p1 = '511400790653';
     $p2 = 'LCURSOR1';
     $p3 = 'LCURSOR2';
     $p4 = 'LCURSOR3';
     $p5 = '';
     $stid = oci_parse($conn, 'begin BILL52.FAKMKWH.CARI(:p1,:p2,:p3,:p4,:p5); end;');
     $p_cursor1 = oci_new_cursor($conn);
     $p_cursor2 = oci_new_cursor($conn);
     $p_cursor3 = oci_new_cursor($conn);
     //Send parameters variable  value  lenght
     oci_bind_by_name($stid, ':p1', $p1, 18) or die('Error binding string1');
     oci_bind_by_name($stid, ':p2', $p_cursor1, -1, OCI_B_CURSOR) or die('Error binding string2');
     oci_bind_by_name($stid, ':p3', $p_cursor2, -1, OCI_B_CURSOR) or die('Error binding string3');
     oci_bind_by_name($stid, ':p4', $p_cursor3, -1, OCI_B_CURSOR) or die('Error binding string4');
     oci_bind_by_name($stid, ':p5', $p5, 100, SQLT_CHR) or die('Error binding string5');
     //Bind Cursor     put -1
     //oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR);
     // Execute Statement
     /*
     		
     		if (oci_execute($p_cursor1)){
     			
     			while (($row = oci_fetch_array($p_cursor1, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
     				echo $row['NOAGENDA'] . "<br />\n";
     			} 
     		}*/
     if (oci_execute($stid)) {
         oci_execute($p_cursor1, OCI_DEFAULT);
         oci_fetch_all($p_cursor1, $cursor, null, null, OCI_FETCHSTATEMENT_BY_ROW);
         echo '<br>';
         print_r($cursor);
     } else {
         $e = oci_error($stid);
         echo $e['message'];
     }
     /*
     		if($stid != false){
     		    // parsing empty query != false
     			if(oci_execute($stid)){
     		        // executing empty query != false
     				if(oci_fetch_all($stid, $cursor, 0, -1, OCI_FETCHSTATEMENT_BY_ROW) == false){
     		            // but fetching executed empty query results in error (ORA-24338: statement handle not executed)
     					$e = oci_error($stid);
     					echo $e['message'];
     				}
     			}
     			else{
     				$e = oci_error($stid);
     				echo $e['message'];
     			}
     		}
     		else{
     			$e = oci_error($link);
     			echo $e['message'];
     		}  */
 }
Example #16
0
 function get($id)
 {
     require_once 'DataBase.php';
     $data = new DataBase();
     $data->open();
     $sql = "BEGIN GETVEHICULO(:id, :rc); END;";
     $sent = oci_parse($data->getConn(), $sql);
     $refcur = oci_new_cursor($data->getConn());
     $this->id = $id;
     oci_bind_by_name($sent, ':id', $this->id);
     oci_bind_by_name($sent, ':rc', $refcur, -1, OCI_B_CURSOR);
     if (!oci_execute($sent)) {
         return false;
     }
     if (!oci_execute($refcur)) {
         return false;
     }
     while (($row = oci_fetch_array($refcur, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
         $this->id = $row['ID_VEHICULO'];
         $this->placa = $row['PLACA'];
         $this->kilometraje = $row['KILOMETRAJE'];
         $this->serie = $row['SERIE'];
         $this->motor = $row['MOTOR'];
         $this->anio = $row['ANIO'];
         $this->color = $row['COLOR'];
         $this->marca = $row['MARCA'];
     }
     oci_free_statement($refcur);
     $data->free($sent);
     $data->close();
     return true;
 }
 public function contohManggilRefCursor($period, $ten_id)
 {
     $db2 = $this->load->database('default2', TRUE);
     $sql = " DECLARE " . "  i_retrun VARCHAR2(90); " . "  BEGIN " . "  C2BI.pack_seacrh_expense.p_seacrh_olo_expense_bck(:params1,:params2, :i_retrun, :cursor); END;";
     $params = array(array('name' => ':params1', 'value' => $period, 'type' => SQLT_CHR, 'length' => 100), array('name' => ':params2', 'value' => $ten_id, 'type' => SQLT_CHR, 'length' => 32));
     // Bind the output parameter
     $stmt = oci_parse($db2->conn_id, $sql);
     foreach ($params as $p) {
         // Bind Input
         oci_bind_by_name($stmt, $p['name'], $p['value'], $p['length']);
     }
     $cursor = oci_new_cursor($db2->conn_id);
     $message = '';
     oci_bind_by_name($stmt, ':i_retrun', $message, 32);
     oci_bind_by_name($stmt, ":cursor", $cursor, -1, OCI_B_CURSOR);
     oci_execute($stmt, OCI_DEFAULT);
     oci_execute($cursor, OCI_DEFAULT);
     // oci_fetch_all($cursor, $out, null, null, OCI_FETCHSTATEMENT_BY_ROW);
     oci_fetch_all($cursor, $res);
     print_r($res);
     exit;
     // return $message;
 }
Example #18
0
function sti_oracle_get_data($connection_string, $data_source_name, $query)
{
    $info = sti_oracle_parse_connection_string($connection_string);
    if ($info["privilege"] == "") {
        $conn = oci_connect($info["user_id"], $info["password"], $info["database"], $info["charset"]);
    } else {
        $conn = oci_pconnect($info["user_id"], $info["password"], $info["database"], $info["charset"], $info["privilege"]);
    }
    if ($conn === false) {
        $err = ocierror();
        return "ServerError:Could not connect {$err['message']}";
    }
    $query = sti_parse_query_parameters($query);
    $stmt = oci_parse($conn, $query);
    if ($stmt === false) {
        $err = oci_error($conn);
        return "ServerError:Parse Error {$err['message']}";
    } else {
        if (strpos($query, "cursor") !== false) {
            $curs = oci_new_cursor($conn);
            oci_bind_by_name($stmt, "cursor", $curs, -1, OCI_B_CURSOR);
        }
        if (oci_execute($stmt, OCI_COMMIT_ON_SUCCESS) === true) {
            if (isset($curs)) {
                if (oci_execute($curs, OCI_DEFAULT) === false) {
                    $err = oci_error();
                    return "ServerError:Cursor Execute Error {$err['message']}";
                }
                $stmt_curs = $curs;
            } else {
                $stmt_curs = $stmt;
            }
            $ncols = oci_num_fields($stmt_curs);
            $column_names = array();
            $column_types = array();
            for ($i = 1; $i <= $ncols; $i++) {
                $column_names[] = oci_field_name($stmt_curs, $i);
                $column_type = oci_field_type($stmt_curs, $i);
                $column_precision = oci_field_precision($stmt_curs, $i);
                $column_types[] = sti_oracle_get_column_type($column_type, $column_precision);
            }
            $xml_output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<Database>";
            oci_fetch_all($stmt_curs, $data);
            for ($i = 0; $i < count($data[$column_names[0]]); $i++) {
                $xml_output .= "<{$data_source_name}>";
                for ($j = 0; $j < count($column_names); $j++) {
                    $value = $data[$column_names[$j]][$i];
                    if ($column_types[$j] == "base64Binary") {
                        $value = base64_encode($value);
                    }
                    if ($column_types[$j] == "dateTime" && strlen($value) > 0 && strpos($value, ".") > 0) {
                        $values = preg_split("/\\./", $value);
                        if (count($values) >= 3) {
                            if (strlen($values[2]) > 2) {
                                $value = $values[2] . '-' . $values[1] . '-' . $values[0];
                            } else {
                                $value = ((int) $values[2] >= 30 ? '19' . $values[2] : '20' . $values[2]) . '-' . $values[1] . '-' . $values[0];
                            }
                        }
                    } else {
                        $value = str_replace("&", "&amp;", $value);
                        $value = str_replace("<", "&lt;", $value);
                        $value = str_replace(">", "&gt;", $value);
                    }
                    $xml_output .= "<{$column_names[$j]}>{$value}</{$column_names[$j]}>";
                }
                $xml_output .= "</{$data_source_name}>";
            }
            $xml_output .= "</Database>";
            if (isset($curs)) {
                oci_free_statement($curs);
            }
            oci_free_statement($stmt);
        } else {
            $err = ocierror($stmt);
            return "ServerError:Execute Error {$err['message']} {$query}";
        }
    }
    return $xml_output;
}
Example #19
0
 public function get($id)
 {
     require_once 'DataBase.php';
     $data = new DataBase();
     $data->open();
     $sql = "BEGIN GETMANTENIMIENTO(:id, :rc); END;";
     $sent = oci_parse($data->getConn(), $sql);
     $refcur = oci_new_cursor($data->getConn());
     $this->id = $id;
     oci_bind_by_name($sent, ':id', $this->id);
     oci_bind_by_name($sent, ':rc', $refcur, -1, OCI_B_CURSOR);
     if (!oci_execute($sent)) {
         return false;
     }
     if (!oci_execute($refcur)) {
         return false;
     }
     while (($row = oci_fetch_array($refcur, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
         $this->factura = $row['FACTURA'];
         $this->proveedor = $row['PROVEEDOR'];
         $this->fecha = $row['FECHA_REPARACION'];
         $this->monto = $row['MONTO_REPARACION'];
         $this->descripcion = $row['DESCRIPCION'];
         $this->vehiculo = $row['ID_VEHICULO'];
         $this->tipoMantenimiento = $row['ID_TIPO_MANTENIMIENTO'];
     }
     oci_free_statement($refcur);
     $data->free($sent);
     $data->close();
 }
Example #20
0
function queryCursor($conexion, $sql)
{
    $fp = fopen("./log/log.log", "a");
    fwrite($fp, DATE("d/M/y") . " " . $sql);
    fwrite($fp, PHP_EOL);
    $curs = oci_new_cursor($conexion);
    $s = oci_parse($conexion, $sql);
    oci_bind_by_name($s, ":cursbv", $curs, -1, OCI_B_CURSOR);
    oci_execute($s, OCI_DEFAULT);
    oci_execute($curs, OCI_DEFAULT);
    $arrayResult = array();
    while ($row = oci_fetch_array($curs, OCI_ASSOC + OCI_RETURN_NULLS)) {
        foreach ($row as $key => $item) {
            fwrite($fp, ($item !== null ? htmlentities($item, ENT_QUOTES) : ' ') . "\t");
            $arrayResult[$key][] = $item;
        }
        fwrite($fp, PHP_EOL);
    }
    fwrite($fp, "################{$_SESSION["active_user_id"]}################");
    fwrite($fp, PHP_EOL);
    fclose($fp);
    oci_free_statement($s);
    oci_free_statement($curs);
    return $arrayResult;
}
Example #21
0
 /**
  * Get cursor. Returns a cursor from the database
  *
  * @return	resource
  */
 public function get_cursor()
 {
     return $this->curs_id = oci_new_cursor($this->conn_id);
 }
Example #22
0
 /**
  * Get a page of entries from the log.  Returns the last 100 records
  * matching the options given.
  * 
  * @param array options
  * @param int user_id	current user
  * @return array log records
  */
 public function listLog($options, $user_id)
 {
     if (empty($options)) {
         $optionsFlag = NULL;
     } else {
         $optionsFlag = 1;
     }
     $sql = "BEGIN SP_LIST_LOG(:P_OPTION_FLAG, :P_USA_ID, :P_OSR_CONSUMER_KEY, :P_OCR_CONSUMER_KEY, :P_OST_TOKEN, :P_OCT_TOKEN, :P_ROWS, :P_RESULT); END;";
     // parse sql
     $stmt = oci_parse($this->conn, $sql) or die('Can not parse query');
     // Bind In and Out Variables
     oci_bind_by_name($stmt, ':P_OPTION_FLAG', $optionsFlag, 255);
     oci_bind_by_name($stmt, ':P_USA_ID', $user_id, 40);
     oci_bind_by_name($stmt, ':P_OSR_CONSUMER_KEY', $options['osr_consumer_key'], 255);
     oci_bind_by_name($stmt, ':P_OCR_CONSUMER_KEY', $options['ocr_consumer_key'], 255);
     oci_bind_by_name($stmt, ':P_OST_TOKEN', $options['ost_token'], 255);
     oci_bind_by_name($stmt, ':P_OCT_TOKEN', $options['oct_token'], 255);
     oci_bind_by_name($stmt, ':P_RESULT', $result, 20);
     //Bind the ref cursor
     $p_row = oci_new_cursor($this->conn);
     oci_bind_by_name($stmt, ':P_ROWS', $p_row, -1, OCI_B_CURSOR);
     //Execute the statement
     oci_execute($stmt);
     // treat the ref cursor as a statement resource
     oci_execute($p_row, OCI_DEFAULT);
     oci_fetch_all($p_row, $rs, null, null, OCI_FETCHSTATEMENT_BY_ROW);
     return $rs;
 }
Example #23
0
function set_permission($postdata)
{
    global $connection;
    $cursor = oci_new_cursor($connection);
    $userId = $postdata->data->userId;
    $ruleId = $postdata->data->ruleId;
    $enabled = $postdata->data->enabled;
    $result = new stdClass();
    if (!($statement = oci_parse($connection, "begin pkg_users.p_set_user_permission(:user_id, :rule_id, :enabled, :permission); end;"))) {
        $error = oci_error();
        $result = new DBError($error["code"], $error["message"]);
        echo json_encode($result);
    } else {
        if (!oci_bind_by_name($statement, ":user_id", $userId, -1, OCI_DEFAULT)) {
            $error = oci_error();
            $result = new DBError($error["code"], $error["message"]);
            echo json_encode($result);
        }
        if (!oci_bind_by_name($statement, ":rule_id", $ruleId, -1, OCI_DEFAULT)) {
            $error = oci_error();
            $result = new DBError($error["code"], $error["message"]);
            echo json_encode($result);
        }
        if (!oci_bind_by_name($statement, ":enabled", $enabled, -1, OCI_DEFAULT)) {
            $error = oci_error();
            $result = new DBError($error["code"], $error["message"]);
            echo json_encode($result);
        }
        if (!oci_bind_by_name($statement, ":permission", $cursor, -1, OCI_B_CURSOR)) {
            $error = oci_error();
            $result = new DBError($error["code"], $error["message"]);
            echo json_encode($result);
        }
        if (!oci_execute($statement)) {
            $error = oci_error();
            $result = new DBError($error["code"], $error["message"]);
            echo json_encode($result);
        } else {
            if (!oci_execute($cursor)) {
                $error = oci_error();
                $result = new DBError($error["code"], $error["message"]);
                echo json_encode($result);
            } else {
                $result = oci_fetch_object($cursor);
            }
        }
    }
    /* Освобождение ресурсов */
    oci_free_statement($statement);
    oci_free_statement($cursor);
    /* Возврат результата */
    echo json_encode($result);
}
Example #24
0
 /**
  * Special non PDO function used to start cursors in the database
  * Remember to call oci_free_statement() on your cursor
  *
  * @access public
  *
  * @return mixed New statement handle, or FALSE on error.
  */
 public function getNewCursor()
 {
     return oci_new_cursor($this->_dbh);
 }
		
    </ul>

  </div>
  </div>
</div>

<div id="wrapper">
  <!-- start page -->
  <div id="page">
    <!-- start content -->
    <div id="content">
      <div class="post">
<?php 
include "PHP/connect.php";
$cursor = oci_new_cursor($conn);
$stmt = oci_parse($conn, "begin P_AUTOR.selByNome(:p_aut_nome, :error, :errorMsg, :result); end;");
oci_bind_by_name($stmt, ":p_aut_nome", $_REQUEST["p_aut_nome"]);
oci_bind_by_name($stmt, ":error", $error, 3);
oci_bind_by_name($stmt, ":errorMsg", $errorMsg, 512);
oci_bind_by_name($stmt, ":result", $cursor, -1, OCI_B_CURSOR);
oci_execute($stmt);
if ($error != 0) {
    echo "<h2 class=\"erro\">Erro: {$errorMsg}</h2>";
} else {
    echo "\n<table> \n<tr>\n<th>Nome</th>\n<th>Data de Nascimento</th>\n<th>&nbsp;</th>\n</tr>";
    oci_execute($cursor);
    while ($result = oci_fetch_object($cursor)) {
        echo "\n<tr>\n<td>{$result->AUT_NOME}</td>\n<td>{$result->AUT_DT_NASC}</td>\n</tr>";
    }
    echo "</table>";
Example #26
0
     $error = oci_error();
     $result = new DBError($error["code"], $error["message"]);
     echo json_encode($result);
 }
 if (!oci_execute($statement)) {
     $error = oci_error();
     $result = new DBError($error["code"], $error["message"]);
     echo json_encode($result);
 } else {
     if (!oci_execute($cursor)) {
         $error = oci_error();
         $result = new DBError($error["code"], $error["message"]);
         echo json_encode($result);
     } else {
         while ($request = oci_fetch_assoc($cursor)) {
             $cursor2 = oci_new_cursor($connection);
             if (!($statement2 = oci_parse($connection, "begin pkg_titules.p_get_tu_doc(:r_id, :tu); end;"))) {
                 $error = oci_error();
                 $result = new DBError($error["code"], $error["message"]);
                 echo json_encode($result);
             } else {
                 if (!oci_bind_by_name($statement2, ":r_id", $request["ID"], -1, OCI_DEFAULT)) {
                     $error = oci_error();
                     $result = new DBError($error["code"], $error["message"]);
                     echo json_encode($result);
                 }
                 if (!oci_bind_by_name($statement2, ":tu", $cursor2, -1, OCI_B_CURSOR)) {
                     $error = oci_error();
                     $result = new DBError($error["code"], $error["message"]);
                     echo json_encode($result);
                 }
Example #27
0
 public function getAll()
 {
     $respuesta = array();
     require_once 'DataBase.php';
     $data = new DataBase();
     $data->open();
     $sql = "BEGIN GETALLRUTAS(:rc); END;";
     $sent = oci_parse($data->getConn(), $sql);
     $refcur = oci_new_cursor($data->getConn());
     oci_bind_by_name($sent, ':rc', $refcur, -1, OCI_B_CURSOR);
     if (!oci_execute($sent)) {
         return false;
     }
     if (!oci_execute($refcur)) {
         return false;
     }
     while (($row = oci_fetch_array($refcur, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
         $elemento = new Ruta();
         $elemento->id = $row['ID_RUTA'];
         $elemento->nombre = $row['NOMBRE'];
         $respuesta[] = $elemento;
     }
     oci_free_statement($refcur);
     $data->free($sent);
     $data->close();
     return $respuesta;
 }
Example #28
0
 public function actionLazyLoad()
 {
     if ($_GET['num'] != '') {
         $num = $_GET['num'];
     }
     $conn = Yii::app()->oracle->getOracleConnection();
     /*$oracle_connect = Yii::app()->oracle;
       $conn = oci_connect($oracle_connect->username,$oracle_connect->password,$oracle_connect->host, $oracle_connect->charset);*/
     $curs = oci_new_cursor($conn);
     $rows = array();
     $s = oci_parse($conn, "begin :p2:=" . Yii::app()->oracle->cursor . ".GET_DOCUMENT(:jsontext); end;");
     $firstvisiblerow = 0;
     $lastvisiblerow = 20;
     $rowscount = $lastvisiblerow - $firstvisiblerow;
     $jscontext = '{"CRT" : {"DOC_VID" : "3"}, "LIM" : {"START" : "' . $num . '", "LENGTH" : "' . $rowscount . '"}}';
     oci_bind_by_name($s, ":p2", $curs, -1, OCI_B_CURSOR);
     oci_bind_by_name($s, ":jsontext", $jscontext, -1, SQLT_CHR);
     oci_execute($s);
     oci_execute($curs);
     while ($row = oci_fetch_array($curs, OCI_ASSOC)) {
         $rows[] = $row;
     }
     //print_r($rows);
     echo json_encode($rows);
 }
Example #29
0
 function Prepare($sql, $cursor = false)
 {
     static $BINDNUM = 0;
     $stmt = oci_parse($this->_connectionID, $sql);
     if (!$stmt) {
         $this->_errorMsg = false;
         $this->_errorCode = false;
         $arr = @oci_error($this->_connectionID);
         if ($arr === false) {
             return false;
         }
         $this->_errorMsg = $arr['message'];
         $this->_errorCode = $arr['code'];
         return false;
     }
     $BINDNUM += 1;
     $sttype = @oci_statement_type($stmt);
     if ($sttype == 'BEGIN' || $sttype == 'DECLARE') {
         return array($sql, $stmt, 0, $BINDNUM, $cursor ? oci_new_cursor($this->_connectionID) : false);
     }
     return array($sql, $stmt, 0, $BINDNUM);
 }
 public function exec_proc($proc, $param, $exportReq)
 {
     $cursor = oci_new_cursor($this->conn);
     for ($i = 0; $i < count($param); $i++) {
         $this->proc_param_list .= "p{$i}=>:param{$i}, ";
     }
     $sql = "begin\n\t\t\t\t{$proc} ({$this->proc_param_list} ref_cur_out=>:result); \n\t\t\t\tend;";
     $parse = oci_parse($this->conn, $sql);
     oci_bind_by_name($parse, ':result', $cursor, -1, OCI_B_CURSOR);
     # Bind input parameters to stored procedure call ($param)
     for ($i = 0; $i < count($param); $i++) {
         oci_bind_by_name($parse, ':param' . $i, $param[$i]);
     }
     oci_execute($parse);
     oci_execute($cursor);
     if ($exportReq) {
         // CSV export
         oci_fetch_all($cursor, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM);
         foreach ($res as $key => $value) {
             // Remove 'ORDERING' from resultset
             array_pop($res[$key]);
         }
         return $res;
     } else {
         oci_fetch_all($cursor, $res, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
         array_shift($res);
         // Remove 'header' row foreach entry on json req
         foreach ($res as $key => $value) {
             // Remove 'ORDERING' from resultset
             unset($res[$key]['ORDERING']);
         }
         return json_encode($res, JSON_PRETTY_PRINT);
     }
 }