rickgaribay.net

Space shuttles aren't built for rocket scientists, they're built for astronauts. The goal isn't the ship, its the moon.
posts - 303, comments - 180, trackbacks - 35

My Links

News

Where's Rick?


AgileAlliance deliver:Agile 2019- 4/29
Desert Code Camp, PHX - 10/11
VS Live Austin, TX - 6/3
VS Live SF - 6/17


About Me
Hands on leader, developer, architect specializing in the design and delivery of distributed systems in lean, agile environments with an emphasis in continuous improvement across people, process and technology. Speaker and published author with 18 years' experience leading the delivery of large and/or complex, high-impact distributed solutions in Retail, Intelligent Transportation, and Gaming & Hospitality.

I'm currently a Principal Engineer at Amazon, within the North America Consumer organization leading our global listings strategy that enable bulk and non-bulk listing experiences for our WW Selling Partners via apps, devices and APIs.

Full bio

Note: All postings on this site are my own and don’t necessarily represent the views of my employer.



Check out my publications on Amazon Kindle!





Archives

Post Categories

Published Works

Adventures Contortions in SSIS Oracle Interop

I currently have the very humble priveledge of working with Oracle as a data source on an SQL Server 2005 Integration Services (SSIS) project.

What the hell are you doing working on Data Warehouse ETL stuff, Rick, you ask- that's a good question (Jack of all trades, master of some as they say), but let's not digress.

The product I am involved in can be deployed either in and Oracle or SQL environment, along with each permutation therein for a total of 4 possible configurations. So, SSIS we thought, would be the perfect choice for handling some non-trivial ETL chores. There are a few things I've stumbled on, and I will document them here, both for posterity and for the benefit of anyone else.

 

Issue

Description

Solution

ORA-00923: FROM keyword not found where expected Brackets in Oracle are like, not cool man. i.e. SELECT [Cost] FROM Table Get out your surgical gloves and sanitize that T-SQL. Remove brackets, dbo. qualifiers, etc (the latter don't make sense in Oracle anyway).
SSIS: Cannot retrieve the column code page info from the OLE DB provider.  This is due to a discrepancy in the way Oracle handles character encoding. Set AlwaysUseDefaultCodePage to true in the OLE DB Source task. Thanks to Jamie for this one.
ORA-00936: missing expression Oh this is brilliantly informative. It looks like Oracle has a problem with the CONVERT function. For example:  CONVERT(varchar(10), VI.START_DATE, 101) AS START_DATE,  The problem is that in Oracle, there is no CONVERT function that takes a given expression and converts it to the requested datatype. There is a TO_CHAR function, but this is not a syntax dialect portable option. The solution in this case was to omit the CONVERT, and instead operate on the DATETIME field natively dealing with the truncation of time later in the ETL pipeline.
ORA-00905: missing keyword

Oracle doesn't like "AS Field" alias notation in a FROM clause. For example, the following code wiill not parse:

SELECT * FROM TABLE AS MyTable

 The solution here is to simply omit the "AS" keyword:

SELECT * FROM TABLE MyTable

If only they were all this easy.

SSIS: One or more columns do not have supported data types, or their data types do not match. When source records funnel into SSIS, the engine translates the native data types into SSIS datatypes. Sometimes, the infered datatype doesn't match with the target within the Lookup trandofrmation or OLE DB target. Right click the OLE DB Source and select "Show Advanced Editor". Click the "Input and Output Columns" tab and change the inferred datatype, saving the properties.
 ORA-03291: Invalid truncate option - missing STORAGE keyword  When executing multiple statements in a batch, SQL allows you to add each statement on a line and terminate the statements with a "GO" statement. GO is not valid in Oracle and it appears that you have to terminate each statement with a "/"

 So in SQL, you coud just do this:

TRUNCATE TABLE Table1
TRUNCATE TABLE Table2
TRUNCATE TABLE Table3
TRUNCATE TABLE Table4

GO

In Oracle, add a "/" after each statement in the "batch":

TRUNCATE TABLE Table1
/
TRUNCATE TABLE Table2
/
TRUNCATE TABLE Table3
/
TRUNCATE TABLE Table4
/

OR

TRUNCATE TABLE Table1;
TRUNCATE TABLE Table2;
TRUNCATE TABLE Table3;
TRUNCATE TABLE Table4;

However, none of these seem to work in an Execute SQL Tasks.

 

     
     

Print | posted on Thursday, March 15, 2007 11:53 AM | Filed Under [ Misc. ]

Comments have been closed on this topic.

Powered by: