Greetings All,
Any help that can be provided concerning the following challenge would be helpful:
I’ve just stepped into an environment with lots of data across multiple platforms, so TDA seems like just the ticket. A primary need is to compare data sets from different sources using outer joins and subqueries in order to determine when data is dropped when passing from one system to the next. Because neither outer joins or subqueries are supported when creating heterogenous queries, the alternative seems to be to write a script that does a SQL select, outputs to a CSV, and dump the results into a database instance where I have write privleges. Repeat with another source, and I end up with the pieces I need where I can manipulate them.
Coming from Teradata to a CSV to an Oracle table works fine. The qualifiers here are that I’m outputting and importing a single column, and that I’ve created my target table in advance - the create new table on the fly methodology doesn’t seem to provide any success.
Coming from one Oracle instance to another remains problematic. In this case I’m using 6 columns to a CSV. I’ve created my target table in advance. The column order doesn’t match, I’m mapping by names (one column name doesn’t match, I used the dialog to specify it). I can write to the CSV file just fine, but on the step that imports to the table I get a break that has a cryptic error, pasted below.
I am using CSV’s in preference to Excel because I can do an overwrite of the file. The Excel option seems to perform better with the interface, but it writes a new worksheet tab on the second pass, and I haven’t figured out a way around that.
The error message is:
Object reference not set to an instance of an object.
Error log is attached.
Thanks for any and all help!
Stucky 4809 Analysis.log (8.05 KB)
It looks like the parser is failing to extract the SQL out of this file --> " C:\Documents and Settings\NBKDKWD\My Documents\Toad Work\STUCKY_ID_create.sql". Can you send me the contents of that file and what type of database is it being executed on, Oracle or TeraData?
Debbie
Hi Debbie,
Thanks for getting back to me. The script in question is a create script for an Oracle 10g table. In the process of trying to work through this exercise, I failed to note that I was trying to re-create a pre-existing table. I believe that is what generated the error.
The functionality for creating a new table on the fly doesn’t seem to work. Also the check box for the option to truncate the table on the ‘Preview Target Data Table’ page of the Data Import Wizard is dimmed out. Is this an issue connected with my data environment (importing from CSV to Oracle 10g via ODBC)?
Based on the preceding I’m writing scripts that detect a table’s existence so I can create, drop and recreate, or truncate as appropriate. Are there known issues around this sort of thing? Any gotcha’s? Best practices? Am I perhaps suffering from not having fixes available that have been incorporated in v. 2.6?
I’m on 2.5 because that’s what my firm is supporting currently…
Thanks,
Bill
Bill,
If the script was executed but had a database error you would see the Oracle error. Your error was a null Object Reference which would indicate something in my code. Are you still getting this error or did this get resolved?
You could add a drop table prior to the create and always attempt to drop the table. Just change the ‘Stop on Error’ attribute on the Execute activity to ‘Continue on Error’. This will ignore the error if thrown on the drop and continue to make the table.
In version 2.5 and 2.6 the export to a new table is very limited. We are going to enhance this area in 3.0 and create a new table with appropriate data types as well as allow you to change them to whatever you want. For now you will need to stick with what you are doing.
I was wondering why you are using ODBC with Oracle? I would use the OCI Oracle connection or Direct Connect with Oracle. Here you can configure the truncate table and preview the table data.
You also mentioned exporting to Excel. In TDA 2.6 there is an option to export to a named worked sheet and clear the data prior to sending. Using this feature will always write to the same sheet and not append. You might want to check that out if CSV is not giving what you need.
I am reviewing this whole area for the 3.0 release and thinking of adding a Data Cleansing/Validation window. Your use case seems to fit into this topic. May I contact you when we are closer to the development on this utility for further info on what you might need?
Debbie
Hi Debbie,
Sorry to take so long to respond. I am no longer getting the error. The error was generated when I attempted to create a table when it already existed. I had kept the default option to stopOnError.
I resolved my requirement by doing a select on user tables along with a rowcount variable to see if the table exists. Then I follow up with an if condition to either truncate or create the table.
(e.g. select OBJECT_NAME, OBJECT_TYPE from user_objects where object_type = ‘TABLE’ and object_name = ‘STUCKY_ID’)
I’ve also applied your suggestion to create a script that has a Drop statement followed by a Create statement implemented with the continueOnError option. Both approaches work well. I find your suggestion to be simpler to implement and it certainly makes life easier when you change your data structures.
I’m using the ODBC drivers because that’s what they had me install when I came onboard. I just checked with our tech guys and they indicate that there is nothing preventing me from using the native drivers. I’m going to set them up. Are there any other areas where the native drivers provide enhanced functionality?
I’m not using 2.6 because it is not yet officially supported by my firm. I am attempting to address that issue.
I would be more than happy to work with you as you approach version 3.0. I have only been working with TDA for a short time, but it has been invaluable in my current work environment. Several of my associates have caught the bug as well.
Regards,
Bill
Hi Bill,
Sounds like your solutions are working. That's great. When you have some time do set up the native connection to Oracle. Quest specializes in adding connectivity features for Oracle, SQL Server, MySQL and DB2. Execution time on large datasets will be faster but most of the features are in the Database browser. The table editor has a simple and advance mode where all aspects of creating a table can be used. Also the right hand side tabs of the browser are very useful.
It is best to install an Oracle Client but you do not have to do that if you don't have the time. Use the Direct Connect method. I have attached a screenshot. Just enter the server name, port number, SID, and logid.
Have Fun.
Debbie
