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

Latest revision as of 00:00, 12 January 2022

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

DESCRIPTION

Oracle database wrapper for Oracle DB version 9+.

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

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 characters.

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

User Comments

CONSTANTS

See EPrints::Database.

User Comments

INSTANCE VARIABLES

See EPrints::Database.

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.

Default method from EPrints::Database this is really slow and this is much faster.

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 )

Oracle use the syntax:

REGEXP_LIKE ($col, $value)

For the quoted regexp $value and 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

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

@@ Line 15: / Line 15: @@
 <!-- Pod2Wiki= -->
 </div>
-<!-- Pod2Wiki=head_synopsis -->
+<!-- Pod2Wiki=head_description -->
-==SYNOPSIS==
+==DESCRIPTION==
-These settings are the default settings for the free Oracle developer version:
+Oracle database wrapper for Oracle DB version 9+.
-  # 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>
@@ Line 33: / Line 26: @@
 <!-- Pod2Wiki= -->
 </div>
-<!-- Pod2Wiki=head_description -->
+<!-- Pod2Wiki=head_synopsis -->
-==DESCRIPTION==
+===Synopsis===
-Oracle database wrapper for Oracle DB version 9+.
+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>
@@ Line 48: / Line 48: @@
 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):
+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"
@@ Line 64: / Line 64: @@
 <!-- Pod2Wiki=head_oracle_specific_annoyances -->
 ===Oracle-specific Annoyances===
-Use the GQLPlus wrapper from http://gqlplus.sourceforge.net/ instead of sqlplus.
+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 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 does not support <tt>AUTO_INCREMENT</tt> (MySQL) nor <tt>SERIAL</tt>  (Postgres).
 Oracle won't <tt>ORDER BY LOBS</tt>.
@@ Line 78: / Line 78: @@
 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.
+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 -->
+{{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.
-<!-- Pod2Wiki= -->
-</div>
-<!-- Pod2Wiki=head_todo -->
-===TODO===
 <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>
@@ Line 98: / Line 89: @@
 <!-- Pod2Wiki= -->
 </div>
-<!-- Pod2Wiki=item_epadmin_create -->
+<!-- Pod2Wiki=head_constants -->
-====epadmin create====
+==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%; '>
@@ Line 108: / Line 100: @@
 <!-- Pod2Wiki= -->
 </div>
-<!-- Pod2Wiki=item_index_name -->
+<!-- Pod2Wiki=head_instance_variables -->
-====$name = $db-&gt;index_name( $table, @columns )====
+==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%; '>
@@ Line 248: / Line 241: @@
   $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%; '>
@@ Line 310: / Line 305: @@
 ===prepare_regexp===
-  $sql = $db-&gt;prepare_regexp( $col, $_value )
+  $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>.
+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%; '>
@@ Line 392: / Line 391: @@
   $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%; '>

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

Latest revision as of 00:00, 12 January 2022

Contents

NAME

DESCRIPTION

Synopsis

Setting up Oracle

Oracle-specific Annoyances

CONSTANTS

INSTANCE VARIABLES

METHODS

connect

prepare_select

create_archive_tables

get_server_version

get_column_type

get_tables

has_sequence

has_column

has_table

counter_current

quote_identifier

prepare_regexp

quote_binary

quote_ordervalue

index_name

sql_as

retry_error

type_info

SEE ALSO

COPYRIGHT

LICENSE

Navigation menu

Search