SQL Server 2008’s Oracle destination fast load option may fail if certain Oracle system views are missing
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 9.2.0.4 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 9.2.0.7, but, not 10.2.x.
When we tried to use fast load option using Attunity’s Oracle destination task to 9.2.0.7, we received the following SSIS error message:
[Oracle Destination [268]] 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=942
SELECT 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=942
SELECT VALUE FROM SYS.LOADER_SKIP_UNUSABLE_INDEXES
XCTEND 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 9.2.0.7 - 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
rem
Rem Copyright (c) 1990, 2004, Oracle. All rights reserved.
Rem NAME
Rem catldr.sql
Rem FUNCTION
Rem Views for the direct path of the loader
Rem NOTES
Rem This script must be run while connected as SYS or INTERNAL.
Rem MODIFIED
Rem msakayed 08/30/04 - column encryption support (project id 5578)
Rem rphillip 05/08/03 - Add view to get full attribute name
Rem msakayed 02/10/03 - Add security clause to loader views
Rem rphillip 12/02/02 - Add view to check for nested varray tables
Rem preilly 11/22/02 - Add view to get version of type used for a column
Rem msakayed 11/11/02 - remove hard tabs
Rem 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 9.2.0.7 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
(
TABLE_OWNER,
TABLE_NAME
)
AS
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
(select oa.obj#
from sys.objauth$ oa
where grantee# in ( select kzsrorol
from x$kzsro
)
)
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
(
VALUE
)
AS
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"
/
