OracleEditor.php is a standalone PHP script which allows you to browse your Oracle database tables and insert, update, and delete rows in any table. It requires no installation and no configuration. Features: – Connects to any Oracle database (local databases, aliases from tnsnames.ora, or complete connection strings) – Lists all tables and views owned by the user, and those owned by other users where access has been granted – Simply select a table or view to display (SELECT) its records Modify the list of fields to select (optional) – Add a WHERE clause (optional)
<?php
/*
OracleEditor.php
$Revision: 1.20 $
by Tim Strehle <tim@strehle.de>
http://oracleeditor.sourceforge.net/
OracleEditor.php is a standalone PHP script which allows you to browse
your Oracle database tables and insert, update and delete rows in any
table. It requires no installation and no configuration.
I wrote it for situations where you quickly need to do some small data
manipulation and don't have a better tool available. OracleEditor.php is
free and Open Source.
Feel free to contact me at tim@strehle.de with questions/comments.
Disclaimer:
Use this software at your own risk. I won't guarantee that it works, and
I won't be held liable for any damage caused by it!
Please make sure to protect this script with a .htaccess password or the
like - never ever allow public access to it. Anyone capable of guessing
your database login will be able to cause severe damage to your database.
$Id: OracleEditor.php,v 1.20 2006/05/12 21:30:40 tim Exp $
$Log: OracleEditor.php,v $
Revision 1.20 2006/05/12 21:30:40 tim
Allow slash and colon in service name for Oracle Instant Client connection
syntax. Allow dollar sign in user name.
Now works with php.ini setting "short_open_tag = Off".
Revision 1.19 2005/06/28 09:11:54 tim
For UTF-8 detection, use getenv('NLS_LANG') instead of $_SERVER[ 'NLS_LANG' ].
Revision 1.18 2005/06/10 14:49:09 tim
Added PHP version and required module check.
Added "online help" via the HTML title attribute.
Selecting a new table or modifying the SQL statement now cancels export mode.
Make the browser scroll to the row to insert/update/delete row.
When switching from popup to manual SQL entry mode, the current SELECT
statement is used to pre-fill the SQL statement textarea.
Revision 1.17 2004/11/17 11:04:22 tim
Now working with the php.ini setting "magic_quotes_gpc = On" as well.
Added accesskeys to buttons and links and automatically focusing form
fields on page load for fast keyboard navigation.
Added set size "1000".
Displaying some Oracle-related environment variables at the bottom.
Revision 1.16 2004/04/27 12:33:02 tim
Beautified the displayed version string.
Revision 1.15 2004/04/27 12:19:31 tim
Added export format "HTML table".
The table popup now shows other Oracle user's tables if the current user
is allowed to access them.
Use CVS revision number for the version number.
Revision 1.14 2004/01/07 09:59:00 tim
Allow dollar sign in table names.
Revision 1.13 2003/11/28 13:40:39 tim
Fixed empty columns not being exported in CSV format.
Revision 1.12 2003/11/28 13:07:44 tim
Added XML and CSV export.
Session ID is now passed along with GET/POST requests, allowing you to
run OracleEditor in multiple browser windows (which wasn't possible with
the cookie-based session ID).
Fixed password validation bug, which filtered special characters from the
password entered (you couldn't log in if your password was "$pecial").
Added ocisetprefetch(), which might improve performance.
Revision 1.11 2003/10/20 11:10:01 tim
Set HTML character set to UTF-8 when the Oracle connection runs in UTF-8
mode (as defined in the NLS_LANG environment variable).
Revision 1.10 2003/10/10 09:24:39 tim
Cosmetics - fixed "Insert new row" line missing one column.
Revision 1.9 2003/09/26 11:31:02 tim
Fixed rownumber INSERT bug.
Revision 1.8 2003/09/24 08:38:30 tim
Added row number display.
Revision 1.7 2003/08/21 06:45:47 tim
Added "Switch to manual SQL entry" option, allowing you to run arbitrary
SQL including DML/DDL statements.
Revision 1.6 2003/08/05 10:31:00 tim
Display views as well.
Revision 1.5 2003/05/26 09:45:17 tim
Now rows from all tables can be edited, not only from tables which have a primary
key defined. And the primary key need not be in the SELECT clause anymore to
be able to edit rows.
Debug mode logs all SQL statements in the PHP error_log.
Fixed not moving to the first set after changes to the WHERE clause.
Requirements:
PHP 4 (version 4.1 or greater) with Session and OCI8 support.
Works fine with the php.ini setting "register_globals = off",
and regardless of the "magic_quotes_gpc" setting.
*/
// Don't write PHP warnings into HTML. Watch your PHP error_log file!
ini_set('display_errors', 0);
// Format version string
$version = trim(substr('$Revision: 1.20 $', 10, -1));
// Fix magic_quotes_gpc garbage
if (get_magic_quotes_gpc())
{ function stripslashes_deep($value)
{ return (is_array($value) ? array_map('stripslashes_deep', $value) : stripslashes($value));
}
$_REQUEST = array_map('stripslashes_deep', $_REQUEST);
}
// To allow multiple independent OracleEditor sessions,
// propagate session ID in the URL instead of a cookie.
ini_set('session.use_cookies', '0');
// We'll add the session ID to URLs ourselves - disable trans_sid
ini_set('url_rewriter.tags', '');
// Initialize session ID
$sid = '';
if (isset($_REQUEST[ 'sid' ]))
$sid = substr(trim(preg_replace('/[^a-f0-9]/', '', $_REQUEST[ 'sid' ])), 0, 13);
if ($sid == '')
$sid = uniqid('');
// Start PHP session
session_id($sid);
session_name('OracleEditor');
session_start();
$setsizes = array( 10, 25, 50, 100, 1000 );
$exportformats = array(
'xml' => array( 'XML', 'text/xml' ),
'csv' => array( 'CSV', 'text/comma-separated-values' ),
'html' => array( 'HTML table', 'text/html' ),
);
if (! isset($_SESSION[ 'exportformat' ]))
$_SESSION[ 'exportformat' ] = 'xml';
// Initialize database connection parameters
if ((! isset($_SESSION[ 'connection' ])) || isset($_REQUEST[ 'disconnect' ]))
pof_blanksession();
if (isset($_REQUEST[ 'connection' ]))
if (is_array($_REQUEST[ 'connection' ]))
{ pof_blanksession();
if (isset($_REQUEST[ 'connection' ][ 'user' ]))
$_SESSION[ 'connection' ][ 'user' ] = substr(trim(preg_replace('/[^a-zA-Z0-9$_-]/', '', $_REQUEST[ 'connection' ][ 'user' ])), 0, 30);
if (isset($_REQUEST[ 'connection' ][ 'password' ]))
$_SESSION[ 'connection' ][ 'password' ] = substr(trim($_REQUEST[ 'connection' ][ 'password' ]), 0, 30);
if (isset($_REQUEST[ 'connection' ][ 'service' ]))
$_SESSION[ 'connection' ][ 'service' ] = substr(trim(preg_replace('|[^a-zA-Z0-9:.() =/_-]|', '', $_REQUEST[ 'connection' ][ 'service' ])), 0, 2000);
}
// Rather dumb character set detection:
// Try switching to UTF-8 automagically on stuff like "NLS_LANG=american_america.UTF8"
$charset = 'ISO-8859-1';
if (getenv('NLS_LANG'))
if (strtoupper(substr(getenv('NLS_LANG'), -5)) == '.UTF8')
$charset = 'UTF-8';
// Initialize debug mode
if (! isset($_SESSION[ 'debug' ])) $_SESSION[ 'debug' ] = false;
if (isset($_REQUEST[ 'debug' ])) $_SESSION[ 'debug' ] = ($_REQUEST[ 'debug' ] == 1);
// Initialize / drop DDL cache
if (! isset($_SESSION[ 'cache' ])) $_SESSION[ 'cache' ] = array();
if (isset($_REQUEST[ 'dropcache' ])) $_SESSION[ 'cache' ] = array();
// Initialize entry mode
if (! isset($_SESSION[ 'entrymode' ])) $_SESSION[ 'entrymode' ] = 'popups';
// Initialize SQL filter fields
if (! isset($_SESSION[ 'sql' ])) $_SESSION[ 'sql' ] = '';
if (! isset($_SESSION[ 'table' ])) $_SESSION[ 'table' ] = '';
if (! isset($_SESSION[ 'select' ])) $_SESSION[ 'select' ] = '*';
if (! isset($_SESSION[ 'where' ])) $_SESSION[ 'where' ] = '';
if (! isset($_SESSION[ 'set' ])) $_SESSION[ 'set' ] = 1;
if (! isset($_SESSION[ 'setsize' ])) $_SESSION[ 'setsize' ] = $setsizes[ 0 ];
if (isset($_REQUEST[ 'select' ])) $_SESSION[ 'select' ] = trim($_REQUEST[ 'select' ]);
// Initialize export mode
$exportmode = false;
if (isset($_REQUEST[ 'export' ]))
$exportmode = true;
// Switch back from export mode
if ($exportmode)
{ // SQL input fields changed?
$check_fields = array( 'sql', 'table', 'select', 'where' );
foreach ($check_fields as $field)
if (isset($_REQUEST[ $field ]))
if ($_REQUEST[ $field ] != $_SESSION[ $field ])
{ $exportmode = false;
break;
}
// History item selected?
if (isset($_REQUEST[ 'history' ]))
if ($_REQUEST[ 'history' ] != '')
$exportmode = false;
}
// Action + record set?
$action = '';
if (isset($_REQUEST[ 'action' ]))
if (($_REQUEST[ 'action' ] == 'edit') || ($_REQUEST[ 'action' ] == 'delete'))
$action = $_REQUEST[ 'action' ];
$actionrecord = false;
if ($action != '')
if (isset($_REQUEST[ 'record' ]))
if (is_array($_REQUEST[ 'record' ]))
if (isset($_REQUEST[ 'record' ][ 'table' ]) && isset($_REQUEST[ 'record' ][ 'rowid' ]))
$actionrecord = $_REQUEST[ 'record' ];
if (! is_array($actionrecord))
$action = '';
// edit or delete cancelled?
if (isset($_REQUEST[ 'editcancel' ]) || isset($_REQUEST[ 'deletecancel' ]))
{ $action = '';
$actionrecord = false;
}
// set changed?
if (isset($_REQUEST[ 'set' ]))
if ($_REQUEST[ 'set' ] != $_SESSION[ 'set' ])
{ $val = intval($_REQUEST[ 'set' ]);
if ($val > 0)
$_SESSION[ 'set' ] = $val;
}
// setsize changed?
if (isset($_REQUEST[ 'setsize' ]))
if ($_REQUEST[ 'setsize' ] != $_SESSION[ 'setsize' ])
if (in_array($_REQUEST[ 'setsize' ], $setsizes))
{ $_SESSION[ 'setsize' ] = $_REQUEST[ 'setsize' ];
$_SESSION[ 'set' ] = 1;
}
// empty column list means *
if ($_SESSION[ 'select' ] == '') $_SESSION[ 'select' ] = '*';
// entry mode changed?
if (isset($_REQUEST[ 'entrymode' ]))
if (($_REQUEST[ 'entrymode' ] == 'popups') || ($_REQUEST[ 'entrymode' ] == 'manual'))
{ $_SESSION[ 'sql' ] = '';
// Switch from "popups" to "manual"? Prefill SQL statement...
if (($_SESSION[ 'entrymode' ] == 'popups') && ($_REQUEST[ 'entrymode' ] == 'manual') && ($_SESSION[ 'table' ] != '') && ($_SESSION[ 'select' ] != ''))
$_SESSION[ 'sql' ] = 'SELECT ' . $_SESSION[ 'select' ] . ' from ' . $_SESSION[ 'table' ] . ' ' . $_SESSION[ 'where' ];
$_SESSION[ 'table' ] = '';
$_SESSION[ 'select' ] = '*';
$_SESSION[ 'where' ] = '';
$_SESSION[ 'set' ] = 1;
$_SESSION[ 'entrymode' ] = $_REQUEST[ 'entrymode' ];
}
// sql changed? (entrymode=manual)
if (isset($_REQUEST[ 'sql' ]))
if ($_REQUEST[ 'sql' ] != $_SESSION[ 'sql' ])
{ $_SESSION[ 'sql' ] = trim($_REQUEST[ 'sql' ]);
$_SESSION[ 'set' ] = 1;
}
// where changed? (entrymode=popups)
if (isset($_REQUEST[ 'where' ]))
if ($_REQUEST[ 'where' ] != $_SESSION[ 'where' ])
{ $_SESSION[ 'where' ] = trim($_REQUEST[ 'where' ]);
$_SESSION[ 'set' ] = 1;
}
// table changed? (entrymode=popups)
if (isset($_REQUEST[ 'table' ]))
if ($_REQUEST[ 'table' ] != $_SESSION[ 'table' ])
{ $newtable = substr(trim(preg_replace('/[^a-zA-Z0-9$#_.-]/', '', $_REQUEST[ 'table' ])), 0, 61);
if ($newtable != $_SESSION[ 'table' ])
{ $_SESSION[ 'table' ] = $newtable;
$_SESSION[ 'select' ] = '*';
$_SESSION[ 'where' ] = '';
$_SESSION[ 'set' ] = 1;
}
// We need a way to set both table + where in HREFs
if (isset($_REQUEST[ 'keepwhere' ]))
$_SESSION[ 'where' ] = $_REQUEST[ 'keepwhere' ];
}
// history item selected?
if (! isset($_SESSION[ 'history' ])) $_SESSION[ 'history' ] = array();
$dont_execute = false;
if (isset($_REQUEST[ 'history' ]))
if ($_REQUEST[ 'history' ] != '')
{ $tmp = intval($_REQUEST[ 'history' ]);
if ($tmp >= 0)
if (isset($_SESSION[ 'history' ][ $tmp ]))
{ $_SESSION[ 'entrymode' ] = $_SESSION[ 'history' ][ $tmp ][ 'entrymode' ];
$_SESSION[ 'set' ] = $_SESSION[ 'history' ][ $tmp ][ 'set' ];
$_SESSION[ 'setsize' ] = $_SESSION[ 'history' ][ $tmp ][ 'setsize' ];
if ($_SESSION[ 'history' ][ $tmp ][ 'entrymode' ] == 'popups')
{ $_SESSION[ 'table' ] = $_SESSION[ 'history' ][ $tmp ][ 'table' ];
$_SESSION[ 'select' ] = $_SESSION[ 'history' ][ $tmp ][ 'select' ];
$_SESSION[ 'where' ] = $_SESSION[ 'history' ][ $tmp ][ 'where' ];
$_SESSION[ 'sql' ] = '';
}
else
{ $_SESSION[ 'sql' ] = $_SESSION[ 'history' ][ $tmp ][ 'sql' ];
$_SESSION[ 'table' ] = '';
$_SESSION[ 'select' ] = '';
$_SESSION[ 'where' ] = '';
}
// Non-SELECT statements should only be shown, not automatically executed
// when switching to them (to avoid unwanted DELETEs etc.)
if ($_SESSION[ 'history' ][ $tmp ][ 'type' ] != 'SELECT')
$dont_execute = true;
}
}
// Build main SQL statement
$main_sql = '';
if ((($_SESSION[ 'table' ] != '') || ($_SESSION[ 'sql' ] != '')) && (! $dont_execute))
{ if ($_SESSION[ 'entrymode' ] == 'popups')
{ // Always select the ROWID - we're using this for "Actions" support instead of the primary key
$main_sql = 'select ';
// Prevent "ORA-00936: missing expression":
// "select *, ROWID" is incorrect, we have to use "select tablename.*, ROWID" instead
if (trim($_SESSION[ 'select' ]) == '*')
$main_sql .= $_SESSION[ 'table' ] . '.';
$rowidsql = ', rowidtochar(ROWID) as ROWID_';
$main_sql .= trim($_SESSION[ 'select' ] . $rowidsql . ' from ' . $_SESSION[ 'table' ] . ' ' . $_SESSION[ 'where' ]);
}
else
$main_sql = $_SESSION[ 'sql' ];
}
// Initialize connection
$conn = false;
if (($_SESSION[ 'connection' ][ 'user' ] != '') && ($_SESSION[ 'connection' ][ 'password' ] != ''))
pof_connect();
// Do export?
$doexport = false;
$export_errormsg = '';
if (isset($_REQUEST[ 'export' ]))
if (is_array($_REQUEST[ 'export' ]))
if (isset($_REQUEST[ 'export' ][ 'doit' ]) && isset($_REQUEST[ 'export' ][ 'format' ]) && isset($_REQUEST[ 'export' ][ 'limit' ]))
$doexport = true;
if ($doexport)
{ // Do the export
// Exporting may take a while
set_time_limit(0);
// Initialize export settings
$exportlimit = abs(intval($_REQUEST[ 'export' ][ 'limit' ]));
$_SESSION[ 'exportformat' ] = $_REQUEST[ 'export' ][ 'format' ];
if (! isset($exportformats[ $_SESSION[ 'exportformat' ] ]))
$_SESSION[ 'exportformat' ] = 'xml';
// Send Content-type header
header(sprintf('Content-Type: %s; name="dbexport.%s"', $exportformats[ $_SESSION[ 'exportformat' ] ][ 1 ], $_SESSION[ 'exportformat' ]));
header(sprintf('Content-disposition: attachment; filename="dbexport.%s"', $_SESSION[ 'exportformat' ]));
// Loop through results
$ok = false;
$cursor = pof_opencursor($main_sql);
if ($cursor)
if (ocistatementtype($cursor) == 'SELECT')
$ok = true;
if ($ok)
{ // Get column list
$columns = array();
$numcols = ocinumcols($cursor);
for ($j = 1; $j <= $numcols; $j++)
if (ocicolumnname($cursor, $j) != 'ROWID_')
$columns[ (ocicolumnname($cursor, $j)) ] = array(
'type' => ocicolumntype($cursor, $j),
'size' => ocicolumnsize($cursor, $j)
);
// Header
if ($_SESSION[ 'exportformat' ] == 'xml')
{ echo sprintf('<' . '?xml version="1.0" encoding="%s"?' . '>', $charset) . "\n";
echo "<!-- Generated by OracleEditor.php (http://oracleeditor.sourceforge.net/) -->\n";
$userstr = $_SESSION[ 'connection' ][ 'user' ];
if ($_SESSION[ 'connection' ][ 'service' ] != '')
$userstr .= '@' . $_SESSION[ 'connection' ][ 'service' ];
echo sprintf('<rowset exported="%s" user="%s" server="%s">', date('Y-m-d\TH:i:s'), $userstr, $_SERVER[ 'SERVER_NAME' ]) . "\n";
echo sprintf("\t<sql>%s</sql>\n", htmlspecialchars($main_sql));
// Column aliases: We can use column names as tag names only if
// they're valid XML names - <count(MYFIELD)> won't work.
$i = 0;
foreach ($columns as $name => $column)
{ $i++;
if (preg_match('/^[a-zA-Z][a-zA-Z0-9_-]*$/', $name) == 0)
$columns[ $name ][ 'alias' ] = 'ALIAS' . $i;
}
echo "\t<columns>\n";
foreach ($columns as $name => $column)
echo sprintf("\t\t" . '<column name="%s" type="%s" size="%s"%s/>' . "\n",
htmlspecialchars($name),
$column[ 'type' ],
$column[ 'size' ],
(isset($column[ 'alias' ]) ? ' alias="' . $column[ 'alias' ] . '"' : '')
);
echo "\t</columns>\n";
}
elseif ($_SESSION[ 'exportformat' ] == 'csv')
{ $first = true;
foreach ($columns as $name => $column)
if ($name != 'ROWID_')
{ if (! $first) echo ', ';
echo sprintf('"%s"', str_replace('"', '""', $name));
$first = false;
}
echo "\n";
}
elseif ($_SESSION[ 'exportformat' ] == 'html')
{ ?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=<?php echo $charset; ?>">
<meta name="date" content="<?php echo date('Y-m-d\TH:i:s'); ?>">
<meta name="generator" content="OracleEditor.php (http://oracleeditor.sourceforge.net/)">
<title>Exported Oracle data (by OracleEditor.php)</title>
</head>
<body>
<h1>Exported Oracle data</h1>
<?php
$userstr = $_SESSION[ 'connection' ][ 'user' ];
if ($_SESSION[ 'connection' ][ 'service' ] != '')
$userstr .= '@' . $_SESSION[ 'connection' ][ 'service' ];
?>
<p>The Oracle user <em><?php echo htmlspecialchars($userstr); ?></em> exported this data on <em><?php echo date('r'); ?></em>
by running the following SQL statement in <a href="http://<?php echo $_SERVER[ 'HTTP_HOST' ]; ?><?php echo $_SERVER[ 'PHP_SELF' ]; ?>">a local copy of OracleEditor.php</a> on <em><?php echo $_SERVER[ 'SERVER_NAME' ]; ?></em>:<br />
<pre><?php echo htmlspecialchars($main_sql); ?></pre></p>
<table border="1">
<tr>
<?php
foreach ($columns as $name => $column)
echo sprintf('<th>%s<br />(%s, %s)</th>' . "\n",
htmlspecialchars($name),
$column[ 'type' ],
$column[ 'size' ]
);
?>
</tr>
<?php
}
// Rows
$i = 1;
while (true)
{ if (! ocifetchinto($cursor, $row, OCI_ASSOC | OCI_RETURN_LOBS))
break;
if ($_SESSION[ 'exportformat' ] == 'xml')
{ echo sprintf("\t<row%s>\n", (isset($row[ 'ROWID_' ]) ? (' id="' . htmlspecialchars($row[ 'ROWID_' ]) . '"') : ''));
foreach ($row as $fieldname => $value)
if ($fieldname != 'ROWID_')
echo sprintf("\t\t<%1\$s>%2\$s</%1\$s>\n",
(isset($columns[ $fieldname ][ 'alias' ]) ? $columns[ $fieldname ][ 'alias' ] : $fieldname ),
htmlspecialchars($value));
echo "\t</row>\n";
}
elseif ($_SESSION[ 'exportformat' ] == 'csv')
{ $first = true;
foreach ($columns as $fieldname => $column)
if ($fieldname != 'ROWID_')
{ if (! $first) echo ', ';
if (isset($row[ $fieldname ]))
echo sprintf('"%s"', str_replace('"', '""', $row[ $fieldname ]));
else
echo '""';
$first = false;
}
echo "\n";
}
elseif ($_SESSION[ 'exportformat' ] == 'html')
{ echo "<tr>\n";
foreach ($columns as $fieldname => $column)
if ($fieldname != 'ROWID_')
{ echo "\t<td>";
if (isset($row[ $fieldname ]))
echo htmlspecialchars($row[ $fieldname ]);
echo "</td>\n";
}
echo "</tr>\n";
}
if (($exportlimit > 0) && ($exportlimit <= ++$i))
break;
}
// Footer
if ($_SESSION[ 'exportformat' ] == 'xml')
{ echo "</rowset>\n";
}
elseif ($_SESSION[ 'exportformat' ] == 'html')
{ ?>
</table>
<p>HTML generated by <a href="http://oracleeditor.sourceforge.net/">OracleEditor.php</a> <?php echo $version; ?> &copy; 2006 by <a href="http://tim.digicol.de/">Tim Strehle</a> &lt;<a href="mailto:tim@strehle.de">tim@strehle.de</a>&gt;</p>
</body>
</html>
<?php
}
pof_closecursor($cursor);
session_write_close();
exit;
}
else
$export_errormsg = 'Unable to export';
}
function pof_blanksession()
{ global $setsizes;
$_SESSION[ 'connection' ] = array(
'user' => '',
'password' => '',
'service' => ''
);
$_SESSION[ 'cache' ] = array();
$_SESSION[ 'debug' ] = false;
$_SESSION[ 'sql' ] = '';
$_SESSION[ 'table' ] = '';
$_SESSION[ 'select' ] = '*';
$_SESSION[ 'where' ] = '';
$_SESSION[ 'set' ] = 1;
$_SESSION[ 'setsize' ] = $setsizes[ 0 ];
$_SESSION[ 'history' ] = array();
}
function pof_sqlline($msg, $error = false)
{ if ($error)
$class = 'sqllineerr';
else
$class = 'sqlline';
$html = '<table><tr><td>' . htmlspecialchars($msg) . '</td></tr></table>' . "\n";
return $html;
}
function pof_connect()
{ global $conn;
$conn = ocilogon($_SESSION[ 'connection' ][ 'user' ], $_SESSION[ 'connection' ][ 'password' ], $_SESSION[ 'connection' ][ 'service' ]);
$err = ocierror();
if (is_array($err))
echo htmlspecialchars('Logon failed: ' . $err[ 'message' ]) . '<br />' . "\n";
}
function pof_disconnect()
{ global $conn;
if ($conn)
ocilogoff($conn);
}
function pof_opencursor($sql, $bind = false)
{ global $conn;
$cursor = ociparse($conn, $sql);
if (! $cursor)
{ $err = ocierror($conn);
if (is_array($err))
echo pof_sqlline('Parse failed: ' . $err[ 'message' ], true);
}
else
{ // This might improve performance?
ocisetprefetch($cursor, $_SESSION[ 'setsize' ]);
if (is_array($bind))
foreach ($bind as $fieldname => $value)
ocibindbyname($cursor, ':' . $fieldname, $bind[ $fieldname ], -1);
$ok = ociexecute($cursor);
if (! $ok)
{ $err = ocierror($cursor);
if (is_array($err))
echo pof_sqlline('Execute failed: ' . $err[ 'message' ], true);
pof_closecursor($cursor);
$cursor = false;
}
}
return $cursor;
}
function pof_closecursor($cursor)
{ if ($cursor)
ocifreestatement($cursor);
}
function pof_gettables()
{ if (! isset($_SESSION[ 'cache' ][ '_alltables' ]))
{ $_SESSION[ 'cache' ][ '_alltables' ] = array();
$sql = sprintf(
"select ' ' as OWNER, TABLE_NAME from USER_TABLES " .
"union " .
"select OWNER, TABLE_NAME from USER_TAB_PRIVS where PRIVILEGE = 'SELECT' and GRANTEE = '%1\$s' " .
"order by OWNER, TABLE_NAME",
strtoupper($_SESSION[ 'connection' ][ 'user' ])
);
if ($_SESSION[ 'debug' ]) error_log($sql);
$cursor = pof_opencursor($sql);
if ($cursor)
{ while (true)
{ if (! ocifetchinto($cursor, $row, OCI_ASSOC | OCI_RETURN_LOBS))
break;
if (trim($row[ 'OWNER' ]) == '')
$_SESSION[ 'cache' ][ '_alltables' ][ ] = $row[ 'TABLE_NAME' ];
else
$_SESSION[ 'cache' ][ '_alltables' ][ ] = $row[ 'OWNER' ] . '.' . $row[ 'TABLE_NAME' ];
}
pof_closecursor($cursor);
}
}
return $_SESSION[ 'cache' ][ '_alltables' ];
}
function pof_getviews()
{ if (! isset($_SESSION[ 'cache' ][ '_allviews' ]))
{ $_SESSION[ 'cache' ][ '_allviews' ] = array();
$sql = 'select VIEW_NAME from USER_VIEWS order by VIEW_NAME';
if ($_SESSION[ 'debug' ]) error_log($sql);
$cursor = pof_opencursor($sql);
if ($cursor)
{ while (true)
{ if (! ocifetchinto($cursor, $row, OCI_ASSOC | OCI_RETURN_LOBS))
break;
$_SESSION[ 'cache' ][ '_allviews' ][ ] = $row[ 'VIEW_NAME' ];
}
pof_closecursor($cursor);
}
}
return $_SESSION[ 'cache' ][ '_allviews' ];
}
function pof_getpk($table)
{ if (! isset($_SESSION[ 'cache' ][ $table ])) $_SESSION[ 'cache' ][ $table ] = array();
if (! isset($_SESSION[ 'cache' ][ $table ][ 'pk' ]))
{ $_SESSION[ 'cache' ][ $table ][ 'pk' ] = '';
$sql = "select COLUMN_NAME from USER_CONS_COLUMNS col, USER_CONSTRAINTS con where con.TABLE_NAME=:TABLE_NAME and con.CONSTRAINT_TYPE='P' and col.CONSTRAINT_NAME=con.CONSTRAINT_NAME";
$bind = array( 'TABLE_NAME' => $table );
if ($_SESSION[ 'debug' ]) error_log($sql);
$cursor = pof_opencursor($sql, $bind);
if ($cursor)
{ if (ocifetchinto($cursor, $row, OCI_NUM))
$_SESSION[ 'cache' ][ $table ][ 'pk' ] = $row[ 0 ];
pof_closecursor($cursor);
}
}
return $_SESSION[ 'cache' ][ $table ][ 'pk' ];
}
function pof_getcoldefs($table)
{ if (! isset($_SESSION[ 'cache' ][ $table ])) $_SESSION[ 'cache' ][ $table ] = array();
if (! isset($_SESSION[ 'cache' ][ $table ][ 'coldefs' ]))
{ $_SESSION[ 'cache' ][ $table ][ 'coldefs' ] = array();
$sql = "select COLUMN_NAME, NULLABLE, DATA_DEFAULT from USER_TAB_COLUMNS where TABLE_NAME=:TABLE_NAME";
$bind = array( 'TABLE_NAME' => $table );
if ($_SESSION[ 'debug' ]) error_log($sql);
$cursor = pof_opencursor($sql, $bind);
if ($cursor)
{ while (true)
{ if (! ocifetchinto($cursor, $row, OCI_ASSOC))
break;
$_SESSION[ 'cache' ][ $table ][ 'coldefs' ][ $row[ 'COLUMN_NAME' ] ] = array(
'nullable' => true,
'default' => ''
);
if (isset($row[ 'NULLABLE' ]))
if ($row[ 'NULLABLE' ] == 'N')
$_SESSION[ 'cache' ][ $table ][ 'coldefs' ][ $row[ 'COLUMN_NAME' ] ][ 'nullable' ] = false;
if (isset($row[ 'DATA_DEFAULT' ]))
$_SESSION[ 'cache' ][ $table ][ 'coldefs' ][ $row[ 'COLUMN_NAME' ] ][ 'default' ] = trim(strtr($row[ 'DATA_DEFAULT' ], '()', ' '));
}
pof_closecursor($cursor);
}
}
return $_SESSION[ 'cache' ][ $table ][ 'coldefs' ];
}
function pof_getforeignkeys($table)
{ if (! isset($_SESSION[ 'cache' ][ $table ])) $_SESSION[ 'cache' ][ $table ] = array();
if (! isset($_SESSION[ 'cache' ][ $table ][ 'constraints' ]))
{ $_SESSION[ 'cache' ][ $table ][ 'constraints' ] = array( 'from' => array(), 'to' => array() );
// Find own + remote foreign key constraint names
// XXX foreign tables might belong to a different user! take R_OWNER into account!
$sql =
"select CONSTRAINT_NAME, R_CONSTRAINT_NAME from USER_CONSTRAINTS where TABLE_NAME=:TABLE_NAME and CONSTRAINT_TYPE='R' and STATUS='ENABLED' " .
"union " .
"select CONSTRAINT_NAME, R_CONSTRAINT_NAME from USER_CONSTRAINTS where R_CONSTRAINT_NAME in " .
"(select CONSTRAINT_NAME from USER_CONSTRAINTS where TABLE_NAME=:TABLE_NAME) ".
"and CONSTRAINT_TYPE='R' and STATUS='ENABLED'";
$bind = array( 'TABLE_NAME' => $table );
if ($_SESSION[ 'debug' ]) error_log($sql);
$cursor = pof_opencursor($sql, $bind);
$names = array();
$constraints = array();
if ($cursor)
{ while (true)
{ if (! ocifetchinto($cursor, $row, OCI_ASSOC))
break;
$names[ ] = $row[ 'CONSTRAINT_NAME' ];
if (isset($row[ 'R_CONSTRAINT_NAME' ]))
if ($row[ 'R_CONSTRAINT_NAME' ] != '')
$names[ ] = $row[ 'R_CONSTRAINT_NAME' ];
}
pof_closecursor($cursor);
}
if (count($names) > 0)
{ $sql = "select CONSTRAINT_NAME, TABLE_NAME, R_CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_NAME in ('" . implode("','", $names) . "')";
if ($_SESSION[ 'debug' ]) error_log($sql);
$cursor = pof_opencursor($sql);
if ($cursor)
{ while (true)
{ if (! ocifetchinto($cursor, $row, OCI_ASSOC))
break;
$constraints[ $row[ 'CONSTRAINT_NAME' ] ] = $row;
}
pof_closecursor($cursor);
}
$sql = "select CONSTRAINT_NAME, COLUMN_NAME from USER_CONS_COLUMNS where CONSTRAINT_NAME in ('" . implode("','", $names) . "')";
if ($_SESSION[ 'debug' ]) error_log($sql);
$cursor = pof_opencursor($sql);
if ($cursor)
{ while (true)
{ if (! ocifetchinto($cursor, $row, OCI_ASSOC))
break;
$constraints[ $row[ 'CONSTRAINT_NAME' ] ][ 'COLUMN_NAME' ] = $row[ 'COLUMN_NAME' ];
}
pof_closecursor($cursor);
}
}
if (count($constraints) > 0)
{ foreach ($constraints as $key => $item)
{ if (! isset($item[ 'R_CONSTRAINT_NAME' ]))
continue;
if ($item[ 'TABLE_NAME' ] == $table)
$_SESSION[ 'cache' ][ $table ][ 'constraints' ][ 'to' ][ $item[ 'COLUMN_NAME' ] ] = array(
'table' => $constraints[ $item[ 'R_CONSTRAINT_NAME' ] ][ 'TABLE_NAME' ],
'column' => $constraints[ $item[ 'R_CONSTRAINT_NAME' ] ][ 'COLUMN_NAME' ]
);
else
{ $col = $constraints[ $item[ 'R_CONSTRAINT_NAME' ] ][ 'COLUMN_NAME' ];
if (! isset($_SESSION[ 'cache' ][ $table ][ 'constraints' ][ 'from' ][ $col ]))
$_SESSION[ 'cache' ][ $table ][ 'constraints' ][ 'from' ][ $col ] = array();
$_SESSION[ 'cache' ][ $table ][ 'constraints' ][ 'from' ][ $col ][ ] = array(
'table' => $item[ 'TABLE_NAME' ],
'column' => $item[ 'COLUMN_NAME' ]
);
}
}
}
}
return $_SESSION[ 'cache' ][ $table ][ 'constraints' ];
}
// Charset header
header('Content-Type: text/html; charset=' . $charset);
?>
<html>
<head>
<title>OracleEditor.php<?php
if ($_SESSION[ 'connection' ][ 'user' ] != '')
{ if ($_SESSION[ 'table' ] != '')
echo ': ' . $_SESSION[ 'table' ];
echo ' (' . $_SESSION[ 'connection' ][ 'user' ];
if ($_SESSION[ 'connection' ][ 'service' ] != '')
echo '@' . $_SESSION[ 'connection' ][ 'service' ];
echo ')';
}
?></title>
<style type="text/css">
body,a,p,span,td,th,input,select,textarea {
font-family:verdana,arial,helvetica,geneva,sans-serif,serif;
font-size:12px;
}
a:link,a:visited,a:active {
color:darkgray;
}
.logo {
color:yellow;
background:black;
font-weight:bold;
font-size:14px;
}
.headerline {
border-style:none;
padding:5px;
background:black;
color:white;
width:100%;
}
.selectform {
border-width:1px;
border-color:#FF9999;
border-style:dashed;
padding:5px;
width:100%;
}
.sqlline {
font-family:courier;
border-style:none;
padding:5px;
width:100%;
color:yellow;
background:black;
}
.sqllineerr {
font-family:courier;
border-style:none;
padding:5px;
width:100%;
color:red;
background:black;
}
.resultgrid {
border-width:1px;
border-color:#FF9999;
border-style:dashed;
padding:5px;
}
.gridheader {
background:#EEEEEE;
color:darkgray;
}
.gridline {
background:white;
}
.gridlinealt {
background:#EEEEEE;
}
.gridfooter {
border-style:none;
background:black;
color:white;
width:100%;
}
</style>
</head>
<body>
<form name="form1" method="post" action="<?php echo $_SERVER[ 'PHP_SELF' ]; ?>">
<input type="hidden" name="sid" value="<?php echo $sid; ?>" />
<?php
if ($conn == false)
{ ?>
<table>
<tr>
<td colspan="2"><span>OracleEditor.php</span> Browse and edit your Oracle database records ...</td>
</tr>
</table>
<?php
// Check requirements
$requirements_ok = true;
$required_version = '4.1.0';
if (version_compare(phpversion(), $required_version) < 0)
{ printf("<strong>PHP too old</strong>: You're running PHP %s, but <strong>PHP %s is required</strong> to run OracleEditor.php!<br />\n", phpversion(), $required_version);
$requirements_ok = false;
}
if (! function_exists('ocilogon'))
{ echo "<strong>PHP has no Oracle OCI support</strong>: Your PHP installation doesn't have the <a href=\"http://www.php.net/manual/en/ref.oci8.php\">OCI8 module</a> installed which is required to run OracleEditor.php!<br />\n";
$requirements_ok = false;
}
if (! function_exists('session_start'))
{ echo "<strong>PHP has no session support</strong>: Your PHP installation doesn't have the <a href=\"http://www.php.net/manual/en/ref.session.php\">Session module</a> installed which is required to run OracleEditor.php!<br />\n";
$requirements_ok = false;
}
// Login form
if ($requirements_ok)
{ ?>
<table>
<tr>
<td>User: </td>
<td><input type="text" name="connection[user]" value="<?php echo $_SESSION[ 'connection' ][ 'user' ]; ?>" title="Enter the Oracle user name" /></td>
<script type="text/javascript">
document.forms[ 'form1' ].elements[ 'connection[user]' ].focus();
</script>
</tr>
<tr>
<td>Password: </td>
<td><input type="password" name="connection[password]" value="" title="Enter the Oracle user's password" /></td>
</tr>
<tr>
<td>Service name: </td>
<td><input type="text" name="connection[service]" value="<?php echo htmlspecialchars($_SESSION[ 'connection' ][ 'service' ]); ?>" title="Enter a tnsnames.ora identifier, or leave blank for local databases" /></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="submit" value="Connect to Oracle" accesskey="c" title="Click to log in [c]" /></td>
</tr>
</table>
<?php
}
}
else
{ // Display connection header
echo '<table><tr><td>';
echo '<span>OracleEditor.php</span> ';
echo 'Connected to Oracle as ' . $_SESSION[ 'connection' ][ 'user' ];
if ($_SESSION[ 'connection' ][ 'service' ] != '')
echo '@' . $_SESSION[ 'connection' ][ 'service' ];
echo ' - <a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&disconnect=1" accesskey="d" title="Click here to log out [d]">Disconnect</a>';
echo '</table>' . "\n";
echo '<table><tr><td>' . "\n";
if ($_SESSION[ 'entrymode' ] == 'popups')
{ // Popup-aided SQL query entry
echo 'SELECT ';
// "select" (column list) input field
echo '<input type="text" name="select" value="' . htmlspecialchars($_SESSION[ 'select' ]) . '" size="20" title="Enter column names (comma-separated), or * for all columns" />';
// "table" selection popup
$alltables = pof_gettables();
$allviews = pof_getviews();
echo ' FROM <select name="table" onChange="javascript:document.forms[0].submit()" title="Select a table/view to display or edit">' . "\n";
$found = false;
echo '<option value="">[Select a table]</option>' . "\n";
foreach ($alltables as $tablename)
{ echo '<option value="' . $tablename . '"';
if (! $found)
if ($tablename == $_SESSION[ 'table' ])
{ echo ' selected="selected"';
$found = true;
}
echo '>' . $tablename . '</option>' . "\n";
}
echo '<option value=""></option>' . "\n";
echo '<option value="">[Select a view]</option>' . "\n";
foreach ($allviews as $viewname)
{ echo '<option value="' . $viewname . '"';
if (! $found)
if ($viewname == $_SESSION[ 'table' ])
{ echo ' selected="selected"';
$found = true;
}
echo '>' . $viewname . '</option>' . "\n";
}
if (! $found)
echo '<option value="" selected="selected">[Select a table/view]</option>' . "\n";
echo '</select>' . "\n";
// "where" input field for WHERE, ORDER BY, GROUP BY, ...
echo ' <input type="text" name="where" value="' . htmlspecialchars($_SESSION[ 'where' ]) . '" size="40" title="Enter GROUP BY or ORDER BY clauses here" />;';
}
else
{ // Manual SQL query/command entry
?>
SQL: [Warning: Be careful with UPDATE, DELETE, DROP etc. - there's no chance to rollback!]<br />
<textarea name="sql" rows="5" cols="80" title="Enter any SQL statement here: SELECT, INSERT, UPDATE, DELETE, ALTER, DROP..."><?php echo htmlspecialchars($_SESSION[ 'sql' ]); ?></textarea>
<script type="text/javascript">
document.forms[ 'form1' ].elements[ 'sql' ].focus();
</script>
<?php
}
// "setsize" selection popup
echo '<br /> Display <select name="setsize" onChange="javascript:document.forms[0].submit()" title="Select the number of rows to display per page">' . "\n";
foreach ($setsizes as $size)
{ echo '<option value="' . $size . '"';
if ($size == $_SESSION[ 'setsize' ])
echo ' selected="selected"';
echo '>' . $size . '</option>' . "\n";
}
echo '</select> records per page.' . "\n";
// Submit button
echo '<input type="submit" accesskey="e" value="' . ($_SESSION[ 'entrymode' ] == 'popups' ? 'Refresh' : 'Execute') . '" title="Click here to execute the SQL statement [e]" />' . "\n";
echo '<input type="submit" accesskey="x" name="export" value="Export" title="Click here to export rows as text, XML or CSV [x]" />' . "\n";
echo str_repeat('&nbsp;', 6);
echo '<a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&entrymode=' . ($_SESSION[ 'entrymode' ] == 'popups' ? 'manual' : 'popups') . '" accesskey="s" title="Click here to switch between manual SQL entry and the table/view popup [s]">';
echo ($_SESSION[ 'entrymode' ] == 'popups' ? 'Switch to manual SQL entry' : 'Switch to popup-aided SQL entry') . '</a>' . "\n";
echo '</td></tr></table>' . "\n";
// Update record if requested
if (($action == 'edit') && isset($_REQUEST[ 'editsave' ]) && is_array($actionrecord) && isset($_REQUEST[ 'edit' ]))
if (is_array($_REQUEST[ 'edit' ]))
if (count($_REQUEST[ 'edit' ]) > 0)
{ $sql = 'update ' . $actionrecord[ 'table' ] . ' set ';
$i = 0;
$bind = array();
foreach ($_REQUEST[ 'edit' ] as $fieldname => $field)
{ if (! (isset($field[ 'mode' ]) && isset($field[ 'value' ]) && isset($field[ 'function' ])))
continue;
if ($i > 0)
$sql .= ', ';
$sql .= $fieldname . '=';
if ($field[ 'mode' ] == 'function')
$sql .= $field[ 'function' ];
else
{ $sql .= ':' . $fieldname;
$bind[ $fieldname ] = $field[ 'value' ];
}
$i++;
}
$sql .= ' where ROWID=chartorowid(:rowid_)';
if ($_SESSION[ 'debug' ]) error_log($sql);
$bind[ 'rowid_' ] = $actionrecord[ 'rowid' ];
echo pof_sqlline($sql . ';');
$updcursor = ociparse($conn, $sql);
if (! $updcursor)
{ $err = ocierror($conn);
if (is_array($err))
echo pof_sqlline('Parse failed: ' . $err[ 'message' ], true);
}
else
{ foreach ($bind as $fieldname => $value)
ocibindbyname($updcursor, ':' . $fieldname, $bind[ $fieldname ], -1);
$ok = ociexecute($updcursor);
if (! $ok)
{ $err = ocierror($updcursor);
if (is_array($err))
echo pof_sqlline('Execute failed: ' . $err[ 'message' ], true);
}
ocifreestatement($updcursor);
}
}
// Delete record if requested
if (($action == 'delete') && isset($_REQUEST[ 'deleteconfirm' ]) && is_array($actionrecord))
{ $sql = 'delete from ' . $actionrecord[ 'table' ] . ' where ROWID=chartorowid(:rowid_)';
if ($_SESSION[ 'debug' ]) error_log($sql);
echo pof_sqlline($sql . ';');
$delcursor = ociparse($conn, $sql);
if (! $delcursor)
{ $err = ocierror($conn);
if (is_array($err))
echo pof_sqlline('Parse failed: ' . $err[ 'message' ], true);
}
else
{ ocibindbyname($delcursor, ':rowid_', $actionrecord[ 'rowid' ], -1);
$ok = ociexecute($delcursor);
if (! $ok)
{ $err = ocierror($delcursor);
if (is_array($err))
echo pof_sqlline('Execute failed: ' . $err[ 'message' ], true);
}
ocifreestatement($delcursor);
}
$action = '';
$actionrecord = false;
}
// Insert record if requested
if (isset($_REQUEST[ 'insertsave' ]) && isset($_REQUEST[ 'insert' ]))
if (is_array($_REQUEST[ 'insert' ]))
if (count($_REQUEST[ 'insert' ]) > 0)
{ $fieldnames = array();
$fieldvalues = array();
$bind = array();
foreach ($_REQUEST[ 'insert' ] as $fieldname => $field)
{ if (! (isset($field[ 'mode' ]) && isset($field[ 'value' ]) && isset($field[ 'function' ])))
continue;
$fieldnames[ ] = $fieldname;
if ($field[ 'mode' ] == 'function')
$fieldvalues[ ] = $field[ 'function' ];
else
{ $fieldvalues[ ] = ':' . $fieldname;
$bind[ $fieldname ] = $field[ 'value' ];
}
}
$sql = 'insert into ' . $_SESSION[ 'table' ] . ' (' . implode(', ', $fieldnames) . ') values (' . implode(', ', $fieldvalues) . ')';
if ($_SESSION[ 'debug' ]) error_log($sql);
echo pof_sqlline($sql . ';');
$inscursor = ociparse($conn, $sql);
if (! $inscursor)
{ $err = ocierror($conn);
if (is_array($err))
echo pof_sqlline('Parse failed: ' . $err[ 'message' ], true);
}
else
{ foreach ($bind as $fieldname => $value)
ocibindbyname($inscursor, ':' . $fieldname, $bind[ $fieldname ], -1);
$ok = ociexecute($inscursor);
if (! $ok)
{ $err = ocierror($inscursor);
if (is_array($err))
echo pof_sqlline('Execute failed: ' . $err[ 'message' ], true);
}
ocifreestatement($inscursor);
}
}
// Run SELECT statement, display results
if ((($_SESSION[ 'table' ] != '') || ($_SESSION[ 'sql' ] != '')) && (! $dont_execute))
{ echo pof_sqlline($main_sql . ';');
if ($_SESSION[ 'debug' ]) error_log($main_sql);
if ($_SESSION[ 'entrymode' ] == 'popups')
$pk = pof_getpk($_SESSION[ 'table' ]);
else
$pk = '';
$cursor = pof_opencursor($main_sql);
$statementtype = '';
if ($cursor)
{ // Add to history
// Remove ROWID select string from the SQL string displayed in the history - it's just ugly
if ($_SESSION[ 'entrymode' ] == 'popups')
$histsql = str_replace($rowidsql, '', $main_sql);
else
$histsql = $main_sql;
foreach ($_SESSION[ 'history' ] as $key => $item)
if ($item[ 'sql' ] == $histsql)
unset($_SESSION[ 'history' ][ $key ]);
$statementtype = ocistatementtype($cursor);
$historyitem = array(
'sql' => $histsql,
'set' => $_SESSION[ 'set' ],
'setsize' => $_SESSION[ 'setsize' ],
'entrymode' => $_SESSION[ 'entrymode' ],
'type' => $statementtype
);
if ($_SESSION[ 'entrymode' ] == 'popups')
{ $historyitem[ 'table' ] = $_SESSION[ 'table' ];
$historyitem[ 'select' ] = $_SESSION[ 'select' ];
$historyitem[ 'where' ] = $_SESSION[ 'where' ];
}
array_unshift($_SESSION[ 'history' ], $historyitem);
if (count($_SESSION[ 'history' ]) > 25)
array_pop($_SESSION[ 'history' ]);
}
if ($statementtype == 'SELECT')
{ // Get column list
$columns = array();
$numcols = ocinumcols($cursor);
for ($j = 1; $j <= $numcols; $j++)
if (ocicolumnname($cursor, $j) != 'ROWID_')
$columns[ (ocicolumnname($cursor, $j)) ] = array(
'type' => ocicolumntype($cursor, $j),
'size' => ocicolumnsize($cursor, $j)
);
// Display main table
if ($exportmode)
{ // Display export settings form
?>
<table>
<tr>
<td align="left">
Export format:
<?php $i = 0; foreach ($exportformats as $value => $config) { $i++; ?>
<label><input type="radio" name="export[format]" value="<?php echo $value; ?>" <?php echo ($value == $_SESSION[ 'exportformat' ] ? 'checked="checked" ' : ''); ?>/><?php echo htmlspecialchars($config[ 0 ]); ?></label>
<?php } ?>
</td>
</tr>
<tr>
<td align="left">
Record limit:
<select name="export[limit]" title="Select the maximum number of rows to be exported">
<option value="100" selected="selected">100</option>
<option value="1000">1000</option>
<option value="0">Unlimited (Are you sure?)</option>
</select>
</td>
</tr>
<tr>
<td align="left">
<input type="submit" name="export[doit]" value="Export now" accesskey="n" title="Click here to download the export file now [n]" />
<input type="button" value="Cancel" accesskey="c" onClick="location.href='<?php echo $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid; ?>'" title="Click here to go back, cancel exporting [c]" />
<?php echo htmlspecialchars($export_errormsg); ?>
</td>
</tr>
</table>
<?php
}
else
{ // Display table header
echo '<table>' . "\n";
echo '<tr>' . "\n";
echo '<th>Row</th>' . "\n";
if ($_SESSION[ 'entrymode' ] == 'popups')
echo '<th>Actions</th>' . "\n";
foreach ($columns as $columnname => $column)
echo '<th>' . $columnname . '<br />(' . $column[ 'type' ] . ', ' . $column[ 'size' ] . ')</th>' . "\n";
echo '</tr>' . "\n";
// Skip previous sets
$offset = 0;
if ($_SESSION[ 'set' ] > 1)
{ $offset = ($_SESSION[ 'set' ] - 1) * $_SESSION[ 'setsize' ];
for ($j = 1; $j <= $offset; $j++)
if (! ocifetch($cursor))
break;
}
$morerows = false;
$foundactionrecord = false;
$foreign = pof_getforeignkeys($_SESSION[ 'table' ]);
// Display records
$i = 0;
while (true)
{ if (! ocifetchinto($cursor, $row, OCI_ASSOC | OCI_RETURN_LOBS))
break;
$i++;
echo '<tr>' . "\n";
echo '<td>' . ($i + $offset) . '</td>' . "\n";
// Is this record to be edited?
$mode = 'show';
if ($action != '')
if (($actionrecord[ 'table' ] == $_SESSION[ 'table' ]) && ($actionrecord[ 'rowid' ] == $row[ 'ROWID_' ]))
{ $mode = $action;
$foundactionrecord = true;
}
// Display Actions column (entrymode=popups)
if ($_SESSION[ 'entrymode' ] == 'popups')
{ echo '<td>';
if ($mode == 'edit')
{ echo '<a name="actionrecord"></a>';
echo '<input type="submit" value="Update" name="editsave" title="Click here to save your changes now" /><br />';
echo '<input type="submit" value="Cancel" name="editcancel" title="Click here to dismiss your changes and go back" />';
}
elseif ($mode == 'delete')
{ echo '<a name="actionrecord"></a>';
echo '<input type="submit" value="Delete" name="deleteconfirm" title="Click here to delete this record now" /><br />';
echo '<input type="submit" value="Cancel" name="deletecancel" title="Click here to go back" />';
}
else
{ $qs = 'record[table]=' . urlencode($_SESSION[ 'table' ]) . '&' .
'record[rowid]=' . urlencode($row[ 'ROWID_' ]);
echo '<a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&action=edit&' . $qs . '#actionrecord" title="Click here to change this record">Update</a><br />';
echo '<a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&action=delete&' . $qs . '#actionrecord" title="Click here to delete this record">Delete</a>';
}
echo '</td>' . "\n";
}
// Display values
if ($mode == 'edit')
{ foreach ($columns as $columnname => $column)
{ $value = '';
$nul = false;
if (isset($row[ $columnname ]))
$value = $row[ $columnname ];
else
$nul = true;
echo '<td>';
if ($columnname == $pk)
echo '<pre>' . htmlspecialchars($value) . '</pre>';
else
{ echo '<nobr>Original value: <nobr>' . htmlspecialchars(substr($value, 0, 50)) . (strlen($value) > 50 ? '...' : '') . '</nobr><br />';
$inputsize = $column[ 'size' ];
if ($inputsize < 4)
$inputsize = 4;
elseif ($inputsize > 48)
$inputsize = 48;
echo '<nobr><input type="radio" name="edit[' . $columnname . '][mode]" value="value" ' . ($nul ? '' : 'checked="checked" ') . '/>' . "\n";
if (($column[ 'type' ] == 'LONG') || ($column[ 'type' ] == 'CLOB'))
echo '<textarea name="edit[' . $columnname . '][value]" rows="10" cols="48" wrap="virtual">' . htmlspecialchars($value) . '</textarea>' . "\n";
else
{ echo '<input type="text" name="edit[' . $columnname . '][value]" value="' . htmlspecialchars($value) .'" size="' . $inputsize . '" ';
if (($column[ 'size' ] <= 256) && (($column[ 'type' ] == 'VARCHAR') || ($column[ 'type' ] == 'VARCHAR2')))
echo 'maxlength="' . $column[ 'size' ] . '" ';
echo '/>';
}
echo '</nobr><br />' . "\n";
echo '<nobr><input type="radio" name="edit[' . $columnname . '][mode]" value="function" ' . ($nul ? 'checked="checked" ' : '') . '/> ' . "\n";
echo 'Function: <input type="text" name="edit[' . $columnname . '][function]" value="' . ($nul ? 'NULL' : '') .'" size="10" /></nobr>' . "\n";
}
echo '</td>' . "\n";
}
}
else
foreach ($columns as $columnname => $column)
{ echo '<td>';
if (isset($row[ $columnname ]))
{ echo '<pre>';
if (isset($foreign[ 'to' ][ $columnname ]))
echo
'<a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid .
'&table=' . urlencode($foreign[ 'to' ][ $columnname ][ 'table' ]) .
'&keepwhere=' . urlencode("where " . $foreign[ 'to' ][ $columnname ][ 'column' ] . "='" . ereg_replace("'", "''", $row[ $columnname ]) . "'") .
'" title="Click here to display the referenced ' . htmlspecialchars($foreign[ 'to' ][ $columnname ][ 'table' ]) . ' record">';
echo htmlspecialchars($row[ $columnname ]);
if (isset($foreign[ 'to' ][ $columnname ]))
echo '</a>';
echo '</pre>';
if (isset($foreign[ 'from' ][ $columnname ]))
foreach ($foreign[ 'from' ][ $columnname ] as $key => $item)
{ if ($key > 0)
echo '<br />';
echo
'<nobr><a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid .
'&table=' . urlencode($item[ 'table' ]) .
'&keepwhere=' . urlencode("where " . $item[ 'column' ] . "='" . ereg_replace("'", "''", $row[ $columnname ]) . "'") .
'" title="Click here to display references to this record in ' . htmlspecialchars($item[ 'table' ] . '.' . $item[ 'column' ]) . '">-&gt; ' .
nl2br(htmlspecialchars(wordwrap($item[ 'table' ] . '.' . $item[ 'column' ], 30, "-\n", true))) . '</a></nobr>' . "\n";
}
}
echo '</td>' . "\n";
}
echo '</tr>' . "\n";
// Check whether there's a next result set
if ($i >= $_SESSION[ 'setsize' ])
{ if (ocifetch($cursor))
$morerows = true;
break;
}
}
if (! $foundactionrecord)
{ $action = '';
$actionrecord = false;
}
// New record row
if ($action == '')
{ echo '<tr>' . "\n";
if (isset($_REQUEST[ 'showinsert' ]))
{ // Find default values + NOT NULL restrictions
$coldefs = pof_getcoldefs($_SESSION[ 'table' ]);
// Paint cells
echo '<td><a name="insertrow"></a>&nbsp;</td>' . "\n";
echo '<td><input type="submit" value="Insert" name="insertsave" /></td>' . "\n";
foreach ($columns as $columnname => $column)
{ $value = '';
$nul = false;
if (isset($coldefs[ $columnname ]))
{ $value = $coldefs[ $columnname ][ 'default' ];
$nul = $coldefs[ $columnname ][ 'nullable' ];
}
echo '<td>';
$inputsize = $column[ 'size' ];
if ($inputsize < 4)
$inputsize = 4;
elseif ($inputsize > 48)
$inputsize = 48;
echo '<nobr><input type="radio" name="insert[' . $columnname . '][mode]" value="value" ' . ($nul ? '' : 'checked="checked" ') . '/>' . "\n";
echo '<input type="text" name="insert[' . $columnname . '][value]" value="' . htmlspecialchars($value) .'" size="' . $inputsize . '" ';
if (($column[ 'size' ] <= 256) && (($column[ 'type' ] == 'VARCHAR') || ($column[ 'type' ] == 'VARCHAR2')))
echo 'maxlength="' . $column[ 'size' ] . '" ';
echo '/></nobr><br />' . "\n";
echo '<nobr><input type="radio" name="insert[' . $columnname . '][mode]" value="function" ' . ($nul ? 'checked="checked" ' : '') . '/> ' . "\n";
echo 'Function: <input type="text" name="insert[' . $columnname . '][function]" value="' . ($nul ? 'NULL' : '') .'" size="10" /></nobr>' . "\n";
echo '</td>' . "\n";
}
}
elseif ($_SESSION[ 'entrymode' ] == 'popups')
echo '<td colspan="' . (count($columns) + 2) . '"><a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&showinsert=1#insertrow" title="Click here to create a new record in ' . htmlspecialchars($_SESSION[ 'table' ]) . '">Insert new row</a></td>';
echo '</tr>' . "\n";
}
echo '</table>' . "\n";
echo '<table><tr><td>' . "\n";
if ($_SESSION[ 'set' ] > 1)
{ echo '<a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&set=1" accesskey="f" title="Click here to go to the first page [f]">|&lt;</a> ';
echo '<a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&set=' . ($_SESSION[ 'set' ] - 1) . '" accesskey="p" title="Click here to go to the previous page [p]">&lt;&lt;</a> ';
}
echo 'Page ' . $_SESSION[ 'set' ];
if ($morerows)
echo ' <a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&set=' . ($_SESSION[ 'set' ] + 1) . '" accesskey="n" title="Click here to go to the next page [n]">&gt;&gt;</a>';
echo '</td></tr></table>' . "\n";
}
}
elseif ($statementtype != '')
{ // Non-SELECT statements
$rowcount = ocirowcount($cursor);
$words = array(
'UPDATE' => 'updated',
'DELETE' => 'deleted',
'INSERT' => 'inserted'
);
$msg = $rowcount . ' row' . ($rowcount == 1 ? '' : 's') . ' ';
if (isset($words[ $statementtype ]))
$msg .= $words[ $statementtype ] . '.';
else
$msg = $statementtype . ' affected ' . $msg . '.';
echo pof_sqlline($msg);
}
pof_closecursor($cursor);
}
// History popup
echo '<table><tr><td>' . "\n";
echo 'History: <select name="history" onChange="javascript:document.forms[0].submit()" title="Select a previous SQL statement">' . "\n";
echo '<option value="" selected="selected"> </option>' . "\n";
foreach ($_SESSION[ 'history' ] as $key => $item)
echo '<option value="' . $key . '">' . htmlspecialchars(substr($item[ 'sql' ], 0, 100)) . '</option>' . "\n";
echo '</select>' . "\n";
echo '</td></tr></table>' . "\n";
// Hidden fields for the currently edited record
if (is_array($actionrecord))
{ echo '<input type="hidden" name="record[table]" value="' . htmlspecialchars($actionrecord[ 'table' ]) . '" />' . "\n";
echo '<input type="hidden" name="record[rowid]" value="' . htmlspecialchars($actionrecord[ 'rowid' ]) . '" />' . "\n";
if ($action != '')
echo '<input type="hidden" name="action" value="' . $action . '" />' . "\n";
}
// Footer
echo '<table><tr>' . "\n";
// "Drop cache" link
echo '<td valign="top"><a href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&dropcache=1" title="After altering tables, click here to force a re-read of table definitions">Drop DDL cache</a></td>' . "\n";
// "Debug" link
echo '<td valign="top"><a title="Click here to switch SQL statement logging on or off" href="' . $_SERVER[ 'PHP_SELF' ] . '?sid=' . $sid . '&debug=';
if ($_SESSION[ 'debug' ])
echo '0">Turn debug mode off';
else
echo '1">Turn debug mode on';
echo '</a><br />(Logs all SQL statements in ' . ini_get('error_log') . ')</td>' . "\n";
// Oracle environment variables display
echo '<td valign="top">Oracle environment variables:<br />';
$env_vars = array( 'ORACLE_SID', 'NLS_LANG', 'NLS_DATE_FORMAT' );
$first = true;
foreach ($env_vars as $env_var)
{ $val = getenv($env_var);
if ($val === false)
continue;
if (! $first) echo '<br />';
echo sprintf("%s=%s\n", $env_var, $val);
$first = false;
}
echo '</td>';
echo '</tr></table>';
}
pof_disconnect();
?>
</form>
<a href="http://oracleeditor.sourceforge.net/" title="OracleEditor.php homepage">OracleEditor.php</a> <?php echo $version; ?> &copy; 2006 by <a href="http://tim.digicol.de/" title="Tim Strehle's homepage on tim.digicol.de">Tim Strehle</a> &lt;<a href="mailto:tim@strehle.de" title="Send e-mail to Tim Strehle">tim@strehle.de</a>&gt;
</body>
</html>
&nbsp;