Partilhar via

SQL Server 2008’s Oracle destination fast load option may fail if certain Oracle system views are missing

  • Artigo

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=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 (.rdbmsadmincatldr.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 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 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"

/

Comments

  • Anonymous
    March 02, 2009
    PingBack from http://www.clickandsolve.com/?p=17116

  • Anonymous
    November 23, 2011
    Got the same the error; Solution Worked Out: Rebuilded the Indexes on the target Oracle table. Package loaded fine....