Editing API:EPrints/Database/Oracle

<!-- Pod2Wiki=_preamble_ 
This page has been automatically generated from the EPrints 3.4 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
 -->{{API}}{{Pod2Wiki}}{{API:Source|file=EPrints/Database/Oracle.pm|package_name=EPrints::Database::Oracle}}[[Category:API|ORACLE]][[Category:API:EPrints/Database|ORACLE]]<div><!-- Edit below this comment -->


<!-- Pod2Wiki=_private_ --><!-- Pod2Wiki=head_name -->
==NAME==
'''EPrints::Database::Oracle''' - custom database methods for Oracle DB

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_description -->
==DESCRIPTION==
Oracle database wrapper for Oracle DB version 9+.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_synopsis -->
===Synopsis===
These settings are the default settings for the free Oracle developer version:

    # Oracle driver settings for database.pl
    $c-&gt;{dbdriver} = "Oracle";
    $c-&gt;{dbhost} = "localhost";
    $c-&gt;{dbsid} = "XE;
    $c-&gt;{dbuser} = "HR";
    $c-&gt;{dbpass} = "HR";
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_setting_up_oracle -->
===Setting up Oracle===
Enable the HR user in Oracle XE.

Set the <tt>ORACLE_HOME</tt> and <tt>ORACLE_SID</tt> environment variables. To  add these globally edit <tt>/etc/profile.d/oracle.sh</tt> (for XE edition):

  export ORACLE_HOME="/usr/lib/oracle/xe/app/oracle/product/10.2.0/server"
  export ORACLE_SID="XE"
  
(Will need to relog to take effect)

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_oracle_specific_annoyances -->
===Oracle-specific Annoyances===
Use the GQLPlus wrapper from http://gqlplus.sourceforge.net/ instead  of sqlplus.

Oracle will uppercase any identifiers that aren't quoted and is case  sensitive, hence mixing quoted and unquoted identifiers will lead to  problems.

Oracle does not support <tt>LIMIT()</tt>.

Oracle does not support <tt>AUTO_INCREMENT</tt> (MySQL) nor <tt>SERIAL</tt>  (Postgres).

Oracle won't <tt>ORDER BY LOBS</tt>.

Oracle requires special means to insert values into <tt>CLOB</tt>/<tt>BLOB</tt>.

Oracle doesn't support <tt>AS</tt> when aliasing.

When specifying char column lengths use (n char) to define character  semantics. Otherwise oracle uses the <tt>nls_length_semantics</tt> setting  to determine whether you meant bytes or characters.

{{API:PodLink|file=DBD/Oracle|package_name=DBD::Oracle|section=|text=DBD::Oracle}} can crash when using <tt>PERL_USE_SAFE_PUTENV</tt>-compiled  Perls, see http://www.eprints.org/tech.php/13984.html.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_constants -->
==CONSTANTS==
See [[API:EPrints/Database#CONSTANTS|EPrints::Database]].

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_instance_variables -->
==INSTANCE VARIABLES==
See [[API:EPrints/Database#INSTANCE_VARIABLES|EPrints::Database]].

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_methods -->
==METHODS==
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_connect -->
===connect===

 $db-&gt;connect()
Connects to the database.  Also sets <tt>LongReadLen</tt> to <tt>128*1024</tt>.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_prepare_select -->
===prepare_select===

 $sth = $db-&gt;prepare_select( $sql, [ %options ] )
Prepare a <tt>SELECT</tt> statement <tt>$sql</tt> and return a handle to it. After preparing a statement use <tt>execute()</tt> to execute it.

Returns a {{API:PodLink|file=DBI|package_name=DBI|section=|text=DBI}} statement handle.

The <tt>LIMIT</tt> SQL keyword is not universally supported, to specify this use the <tt>limit</tt> option.

Options:

    limit - limit the number of rows returned
    offset - return '''limit''' number of rows after offset
  
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_create_archive_tables -->
===create_archive_tables===

 $success = $db-&gt;create_archive_tables()
Creates all the SQL tables for all datasets.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_get_server_version -->
===get_server_version===

 $version = $db-&gt;get_server_version
Returns the database server version.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_get_column_type -->
===get_column_type===

 $real_type = $db-&gt;get_column_type( $name, $type, $not_null, [ $length ] )
Returns a SQL column definition for <tt>$name</tt> of type <tt>$type</tt>. If  <tt>$not_null</tt> is <tt>true</tt> the column will be set to <tt>NOT NULL</tt>. For  column types that require a length use <tt>$length</tt>.

<tt>$type</tt> is the SQL type. The types are constants defined by this  module, to import them use:

  use EPrints::Database qw( :sql_types );
  
Supported types (n = requires LENGTH argument):

Character data: <tt>SQL_VARCHAR(n)</tt>, <tt>SQL_LONGVARCHAR</tt>.

Binary data: <tt>SQL_VARBINARY(n)</tt>, <tt>SQL_LONGVARBINARY</tt>.

Integer data: <tt>SQL_TINYINT</tt>, <tt>SQL_SMALLINT</tt>, <tt>SQL_INTEGER</tt>, 

Floating-point data: <tt>SQL_REAL</tt>, <tt>SQL_DOUBLE</tt>.

Time data: <tt>SQL_DATE</tt>, <tt>SQL_TIME</tt>.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_get_tables -->
===get_tables===

 @tables = $db-&gt;get_tables
Return a list of all the tables in the database.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_has_sequence -->
===has_sequence===

 $boolean = $db-&gt;has_sequence( $name )
Return <tt>true</tt> if a sequence of the given $&lt;name&gt; exists in the  database.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_has_column -->
===has_column===

 $boolean = $db-&gt;has_column( $table, $column )
Return <tt>true</tt> if the named database <tt>$table</tt> has the named  <tt>$column</tt>.

Default method from [[API:EPrints/Database|EPrints::Database]] this is really slow and this  is much faster.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_has_table -->
===has_table===

 $boolean = $db-&gt;has_table( $table )
Returns boolean dependent on whether the named <tt>$table</tt> exists in the  database.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_counter_current -->
===counter_current===

 $n = $db-&gt;counter_current( $counter )
Return the value of the previous counter_next on <tt>$counter</tt>.

Oracle doesn't support getting the <tt>current</tt> value of a sequence so this method always returns <tt>undef</tt>.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_quote_identifier -->
===quote_identifier===

 $id = $db-&gt;quote_identifier( @columns )
This method quotes and returns the given database identifier. If more  than one name is supplied joins them using the correct database  joining character (typically <tt>.</tt>).

Oracle restricts identifiers to:

   30 chars long
   start with a letter [a-z]
   { [a-z0-9], $, _, # }
   case insensitive
   not a reserved word (unless quoted?)
  
Identifiers longer than 30 characters will be abbreviated to the first  5 characters of the identifier and 25 characters from an MD5 derived  from the identifier. This should make name collisions unlikely.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_prepare_regexp -->
===prepare_regexp===

 $sql = $db-&gt;prepare_regexp( $col, $value )
Oracle use the syntax:

 REGEXP_LIKE ($col, $value)
 
For the quoted regexp <tt>$value</tt> and the quoted column <tt>$col</tt>.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_quote_binary -->
===quote_binary===

 $str = $db-&gt;quote_binary( $value )
Oracle requires transforms of binary data to work correctly.

This method should be called on data <tt>$value</tt> containing null bytes or back-slashes before being passed on [[API:EPrints/Database/Oracle#quote_value|quote_value]].

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_quote_ordervalue -->
===quote_ordervalue===

 $str = $db-&gt;quote_ordervalue( $field, $value )
Oracle can't order by <tt>CLOB</tt>S so need special treatment when creating  the ordervalues tables. This method fixes up <tt>$value</tt> to limit it to  1000 characters (4000 bytes) or returns <tt>undef</tt> if <tt>$value</tt> is not  defined.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_index_name -->
===index_name===

 $name = $db-&gt;index_name( $table, @cols )
Should return the name of the first index that starts with named  columns <tt>@cols</tt> in the named <tt>$table</tt>. However, this is not  supported by Oracle so always returns <tt>1</tt>.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_sql_as -->
===sql_as===

 $sql = $db-&gt;sql_AS()
Returns the syntactic glue to use when aliasing. Oracle does not  require a phrase so just returns a space character.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_retry_error -->
===retry_error===

 $boolean = $db-&gt;retry_error()
Returns a boolean for whether the database error is a retry error. Based on whether {{API:PodLink|file=DBI#err|package_name=DBI#err|section=|text=DBI#err}} code is <tt>3113</tt> or <tt>3114</tt>.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=item_type_info -->
===type_info===

 $type_info = $db-&gt;type_info( $data_type )
See {{API:PodLink|file=DBI|package_name=DBI|section=type_info|text=DBI/type_info}}.  Oracle has is own type information mappings.

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_see_also -->
==SEE ALSO==
[[API:EPrints/Database|EPrints::Database]]

<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=head_copyright -->
==COPYRIGHT==
{{API:Copyright}}
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
<span style='display:none'>User Comments</span>
<!-- Edit below this comment -->


<!-- Pod2Wiki= -->
</div>
<!-- Pod2Wiki=_postamble_ --><!-- Edit below this comment -->