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

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

  • Comments 2

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"

/

 

 

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
Page 1 of 1 (2 items)