Oracle OLE DB source in SSIS

An OLE DB source to Oracle can be added to a SSIS package.

Example data

Server: locksnlatches.mydomain.com
Oracle instance/SID: dwh
Username/schema: scott
Password: tiger

An OLE DB source to Oracle can be added as follows:

• From the menubar, select SSIS -> New connection... -> OLEDB -> Add -> New
  Provider: Oracle Provider for OLE DB
  Server or filename: locksnlatches/dwh
  User name: scott
  Password: tiger

Notes: 

• The "Oracle Provider for OLE DB" provider always exists in SSIS. It is a wrapper around the Oracle Client driver that is installed when installing Integration Services. However, the Oracle Client has to be installed separately. It can be downloaded from the Oracle website: instantclient-basiclite-nt-11.2.0.2.0. Install the 32 bit version of the driver to be able to use it in the design environment. The Visual Studio / SSIS IDE does not support 64 bit components
• It is possible to use the 64 bit Oracle Client driver during execution of the package, e.g. via dtexec, but it needs to be installed additionally. The SSIS package has to be configured to use this different version during runtime.
• If the server cannot be found, then use the fully qualified name. One reason might be that the Oracle server is located in a different (Windows) domain as the SSIS server.

Reference SQL Server table with spaces from Oracle

It is possible to select data from a SQL Server table that contains spaces by using a database link from Oracle.

The syntax is as follows:

SELECT * FROM "DBO$TABLE WITH SPACES"@MYDBLINK

with

SQL Server login/user: dbo
SQL Server table name: table with spaces
DB link: mydblink

Query Oracle from SQL Server

Start with the following steps:

• Install the Oracle Instant Client (OIC) on the machine where SQL Server is running and add the "Oracle Provider for OLE DB" driver, e.g. to folder c:\oracle11g.
• For a SQL Server x64 version running on a Windows x64 OS download the instantclient-basiclite-windows.x64-11.2.0.2.0.zip. This is a 22 MB download.
• Then add the Oracle Data Access components, to get the "OLE DB for Oracle" driver:  http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
  At the time of writing (March 2012): select the "64-bit ODAC 11.2 Release 4 (11.2.0.3.0) Xcopy for Windows x64". This is a 54 MB download
• Inside SQL Server Management Studio:
  Linked Servers -> Providers -> OraOLEDB.Oracle -> Allow inprocess = true
• Optional: to enable adhoc querying (querying without a linked server), then enable this:
• SQL Server 2005 via surface area configuration tool
• SQL Server 2008 via facets: inside SQL Server Management Studio, right click the server -> facets -> facet: surface area configuration -> AdHocRemoteQueries = true
• Optional: if the connection to Oracle should be based on a TNS names entry, then add an "admin" folder to the OIC folder. Put or create an tnsnames.ora file there, e.g. c:\oracle11g\admin\tnsnames.ora
• Optional: use "tnsping" to test the entry from the command prompt, e.g. "tnsping dwh.world". If it doesn't recognize the entry, then create an environment variable called "TNS_ADMIN" and let it point to this "admin" folder, e.g. c:\oracle11g\admin
• Optional: if the following steps to get data from an Oracle instance don't work, then edit this registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI.
  Set the following values:
  OracleXaLib=oraclient11.dll
  OracleSqlLib=orasql11.dll
  OracleOciLib=oci.dll

Note: these DDLs can be found in the c:\oracle11g\product\11.2.0\client_1\BIN folder. The specific version depends on the Oracle Client and might be for example oraclient10.dll en orasql10.dll. Use the names of the DDLs as found in this folder.

Three ways to get the data from an Oracle instance

Example:

Server: locksnlatches
Oracle instance (SID): dwh
Port: 1521
Schema/user: scott
Password: tiger
tnsnames.ora entry:

dwh.world =
(DESCRIPTION=
  (CID=DWH_APP)
  (ADDRESS_LIST=
    (ADDRESS=
      (PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)
     )
   )
  (CONNECT_DATA=
    (SID=dwh)
  )
)

1) Using a linked server

• Linked Servers -> New linked server... ->  General ->
• linked server: lnk_locksnlatches (this name can be anything)
• provider: Oracle provider for OLE DB
• product name: oracle (this name can be anything)
• data source: (DESCRIPTION=(CID=DWH_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)))(CONNECT_DATA=(SID=dwh)))
• provider string: leave empty!
• location: leave empty!
• catalog: leave empty!
• Linked Servers -> New linked server... ->  Security ->
• Be made using this security context
• Remote login: scott
• With password: tiger

Use the four-point notation to retrieve data from Oracle:

SELECT 1 FROM lnk_locksnlatches..scott.dual

Note: the "security" tab specifies the Oracle schema/user and password used by the connection!

2) Without a linked server (TNS-less)

SELECT * FROM OPENROWSET('OraOLEDB.Oracle', '(DESCRIPTION=(CID=DWH_APP)(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=locksnlatches)(PORT=1521)))(CONNECT_DATA=(SID=dwh)))';'scott';'tiger','SELECT 1 FROM DUAL') x

3) Without a linked server (TNS-full)

SELECT * FROM OPENROWSET('OraOLEDB.Oracle', 'dwh.world';'scott';'tiger','SELECT 1 FROM DUAL') x

Quipu connections

Several ways to create a database connection in the open source data warehouse generator Quipu.

SQL Express

• SQL Server configuration manager -> 
• Protocols for SQLExpress -> TCP/IP: enabled
• TCP/IP -> IP Adresses -> IP All -> TCP Dynamic Ports: empty
• TCP/IP -> IP Adresses -> IP All -> TCP Port: 666
• SQL Server management studio -> SQL Express server properties -> Security -> Server Authentication: SQL Server and Windows Authentication mode
• Create a SQL Server authentication login on the database server and assign it to the database that will be used by the connection, e.g. login: quipu, password: quipu
• If the database runs on the same machine as Quipu, use "localhost" as the host name. Otherwise use the remote host name

Note:

• Do not use the instance name, e.g. using "AdventureWorks;instance=sqlexpress" as database works, but will, depending on the SQL Server version, result in an "unable to get information" error.

Working with dates in OBIEE

Here are several options to set date formats in OBIEE. Option #1 is the best way to work with dates and comparison of dates to strings containing dates in a particular format.

See also: http://gerardnico.com/wiki/dat/obiee/cast_as_date

The most important settings (with example values) are:

• DATE_DISPLAY_FORMAT parameter in nqsconfig.ini: YYYY/MM/DD
• DATESHORTFORMAT parameter in localedefinitions.xml (based on the locale set in nqsconfig.ini): M/D/YYYY
• NLS_DATE_FORMAT parameter of the session (or database default): DD-MON-RR

Do not confuse these with parameters in dbfeatures.ini. There this format is the default for almost all database platforms in parameter DATE_FORMAT parameter.

Method 1 is used for implicit casting only if physical -> Database -> Features: CAST_SUPPORTED is available and enabled. Otherwise the settings described in method 4 (nqsconfig.ini) are used.

1) Tell Oracle how to interpret the casting of a string to date or vice versa (preferred method)

Explicitly set the Oracle NLS property for this to work unambiguously. This property can be set on a database level, affecting all connections made to the database. Alternatively, the property can also be set on the OBI connection in the physical layer of the Admin tool to only affect the current session:

• Double-click "Connection pool" for the Oracle connection
• Select "Connection Scripts" tab
• Add "Execute on Connect" script and type:
  ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD'

Then, use the following function in Answers formulae:

CAST('20110215' TO DATE)

Note: to check the value of the NLS_DATE_FORMAT parameter at the database level, e.g. from SQL Developer, execute the following statement:

SELECT * FROM (

SELECT 'instance' as domain, parameter, value FROM NLS_INSTANCE_PARAMETERS UNION

SELECT 'database' as domain, parameter, value FROM NLS_DATABASE_PARAMETERS UNION

SELECT 'session' as domain, parameter, value FROM NLS_SESSION_PARAMETERS

) x WHERE PARAMETER ='NLS_DATE_FORMAT'

2) Convert a date to string or vice versa using an Oracle database function

Use the OBIEE function EVALUATE to make use of an Oracle database function. In this case, TO_DATE:

Convert string to date:
EVALUATE('TO_DATE(%1,%2)' AS DATE,'20111231','YYYYMMDD')

Examples of what doesn't work:
EVALUATE('TO_DATE(%1)' AS DATE,'20100101')

--> does not work, unless the date happens to be in the nls parameter format

CAST('20110215' TO DATE) 

--> does not work, unless the string happens to be in the nls parameter format

Convert date to string:
EVALUATE('TO_CHAR(%1,%2)' AS CHAR,DIM_DATE.TODAY,'YYYYMMDD')

EVALUATE('TO_CHAR(%1,''YYYYMMDD'')' AS CHAR,DIM_DATE.TODAY)

Examples of what doesn't work:
Conversions may fail with "date format not recognized" or "datetime value does not match the specified format".

EVALUATE('TO_CHAR(%1,%2)',DIM_DATE.TODAY,'YYYYMMDD')
--> does not work, because the result needs to be explicitly cast to a text datatype

EVALUATE('TO_CHAR(%1)',DIM_DATE.TODAY)
--> does not work, unless the date happens to be in the nls parameter format

3) Convert a date to string using different OBIEE functions

An alternative method is to convert the date to a string in stead of converting a testing string to a date. The difference is this:

DATE = ConvertToDate(STRING) 

vs 

ConvertToString(DATE) = STRING

Convert date to number:

year(FACT_ORDERLINE.DATE) * 10000 + month(FACT_ORDERLINE.DATE) * 100 + dayofmonth(FACT_ORDERLINE.DATE) = 20110231

Note: here the lefthand and righthand side are numbers, not strings, but the idea is the same.

4) Convert a string to date using the OBIEE function "DATE"

In OBIEE, an equivalent of the Oracle function TO_DATE is the DATE function which has a fixed syntax:

YYYY-MM-DD. 

For example:

DATE '2011-01-15'

Note:

• This syntax cannot be configured and does not depend on locale or nls settings
• The function cannot be found in the list of functions when adding a formula in Answers. 
• It does not use ( or ) signs, but only single-quotes

5*) Set uniform datatypes in the Physical Layer

Set the datatype property in the physical layer on the fields/attributes. Allowed date/time datatypes are:

• DATE
• TIMESTAMP

Try to use one, e.g. DATE, as the default to achieve a more universal way of working with dates. TIMESTAMP is required when there is an essential time part. If it is always "00:00:00", then use DATE.

6*) Locale definitions in configuration files

• [bipath]\web\config\localedefinitions.xml
  -- displays possible formats to choose from
  -- locale "us-en" is based on locale: us
  -- locale "us" contains all possible attributes to choose from, so is independend of the regional settings
  -- of the Windows server
  locale: us-en
• [bipath]\server\config\nqsconfig.ini
  -- for displaying messages
  -- this maps to "us-en" in localedefinitions.xml
  LOCALE = "English-usa";
  -- for input and output of dates
  DATE_TIME_DISPLAY_FORMAT = "yyyy/mm/dd hh:mi:ss";
  DATE_DISPLAY_FORMAT = "yyyy/mm/dd";
  TIME_DISPLAY_FORMAT = "hh:mi:ss";
  -- when on, obi does not guess what a string containing a date means
  STRONG_DATETIME_TYPE_CHECKING = ON;

7*) Locale definitions in Answers webinterface

Set the locale, e.g. "English - United states" on the connection window or in your account settings: Settings -> Account

8*) Save display format

The default display format of a date is set in dbfeatures.ini (?)

It can be overriden for all date datatypes or specific attributes in Answers:

Criteria -> Column properties -> Save -> As the system-wide...

Test casting of date to string

Example: database or session NLS parameter = 'YYYYMMDD'

CAST(DIM_DATE.TODAY AS CHAR)
CAST('20110211' AS DATE)
CAST('01-FEB-11' AS DATE) --> will fail if nls parameter is set to YYYYMMDD

Notes:

• Column properties -> date format (i.e.date or text) is set automatically after the result of a conversion. It does not steer, in any way, the input of functions.

CTAS disregards number precision and scale

When using a "create table as select" (CTAS) to create a table based on a query, when that query also contains a window function/analytical function in the SELECT-clause, results in the precision and scale being omitted.

The precision/scale are neglected, when running the following example in Oracle 11g:

CREATE OR REPLACE FORCE VIEW VSA_X AS
SELECT
CAST(1 AS NUMERIC(10,2)) AS FLD1,
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
FROM DUAL;

DROP TABLE X;

CREATE TABLE X AS SELECT * FROM VSA_X;

Table X will have no precision/scale for FLD1. It is bypassed and will simply have NUMBER for its datatype. This is a bug in Oracle.

Note:

• Using NUMBER in stead of NUMERIC makes no difference, the bug is still there.
• Explicitly casting the ROW_NUMBER() function also makes no difference.

Solution:

Put the window function inside a derived table:

CREATE OR REPLACE FORCE VIEW VSA_X AS
SELECT
CAST(FLD1 AS NUMERIC(10,2)) AS FLD1,
RN
FROM
(
  SELECT
  1 AS FLD1,
  ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
  FROM DUAL
) x;

Keywords: CTAS omits precision, CTAS omits number precision, CTAS bypasses precision, CTAS bypasses number precision.

TNS service names ODBC dropdown box garbled, messed up, gibberish

Most likely cause is that there are two or more different Oracle clients installed, with two or more home folders and tnsnames.ora files.

For example, an Oracle 10 client is installed or Oracle XE is installed. Both contain a home folder with tnsnames.ora. Later, the Oracle 11g instant client + ODBC compontents is added. Now there are two home folders.

In this case, the TNS_ADMIN environment variable needs to exist:

• Create TNS_ADMIN variable via control panel -> system -> advanced -> environment variables -> system variables
• Set is to one of the admin folders, eg.:
  c:\oracle\ic_x32_11.2.0.1.0_basiclite\NETWORK\ADMIN
  Note: make sure is contains the tnsnames.ora, sqlnet.ora and listener.ora files.
• Create a ODBC connection using the Oracle driver and check if the "tns service name" dropdown box contains readable entries
• Just to be sure, replace the body of all other tnsnames.ora files with:
  IFILE=c:\oracle\ic_x32_11.2.0.1.0_basiclite\NETWORK\ADMIN

SQL Developer thick/OCI driver

The following error messages may appear when using SQL Developer to select data from a SQL Server table via a (heterogeneous or DG4ODBC) database link on an Oracle 11g DB:

Bigger type length than Maximum
OALL8 is in an inconsistent state

Solution: switch to the thick driver to work around this.

In SQL Developer: tools -> preferences -> database -> advanced -> use oci/thick driver = checked.

If this does not work or when getting the error messages:

no ocijdbc11 in java.library.path
vendor code 0

then:

- Download Oracle Instant Client 11.1.0.7.0 basic lite (easily found with Google, register with Oracle to download)

Note: version 11.2 does not work with SQL Developer!

Note: take the 64-bits version on a 64-bits OS, like Windows7 x64.

- Extract files to c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Add to environment variable PATH: c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Create a batch file called sd.bat in the rootfolder of the SQL Developer installation, with the following body:

set ORACLE_HOME="c:\ora\instantclient_x64_11.1.0.7.0_basiclite"
start sqldeveloper.exe

- Double-click sd.bat to start SQL Developer
- Add a connection with Connection type = basic

To make a connection with Connection type = TNS:

- Create folder c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Put these files in the admin folder:

sqlnet.ora
tnsnames.ora

- In SQL Developer: tools -> preferences -> database -> advanced -> tnsnames folder: c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Add a connection with Connection type = TNS

Note the following:

• The solution above even (or especially) works when another Oracle client is already installed. For example, if the OracleXE DB (which, at the time of writing, is 10g) is installed locally, then the Oracle Client 10 is also installed. But this client might not work when trying to connect to a remote Oracle 11g DB
• There is no need to set the environment variables ORACLE_HOME or TNS_ADMIN variable on the system. It does not matter what their values are
• The order of folders in the variable PATH does not matter
• Most likely Help -> About -> Properties -> oracle.home does not point to the correct Oracle home folder. So not to c:\ora\instantclient_x64_11.1.0.7.0_basiclite. It does not matter what the value is
• Unfortunately, the ORACLE_HOME trick does not work with the PATH variable; it is no use adding that to the batch file
• The error "Bigger type length than Maximum" is related to a bug in the implementation of the thin part of the JDBC driver. Not to heterogeneous services, Oracle DB, SQL Server, SQL Developer or the thick part of the JDBC driver. (Note: the same driver, e.g. ojdbc6.jar, contains the thin and the thick classes)

Oracle JDBC connections

The following JDBC drivers exist for Oracle:

• classes111.jar: Java 1.1
• classes12.jar: 1.2 and 1.3
• ojdbc14.jar: 1.4 and 1.5 (no JDBC3 and 4 features)
• ojdbc5.jar: 1.5
• ojdbc6.jar: 1.6

Java 1.5 = Java JRE/SE 5 and Java 1.6 = Java JRE/SE 6. Additional jar files are required for some features.

All the classes to support basic functionality for the Thin and OCI drivers are inside the same jar. In short, for simplification, the same file contains both the thin and the thick (=OCI) classes.

Joe Weinstein on forums.oracle.com:

The jar is all of the thin driver and the java portion of the OCI driver. In order to
run in OCI mode, you would use this same jar, giving it the OCI URL instead of the thin
URL, and you would have to have the whole Oracle client installed on your machine,
and your OS environment set up to point to the OCI DLLs as well as the standard C
libraries. Note that the version of the OCI libraries that works with one driver jar
may not work with another driver jar, so if you go the OCI route, you may have to
install a whole new OCI client just to get a particular driver fix.

Thick URL: jdbc:oracle:oci8:@<database_name>
The database_name is the Net Service name of a mapping in tnsnames.ora.

Thin URL: jdbc:oracle:thin:@<server>:1521:<database_name>
The database_name is the database name part of the database service on the (remote) server. E.g. Windows service "OracleServiceORCL", then the database_name is "ORCL".

Oracle released different versions for each driver, eg. 11.1.0.6.0 or 11.1.0.7.0.

To get the different versions, download the Oracle Instant Client of choice from the Oracle website and extract the ojdbc.jar files to one folder. Rename them using the version. This makes testing or switching easy:

ojdbc6_11gR1_11.1.0.6.0.jar
ojdbc6_11gR1_11.1.0.7.0.jar
ojdbc6_11gR1_11.2.0.1.0.jar
etc

Databases

Row-oriented:
Oracle DB
SQL Server
SQL Server CE
MySql: InnoDB
Sybase Advantage Database Server (ADS)
MS Access

Column-oriented:
Extreme-DB
InfiniDB Enterprise Edition
Infobright
MonetDB
ParAccel Analytic Database
Sybase IQ
Sybase Adaptive Server Enterprise (RDBMS & column-oriented)
Vectorwise
Vertica

Appliance:
Aster Data (MPP, MapReduce)
Greenplum (MPP, MapReduce)
Netezza (MPP)
Teradata (MPP)

In-process:
SQLite

Change column position

In Oracle, it is not straightforward to change the position of a column. Let alone one that contains data.

Without resolving to manually changing metadata tables, but using SQL, the following trick can be applied:

- Create a new table called "newtable" with the desired column order, based on "oldcolumn"

Note: generate DDL script for "oldtable" and only change the position of the columns. Do not apply constraints, defaults, primary keys, foreign keys, etc. They are named and the names cannot exists twice in a schema

- Insert all data from "oldtable" into "newtable" using a DML INSERT-statement
- Drop "oldtable"
- Rename "newtable" as "oldtable"
- Apply constraints, defaults, etc to "oldtable"

Note: if columns are deleted or newly added to "newtable", then this is not straightforward and each constraint, default, etc should be inspected before applying

Or you can use the "dbms_redefinition" package (bhaski on www.geekinterview.com):

But you should have these previleges -->
# execute access on the dbms_redefinition package
# create any table # alter any table
# drop any table
# lock any table
# select any table

create table tab1(col1 char,col3 number);

alter table tab1 add(col2 char);

create table tab2 as select col1,col2,col3 from tab1; exec

dbms_redefinition.can_redef_table('schema_name','tab1');

begin
dbms_redefinition.start_redef_table('schema_name','tab1','tab2','col1,col2,col3');
dbms_redefinition.finish_redef_table( 'schema_name', 'tab1', 'tab2' );
end;

select * from tab1;
col1 col2 col3

Change column data type

In Oracle, it is not straightforward to change the data type of a column that contains data. Without resolving to manually changing metadata tables, but using SQL, the following trick can be applied:

- Add a new column to the table called "new column" with the new data type
- Copy the values of "old column" to "new column" using a DML UPDATE statement
- Remove the "old column" from the table using a DDL ALTER statement

Identifying keys

A key that is made up of one field is a simple key. All other keys are combined keys. There are two types of combined keys, compound and composite:

Simple key: key that consists of one field

Combined key: key that consists of more than one field

Compound key: combined key in which each field is itself a simple key (of another entity)

Composite key: combined key, that is not a compound key

A new term I have made up myself:

Concatenated key: a simple key, that is the concatenation of the fields in a combined key

Truncate tables that have a foreign key

A script to truncate tables that have foreign keys referencing them. It also keeps one record in the table, the one with ID=0. In this case, each table has this field as a primary key.

Finally, some tables are not to be truncated. The readible names of these tables are in a metadata table called TDWM_TABLES. The readible name is in the field VIEWNAME.

The process is:

- disable constraints on the selected tables, i.e. with names like 'TDWH_%'
- create a new intermediate table via CTAS that contains the records to keep, i.e. with ID=0
- truncate the table
- copy records back from the intermediate table
- drop intermediate table
- enable constraints on the selected tables, i.e. with names like 'TDWH_%'. Note: enable PK constraints before enabling the FK constraints!

Here is the script:

/* SCRIPT */

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN

-- Disable constraints
DBMS_OUTPUT.PUT_LINE ('-> Disabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' CASCADE' as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%')
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;

-- Truncate tables
DBMS_OUTPUT.PUT_LINE ('-> Truncating tables');
FOR reg IN (
SELECT
'CREATE TABLE Z_' || TABLE_NAME || ' AS SELECT * FROM ' || TABLE_NAME || ' WHERE ID=0' AS ST1,
'TRUNCATE TABLE ' || TABLE_NAME AS ST2,
'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM Z_' || TABLE_NAME AS ST3,
'DROP TABLE Z_' || TABLE_NAME AS ST4
FROM USER_TABLES t
INNER JOIN TDWM_TABLES b ON UPPER(t.TABLE_NAME) = UPPER(b.TABLENAME)
WHERE t.TABLE_NAME LIKE 'TDWH_%' AND
UPPER(b.VIEWNAME) NOT IN ('TDAYOFWEEK','TMONTHOFYEAR','TQUARTEROFYEAR')
)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
DBMS_OUTPUT.PUT_LINE (reg.ST2); EXECUTE IMMEDIATE reg.ST2;
DBMS_OUTPUT.PUT_LINE (reg.ST3); EXECUTE IMMEDIATE reg.ST3;
DBMS_OUTPUT.PUT_LINE (reg.ST4); EXECUTE IMMEDIATE reg.ST4;
END LOOP;

-- Enable constraints
DBMS_OUTPUT.PUT_LINE ('-> Enabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%' ORDER BY CONSTRAINT_NAME DESC)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;
END;

Unlimited login attempts on Oracle 11g

In Oracle 10g, by default, each user has unlimited login attempts before being blocked. In Oracle 11g this behaviour is changed to 10 attempts.

When a user/schema is created, it gets assigned to the default profile. The easiest way for unlimited login attempts for all users is to give the default profile the rights:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Reconfigure OracleXE built-in webserver's port

OracleXE comes with a built-in webserver, running on port 8080. Since this is a wellknown port, chances are that it is already in use on your machine, and therefore conflicting with an already running webserver.

Check this blog to reconfigure it:

http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html

External tables

Data can be imported into and exported from Oracle 10g in different ways. E.g. by using the SQL*Loader tool for text files (sqlldr) or the Data Pump (impdp/expdp) tools for the propriety Oracle RAW format. For previous Oracle versions, the tools imp and are also available. Those import/export to external locations on the client machine, not the server machine.

Another way to get data in and out of Oracle is to define an external table. To do this you have to create a table in Oracle in the regular way using a CREATE TABLE statement, but with adding an additional ORGANIZATION EXTERNAL clause.

Within this clause the properties of the external (server side!) location can be specified.

EXAMPLE

CREATE TABLE TEST
(
  "CODE" VARCHAR2(17 BYTE)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_PUMP_DIR
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET WE8MSWIN1252
    FIELDS TERMINATED BY 0X'09'
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
  )
  LOCATION ( DATA_PUMP_DIR: 'EXTFILE.TXT' )
)

FIELD LIST

The list of fields following the create table keywords cannot be inferred dynamically, but has to be specified explicitly. So it is not possible to "hook up" a text file and retrieving the list of fields automatically.

Trick: import the data first into a table using SQL Developer, TOAD, etc, and create the DDL script from it. Finally, copy and paste the field list from this script.

It is also possible to specify the "external" list of fields inside the organization external clause. This is, for example, similar to how it is done using Oracle Warehouse Builder (OWB).

TYPE

One of these properties is "TYPE", which specifies the access driver that Oracle uses. There are two values for this property:

• ORACLE_LOADER: only read access, from all kinds of text files (but not, for example, Excel .xls)
• ORACLE_DATAPUMP: read and write access, a propriety RAW format

The access driver is a wrapper Oracle uses to call the previously mentioned SQL*Loader or Data Pump functionality.

An error occurs when an insert statement is performed on a table is created with access driver ORACLE_LOADER:

SQL Error: ORA-30657: operation not supported on external organized table30657.0000 -  "operation not supported on external organized table"
*Cause:    User attempted on operation on an external table which is
           not supported.
*Action:   Don't do that!

The ORACLE_DATAPUMP type has to be used to export data from Oracle using an external table. Unfortunately, this data cannot be humanly read or imported into any other tool then an Oracle database.

DEFAULT DIRECTORY / LOCATION

A directory object has to be available in Oracle. If it is not already present, it has to be created. For example:

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'C:\'

Now the tag DATA_PUMP_DIR can be used in the organization external clause. The DEFAULT DICTIONARY property is mandatory and has to be used when setting the LOCATION.

A path specification is not allowed between the single-quotes.

This is not valid:

• LOCATION ( 'C:\EXTFILE.TXT' ) 

ACCESS PARAMETERS

The options are rather straightforward, except perhaps "FIELDS TERMINATED BY". To set the field delimiter a character can be specified between single-quotes. But it is also possible to specify special characters like tab or linefeed.

To specify a tab:

FIELDS TERMINATED BY 0X'09'

The 0X instructs Oracle that the value between the single-quotes should be interpreted as an ASCII value. In the example, ASCII character 9 is a tab.

OPTIONALLY ENCLOSED BY '"'

It is also possible to specify that some values have double-quotes around them. This however does not work in the following case:

Column 1 value - tab - tab - Column 3 value

Here a record has a value for columns 1 and 3, but not for column 2. It should be read as NULL, but is actually skipped. This results in the value of column 3 being wrongly inserted in column 2.

CHARACTER SET

Explicitly setting the character set of the external location prevents problems with interpreting the data and can improve performance.

From Odi's blog:

If you do not specify the CHARACTERSET parameter, the database character set is used to interpret the input file. As the input file is in WE8MSWIN1252, the ORACLE_LOADER driver gets confused trying to interpret single-byte WE8MSWIN1252 codes as multibyte AL32UTF8 codes.

The character set of the input file depends on the way it was created. Even on US Windows, you can create text files in different encodings. Notepad allows you to save the file in ANSI code page (=WE8MSWIN1252 on US Windows), Unicode (=AL16UTF16LE), Unicode big endian (=AL16UTF16), and UTF-8 (=AL32UTF8). The Command Prompt edit.exe editor saves the files in the OEM code page (=US8PC437 on US Windows).

-- Sergiusz

LOGGING

All errors are logged to a bad file - containing the violating records - and a log file - containing messages about the error that occured.

To disable logging, (some of) the following keywords can be added to the clause:

• NODISCARDFILE
• NOBADFILE
• NOLOGFILE

Oracle specification of the ORACLE_LOADER access driver:

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10825/et_params.htm

Measuring disk I/O speed

Disk thruput tester is a handy tool to test the sequential read and write performance of storage media, like harddisks and SSD. It supports configurating custom block sizes:

http://www.objectso.nl/

Using this tool, testing the read/write performance of 8KB, 16KB, 32KB, etc, sized blocks is a breeze. The size and location of the test file is also configurable.

Note: on the website the tool is called "disk throughput tester".

Cursor variants in Oracle 10g

Examples of different types of cursors. Each has its own pros and cons. Example 1 is the most straightforward and easiest to understand. The variants in example 3 perform better. I've put them here for reference.

-- 1) implicit cursor

DECLARE l_old_tbs VARCHAR(100) := 'USERS';
BEGIN
FOR item IN (
SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs )
LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;

-- 2) explicit cursor

DECLARE l_old_tbs VARCHAR(100);
CURSOR c1 IS (SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs);
BEGIN
l_old_tbs := 'USERS'; -- set variable here, otherwise it is NULL!
FOR item IN c1 LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;

-- 3a) local collection variable with multiple fields using BULK COLLECT and FORALL

CREATE TABLE TEST1 (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30));
DECLARE l_old_tbs VARCHAR(100);
TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN l_old_tbs:= 'USERS';
-- fill local collection "tmp" with one bulk operation
SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch. Check dbms_output, this is only one line.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 VALUES tmp(i);
dbms_output.put_line('first: 'tmp.FIRST', last: 'tmp.LAST);
COMMIT;
END;

-- 3b) local collection variable with single field using BULK COLLECT and FORALL

DECLARE l_old_tbs VARCHAR(100);
TYPE t_tmp IS TABLE OF ALL_TABLES.TABLE_NAME%TYPE;
tmp t_tmp;
BEGIN
l_old_tbs:= 'USERS';
-- fill "tmp" with one bulk operation
SELECT TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 (TABLE_NAME) SELECT tmp(i) FROM DUAL;
COMMIT;
END;

-- 3c) local collection variable with multiple fields using BULK COLLECT and FOR

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
  l_old_tbs VARCHAR2(100);
  TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
  TYPE t_tmp IS TABLE OF r_tmp;
  tmp t_tmp;
BEGIN
  l_old_tbs:= 'USERS';
  -- fill local collection "tmp" with one bulk operation
  SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
  -- update/insert/delete with one bulk operation; select not permitted.
  -- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
  -- always use "commit" statement
  FOR i IN 1..tmp.COUNT
  LOOP
    --INSERT INTO TEST1 VALUES tmp(i);
    dbms_output.put_line('tablename: '||tmp(i).TABLE_NAME||', last: '||tmp.LAST);
    COMMIT;
  END LOOP;
END;

-- 3d) local collection variable with single field using BULK COLLECT and FOR

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
  i INT := 0;
  l_tabname VARCHAR2(100);
  TYPE r_tmp IS RECORD (TABLE_NAME USER_TABLES.TABLE_NAME%TYPE);
  TYPE t_tmp IS TABLE OF r_tmp;
  tmp t_tmp;
BEGIN
  -- fill local collection "tmp" with one bulk operation
  SELECT TABLE_NAME BULK COLLECT INTO tmp FROM USER_TABLES WHERE UPPER(TABLE_NAME) LIKE 'TTMP_%';
  -- update/insert/delete with one bulk operation; select not permitted.
  -- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
  -- always use "commit" statement
  FOR i IN 1..tmp.COUNT
    LOOP
      dbms_output.put_line(i);
      l_tabname := 'DROP TABLE ' || tmp(i).TABLE_NAME; 
      dbms_output.put_line(l_tabname);
      EXECUTE IMMEDIATE l_tabname;
      COMMIT;  
    END LOOP;
END;

-- 4) use a temp table

CREATE GLOBAL TEMPORARY TABLE tmp (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30);

Oracle SQL Developer screen redraw glitch

Perform the following steps to fix the redraw, or screen refresh, bug that can occur in Oracle SQL Developer when used on Windows Vista and Windows 7. They occur for example when scrolling horizontally or vertically through the records of a table when the records don't fit the window.

Start with just the sqldeveloper.conf adjustment. If that doesn't fix it, then perform all steps.

Steps to solve the issue:

• Add or replace the attribute of the following config file.
  ..\sqldeveloper\bin\sqldeveloper.conf
  AddVMOption -Dsun.java2d.noddraw=true
• Install the most recent jdk.
  Last seen here: http://java.sun.com/javase/downloads/index.jsp
  Select the Java SE Development Kit (JDK). At this time, JDK 6 Update 16 is the most recent version. Install it in C:\Program Files\Java\jdk1.6.0_16
• Add or replace the attributes of the following config files.
  ..\ide\bin\jdk.conf
  SetJavaHome C:\Program Files\Java\jdk1.6.0_16
  ..\ide\bin\ide.conf
  AddVMOption -Xmx256M
  AddVMOption -Dsun.awt.keepWorkingSetOnMinimize=true