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;
}
}
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;
}
/**
* 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;
}
/**
* @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);
}
}
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;
}
/**
* 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";
function _set_curs_id($name = '')
{
$this->curs_id[$name] = oci_new_cursor($this->conn_id);
return $this->curs_id[$name];
}
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);
}
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;
}
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);
}
}
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'];
} */
}
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;
}
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("&", "&", $value);
$value = str_replace("<", "<", $value);
$value = str_replace(">", ">", $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;
}
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();
}
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;
}
/**
* Get cursor. Returns a cursor from the database
*
* @return resource
*/
public function get_cursor()
{
return $this->curs_id = oci_new_cursor($this->conn_id);
}
/**
* 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;
}
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);
}
/**
* 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> </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>";
$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);
}
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;
}
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);
}
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);
}
}
