Difference between revisions of "API:EPrints/Database/Oracle"

From EPrints Documentation
Jump to: navigation, search
Line 1: Line 1:
 
<!-- Pod2Wiki=_preamble_  
 
<!-- Pod2Wiki=_preamble_  
−
This page has been automatically generated from the EPrints 3.2 source. Any wiki changes made between the 'Pod2Wiki=*' and 'Edit below this comment' comments will be lost.
+
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=perl_lib/EPrints/Database/Oracle.pm|package_name=EPrints::Database::Oracle}}[[Category:API|ORACLE]][[Category:API:EPrints/Database|ORACLE]][[Category:API:EPrints/Database/Oracle|ORACLE]]<div><!-- Edit below this comment -->
+
  -->{{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 -->
  
  
Line 8: Line 8:
 
'''EPrints::Database::Oracle''' - custom database methods for Oracle DB
 
'''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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 +
</div>
 
<!-- Pod2Wiki=head_synopsis -->
 
<!-- Pod2Wiki=head_synopsis -->
 
==SYNOPSIS==
 
==SYNOPSIS==
 
These settings are the default settings for the free Oracle developer version:
 
These settings are the default settings for the free Oracle developer version:
  
−
<source lang="perl"># Oracle driver settings for database.pl
+
  # Oracle driver settings for database.pl
−
$c->{dbdriver} = "Oracle";
+
  $c-&gt;{dbdriver} = "Oracle";
−
$c->{dbhost} = "localhost";
+
  $c-&gt;{dbhost} = "localhost";
−
$c->{dbsid} = "XE;
+
  $c-&gt;{dbsid} = "XE;
−
$c->{dbuser} = "HR";
+
  $c-&gt;{dbuser} = "HR";
−
$c->{dbpass} = "HR";</source>
+
  $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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 +
</div>
 
<!-- Pod2Wiki=head_description -->
 
<!-- Pod2Wiki=head_description -->
 
==DESCRIPTION==
 
==DESCRIPTION==
 
Oracle database wrapper for Oracle DB version 9+.
 
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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 +
</div>
 
<!-- Pod2Wiki=head_setting_up_oracle -->
 
<!-- Pod2Wiki=head_setting_up_oracle -->
 
===Setting up Oracle===
 
===Setting up Oracle===
Line 41: Line 50:
 
Set the ORACLE_HOME and ORACLE_SID environment variables. To add these globally edit /etc/profile.d/oracle.sh (for XE edition):
 
Set the ORACLE_HOME and ORACLE_SID environment variables. To add these globally edit /etc/profile.d/oracle.sh (for XE edition):
  
−
<pre>  export ORACLE_HOME="/usr/lib/oracle/xe/app/oracle/product/10.2.0/server"
+
  export ORACLE_HOME="/usr/lib/oracle/xe/app/oracle/product/10.2.0/server"
−
   export ORACLE_SID="XE"</pre>
+
   export ORACLE_SID="XE"
−
 
+
 
 
(Will need to relog to take effect)
 
(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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 +
</div>
 
<!-- Pod2Wiki=head_oracle_specific_annoyances -->
 
<!-- Pod2Wiki=head_oracle_specific_annoyances -->
 
===Oracle-specific Annoyances===
 
===Oracle-specific Annoyances===
Line 70: Line 82:
 
DBD::Oracle can crash when using PERL_USE_SAFE_PUTENV-compiled Perls, see http://www.eprints.org/tech.php/13984.html
 
DBD::Oracle can crash when using PERL_USE_SAFE_PUTENV-compiled Perls, see http://www.eprints.org/tech.php/13984.html
  
−
Oracle treats empty string ("") as NULL.
+
<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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 +
</div>
 
<!-- Pod2Wiki=head_todo -->
 
<!-- Pod2Wiki=head_todo -->
 
===TODO===
 
===TODO===
−
* epadmin create
+
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
−
* $name = $db-&gt;index_name( $table, @columns )
+
<span style='display:none'>User Comments</span>
 +
<!-- Edit below this comment -->
 +
 
 +
 
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_epadmin_create -->
 +
====epadmin create====
 +
 
 +
<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 -->
 +
====$name = $db-&gt;index_name( $table, @columns )====
 +
 
 +
<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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
 +
</div>
 
<!-- Pod2Wiki=head_methods -->
 
<!-- Pod2Wiki=head_methods -->
 
==METHODS==
 
==METHODS==
−
<!-- Pod2Wiki=head_get_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%; '>
−
===get_server_version===
+
<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 -->
 +
 
  
−
<source lang="perl">$version = $db->get_server_version
+
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_create_archive_tables -->
 +
===create_archive_tables===
  
−
</source>
+
$success = $db-&gt;create_archive_tables()
−
Return the database server version.
+
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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
<!-- Pod2Wiki=head_get_column_type -->
+
</div>
−
===get_column_type===
+
<!-- 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 -->
  
−
<source lang="perl">$real_type = $db->get_column_type( NAME, TYPE, NOT_NULL, [, LENGTH ] )
 
  
−
</source>
+
<!-- Pod2Wiki= -->
−
Returns a column definition for NAME of type TYPE. If NOT_NULL is true the column will be created NOT NULL. For column types that require a length use LENGTH.
+
</div>
 +
<!-- Pod2Wiki=item_get_column_type -->
 +
===get_column_type===
  
−
TYPE is the SQL type. The types are constants defined by this module, to import them use:
+
$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>.
  
−
<pre>  use EPrints::Database qw( :sql_types );</pre>
+
<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):
 
Supported types (n = requires LENGTH argument):
  
−
Character data: SQL_VARCHAR(n), SQL_LONGVARCHAR.
+
Character data: <tt>SQL_VARCHAR(n)</tt>, <tt>SQL_LONGVARCHAR</tt>.
  
−
Binary data: SQL_VARBINARY(n), SQL_LONGVARBINARY.
+
Binary data: <tt>SQL_VARBINARY(n)</tt>, <tt>SQL_LONGVARBINARY</tt>.
  
−
Integer data: SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER.
+
Integer data: <tt>SQL_TINYINT</tt>, <tt>SQL_SMALLINT</tt>, <tt>SQL_INTEGER</tt>,
  
−
Floating-point data: SQL_REAL, SQL_DOUBLE.
+
Floating-point data: <tt>SQL_REAL</tt>, <tt>SQL_DOUBLE</tt>.
  
−
Time data: SQL_DATE, SQL_TIME.
+
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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
<!-- Pod2Wiki=head_get_tables -->
+
</div>
 +
<!-- Pod2Wiki=item_get_tables -->
 
===get_tables===
 
===get_tables===
  
−
<source lang="perl">@tables = $db->get_tables
+
@tables = $db-&gt;get_tables
−
 
 
−
</source>
 
 
Return a list of all the tables in the database.
 
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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
<!-- Pod2Wiki=head_has_sequence -->
+
</div>
 +
<!-- Pod2Wiki=item_has_sequence -->
 
===has_sequence===
 
===has_sequence===
  
−
<source lang="perl">$boolean = $db->has_sequence( $name )
+
$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>.
 +
 
 +
<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===
  
−
</source>
+
$boolean = $db-&gt;has_table( $table )
−
Return true if a sequence of the given name exists in the database.
+
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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
<!-- Pod2Wiki=head_has_column -->
+
</div>
−
===has_column===
+
<!-- Pod2Wiki=item_counter_current -->
 +
===counter_current===
  
−
<source lang="perl">$boolean = $db->has_column( $tablename, $columnname )
+
$n = $db-&gt;counter_current( $counter )
 +
Return the value of the previous counter_next on <tt>$counter</tt>.
  
−
</source>
+
Oracle doesn't support getting the <tt>current</tt> value of a sequence so this method always returns <tt>undef</tt>.
−
Return true if the a table of the given name has a column named $columnname 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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
<!-- Pod2Wiki=head_quote_identifier -->
+
</div>
 +
<!-- Pod2Wiki=item_quote_identifier -->
 
===quote_identifier===
 
===quote_identifier===
  
−
<source lang="perl">$id = $db->quote_identifier( $col [, $col ] )
+
$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>).
−
</source>
 
−
This method quotes and returns the given database identifier. If more than one name is supplied joins them using the correct database join character (typically '.').
 
  
 
Oracle restricts identifiers to:
 
Oracle restricts identifiers to:
  
−
<pre>  30 chars long
+
  30 chars long
 
   start with a letter [a-z]
 
   start with a letter [a-z]
 
   { [a-z0-9], $, _, # }
 
   { [a-z0-9], $, _, # }
 
   case insensitive
 
   case insensitive
−
   not a reserved word (unless quoted?)</pre>
+
   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 )
 +
The syntax used for regular expressions varies across databases. This method takes two quoted string and returns a SQL expression that will apply the quoted regexp <tt>$value</tt> to 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.
  
−
Identifiers longer than 30 chars will be abbreviated to the first 5 chars of the identifier and 25 characters from an MD5 derived from the identifier. This should make name collisions unlikely.
+
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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
<!-- Pod2Wiki=head_copyright -->
+
</div>
−
==COPYRIGHT==
+
<!-- Pod2Wiki=item_quote_ordervalue -->
−
Copyright 2000-2011 University of Southampton.
+
===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>.
  
−
This file is part of EPrints http://www.eprints.org/.
+
<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 -->
  
−
EPrints is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
 
  
−
EPrints is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more details.
+
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=item_type_info -->
 +
===type_info===
  
−
You should have received a copy of the GNU Lesser General Public License along with EPrints. If not, see http://www.gnu.org/licenses/.
+
  $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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
<!-- Pod2Wiki=_postamble_ -->
+
</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 -->
 
<!-- Edit below this comment -->
 +
 +
 +
<!-- Pod2Wiki= -->
 +
</div>
 +
<!-- Pod2Wiki=_postamble_ --><!-- Edit below this comment -->

Revision as of 16:29, 25 December 2021

EPrints 3 Reference: Directory Structure - Metadata Fields - Repository Configuration - XML Config Files - XML Export Format - EPrints data structure - Core API - Data Objects

API: Core API

Latest Source Code (3.4, 3.3) | Revision Log | Before editing this page please read Pod2Wiki


NAME

EPrints::Database::Oracle - custom database methods for Oracle DB

User Comments


SYNOPSIS

These settings are the default settings for the free Oracle developer version: 

 # Oracle driver settings for database.pl
 $c->{dbdriver} = "Oracle";
 $c->{dbhost} = "localhost";
 $c->{dbsid} = "XE;
 $c->{dbuser} = "HR";
 $c->{dbpass} = "HR";

User Comments


DESCRIPTION

Oracle database wrapper for Oracle DB version 9+.

User Comments


Setting up Oracle

Enable the HR user in Oracle XE.

Set the ORACLE_HOME and ORACLE_SID environment variables. To add these globally edit /etc/profile.d/oracle.sh (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)

User Comments


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 LIMIT().

Oracle does not support AUTO_INCREMENT (MySQL) nor SERIAL (Postgres).

Oracle won't ORDER BY LOBS.

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

Oracle doesn't support "AS" when aliasing.

When specifying char column lengths use (n char) to define character semantics. Otherwise oracle uses the "nls_length_semantics" setting to determine whether you meant bytes or chars.

DBD::Oracle can crash when using PERL_USE_SAFE_PUTENV-compiled Perls, see http://www.eprints.org/tech.php/13984.html

User Comments


TODO

User Comments


epadmin create

User Comments


$name = $db->index_name( $table, @columns )

User Comments


METHODS

User Comments


connect

$db->connect()

Connects to the database. Also sets LongReadLen to 128*1024.

User Comments


prepare_select

$sth = $db->prepare_select( $sql, [ %options ] )

Prepare a SELECT statement $sql and return a handle to it. After preparing a statement use execute() to execute it.

Returns a DBI statement handle.

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

Options: 

   limit - limit the number of rows returned
   offset - return limit number of rows after offset

User Comments


create_archive_tables

$success = $db->create_archive_tables()

Creates all the SQL tables for all datasets.

User Comments


get_server_version

$version = $db->get_server_version

Returns the database server version.

User Comments


get_column_type

$real_type = $db->get_column_type( $name, $type, $not_null, [ $length ] )

Returns a SQL column definition for $name of type $type. If $not_null is true the column will be set to NOT NULL. For column types that require a length use $length.

$type 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: SQL_VARCHAR(n), SQL_LONGVARCHAR.

Binary data: SQL_VARBINARY(n), SQL_LONGVARBINARY.

Integer data: SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER,

Floating-point data: SQL_REAL, SQL_DOUBLE.

Time data: SQL_DATE, SQL_TIME.

User Comments


get_tables

@tables = $db->get_tables

Return a list of all the tables in the database.

User Comments


has_sequence

$boolean = $db->has_sequence( $name )

Return true if a sequence of the given $<name> exists in the database.

User Comments


has_column

$boolean = $db->has_column( $table, $column )

Return true if the named database $table has the named $column.

User Comments


has_table

$boolean = $db->has_table( $table )

Returns boolean dependent on whether the named $table exists in the database.

User Comments


counter_current

$n = $db->counter_current( $counter )

Return the value of the previous counter_next on $counter.

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

User Comments


quote_identifier

$id = $db->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 .).

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.

User Comments


prepare_regexp

$sql = $db->prepare_regexp( $col, $_value )

The syntax used for regular expressions varies across databases. This method takes two quoted string and returns a SQL expression that will apply the quoted regexp $value to the quoted column $col.

User Comments


quote_binary

$str = $db->quote_binary( $value )

Oracle requires transforms of binary data to work correctly.

This method should be called on data $value containing null bytes or back-slashes before being passed on quote_value.

User Comments


quote_ordervalue

$str = $db->quote_ordervalue( $field, $value )

Oracle can't order by CLOBS so need special treatment when creating the ordervalues tables. This method fixes up $value to limit it to 1000 characters (4000 bytes) or returns undef if $value is not defined.

User Comments


index_name

$name = $db->index_name( $table, @cols )

Should return the name of the first index that starts with named columns @cols in the named $table. However, this is not supported by Oracle so always returns 1.

User Comments


sql_as

$sql = $db->sql_AS()

Returns the syntactic glue to use when aliasing. Oracle does not require a phrase so just returns a space character.

User Comments


retry_error

$boolean = $db->retry_error()

Returns a boolean for whether the database error is a retry error. Based on whether DBI#err code is 3113 or 3114.

User Comments


type_info

$type_info = $db->type_info( $data_type )

See DBI/type_info. Oracle has is own type information mappings.

User Comments


COPYRIGHT

© Copyright 2000-2024 University of Southampton.

EPrints 3.4 is supplied by EPrints Services.

http://www.eprints.org/eprints-3.4/

LICENSE

This file is part of EPrints 3.4 http://www.eprints.org/.

EPrints 3.4 and this file are released under the terms of the GNU Lesser General Public License version 3 as published by the Free Software Foundation unless otherwise stated.

EPrints 3.4 is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with EPrints 3.4. If not, see http://www.gnu.org/licenses/.

User Comments


Retrieved from ‘https://wiki.eprints.org/w/index.php?title=API:EPrints/Database/Oracle&oldid=13978’