Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator 11g Release 1 (11.1.1) E12644-05 |
|
![]() Previous |
![]() Next |
This appendix describes how to work with the Oracle Data Integrator driver for LDAP.
This appendix includes the following sections:
With Oracle Data Integrator Driver for LDAP (LDAP driver) Oracle Data Integrator is able to manipulate complex LDAP trees using standard SQL queries.
The LDAP driver supports:
Manipulation of LDAP entries, their object classes and attributes
Standard SQL (Structured Query Language) Syntax
Correlated subqueries, inner and outer joins
ORDER BY and GROUP BY
COUNT, SUM, MIN, MAX, AVG and other functions
All Standard SQL functions
Referential Integrity (foreign keys)
Persisting modifications into directories
The LDAP driver works in the following way:
The driver loads (upon connection) the LDAP structure and data into a relational schema, using a LDAP to Relational Mapping.
The user works on the relational schema, manipulating data through regular SQL statements. Any changes performed in the relational schema data (insert/update) are immediately impacted by the driver in the LDAP data.
The LDAP to Relational Mapping is a complex but automated process that is used to generate a relational structure. As LDAP servers do not provide metadata information in a standard way, this mapping is performed using data introspection from the LDAP tree. Therefore, automatic mapping is carried out on the contents of the LDAP tree used as a source for this process.
This section contains the following topics:
The LDAP driver maps LDAP elements to a relational schema in the following way:
Each LDAP class or combination of classes is mapped to a table. Each entry from the LDAP tree is mapped to a record in the table.
Each attribute of the class instances is mapped to a column.
Hierarchical relationships between entries are mapped using foreign keys. A table representing a hierarchical level is created with a primary key called <tablename>PK
. Records reference their parent tables through a <parent_level_tablename>FK column
. The root of the LDAP tree structure is mapped to a table called ROOT
containing a ROOTPK
column in a unique record.
Attributes with multiple values for an entry (for example, a Person entry with several email attributes) are mapped as sub-tables called <parent_tablename><attribute_name>
. Each sub-table contains a <parent_tablename>FK column
linking it to the parent table.
Figure A-1 shows an LDAP tree with OrganizationalUnit entries linking to Person instances. In this case, certain Person entries have multiple email addresses.
This LDAP tree will be mapped into the following relational structure:
The ROOT
table represents the root of the hierarchy and contains one ROOTPK
column.
The ORGANIZATIONALUNIT
table represents different organizationalUnit instances of the tree. It contains the ORGANIZATIONALUNITPK
primary key column and the attributes of the organizationalUnit instances (cn, telephoneNumber, etc.). It is linked to the ROOT
table by the ROOTFK
foreign key column.
The PERSON
table represents the instances of the person class. It contains the PERSONPK
primary key column and the ORGANIZATIONALUNITFK
linking it to the ORGANIZATIONALUNIT
table and the attributes of PERSON
instances, (telephoneNumber, description, cn).
The email attribute appears as a PERSON_EMAIL
table containing the EMAIL
column and a PERSONFK
linking a list of email attributes to a PERSON
record.
Figure A-2 shows the resulting relational structure.
Figure A-2 Relational Structure mapped from the LDAP Tree Example shown in Figure A-1
In LDAP directories, class entries are often specified by inheriting attributes from multiple class definitions. In the relational mapping procedure, the LDAP driver translates this fact by combining each combination of classes in an LDAP entry to generate a new table.
For example, some entries of the Person class may also be instances of either of the Manager or BoardMember classes (or both). In this case, the mapping procedure would generate a PERSON
table (for the instances of Person) but also MANAGER_PERSON
, BOARDMEMBER_PERSON
, BOARDMEMBER_MANAGER_PERSON
and so forth, tables depending on the combination of classes existing in the LDAP tree.
In order to avoid unnecessary multiplication of generated tables, it is possible to parameterize this behavior. The Grouping Factor parameter allows this by defining the number of divergent classes below which the instances remain grouped together in the same table. This resulting table contains flag columns named IS_<classname>, whose values determine the class subset to which the instance belongs. For example, if IS_<classname>
is set to 1
, then the instance represented by the record belongs to <classname>
.
The behavior where one table is created for each combination of classes corresponds to a Grouping Factor equal to zero. With a grouping factor equal to one, instances with only one divergent class remain in the same table.
In our example, with a Grouping Factor higher than or equal to 2, all company person instances (including Person, Manager and BoardMember class instances) are grouped in the PERSON
table. The IS_MANAGER
and IS_BOARDMEMBER
columns enable the determination of PERSON records that are also in the Manager and/or BoardMember classes.
This section details some specific situations of the mapping process.
Table name length limits and collisions: In certain cases, name-length restrictions may result in possible object name collisions. The LDAP driver avoids such situations by automatically generating 3 digit suffixes to the object name.
Key column: It is possible to have the driver automatically create an additional SNPSLDAPKEY
column containing the Relative Distinguished Name (RDN) that can be used as identifier for the current record (original LDAP class instance). This is done by setting the key_column
URL property to true. This SNPSLDAPKEY
column must be loaded if performing DML commands that update the LDAP tree contents. Note that this column is created only in tables that originate from LDAP instances. Tables that correspond to multiple valued instance attributes will not be created with these columns.
Case sensitivity: This is set by the case_sens
URL property that makes the RDBMS and LDAP servers to enforce case-sensitivity.
Special characters: It is possible in LDAP to have non-alphanumeric characters into attribute or class names. These characters are converted to underscores ("_
") during the mapping. Exception: If non alphanumeric, the first character is converted to "x
".
SQL Reversed Keywords: Generated tables and columns with names that match SQL keywords are automatically renamed (an underscore is added after their name) in the relational structure to avoid naming conflicts between table/column names and SQL keywords. For example, a class named SELECT
will be mapped to a table named SELECT_
.
As LDAP servers do not provide metadata information in a standard way, the LDAP to Relational Mapping process is performed by default using data introspection from the LDAP tree.
With the LDAP driver it is also possible to use a Reference LDAP Tree for the LDAP to Relational Mapping process instead of using the LDAP tree that contains the actual data.
This Reference LDAP Tree is configured using the lm_props
property of the driver URL. This property specifies a.properties
file that contains the connection information to a LDAP tree whose hierarchical structure rigorously reflects that of the operational LDAP tree but without the accompanying data volume.
This technique reveals certain advantages:
The Reference LDAP Tree can be maintained by the directory administrator as a stable definition of the operational LDAP tree.
The Reference LDAP Tree contains few instances that make up the skeleton of the real LDAP tree, and the LDAP to Relational Mapping process runs faster on this small reference tree. This is particularly important for large operational LDAP directories, and will result in reduced processing time and resources for running the procedure.
The use of this technique, however, imposes a certain number of constraints in the design of the precise structure of the Reference LDAP Tree:
All optional LDAP instance attributes must be instantiated in the reference entries. Even if these attributes are absent in the operational LDAP directory entries, they must be declared in the Reference LDAP Tree if they are to be used at a later time.
Any multiple valued attributes that exist in the operational LDAP directory must be instantiated as such in the Reference LDAP Tree. For example, if any Person instance in the operational LDAP directory possesses two telephoneNumber attributes, then the generic Person class must instantiate at least two telephoneNumber attributes in the Reference LDAP Tree.
Note: These issues have a direct impact on the generated relational structure by forcing the creation of additional tables and columns to map multiple attribute fields and must be taken into consideration when designing the Reference LDAP Tree. |
This section contains the following topics:
The relational structure resulting from the LDAP to Relational mapping may be managed by virtual mapping or stored in an external database.
The virtual mapping stores the relational structure in the run-time agent's memory and requires no other component. The relational structure is transparently mapped by the driver to the LDAP tree structure. SQL commands and functions that are available for the LDAP driver are listed in the SQL Syntax.
Note: The virtual mapping may require a large amount of memory for large LDAP tree structures. |
The external database may be any relational database management system. The driver connects through JDBC to this engine and uses it to store the relational schema. This method provides the following benefits:
Processing and storage capabilities of the selected external database engine.
Acccess to the specific SQL statements, procedures, and functions of the external database engine.
Flexible persistence of the relational structure. This schema content may persist after the connection to the LDAP driver is closed.
See Section A.3.2, "Using an External Database to Store the Data" for more information on how to set up external storage.
DML operations on tables in the relational are executed with standard SQL statements.
Modifications made to the relational data are propagated to the directory depending on the selected storage :
In the case where the virtual mapping is used, all insert, update, and delete requests are automatically propagated to the original LDAP server in an autocommit mode. No explicit COMMIT or ROLLBACK statements will have any impact on the Oracle Data Integrator driver for LDAP.
In the case where the external database is used to store the relational structure, all types of DML statements may be used with the driver. However, it is important to know that no modifications will be propagated to the original LDAP server.
The Oracle Data Integrator driver for LDAP is automatically installed during the Oracle Data Integrator installation. The following topics cover advanced configuration topics and reference information.
This section contains the following topics:
Note: You must add the libraries and drivers required to connect the LDAP directory using JNDI to the Oracle Data Integrator classpath. |
Note: If using an external database engine you must also make sure that the JDBC driver used to connect to the external database and the .properties file are in the classpath. |
This section details the driver configuration.
The driver name is: com.sunopsis.ldap.jdbc.driver.SnpsLdapDriver
The driver supports two URL formats:
jdbc:snps:ldap?<property=value>[&...]
jdbc:snps:ldap2?<property=value>[&...]
The first URL requires the LDAP directory password to be encoded. The second URL allows you to give the LDAP directory password without encoding it.
Note: It is recommended to use the first URL to secure the LDAP directory password. |
The LDAP driver uses different properties depending on the established connection. Figure A-3 shows when to use which properties.
Figure A-3 Properties Files for LDAP Driver
The LDAP driver connects to the LDAP directory. You can configure this connection with the properties that start with ldap_
. For example, ldap_basedn
. Instead of passing the LDAP directory properties in the driver URL, you can use a properties file for the configuration of the connection to the LDAP directory. This properties file must be specified in the ldap_props
property of the driver URL.
If you want to use the hierarchical structure of the LDAP tree without the accompanying data volume, you can use the Reference LDAP tree. The connection to the Reference LDAP tree is configured with the properties that start with lm_
. For example, lm_basedn
. Instead of passing the lm_
properties in the driver URL, you can use a properties file. This properties file must be specified in the lm_props
property of the driver URL. See Section A.2.1.4, "Reference LDAP Tree" for more information.
To configure the connection of the LDAP driver to an external database, use the properties that start with db_
. For example, db_url
. Instead of passing the external database properties in the driver URL, you can use a properties file for the configuration of the connection to the external database. This properties file must be specified in the db_props
property of the driver URL. See Section A.3.2, "Using an External Database to Store the Data" for more information.
Table A-1 describes the properties that can be passed in the driver URL.
Table A-1 Driver Properties
Property | Mandatory | Type | Default | Description |
---|---|---|---|---|
db_props or dp |
No |
string (file location) |
Empty string |
Name of a Note: This property should contain the name of the .properties file without the file extension. Note: This Note: You can specify the external database connection configuration using all the |
ldap_props or lp |
No |
string (file location) |
N/A |
Name of a Note: This property should contain the name of the .properties file without the file extension. Note: This Note: You can specify the LDAP directory connection configuration using all the |
lm_props or lm |
No |
string (file location) |
N/A |
Name of a Note: This property should contain the name of the .properties file without the file extension. Note: This Note: You can specify the reference LDAP directory connection configuration using all the |
case_sens or cs |
No |
boolean (true | false) |
false |
Enable / disable case sensitive mode for both LDAP- and RDBMS-managed objects. |
alias_bundle or ab |
No |
string (file location) |
Empty string |
Full name of a properties file including both the absolute path to the properties file and the file extension. The properties file is a file that contains the list of aliases for the LDAP to Relational Mapping. If this file does not exist, it will be created by the driver. See Section A.3.4, "Table Aliases Configuration" for more information. Note: The file extension does not need to be |
alias_bundle_encoding or abe |
No |
string (encoding code) |
Default encoding |
Alias bundle file encoding. This encoding is used while reading and overwriting the alias_bundle file. If it is not defined then the default encoding would be used. You will find a list of supported encoding at the following URL: |
grouping_factor or gf |
No |
alphanumeric |
2 |
Determines how many object classes will be grouped together to set up a single relational table mapping. See Section A.2.1.2, "Grouping Factor" for more information. |
key_column or kc |
No |
boolean (true | false) |
false |
If set to true, a technical column called |
numeric_ids or ni |
No |
boolean (true | false) |
false |
If set to true, all internal Primary and Foreign Keys are of NUMERIC type. Otherwise, they are of the VARCHAR type. |
id_length or il |
No |
Alphanumeric |
10 / 30 |
The length of the internal Primary and Foreign Key columns. The default is 10 for NUMERIC column types and 30 for VARCHAR column types. |
table_prefix or tp |
No |
string |
N/A |
Prefix added to relational tables of the current connection. |
log_file or lf |
No |
string (file location) |
N/A |
Trace log file name. If the log file name is not set the trace data is displayed on the standard output. The presence of this property triggers trace logging for a particular relational schema. |
log_level or ll |
No |
alphanumeric |
1 |
Log level. This property is ignored if log_file is not specified. The log level can is a bit mask that can be specified either in hexadecimal or decimal value. Log Level Values:
Examples:
|
ldap_auth |
No |
string |
simple |
LDAP Directory authentication method. See the |
ldap_url |
Yes |
string |
N/A |
LDAP Directory URL. See the |
ldap_user |
No |
string |
Empty string |
LDAP Directory user name. See the |
ldap_password |
No |
string |
Empty string |
LDAP Directory user password. See the |
lldap_basedn |
No |
string |
N/A |
LDAP Directory basedn. See the |
lm_auth |
No |
string |
simple |
Reference LDAP authentication method. See the |
lm_url |
Yes |
string |
N/A |
Reference LDAP URL. See the |
lm_user |
No |
string |
Empty string |
Reference LDAP Directory user name. See the |
lm_password |
No |
string |
Empty string |
Reference LDAP Directory user password. See the |
lm_basedn |
No |
string |
N/A |
Reference LDAP Directory basedn. See the |
db_driver |
Yes |
string |
N/A |
External Database JDBC Driver. See the |
db_url |
Yes |
string |
N/A |
External Database JDBC URL. See the |
db_user |
No |
string |
Empty string |
External Database user. See the |
db_password |
No |
string |
Empty string |
External Database password. See the |
db_schema |
No |
string |
Empty string |
External Database schema. See the |
db_catalog |
No |
string |
Empty string |
External Database catalog. See the |
db_drop_on_disconnect or db_dod |
No |
boolean (true|false) |
true |
Drop tables on disconnect on the external database. See the |
db_load_mode or db_lm |
No |
string |
ci |
Loading method for the external database. See the |
tna |
No |
boolean (true|false) |
true |
Transform Non Ascii. Set to false to keep non-ascii characters. |
URL Examples
The following section lists URL examples:
jdbc:snps:ldap?lp=ldap_mir&ldap_basedn=o=tests&gf=10&lf=
Connects to the LDAP directory specified in the ldap_mir .properties
file, overriding the basedn property of the ldap bundle and using a grouping factor of 10. General information (important) is sent to the standard output.
jdbc:snps:ldap?lp=ldap_ours&lm=generic&ab=c:/tmp/aliases.txt&gf=10&kc=true
Connects to the LDAP directory using the ldap_ours .properties
file; a generic Directory tree for relational model creation is signaled by the lm property; an alias bundle file is used for the creation of the relational structure; a maximum grouping factor of 10 is used; key column creation is enabled for the SNPSLDAPKEY field to allow updates requests in the relational model.
jdbc:snps:ldap?lp=ldap_mir&dp=mysql_mir_ldap&ldap_basedn=dc=tests&lm=ldap_mir&lm_basedn=dc=model&ab=d:/temp/mapldap.txt&
Connects to the LDAP directory using the ldap_mir .properties
file; overriding ldap basedn property; using the "dc=model" subtree of the same directory to perform mapping; using an alias bundle; overriding the lm database property (load mode); specifying a grouping factor of 0 to indicate no grouping (grouping disabled); Full trace logging is activated.
Connects to a LDAP directory on the hydraroid machine. The LDAP server connection information - url, base dn, user and password - is specified in the URL using the ldap_xxx properties.
jdbc:snps:ldap?ldap_url=ldap://hydraroid:389/dc=localhost,dc=localdomain&ldap_password=KPLEKFMJKCLFJMDFDDGPGPDB&ldap_user=cn=orcladmin&ldap_basedn=ou=applications
The relational structure resulting from the LDAP to relational mapping of the LDAP tree can be stored in the run-time agent's memory or in an external database.
Note: The list of technologies that support external storage is available on Oracle Technical Network (OTN) :
|
The external storage is configured with a set of properties described in Table A-2.
The external storage properties can be passed in several ways:
The properties can be directly set in the driver URL. When using this method, the properties have to be prefixed with db_
. For example, if connecting to an Oracle database, specify the Oracle JDBC driver name in the driver
parameter as follows:
db_driver=oracle.jdbc.driver.OracleDriver
.
The properties can be specified on the Properties tab of the Data Server editor in Topology Navigator. When using this method, the properties have to be prefixed with db_
. For example, if you want to set the driver
parameter:
In the Key column, enter db_driver
In the Value column, enter oracle.jdbc.driver.OracleDriver
if you are connecting to an Oracle database.
The properties can be set in an external database properties file. This properties file, also called property bundle, is a text file with the .properties
extension containing a set of lines with on each line a <property>=<value>
pair.This external database porperties file contains the properties of a JDBC connection to the relational database schema. The properties file is referenced using the db_props
property in the JDBC URL.
Note: It is important to understand that the LDAP driver loads external property bundle files once only at runtime startup. If errors occur in these files, it is advisable to exit Oracle Data Integrator and then reload it before re-testing. |
When using this method, note the following:
The properties in the properties file are not prefixed and used as described in Table A-2.
The db_props
property is set to the name of the properties file without the .properties
extension. For example, if you have in your classpath the prod_directory.properties
file, you should refer to this file as follows: db_props=prod_directory
.
The db_props
property indicates that the schema must be loaded in a database schema whose connection information is stored in a external database properties file.
The properties files have to be deployed by the agent using the LDAP connection. The location the properties file depends on the agent you are using:
Local agent (Studio): Place the external DB properties file in the <user.dir>/odi/oracledi/userlib
folder
Standalone Agent: Place the external DB properties file in oracledi/agent/drivers
folder
JavaEE Agent: The external DB properties file should be packed into a JAR or ZIP file and added to the template generated by the Java EE agent. See "Deploying an Agent in a Java EE Application Server (Oracle WebLogic Server)" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for more information.
When using property bundle files, you must make sure that the property bundle is present in the Oracle Data Integrator classpath. Typically, you should install this bundle in the drivers directories.
Note: When connecting to the external database, the LDAP driver uses JDBC connectivity. Make sure that the JDBC driver to access this external database is also available in the ODI classpath. |
It is possible to set or override the external database properties on the URL. These properties must be prefixed with the string db_
. For example:
jdbc:snps:ldap?ldap_url=ldap://localhost:389/&ldap_basedn=o=company&db_driver=oracle.jdbc.driver.OracleDriver
&db_url=<external_db_url>
The properties for configuring external storage are described inTable A-2.
Table A-2 External Database Connection Properties
Property | Mandatory | Type | Default | Description |
---|---|---|---|---|
driver |
Yes |
string |
N/A |
JDBC driver name |
url |
Yes |
string |
N/A |
JDBC URL |
user |
No |
string |
Empty string |
Login used to connect the database |
password |
No |
string |
Empty string |
Encrypted database user password. Note: To encrypt the password, use the |
schema |
No |
string |
Empty string |
Database schema storing the LDAP Tree. This property should not be used for Microsoft SQLServer, and the catalog property should be used instead. |
catalog |
No |
string |
Empty string |
Database catalog storing the LDAP Tree. For Microsoft SQL Server only. This property should not be used simultaneously with the schema property. |
drop_on_disconnect or dod |
No |
boolean (true | false) |
true |
If true, drop the tables from the database at disconnection time. If set to false the tables are preserved in the database. |
load_mode or lm |
No |
string |
ci |
The loading method. Values may be:
|
The following is an example of an external database .properties
file to connect to an external Oracle database:
driver=oracle.jdbc.OracleDriver url=jdbc:oracle:thin:@hydraro:1521:SNPTST1 user=LDAP_T_1 password=ENCODED_PASSWORD schema=LDAP_T_1
The Oracle Data Integrator driver for LDAP uses the properties described in Table A-3 to connect to a directory server that contains the LDAP data or the Reference LDAP Tree. These properties can be provided either in a property bundle file or on the driver URL.
The properties for configuring a directory connection are detailed in Table A-3.
Table A-3 LDAP Directory Connection Properties
Property | Mandatory | Type | Default | Description |
---|---|---|---|---|
auth |
No |
string |
simple |
The authentication method |
url |
Yes |
string |
N/A |
URL to connect to the directory. It is an LDAP URL. Note: This driver supports the LDAPS (LDAP over SSL) protocol. The LDAPS URL must start with ldaps://. To connect a server using LDAPS, you must manually install the certificate in the java machine. See the keytool program provided with the JVM for more information. |
user |
No |
string |
Empty string |
The LDAP server user-login name. Mandatory only if "auth" is set. Note: If user and password properties are provided to create the connection with the JDBC Driver for LDAP, then they are used to connect the LDAP directory. |
password |
No |
string |
Empty string |
LDAP server user-login password. Mandatory only if "auth" is set. Note: The password needs to be encrypted, unless the 'jdbc:snps:ldap2' URL syntax. Note: To encrypt the password, use the |
basedn |
No |
string |
N/A |
The base dn with which you wish to connect to the LDAP tree. The base dn is the top level of the LDAP directory tree. If it not specified, the base dn specified in the LDAP URL is used. |
The following is an example of an LDAP properties file content:
url=ldap://ours:389 user=cn=Directory Manager password=ENCODED_PASSWORD basedn=dc=oracle,dc=com
The LDAP driver allows a certain flexibility in the definition of the model table names in Oracle Data Integrator by the use of table aliases. This is particularly useful when the algorithm used to navigate the LDAP tree generates long composite names from the LDAP object class hierarchy. To avoid issues related to RDBMS-specific object name-length constraints, the LDAP driver can set up and use aliases.
Note: It is also possible to change the default "Maximum Table Name Length" and "Maximum Column Name Length" values on the Others tab of the Technology Editor in the Physical Architecture accordion. |
To create a table alias file:
In the LDAP Driver Data Server URL, include and set the alias_bundle (ab) property that indicates the name of the alias text file, for example:
jdbc:snps:ldap?.....&ab=C:/tmp/aliases.txt&...
.
The alias file is created by the driver at connection time when the alias_bundle
property is specified. Typically, a user connects initially through the LDAP driver which creates this file containing a list of potential table names to be created by the reverse-engineering operation.
Test the connection to the LDAP data server.
Verify the that the text file has been created and has the expected structure. The list consists of <original table name > = <desired alias name>
values. Example A-1 shows an extract of an alias file after the user has provided shortened names. See step 4 for more information.
In the alias text file, add short text value aliases to replace the originally derived composite names and save the file.
Reconnect to the same LDAP data server. The relational schema is created and this time the aliases will be used for defining relational table names.
Now reverse-engineer the LDAP directory as described in Section 26.5.2, "Reverse-Engineering an LDAP Model". Oracle Data Integrator will create datastores with the table names defined as aliases in the alias file.
Note: If any modifications have been applied to the object class structure or attribute sets of the LDAP directory, the driver will rewrite this file while including the new or modified entries to the table name list. |
The SQL statements described in Section A.4.1, "SQL Statements" are available when using the Oracle Data Integrator driver for LDAP. They enable the management of relational data structure and data through standard SQL Syntax.
Note:
|
Table A-4 summarizes the recommendations to apply when performing the listed DML operations on specific key fields.
Table A-4 DML Opertaions on Key Fields
Type of Column | Insert | Update | Delete |
---|---|---|---|
Foreign Key |
Pay attention to master table referential constraints and ordered table populate operations. |
Not permitted |
Pay attention to master table referential constraints and ordered delete requests. |
Primary Key |
Pay attention to slave table referential constraints and ordered table populate operations. |
Not permitted |
Pay attention to slave table referential constraints and ordered delete requests |
IS_xxx |
Pay attention to associating the correct flag value to the original object class. |
Not permitted |
OK |
Key_Column |
Pay attention to setting the RDN value in the correct LDAP syntax. |
Not permitted |
OK |
Any number of commands may be combined. The semicolon (;) may be used to separate each command but is not necessary.
DISCONNECT
Closes this connection.
Remarks
It is not required to call this command when using the JDBC interface: it is called automatically when the connection is closed.
After disconnecting, it is not possible to execute other queries with this connection.
Insert one or more new rows of data into a table.
INSERT INTO <table_name> [ ( <column_name> [,...] ) ]
{ VALUES (<expression> [,...]) | <SELECT Statement> }
Retrieves information from one or more tables in the schema.
SELECT [DISTINCT] { <select_expression> | <table_name>.* | * } [, ... ] [ INTO <new_table> ] FROM <table_list> [ WHERE <expression> ] [ GROUP BY <expression> [, ...] ] [ ORDER BY <order_expression> [, ...] ] [ { UNION [ALL] | {MINUS|EXCEPT} | INTERSECT } <select_statement> ]
<table_list> ::=
<table_name> [ { INNER | LEFT [OUTER] } JOIN <table_name> ON <expression> ]
[, ...]
<select_expression> ::=
{ <expression> | COUNT(*) | {COUNT | MIN | MAX | SUM | AVG}
(<expression>) <column_alias>}
<order_expression> ::=
{ <column_number> | <column_alias> | <select_expression> } [ ASC | DESC ]
Modifies data of a table in the database.
UPDATE table SET column = <expression> [, ...] [WHERE <expression>]
<expression> ::=
[NOT] <condition> [ { OR | AND } <condition>
]
<condition> ::= { <value> [ || <value> ] | <value> { = | < | <= | > | >= | <> | != | IS [NOT] } <value> | EXISTS(<select_statement>) | <value> BETWEEN <value> AND <value> | <value> [NOT] IN ( {<value> [, ...] | selectStatement } ) | <value> [NOT] LIKE <value> [ESCAPE] value }
<value> ::= [ + | - ] { term [ { + | - | * | / } term ] | ( condition ) | function ( [parameter] [,...] ) | selectStatement giving one value
<term> ::=
{ 'string' | number | floatingpoint | [table.]column | TRUE | FALSE | NULL }
<string> ::=
Starts and ends with a single '. In a string started with ' use '' to create a '.
LIKE uses '%' to match any (including 0) number of characters, and '_' to match exactly one character. To search for '%' itself, '\%' must be used, for '_' use '\_'; or any other escaping character may be set using the ESCAPE clause.
<name> ::=
A name starts with a letter and is followed by any number of letters or digits. Lowercase is changed to uppercase except for strings and quoted identifiers. Names are not case-sensitive.
Quoted identifiers can be used as names (for example for tables or columns). Quoted identifiers start and end with ". In a quoted identifier use "" to create a ". With quoted identifiers it is possible to create mixed case table and column names. Example: CREATE TABLE "Address" ("Nr" INTEGER,"Name" VARCHAR); SELECT * FROM "Address". Quoted identifiers are not strings.
<values> ::=
A 'date' value starts and ends with ', the format is yyyy-mm-dd (see java.sql.Date).
A 'time' value starts and ends with ', the format is hh:mm:ss (see java.sql.Time).
Binary data starts and ends with ', the format is hexadecimal. '0004ff' for example is 3 bytes, first 0, second 4 and last 255 (0xff).
Table A-5 describes the numeric functions.
Table A-5 Numeric Functions
Function | Description |
---|---|
ABS(d) |
returns the absolute value of a double value |
ACOS(d) |
returns the arc cosine of an angle |
ASIN(d) |
returns the arc sine of an angle |
ATAN(d) |
returns the arc tangent of an angle |
ATAN2(a,b) |
returns the tangent of a/b |
BITAND(a,b) |
returns a & b |
BITOR(a,b) |
returns a | b |
CEILING(d) |
returns the smallest integer that is not less than d |
COS(d) |
returns the cosine of an angle |
COT(d) |
returns the cotangent of an angle |
DEGREES(d) |
converts radians to degrees |
EXP(d) |
returns e (2.718...) raised to the power of d |
FLOOR(d) |
returns the largest integer that is not greater than d |
LOG(d) |
returns the natural logarithm (base e) |
LOG10(d) |
returns the logarithm (base 10) |
MOD(a,b) |
returns a modulo b |
PI() |
returns pi (3.1415...) |
POWER(a,b) |
returns a raised to the power of b |
RADIANS(d) |
converts degrees to radians |
RAND() |
returns a random number x bigger or equal to 0.0 and smaller than 1.0 |
ROUND(a,b) |
rounds a to b digits after the decimal point |
SIGN(d) |
returns -1 if d is smaller than 0, 0 if d==0 and 1 if d is bigger than 0 |
SIN(d) |
returns the sine of an angle |
SQRT(d) |
returns the square root |
TAN(d) |
returns the trigonometric tangent of an angle |
TRUNCATE(a,b) |
truncates a to b digits after the decimal point |
Table A-6 describes the string functions.
Table A-6 String Functions
Function | Description |
---|---|
ASCII(s) |
returns the ASCII code of the leftmost character of s |
BIT_LENGTH(s) |
returns the string length in bits |
CHAR(c) |
returns a character that has the ASCII code c |
CHAR_LENGTH(s) |
returns the string length in characters |
CONCAT(str1,str2) |
returns str1 + str2 |
DIFFERENCE(s1,s2) |
returns the difference between the sound of s1 and s2 |
HEXTORAW(s1) |
returns the string translated from hexadecimal to raw |
INSERT(s,start,len,s2) |
returns a string where len number of characters beginning at start has been replaced by s2 |
LCASE(s) |
converts s to lower case |
LEFT(s,count) |
returns the leftmost count of characters of s |
LENGTH(s) |
returns the number of characters in s |
LOCATE(search,s,[start]) |
returns the first index (1=left, 0=not found) where search is found in s, starting at start |
LTRIM(s) |
removes all leading blanks in s |
OCTET_LENGTH(s) |
returns the string length in bytes |
RAWTOHEX(s) |
returns translated string |
REPEAT(s,count) |
returns s repeated count times |
REPLACE(s,replace,s2) |
replaces all occurrences of replace in s with s2 |
RIGHT(s,count) |
returns the rightmost count of characters of s |
RTRIM(s) |
removes all trailing blanks |
SOUNDEX(s) |
returns a four character code representing the sound of s |
SPACE(count) |
returns a string consisting of count spaces |
SUBSTR(s,start[,len]) |
(alias for substring) |
SUBSTRING(s,start[,len]) |
returns the substring starting at start (1=left) with length len. Another syntax is SUBSTRING(s FROM start [FOR len]) |
TRIM |
TRIM([{LEADING | TRAILING | BOTH}] FROM s): removes trailing and/or leading spaces from s. |
UCASE(s) |
converts s to upper case |
LOWER(s) |
converts s to lower case |
UPPER(s) |
converts s to upper case |
Table A-7 describes the date and time functions.
Table A-7 Date and Time Functions
Function | Description |
---|---|
CURDATE() |
returns the current date |
CURTIME() |
returns the current time |
CURRENT_DATE |
returns the current date |
CURRENT_TIME |
returns the current time |
CURRENT_TIMESTAMP |
returns the current timestamp |
DATEDIFF(s, d1,d2) |
returns the counts of unit of times specified in s elapsed from datetime d1 to datetime d2. s may take the following values: 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. |
DAYNAME(date) |
returns the name of the day |
DAYOFMONTH(date) |
returns the day of the month (1-31) |
DAYOFWEEK(date) |
returns the day of the week (1 means Sunday) |
DAYOFYEAR(date) |
returns the day of the year (1-366) |
EXTRACT |
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM <datetime>): extracts the appropriate part from the <datetime> value. |
HOUR(time) |
return the hour (0-23) |
MINUTE(time) |
returns the minute (0-59) |
MONTH(date) |
returns the month (1-12) |
MONTHNAME(date) |
returns the name of the month |
NOW() |
returns the current date and time as a timestamp |
QUARTER(date) |
returns the quarter (1-4) |
SECOND(time) |
returns the second (0-59) |
WEEK(date) |
returns the week of this year (1-53) |
YEAR(date) |
returns the year |
Note that A date value starts and ends with ', the format is yyyy-mm-dd (see java.sql.Date). A time value starts and ends with ', the format is hh:mm:ss (see java.sql.Time).
Table A-8 describes the system functions.
Table A-8 System Functions
Function | Description |
---|---|
IFNULL(exp,value) |
if exp is null, value is returned else exp |
CASEWHEN(exp,v2,v2) |
if exp is true, v1 is returned, else v2 |
CONVERT(term,type) |
converts exp to another data type |
COALESCENCE(e1,e2,e3,...) |
if e1 is not null then it is returned, else e2 is evaluated. If e2 is null, then is it returned, else e3 is evaluated and so on. |
NULLIF(v1,v2) |
returns v1 if v1 is not equal to v2, else returns null |
CASE WHEN |
There are two syntax for the CASE WHEN statement: CASE v1 WHEN v2 THEN v3 [ELSE v4] END: if v1 equals v2 then returns v3 [otherwise v4 or null if ELSE is not specified]. CASE WHEN e1 THEN v1[WHEN e2 THEN v2] [ELSE v4] END: when e1 is true return v1 [optionally repeated for more cases] [otherwise v4 or null if there is no ELSE] |
CAST(term AS type) |
converts exp to another data type |
Table A-9 describes the system and connection functions.
Table A-10 lists the JDBC API features of the Oracle Data Integrator driver for LDAP.
Table A-10 JDBC API Features
Feature Groups | JDBC Version | Support |
---|---|---|
Batch Update |
2.0 Core |
Yes |
Blob/Clob |
2.0 Core |
No |
JNDI DataSources |
2.0 Optional |
No |
Failover support |
- |
No |
Transaction SavePoints |
3.0 |
No |
Unicode support |
- |
No |
Disributed Transaction |
2.0 Optional |
No |
Connection Pooling |
2.0 Optional |
No |
Cluster support |
- |
No |
The following table identifies the JDBC classes supported by the Oracle Data Integrator driver for LDAP.
Table A-11 JDBC Classes
JDBC Classes | JDBC Version | Support |
---|---|---|
Array |
2.0 Core |
No |
Blob |
2.0 Core |
No |
Clob |
2.0 Core |
No |
CallableStatement |
1.0 |
Yes |
Connection |
1.0 |
Yes |
ConnectionPoolDataSource |
2.0 Optional |
No |
DatabaseMetaData |
1.0 |
Yes |
DataSource |
2.0 Optional |
No |
Driver |
1.0 |
Yes |
PreparedStatement |
1.0 |
Yes |
Ref |
2.0 Core |
No |
RowSet |
2.0 Optional |
No |
ResultSet |
1.0 |
Yes |
ResultSetMetaData |
1.0 |
Yes |
Statement |
1.0 |
Yes |
Struct |
2.0 Core |
No |
XAConnection |
2.0 Optional |
No |
XADataSource |
2.0 Optional |
No |