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

From EPrints Documentation
Jump to: navigation, search
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:
  
−
  # Oracle driver settings for database.pl
+
<source lang="perl"># Oracle driver settings for database.pl
−
  $c-&gt;{dbdriver} = "Oracle";
+
$c->{dbdriver} = "Oracle";
−
  $c-&gt;{dbhost} = "localhost";
+
$c->{dbhost} = "localhost";
−
  $c-&gt;{dbsid} = "XE;
+
$c->{dbsid} = "XE;
−
  $c-&gt;{dbuser} = "HR";
+
$c->{dbuser} = "HR";
−
  $c-&gt;{dbpass} = "HR";
+
$c->{dbpass} = "HR";</source>
−
 
+
 
−
<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 50: Line 41:
 
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):
  
−
  export ORACLE_HOME="/usr/lib/oracle/xe/app/oracle/product/10.2.0/server"
+
<pre>  export ORACLE_HOME="/usr/lib/oracle/xe/app/oracle/product/10.2.0/server"
−
   export ORACLE_SID="XE"
+
   export ORACLE_SID="XE"</pre>
−
 
+
 
 
(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 82: Line 70:
 
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
  
−
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
Oracle treats empty string ("") as NULL.
−
<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===
−
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
* epadmin create
−
<span style='display:none'>User Comments</span>
+
* $name = $db-&gt;index_name( $table, @columns )
−
<!-- 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==
−
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
<!-- Pod2Wiki=head_get_server_version -->
−
<span style='display:none'>User Comments</span>
+
===get_server_version===
−
<!-- Edit below this comment -->
 
  
 +
<source lang="perl">$version = $db->get_server_version
  
−
<!-- Pod2Wiki= -->
+
</source>
−
</div>
 
−
<!-- Pod2Wiki=item_get_server_version -->
 
−
===get_server_version===
 
−
 
 
−
$version = $db-&gt;get_server_version
 
 
Return the database server version.
 
Return 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 -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
</div>
+
<!-- Pod2Wiki=head_get_column_type -->
−
<!-- Pod2Wiki=item_get_column_type -->
 
 
===get_column_type===
 
===get_column_type===
  
−
$real_type = $db-&gt;get_column_type( NAME, TYPE, NOT_NULL, [, LENGTH ] )
+
<source lang="perl">$real_type = $db->get_column_type( NAME, TYPE, NOT_NULL, [, LENGTH ] )
 +
 
 +
</source>
 
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.
 
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.
  
 
TYPE is the SQL type. The types are constants defined by this module, to import them use:
 
TYPE is the SQL type. The types are constants defined by this module, to import them use:
  
−
  use EPrints::Database qw( :sql_types );
+
<pre>  use EPrints::Database qw( :sql_types );</pre>
−
 
+
 
 
Supported types (n = requires LENGTH argument):
 
Supported types (n = requires LENGTH argument):
  
Line 162: Line 122:
 
Time data: SQL_DATE, SQL_TIME.
 
Time data: SQL_DATE, SQL_TIME.
  
−
<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_get_tables -->
−
<!-- Pod2Wiki=item_get_tables -->
 
 
===get_tables===
 
===get_tables===
  
−
@tables = $db-&gt;get_tables
+
<source lang="perl">@tables = $db->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= -->
−
</div>
+
<!-- Pod2Wiki=head_has_sequence -->
−
<!-- Pod2Wiki=item_has_sequence -->
 
 
===has_sequence===
 
===has_sequence===
  
−
$boolean = $db-&gt;has_sequence( $name )
+
<source lang="perl">$boolean = $db->has_sequence( $name )
 +
 
 +
</source>
 
Return true if a sequence of the given name exists in the database.
 
Return true if a sequence of the given name 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= -->
−
</div>
+
<!-- Pod2Wiki=head_has_column -->
−
<!-- Pod2Wiki=item_has_column -->
 
 
===has_column===
 
===has_column===
  
−
$boolean = $db-&gt;has_column( $tablename, $columnname )
+
<source lang="perl">$boolean = $db->has_column( $tablename, $columnname )
 +
 
 +
</source>
 
Return true if the a table of the given name has a column named $columnname in the database.
 
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= -->
−
</div>
+
<!-- Pod2Wiki=head_quote_identifier -->
−
<!-- Pod2Wiki=item_quote_identifier -->
 
 
===quote_identifier===
 
===quote_identifier===
  
−
$id = $db-&gt;quote_identifier( $col [, $col ] )
+
<source lang="perl">$id = $db->quote_identifier( $col [, $col ] )
 +
 
 +
</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 '.').
 
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:
  
−
  30 chars long
+
<pre>  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?)
+
   not a reserved word (unless quoted?)</pre>
−
 
+
 
 
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.
 
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.
  
−
<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_copyright -->
 
<!-- Pod2Wiki=head_copyright -->
 
==COPYRIGHT==
 
==COPYRIGHT==
−
<div style='background-color: #e8e8f; margin: 0.5em 0em 1em 0em; border: solid 1px #cce;  padding: 0em 1em 0em 1em; font-size: 80%; '>
+
Copyright 2000-2011 University of Southampton.
−
<span style='display:none'>User Comments</span>
+
 
 +
This file is part of EPrints http://www.eprints.org/.
 +
 
 +
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.
 +
 
 +
You should have received a copy of the GNU Lesser General Public License along with EPrints.  If not, see http://www.gnu.org/licenses/.
 +
 
 
<!-- Edit below this comment -->
 
<!-- Edit below this comment -->
  
  
 
<!-- Pod2Wiki= -->
 
<!-- Pod2Wiki= -->
−
</div>
+
<!-- Pod2Wiki=_postamble_ -->
−
<!-- Pod2Wiki=_postamble_ --><!-- Edit below this comment -->
+
<!-- Edit below this comment -->

Revision as of 09:56, 22 January 2013

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


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";


DESCRIPTION

Oracle database wrapper for Oracle DB version 9+.


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)


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

Oracle treats empty string ("") as NULL.


TODO

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


METHODS

get_server_version

$version = $db->get_server_version

Return the database server version.


get_column_type

$real_type = $db->get_column_type( NAME, TYPE, NOT_NULL, [, LENGTH ] )

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.

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.


get_tables

@tables = $db->get_tables

Return a list of all the tables in the database.


has_sequence

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

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


has_column

$boolean = $db->has_column( $tablename, $columnname )

Return true if the a table of the given name has a column named $columnname in the database.


quote_identifier

$id = $db->quote_identifier( $col [, $col ] )

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: 

   30 chars long
   start with a letter [a-z]
   { [a-z0-9], $, _, # }
   case insensitive
   not a reserved word (unless quoted?)

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.


COPYRIGHT

Copyright 2000-2011 University of Southampton.

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

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.

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



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