API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 29th, 2016
109
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 18.94 KB | None | 0 0
raw download clone embed print report
  1. <?
  2. define('ORA_CHARSET_DEFAULT', 'SPANISH_SPAIN.AL32UTF8');
  3. define('ORA_CONNECTION_TYPE_DEFAULT', 1);
  4. define('ORA_CONNECTION_TYPE_PERSISTENT', 2);
  5. define('ORA_CONNECTION_TYPE_NEW', 3);
  6. define('ORA_MESSAGES_NOT_CONNECTED', 'Not connected to Oracle instance');
  7.  
  8. class ORACLE {
  9. private static $_instance;
  10. private $conn_handle;
  11. private $conn_data;
  12. private $errors_pool;
  13. private $statements = array();
  14. private $autocommit = false;
  15. private $fetch_mode = OCI_BOTH;
  16. private $last_query;
  17. private $var_max_size = 1000;
  18. private $execute_status = false;
  19. private $charset;
  20. private $session_mode = OCI_DEFAULT;
  21.  
  22. /**
  23. * Set array fetching mode for Fetch methods
  24. *
  25. * @param mixed $mode
  26. */
  27. public function SetFetchMode($mode = OCI_BOTH){
  28. $this->fetch_mode = $mode;
  29. }
  30.  
  31. /**
  32. * Set on|off auto commit option
  33. *
  34. * @param bool $mode
  35. */
  36. public function SetAutoCommit($mode = true){
  37. $this->autocommit = $mode;
  38. }
  39.  
  40. /**
  41. * Set variable max size for binding
  42. *
  43. * @param int $size
  44. */
  45. public function SetVarMaxSize($size){
  46. $this->var_max_size = $size;
  47. }
  48.  
  49. /**
  50. * Returns the last error found.
  51. *
  52. */
  53. public function GetError(){
  54. return @oci_error($this->conn_handle);
  55. }
  56.  
  57. /**
  58. * Set nls_lang
  59. *
  60. * @param string $charset
  61. */
  62. public function SetNlsLang($charset = ORA_CHARSET_DEFAULT){
  63. $this->charset = $charset;
  64. }
  65.  
  66. /**
  67. * Constructor
  68. *
  69. */
  70. public function __construct(){
  71. $this->SetNlsLang('CL8MSWIN1251');
  72. $this->SetFetchMode(OCI_ASSOC);
  73. $this->SetAutoCommit(false);
  74. }
  75.  
  76. /**
  77. * Connect to Oracle DB
  78. *
  79. * @param string $host
  80. * @param string $user
  81. * @param string $pass
  82. * @param int $mode (OCI_DEFAULT, OCI_SYSDBA, OCI_SYSOPER)
  83. * @param int $type (ORA_CONNECTION_TYPE_DEFAULT, ORA_CONNECTION_TYPE_NEW, ORA_CONNECTION_TYPE_PERSISTENT)
  84. * @return bool
  85. */
  86. public function Connect($host = 'localhost', $user='', $pass='', $mode = OCI_DEFAULT, $type = ORA_CONNECTION_TYPE_DEFAULT){
  87. switch ($type) {
  88. case ORA_CONNECTION_TYPE_PERSISTENT: {
  89. $this->conn_handle = oci_pconnect($user, $pass, $host, $this->charset, $mode);
  90. }; break;
  91. case ORA_CONNECTION_TYPE_NEW: {
  92. $this->conn_handle = oci_new_connect($user, $pass, $host, $this->charset, $mode);
  93. }; break;
  94. default:
  95. $this->conn_handle = oci_connect($user, $pass, $host, $this->charset, $mode);
  96. }
  97. return is_resource($this->conn_handle) ? true : false;
  98. }
  99.  
  100. /**
  101. * Destructor
  102. *
  103. */
  104. public function __destruct(){
  105. if (is_resource($this->conn_handle)) {
  106. @oci_close($this->conn_handle);
  107. }
  108. }
  109.  
  110. /**
  111. * This function return last command exec status
  112. *
  113. * @return bool
  114. */
  115. public function GetExecuteStatus(){
  116. return $this->execute_status;
  117. }
  118.  
  119. private function GetBindingType($var){
  120. if (is_a($var, "OCI-Collection")) {
  121. $bind_type = SQLT_NTY;
  122. $this->SetVarMaxSize(-1);
  123. } elseif (is_a($var, "OCI-Lob")) {
  124. $bind_type = SQLT_CLOB;
  125. $this->SetVarMaxSize(-1);
  126. } else {
  127. $bind_type = SQLT_CHR;
  128. }
  129. return $bind_type;
  130. }
  131.  
  132. /**
  133. * Private method for execute any sql query or pl/sql
  134. *
  135. * @param string $sql_text
  136. * @param array | false $bind
  137. * @return resource | false
  138. */
  139. private function Execute($sql_text, &$bind = false){
  140. if (!is_resource($this->conn_handle)) return false;
  141. $this->last_query = $sql_text;
  142.  
  143. $stid = @oci_parse($this->conn_handle, $sql_text);
  144.  
  145. $this->statements[$stid]['text'] = $sql_text;
  146. $this->statements[$stid]['bind'] = $bind;
  147.  
  148. if ($bind && is_array($bind)) {
  149. foreach($bind as $k=>$v){
  150. oci_bind_by_name($stid, $k, $bind[$k], $this->var_max_size, $this->GetBindingType($bind[$k]));
  151. }
  152. }
  153. $com_mode = $this->autocommit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT;
  154. $this->execute_status = oci_execute($stid, $com_mode);
  155. return $this->execute_status ? $stid : false;
  156. }
  157.  
  158. /**
  159. * select command wraooer
  160. *
  161. * @param string $sql the query text
  162. * @param array | false $bind array of pairs binding variables
  163. * @return resource | false
  164. */
  165. public function Select($sql, $bind = false){
  166. return $this->Execute($sql, $bind);
  167. }
  168.  
  169. /**
  170. * Fetch array of select statement
  171. *
  172. * @param resource $statement valid OCI statement id
  173. * @return array
  174. */
  175. public function FetchArray($statement){
  176. return oci_fetch_array($statement, $this->fetch_mode);
  177. }
  178.  
  179. /**
  180. * Function Returns a numerically indexed array containing the next result-set row of a query.
  181. * Each array entry corresponds to a column of the row. This function is typically called in a loop
  182. * until it returns FALSE, indicating no more rows exist.
  183. *
  184. * @param resource $statement valid OCI statement id
  185. * @return array Returns a numerically indexed array. If there are no more rows in the statement then FALSE is returned.
  186. */
  187. public function FetchRow($statement){
  188. return oci_fetch_row($statement);
  189. }
  190.  
  191. /**
  192. * Fetch rows from select operation
  193. *
  194. * @param resourse $statement valid OCI statement identifier
  195. * @param int $skip number of initial rows to ignore when fetching the result (default value of 0, to start at the first line).
  196. * @param int $maxrows number of rows to read, starting at the skip th row (default to -1, meaning all the rows).
  197. * $return array
  198. */
  199. public function FetchAll($statement, $skip = 0, $maxrows = -1){
  200. $rows = array();
  201. oci_fetch_all($statement, $rows, $skip, $maxrows, OCI_FETCHSTATEMENT_BY_ROW);
  202. return $rows;
  203. }
  204.  
  205. /**
  206. * Fetch row as object
  207. *
  208. * @param resource $statement valid OCI statement identifier
  209. * @return object
  210. * @author Sergey Pimenov
  211. */
  212. public function FetchObject($statement){
  213. return oci_fetch_object($statement);
  214. }
  215.  
  216. /**
  217. * Fetches the next row (for SELECT statements) into the internal result-buffer.
  218. *
  219. * @param resource $statement valid OCI statement id
  220. * @return bool
  221. */
  222. public function Fetch($statement){
  223. return oci_fetch($statement);
  224. }
  225.  
  226. /**
  227. * Returns the data from field in the current row, fetched by Fetch()
  228. *
  229. * @param resource $statement valid OCI statement id
  230. * @param mixed $field Can be either use the column number (1-based) or the column name (in uppercase).
  231. * @return mixed
  232. */
  233. public function Result($statement, $field){
  234. return oci_result($statement, $field);
  235. }
  236.  
  237. /**
  238. * Associates a PHP variable with a column for query fetches using Fetch().
  239. *
  240. * @param resource $statement A valid OCI statement identifier
  241. * @param string $column_name The column name used in the query.
  242. * @param mixed $variable The PHP variable that will contain the returned column value.
  243. * @param int $type The data type to be returned.
  244. * @return bool
  245. */
  246. public function DefineByName($statement , $column_name , &$variable, $type = SQLT_CHR){
  247. return oci_define_by_name($statement, $column_name, $variable, $type);
  248. }
  249.  
  250. public function FieldIsNull($statement, $field){
  251. return oci_field_is_null($statement, $field);
  252. }
  253.  
  254. public function FieldName($statement, int $field){
  255. return oci_field_name($statement, $field);
  256. }
  257.  
  258. public function FieldPrecition($statement, int $field){
  259. return oci_field_precision($statement, $field);
  260. }
  261.  
  262. public function FieldScale($statement, int $field){
  263. return oci_field_scale($statement, $field);
  264. }
  265.  
  266. public function FieldSize($statement, $field){
  267. return oci_field_size($statement, $field);
  268. }
  269.  
  270. public function FieldTypeRaw($statement, int $field){
  271. return oci_field_type_raw($statement, $field);
  272. }
  273.  
  274. public function FieldType($statement, int $field){
  275. return oci_field_type($statement, $field);
  276. }
  277.  
  278. /**
  279. * Insert row into table
  280. *
  281. * @param string $table name of table
  282. * @param array $arrayFieldsValues define pair field => value
  283. * @param array $bind define pairs holder => value for binding
  284. * @param array $returning define fields for returning clause in insert statement
  285. * @return mixed if $returnig is defined function return array of fields defined in $returning
  286. * @author Sergey Pimenov
  287. */
  288. public function Insert($table, $arrayFieldsValues, &$bind = false, $returning = false){
  289. if (empty($arrayFieldsValues)) return false;
  290. $fields = array();
  291. $values = array();
  292. foreach($arrayFieldsValues as $f=>$v){
  293. $fields[] = $f;
  294. $values[] = $v;
  295. }
  296. $fields = implode(",", $fields);
  297. $values = implode(",", $values);
  298. $ret = "";
  299. if ($returning) {
  300. foreach($returning as $f=>$h){
  301. $ret_fields[] = $f;
  302. $ret_binds[] = ":$h";
  303. $bind[":$h"] = "";
  304. }
  305. $ret = " returning ".(implode(",", $ret_fields))." into ".(implode(",",$ret_binds));
  306. }
  307. $sql = "insert into $table ($fields) values($values) $ret";
  308. $result = $this->Execute($sql, $bind);
  309. if ($result === false) return false;
  310. if ($returning === false) {
  311. return $result;
  312. } else {
  313. $result = array();
  314. foreach($returning as $f=>$h){
  315. $result[$f] = $bind[":$h"];
  316. }
  317. return $result;
  318. }
  319. }
  320.  
  321. /**
  322. * Method for update data in table
  323. *
  324. * @param string $table
  325. * @param array $arrayFieldsValues
  326. * @param string | false $condition
  327. * @param array | false $bind
  328. * @return resource
  329. */
  330. public function Update($table, $arrayFieldsValues, $condition = false, &$bind = false, $returning = false){
  331. if (empty($arrayFieldsValues)) return false;
  332. $fields = array();
  333. $values = array();
  334. foreach($arrayFieldsValues as $f=>$v){
  335. $fields[] = "$f = $v";
  336. }
  337. $fields = implode(",", $fields);
  338. if ($condition === false) { $condition = "true";}
  339. $ret = "";
  340. if ($returning) {
  341. foreach($returning as $f=>$h){
  342. $ret_fields[] = $f;
  343. $ret_binds[] = ":$h";
  344. $bind[":$h"] = "";
  345. }
  346. $ret = " returning ".(implode(",", $ret_fields))." into ".(implode(",",$ret_binds));
  347. }
  348. $sql = "update $table set $fields where $condition $ret";
  349. $result = $this->Execute($sql, $bind);
  350. if ($result === false) return false;
  351. if ($returning === false) {
  352. return $result;
  353. } else {
  354. $result = array();
  355. foreach($returning as $f=>$h){
  356. $result[$f] = $bind[":$h"];
  357. }
  358. return $result;
  359. }
  360. }
  361.  
  362. public function Delete($table, $condition, &$bind = false, $returning = false){
  363. if ($condition === false) { $condition = "true";}
  364. $ret = "";
  365. if ($returning) {
  366. foreach($returning as $f=>$h){
  367. $ret_fields[] = $f;
  368. $ret_binds[] = ":$h";
  369. $bind[":$h"] = "";
  370. }
  371. $ret = " returning ".(implode(",", $ret_fields))." into ".(implode(",",$ret_binds));
  372. }
  373. $sql = "delete from $table where $condition $ret";
  374. $result = $this->Execute($sql, $bind);
  375. if ($result === false) return false;
  376. if ($returning === false) {
  377. return $result;
  378. } else {
  379. $result = array();
  380. foreach($returning as $f=>$h){
  381. $result[$f] = $bind[":$h"];
  382. }
  383. return $result;
  384. }
  385. }
  386.  
  387. /**
  388. * Gets the number of rows affected during statement execution.
  389. *
  390. * @param resource $statement
  391. * @return int
  392. */
  393. public function NumRows($statement){
  394. return oci_num_rows($statement);
  395. }
  396.  
  397. /**
  398. * Synonym for NumRows()
  399. *
  400. * @param resource $statement
  401. * @return int
  402. */
  403. public function RowsAffected($statement){
  404. return $this->NumRows($statement);
  405. }
  406.  
  407. /**
  408. * Gets the number of columns in the given statement.
  409. *
  410. * @param resource $statement
  411. * @return int
  412. */
  413. public function NumFields($statement){
  414. return oci_num_fields($statement);
  415. }
  416.  
  417. /**
  418. * Synonym for NumFields()
  419. *
  420. * @param resource $statement
  421. * @return int
  422. */
  423. public function FieldsCount($statement){
  424. return $this->NumFields($statement);
  425. }
  426.  
  427. // Support Lob
  428.  
  429. /**
  430. * Allocates resources to hold descriptor or LOB locator.
  431. *
  432. * @param resource $connection
  433. * @param int $type Valid values for type are: OCI_DTYPE_FILE, OCI_DTYPE_LOB and OCI_DTYPE_ROWID.
  434. * @return OCI-Lob
  435. */
  436. public function NewDescriptor($type = OCI_DTYPE_LOB){
  437. return oci_new_descriptor($this->conn_handle, $type);
  438. }
  439.  
  440. /**
  441. * Allocates a new collection object
  442. *
  443. * @param string $typename Should be a valid named type (uppercase).
  444. * @param string $schema Should point to the scheme, where the named type was created. The name of the current user is the default value.
  445. * @return OCI-Collection
  446. */
  447. public function NewCollection($typename, $schema = null){
  448. return oci_new_collection($this->conn_handle, $typename, $schema);
  449. }
  450.  
  451. // Support stored procedures and functions
  452.  
  453. /**
  454. * Method for execute stored procedure
  455. *
  456. * @param mixed $name
  457. * @param string $params
  458. * @param mixed $bind
  459. * @return resource
  460. */
  461. public function StoredProc($name, $params = false, &$bind = false){
  462. if ($params) {
  463. if (is_array($params)) $params = implode(",", $params);
  464. $sql = "begin $name($params); end;";
  465. } else {
  466. $sql = "begin $name; end;";
  467. }
  468. return $this->Execute($sql, $bind);
  469. }
  470.  
  471. /**
  472. * Methos for execute stored function
  473. *
  474. * @param mixed $name
  475. * @param string $params
  476. * @param mixed $bind
  477. * @return mixed
  478. */
  479. public function Func($name, $params = false, $bind = false){
  480. if ($params) {
  481. if (is_array($params)) $params = implode(",", $params);
  482. $sql = "select $name($params) as RESULT from dual";
  483. } else {
  484. $sql = "select $name from dual";
  485. }
  486. $h = $this->Execute($sql, $bind);
  487. $r = $this->FetchArray($h);
  488. return $r['RESULT'];
  489. }
  490.  
  491. /**
  492. * Method execute cursor defined in stored proc
  493. *
  494. * @param string $stored_proc stored proc where cursor is defined
  495. * @param string $bind binding for out parameter in stored proc
  496. * @return resource
  497. * @example Cursor("utils.get_cursor", "dataset"); //begin utils.get_cursor(:dataset); end;
  498. */
  499. public function Cursor($stored_proc, $bind){
  500. if (!is_resource($this->conn_handle)) return false;
  501. $sql = "begin $stored_proc(:$bind); end;";
  502. $curs = oci_new_cursor($this->conn_handle);
  503. $stmt = oci_parse($this->conn_handle, $sql);
  504. oci_bind_by_name($stmt, $bind, $curs, -1, OCI_B_CURSOR);
  505. oci_execute($stmt);
  506. oci_execute($curs);
  507. $this->FreeStatement($stmt);
  508. return $curs;
  509. }
  510.  
  511. /**
  512. * Invalidates a cursor, freeing all associated resources and cancels the ability to read from it.
  513. *
  514. * @param resource $statement valid OCI statement id
  515. * @return bool
  516. */
  517. public function Cancel($statement){
  518. return oci_cancel($statement);
  519. }
  520.  
  521. /**
  522. * Free resource of OCI statement identifier
  523. *
  524. * @param resource $stid
  525. * @return bool
  526. * @author Sergey Pimenov
  527. */
  528. public function FreeStatement($stid){
  529. unset($this->statements[$stid]);
  530. return oci_free_statement($stid);
  531. }
  532.  
  533. /**
  534. * Free array of resources of OCI statement identifier
  535. *
  536. * @param array $array_stid
  537. * @return bool
  538. * @author Sergey Pimenov
  539. */
  540. public function FreeStatements($array_stid){
  541. if (is_array($array_stid)) foreach($array_stid as $stid) {
  542. unset($this->statements[$stid]);
  543. oci_free_statement($stid);
  544. }
  545. return true;
  546. }
  547.  
  548. /**
  549. * Commit transaction
  550. *
  551. * @return bool
  552. * @author Sergey Pimenov
  553. */
  554. public function Commit(){
  555. if (is_resource($this->conn_handle))
  556. return @oci_commit($this->conn_handle);
  557. else
  558. return false;
  559. }
  560.  
  561. /**
  562. * Rollback transaction
  563. *
  564. * @return bool
  565. * @author Sergey Pimenov
  566. */
  567. public function Rollback(){
  568. if (is_resource($this->conn_handle))
  569. return @oci_rollback($this->conn_handle);
  570. else
  571. return false;
  572. }
  573.  
  574. /**
  575. * Enables or disables internal debug output.
  576. *
  577. * @param bool $mode
  578. */
  579. public function InternalDebug($mode){
  580. oci_internal_debug($mode);
  581. }
  582.  
  583.  
  584. public function GetStatement($stid){
  585. return $this->statements[$stid] ? $this->statements[$stid] : false;
  586. }
  587.  
  588. /**
  589. * Get sql text operation
  590. *
  591. * @param resource $stid valid OCI statement id
  592. * @return string
  593. */
  594. public function QuerySnapshot($stid = false){
  595. if ($stid) return $this->statements[$stid]['text']; else return $this->last_query;
  596. }
  597.  
  598. /**
  599. * Get Oracle Server version
  600. *
  601. * @return string | false
  602. */
  603. public function ServerVer(){
  604. if (is_resource($this->conn_handle))
  605. return @oci_server_version($this->conn_handle);
  606. else
  607. return false;
  608. }
  609.  
  610. public function SetAction(string $action_name){
  611. return @oci_set_action($this->conn_handle, $action_name);
  612. }
  613.  
  614. public function SetClientID(string $client_id){
  615. return @oci_set_client_identifier($this->conn_handle, $client_id);
  616. }
  617.  
  618. public function SetClientInfo(string $client_info){
  619. return @oci_set_client_info($this->conn_handle, $client_info);
  620. }
  621.  
  622. public function SepPrefetch(int $rows){
  623. return oci_set_prefetch($this->conn_handle, $rows);
  624. }
  625.  
  626. /**
  627. * Returns a keyword identifying the type of the OCI statement.
  628. *
  629. * @param resource $statement
  630. * @return string (ALTER, BEGIN, CALL, CREATE, DECLARE, DELETE, DROP, INSERT, SELECT, UPDATE, UNKNOWN) return false on error
  631. */
  632. public function StatementType($statement){
  633. return oci_statement_type($statement);
  634. }
  635.  
  636. public function DumpQueriesStack(){
  637. var_dump($this->statements);
  638. }
  639.  
  640. public function Bye(){
  641. $this->__destruct();
  642. }
  643.  
  644. public function get_handle(){
  645. return $this->conn_handle;
  646. }
  647. }
  648. ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!