10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Greetings.
I have script to monitor the disk space of folder it runs every 17 min with help of cron. It sends email when disk size reaches to 85 %. Now the issue is that it continousely generates email until we clear some space in that folder. Is it possible to restrict the Script to send only... (14 Replies)
Discussion started by: manju98458
14 Replies
2. UNIX for Advanced & Expert Users
Hi,
I have a Debian 6 machine running Postfix 2.7.1. The email server works pretty well. I discovered that any non-root user can access to the mail queue using postqueue command just like root. How can I limit this access? (1 Reply)
Discussion started by: mjdousti
1 Replies
3. Solaris
I'm looking for a way to limit connections to a Solaris 10 box from any single IP.
The problem is that I've had more experience doing this with IPTables on Linux, rather than with IPFilter, which I've found to be somewhat feature-poor.
I hope there is some way to do this using IPFilter, I've... (2 Replies)
Discussion started by: spynappels
2 Replies
4. Shell Programming and Scripting
Hello ,
When using vim, can ctag and cscope support recording search results and displaying the history results ? Once I jump to one tag, I can use :tnext to jump to next tag, but how can I display the preview search result? (0 Replies)
Discussion started by: 915086731
0 Replies
5. Shell Programming and Scripting
Hi,
I've a file which is "|" delimited. I want to replace the first field with rownum, how can I do it with awk or sed command? There is no header record.
sample file
1195|ABC|DEF|30 LATE DR||CHARLOTTE|NC||NONE@NONE.COM|I|1
1227|PQR|STU|3 KING DR||LIVONIA|NJ|481524071|YAHOO@YAHOO.COM|I|3... (4 Replies)
Discussion started by: rudoraj
4 Replies
6. Solaris
Hello,
Using a Solaris SunOS 5.10, is there anyway to limit the number of processors utilised by an external vendor application over the server, from the unix OS perspective? (1 Reply)
Discussion started by: pgop
1 Replies
7. HP-UX
Hi,
I have searched the web and have come back with nothing that is satisfactory for what I require. SFTP is my corporations new file transfer standard. What I require is a method to lock down SFTP users to their directory (they may go to sub directories) while not restricting regular users. ... (2 Replies)
Discussion started by: Emancipator
2 Replies
8. HP-UX
I have tried limiting failed logins to three by the following method
logins -ox \ | awk -F: '($8 != "LK" && $1 != "root") { print $1 }' \ | while read logname; do
/usr/lbin/modprpw -m umaxlntr=3 "$logname"
done
/usr/lbin/modprdef -m umaxlntr=3
but it is failing on the 4th... any ideas?... (1 Reply)
Discussion started by: csaunders
1 Replies
9. Cybersecurity
I am migrating from 5.2 to 5.3 AIX. In previous versions of AIX, including 5.2, I've been able to limit user's logins to 1 by using the following script named Block_user:
#!/bin/ksh
USER=$1
NUM=`who | grep $USER | cut -c1-8 | wc -l`
#The above ' is not a single quote but back quote
if ]... (2 Replies)
Discussion started by: Confused_lulu
2 Replies
10. UNIX for Dummies Questions & Answers
Hi, I'm new to linux and unix, and i have couple of problems:
1) how can i limit the access for a user, for example, i created a user, and i want that this user will be able to be only in one directory, and will see only the files i want him to.
2) I have a domain name, and i want that every... (4 Replies)
Discussion started by: misha
4 Replies
OCI_FETCH_ARRAY(3) OCI_FETCH_ARRAY(3)
oci_fetch_array - Returns the next row from a query as an associative or numeric array
SYNOPSIS
array oci_fetch_array (resource $statement, [int $mode])
DESCRIPTION
Returns an array containing the next result-set row of a query. Each array entry corresponds to a column of the row. This function is typ-
ically called in a loop until it returns FALSE, indicating no more rows exist.
If $statement corresponds to a PL/SQL block returning Oracle Database 12c Implicit Result Sets, then rows from all sets are consecutively
fetched. If $statement is returned by oci_get_implicit_resultset(3), then only the subset of rows for one child query are returned.
For details on the data type mapping performed by the OCI8 extension, see the datatypes supported by the driver
PARAMETERS
o $statement
-A valid OCI8 statement identifier created by oci_parse(3) and executed by oci_execute(3), or a REF CURSOR statement identifier.
Can also be a statement identifier returned by oci_get_implicit_resultset(3).
o $mode
- An optional second parameter can be any combination of the following constants:
oci_fetch_array(3) Modes
+-----------------+---------------------------------------------------+
| Constant | |
| | |
| | Description |
| | |
+-----------------+---------------------------------------------------+
| | |
| OCI_BOTH | |
| | |
| | Returns an array with both associative and |
| | numeric indices. This is the same as OCI_ASSOC + |
| | OCI_NUM and is the default behavior. |
| | |
| | |
| OCI_ASSOC | |
| | |
| | Returns an associative array. |
| | |
| | |
| OCI_NUM | |
| | |
| | Returns a numeric array. |
| | |
| | |
|OCI_RETURN_NULLS | |
| | |
| | Creates elements for NULL fields. The element |
| | values will be a PHP NULL. |
| | |
| | |
|OCI_RETURN_LOBS | |
| | |
| | Returns the contents of LOBs instead of the LOB |
| | descriptors. |
| | |
+-----------------+---------------------------------------------------+
The default $mode is OCI_BOTH. Use the addition operator "+" to specify more than one mode at a time.
RETURN VALUES
Returns an array with associative and/or numeric indices. If there are no more rows in the $statement then FALSE is returned.
By default, LOB columns are returned as LOB descriptors.
DATE columns are returned as strings formatted to the current date format. The default format can be changed with Oracle environment vari-
ables such as NLS_LANG or by a previously executed ALTER SESSION SET NLS_DATE_FORMAT command.
Oracle's default, non-case sensitive column names will have uppercase associative indices in the result array. Case-sensitive column names
will have array indices using the exact column case. Use var_dump(3) on the result array to verify the appropriate case to use for each
query.
The table name is not included in the array index. If your query contains two different columns with the same name, use OCI_NUM or add a
column alias to the query to ensure name uniqueness, see example #7. Otherwise only one column will be returned via PHP.
EXAMPLES
Example #1
oci_fetch_array(3) with OCI_BOTH
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT department_id, department_name FROM departments');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) {
// Use the uppercase column names for the associative array indices
echo $row[0] . " and " . $row['DEPARTMENT_ID'] . " are the same<br>
";
echo $row[1] . " and " . $row['DEPARTMENT_NAME'] . " are the same<br>
";
}
oci_free_statement($stid);
oci_close($conn);
?>
Example #2
oci_fetch_array(3) with OCI_NUM
<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description CLOB);
INSERT INTO mytab (id, description) values (1, 'A very long string');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_NUM)) != false) {
echo $row[0] . "<br>
";
echo $row[1]->read(11) . "<br>
"; // this will output first 11 bytes from DESCRIPTION
}
// Output is:
// 1
// A very long
oci_free_statement($stid);
oci_close($conn);
?>
Example #3
oci_fetch_array(3) with OCI_ASSOC
<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description CLOB);
INSERT INTO mytab (id, description) values (1, 'A very long string');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC)) != false) {
echo $row['ID'] . "<br>
";
echo $row['DESCRIPTION']->read(11) . "<br>
"; // this will output first 11 bytes from DESCRIPTION
}
// Output is:
// 1
// A very long
oci_free_statement($stid);
oci_close($conn);
?>
Example #4
oci_fetch_array(3) with OCI_RETURN_NULLS
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT 1, null FROM dual');
oci_execute($stid);
while (($row = oci_fetch_array ($stid, OCI_ASSOC)) != false) { // Ignore NULLs
var_dump($row);
}
/*
The above code prints:
array(1) {
[1]=>
string(1) "1"
}
*/
$stid = oci_parse($conn, 'SELECT 1, null FROM dual');
oci_execute($stid);
while (($row = oci_fetch_array ($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) { // Fetch NULLs
var_dump($row);
}
/*
The above code prints:
array(2) {
[1]=>
string(1) "1"
["NULL"]=>
NULL
}
*/
?>
Example #5
oci_fetch_array(3) with OCI_RETURN_LOBS
<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description CLOB);
INSERT INTO mytab (id, description) values (1, 'A very long string');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) != false) {
echo $row['ID'] . "<br>
";
echo $row['DESCRIPTION'] . "<br>
"; // this contains all of DESCRIPTION
// In a loop, freeing the large variable before the 2nd fetch reduces PHP's peak memory usage
unset($row);
}
// Output is:
// 1
// A very long string
oci_free_statement($stid);
oci_close($conn);
?>
Example #6
oci_fetch_array(3) with case sensitive column names
<?php
/*
Before running, create the table:
CREATE TABLE mytab ("Name" VARCHAR2(20), city VARCHAR2(20));
INSERT INTO mytab ("Name", city) values ('Chris', 'Melbourne');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'select * from mytab');
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
// Because 'Name' was created as a case-sensitive column, that same
// case is used for the array index. However uppercase 'CITY' must
// be used for the case-insensitive column index
print $row['Name'] . "<br>
"; // prints Chris
print $row['CITY'] . "<br>
"; // prints Melbourne
oci_free_statement($stid);
oci_close($conn);
?>
Example #7
oci_fetch_array(3) with columns having duplicate names
<?php
/*
Before running, create the tables:
CREATE TABLE mycity (id NUMBER, name VARCHAR2(20));
INSERT INTO mycity (id, name) values (1, 'Melbourne');
CREATE TABLE mycountry (id NUMBER, name VARCHAR2(20));
INSERT INTO mycountry (id, name) values (1, 'Australia');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$sql = 'SELECT mycity.name, mycountry.name
FROM mycity, mycountry
WHERE mycity.id = mycountry.id';
$stid = oci_parse($conn, $sql);
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC);
var_dump($row);
// Output only contains one "NAME" entry:
// array(1) {
// ["NAME"]=>
// string(9) "Australia"
// }
// To query a repeated column name, use an SQL column alias like "AS ctnm":
$sql = 'SELECT mycity.name AS ctnm, mycountry.name
FROM mycity, mycountry
WHERE mycity.id = mycountry.id';
$stid = oci_parse($conn, $sql);
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC);
var_dump($row);
// Output now contains both columns selected:
// array(2) {
// ["CTNM"]=>
// string(9) "Melbourne"
// ["NAME"]=>
// string(9) "Australia"
// }
oci_free_statement($stid);
oci_close($conn);
?>
Example #8
oci_fetch_array(3) with DATE columns
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Set the date format for this connection.
// For performance reasons, consider changing the format
// in a trigger or with environment variables instead
$stid = oci_parse($conn, "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
oci_execute($stid);
$stid = oci_parse($conn, 'SELECT hire_date FROM employees WHERE employee_id = 188');
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC);
echo $row['HIRE_DATE'] . "<br>
"; // prints 1997-06-14
oci_free_statement($stid);
oci_close($conn);
?>
Example #9
oci_fetch_array(3) with REF CURSOR
<?php
/*
Create the PL/SQL stored procedure as:
CREATE OR REPLACE PROCEDURE myproc(p1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;
END;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'BEGIN myproc(:rc); END;');
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// Execute the returned REF CURSOR and fetch from it like a statement identifier
oci_execute($refcur);
echo "<table border='1'>
";
while (($row = oci_fetch_array($refcur, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
echo "<tr>
";
foreach ($row as $item) {
echo " <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : " ")."</td>
";
}
echo "</tr>
";
}
echo "</table>
";
oci_free_statement($refcur);
oci_free_statement($stid);
oci_close($conn);
?>
Example #10
Pagination with oci_fetch_array(3) using a LIMIT-like query
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Find the version of the database
preg_match('/Release ([0-9]+)./', oci_server_version($conn), $matches);
$oracleversion = $matches[1];
// This is the query you want to "page" through
$sql = 'SELECT city, postal_code FROM locations ORDER BY city';
if ($oracleversion >= 12) {
// Make use of Oracle 12c OFFSET / FETCH NEXT syntax
$sql = $sql . ' OFFSET :offset ROWS FETCH NEXT :numrows ROWS ONLY';
} else {
// Older Oracle versions need a nested query selecting a subset
// from $sql. Or, if the SQL statement is known at development
// time, consider using a row_number() function instead of this
// nested solution. In production environments, be careful to
// avoid SQL Injection issues with concatenation.
$sql = "SELECT * FROM (SELECT a.*, ROWNUM AS my_rnum
FROM ($sql) a
WHERE ROWNUM <= :offset + :numrows)
WHERE my_rnum > :offset";
}
$offset = 0; // skip this many rows
$numrows = 5; // return 5 rows
$stid = oci_parse($conn, $sql);
oci_bind_by_name($stid, ':numrows', $numrows);
oci_bind_by_name($stid, ':offset', $offset);
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
echo $row['CITY'] . " " . $row['POSTAL_CODE'] . "<br>
";
}
// Output is:
// Beijing 190518
// Bern 3095
// Bombay 490231
// Geneva 1730
// Hiroshima 6823
oci_free_statement($stid);
oci_close($conn);
?>
Example #11
oci_fetch_array(3) with Oracle Database 12 c Implicit Result Sets
<?php
$conn = oci_connect('hr', 'welcome', 'localhost/pdborcl');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Requires OCI8 2.0 and Oracle Database 12c
// Also see oci_get_implicit_resultset()
$sql = 'DECLARE
c1 SYS_REFCURSOR;
BEGIN
OPEN c1 FOR SELECT city, postal_code FROM locations WHERE ROWNUM < 4 ORDER BY city;
DBMS_SQL.RETURN_RESULT(c1);
OPEN c1 FOR SELECT country_id FROM locations WHERE ROWNUM < 4 ORDER BY city;
DBMS_SQL.RETURN_RESULT(c1);
END;';
$stid = oci_parse($conn, $sql);
oci_execute($stid);
// Note: oci_fetch_all and oci_fetch() cannot be used in this manner
echo "<table>
";
while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
echo "<tr>
";
foreach ($row as $item) {
echo " <td>".($item!==null?htmlentities($item, ENT_QUOTES|ENT_SUBSTITUTE):" ")."</td>
";
}
echo "</tr>
";
}
echo "</table>
";
// Output is:
// Beijing 190518
// Bern 3095
// Bombay 490231
// CN
// CH
// IN
oci_free_statement($stid);
oci_close($conn);
?>
NOTES
Note
Associative array indices need to be in uppercase for standard Oracle columns that were created with case insensitive names.
Note
For queries returning a large number of rows, performance can be significantly improved by increasing oci8.default_prefetch or using
oci_set_prefetch(3).
Note
The function oci_fetch_array(3) is insignificantly slower than oci_fetch_assoc(3) or oci_fetch_row(3), but is more flexible.
SEE ALSO
oci_fetch(3), oci_fetch_all(3), oci_fetch_assoc(3), oci_fetch_object(3), oci_fetch_row(3), oci_set_prefetch(3).
PHP Documentation Group OCI_FETCH_ARRAY(3)
