Author: Nicholas Dritsas
Reviewers: Lubor Kollar, Michael Thomassy, Sanjay Mishra
SQL Server 2008’s latest feature pack, that can be found here, contains a new Oracle connector by Attunity that supports Oracle versions 188.8.131.52 and higher. When you use this connector in SSIS 2008 to send data into Oracle, you may not be able to use fast load if some Oracle system views are missing. This is the case in Oracle 184.108.40.206, but, not 10.2.x.
When we tried to use fast load option using Attunity’s Oracle destination task to 220.127.116.11, we received the following SSIS error message:
[Oracle Destination ] Error: Fast Load error encountered during PreLoad or Setup phase. Class: OCI_ERROR Status: -1 Code: 0 Note: At: ORAOPRdrpthEngine.c:735 Text: ORA-00942: table or view does not exist.
We also switched on tracing at the Oracle instance level, and then scanned the trace files looking for exceptions. This is what we found:
PARSE ERROR #4:len=207 dep=0 uid=26 oct=3 lid=26 tim=63762441650753 err=942SELECT DECODE(COUNT(*), 0, 0, 1) FROM SYS.LOADER_NESTED_VARRAYS WHERE TABLE_NAME = :tname AND TABLE_OWNER = :owner
PARSE ERROR #4:len=51 dep=0 uid=26 oct=3 lid=26 tim=63762441654278 err=942SELECT VALUE FROM SYS.LOADER_SKIP_UNUSABLE_INDEXESXCTEND rlbk=0, rd_only=1
The loader_ (sql loader) views are usually created when the catalogue script has been executed. The specific script that creates the loader_ objects is (.\rdbms\admin\catldr.sql).
In this case, these two views causing the errors don't exist in the catldr.sql file for 18.104.22.168 - We have confirmed this to the case for both Unix (aix 51) and Windows Server 2003.
However - both these views do exist in the 10.2.0.2 catldr.sql file for Windows, i.e.:
rem rem $Header: catldr.sql 31-aug-2004.15:07:06 msakayed Exp $ ulview.sql remRem Copyright (c) 1990, 2004, Oracle. All rights reserved. Rem NAMERem catldr.sqlRem FUNCTIONRem Views for the direct path of the loaderRem NOTESRem This script must be run while connected as SYS or INTERNAL.Rem MODIFIEDRem msakayed 08/30/04 - column encryption support (project id 5578) Rem rphillip 05/08/03 - Add view to get full attribute nameRem msakayed 02/10/03 - Add security clause to loader viewsRem rphillip 12/02/02 - Add view to check for nested varray tablesRem preilly 11/22/02 - Add view to get version of type used for a columnRem msakayed 11/11/02 - remove hard tabsRem msakayed 11/01/02 - Bug #2643907: add LOADER_SKIP_UNUSABLE_INDEXES
We did create these missing views (see script below) on our DEV instance (running 22.214.171.124 on Aix) and the connector now seems to be functioning correctly with the both the fast load (direct path) and the table name access modes.
However, we have noticed some other unusual behavior with the Fast load - direct path method, especially on special characters and space trimming. It seems trailing spaces are removed and special characters may convert to ?. But, if you do not use fast load option, the trailing spaces and special characters are loaded into Oracle fine. We have filed a bug and we follow up with a blog when there is a fix.
Missing system views script (provided as is per Oracle’s 9.2.07 scripts)
CREATE OR REPLACE VIEW SYS.LOADER_NESTED_VARRAYS
select u.name as table_owner, o.name as table_name
from col$ c, obj$ o, user$ u, ntab$ nt
where o.obj# = nt.ntab# and o.owner# = u.user# and
c.obj# = nt.obj# and c.type# = 123 and c.intcol# = nt.intcol#
and (o.owner# = userenv('schemaid')
or o.obj# in
from sys.objauth$ oa
where grantee# in ( select kzsrorol
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
GRANT SELECT ON SYS.LOADER_NESTED_VARRAYS TO "PUBLIC"
CREATE OR REPLACE VIEW SYS.LOADER_SKIP_UNUSABLE_INDEXES
select count(*) as value from v$parameter
where upper(name) = 'SKIP_UNUSABLE_INDEXES'
and value = 'TRUE'
GRANT SELECT ON SYS.LOADER_SKIP_UNUSABLE_INDEXES TO "PUBLIC"
PingBack from http://www.clickandsolve.com/?p=17116