I have a table in excel format that I’m trying to import into an Oracle table. I can do so successfully in Toad for Oracle, but when I try to import the same file into the same table using Toad for Data Analysts it doesn’t work. In Toad for Data Analysts, the import appears to be successful. The event log states that rows have been read and inserted, but looking at the table there are no rows. In the Output portion there is an error listed, which is: ORA-02005: implicit (-1) length not valid for this bind or define datatype. Is there something I should be doing differently in TDA vs. Toad for Oracle?
Thanks!
Amanda
What type of Oracle connection are you using in TDA - ODBC, Direct Connect, or OCI? All three of these process differently when importing data. What is the version of Oracle and TDA?
Is it possible to have you send me your import file along with the DDL of the table it is going into? (Or just list the columns and datatypes)
Also, when the export wizard is done there is a link to view the log file. Can you copy and paste that whole log file into a text file and send?
If I were going to take a guess of what is going on based on the Oracle error code I would guess that one of the column data types is an issue or one of the row values for a data type that should never be null is causing the issue.
You can send the files to me directly at dpeabody@quest.com
Debbie
My Oracle version is 10g and TDA is 2.7.0.348. I believe my connection is ODBC.
Below is the log:
11:58:20 AM Thread (34) Import Started [9/22/2011 11:58:20 AM]
11:58:21 AM Thread (34) Processing “Input_Trial.xls” into “KC.SAS_TEMP_PATIENTS”
11:58:21 AM Thread (34) Importing 1 out of 1 files
11:58:21 AM Thread (34) Reading from file Input_Trial.xls
11:58:21 AM Thread (34) Inserting row 4 into table SAS_TEMP_PATIENTS
11:58:21 AM Thread (34) Currently 4 out of 4 rows were imported to table SAS_TEMP_PATIENTS.
I had already tried adding extra columns to my import file so that none of the fields would be null, but it hasn’t helped. Could it be a problem that the Oracle table is set up to be temporary? I just emailed the files.
I can get an error if I use TDA 2.7 and import using an OCI Oracle conneciton. The error I get is different than yours but it does not import. It does import successfully using a Direct Connect or ODBC connection.
I have also checked using the current GA release of TDA 3.0.0.1694 and it does not fail with any of the connection types. I would suggest you upgrade to lateset release or define a Direct Connecioin to Oracle for this task. To do this, create a new connection and in the middle of the properties choose the tab that says “Direct”.
Debbie
Message was edited by: Debbie Peabody
I downloaded and installed the TDA 3.0 and tried the import. I still get the same issue and the same error message.
Something is different between our two environments.
Can you try importing the file again but exclude importing to any of the VARCHAR(4000) columns?
Also, go to the Help menu and generate a support bundle and email to me. This will give me more info on your environment and an additional error log that I can look at.
Debbie
I tried not using the VARCHAR fields, but still no luck. I emailed the support bundle zip file.
Thanks!!
Amanda
Thanks for the support bundle.
I have tried everything I can think of and I cannot reproduce this issue. I see you have an Oracle 10.1 client installed. I tried this with an Oracle 10.2 and 11.1 client, boty are fine.
Let’s try two more things.
-
Change your commit setting to Auto Commit ON. This can be changed in the bottom left of the application. See screenshot. Change this, reconnect, and retry the import.
-
Create a Direct Connection. This does not use the Oracle client. See screenshot. Connect and try the import from the import wizard in the Tools menu. (Not automation)
Debbie
Thanks for the support bundle.
I have tried everything I can think of and I cannot reproduce this issue. I see you have an Oracle 10.1 client installed. I tried this with an Oracle 10.2 and 11.1 client, boty are fine.
Let's try two more things.
-
Change your commit setting to Auto Commit ON. This can be changed in the bottom left of the application. See screenshot. Change this, reconnect, and retry the import.
-
Create a Direct Connection. This does not use the Oracle client. See screenshot. Connect and try the import from the import wizard in the Tools menu. (Not automation)
Debbie
Thanks, I've tried both options and still no luck. I have found that when viewing the details of this table as well as other view in the same schema, if I click on the "Script" tab, the same message appears in the Output (see attached image).
Thanks for that observation. That error is a red herring for what you are experiencing. It is an error only for polling DBMS output. You can turn it off by toggling the ‘Clock’ sticky button on the output window.
[cid:image001.png@01CC7D39.AEACA660]
We are going down the wrong path on this issue. Can you try creating a regular table and importing. Let’s get that to work and then we can go back to the temp table and see what might be occurring. (IE: scope of session. In fact, that might be the issue. TDA uses several connections to execute commands faster and if your temp table is only of session scope, the import may not see it, etc)
Debbie
I have not yet tried creating a regular table (and I’m not sure I have rights to do that) but I’ll try. I do know that the temp table is only session duration. Under the “statistics” tab duration is given as sys$session.
Thanks!
Amanda
You should be able to create a table under your logid schema.
Debbie
No luck, insufficient privileges...
Wow. That is pretty locked down!
I assume that you want to import the data into Oracle so you can manipulate the data and requery it. If this is the case, why not import it to the Toad Sample Database(Access) or if you have TDA Pro, import into the Local Storage database. That is the main purpose of these two storage areas.
Debbie
I’ll try that. My purpose is that the excel file basically identifies a list of people. I am trying to query various schema for data for that list of people. I was trying to avoid exporting files to my local MS Access that contained data for everyone and subsequently paring down to what I needed. As a novice I’m less familiar with using the Access connection in TDA especially at the same time as an Oracle connection. Does that impose some limitations on query structure by making it a heterogeneous query?
Thanks so much for all your help!
Amanda
So I did confirm your issue has to do with how Oracle handles temp tables. They are only per session and TDA uses more than one session to view and import.
I would still need to know more about how you want to go about your process. If you want to build an automated process where you query your excel list and then for each row of the excel list execute a query in Oracle to get some data for that particular department, etc, then you can accomplish this in automation and is not considered a heterogenious query.
If you want to use two desparate databases in the same query at the same then I would encourage you to use TDA 3.0 Pro edition. It has a brand new Cross-conneciton query engine that makes this very simple and effective to do.
But I am unsure which of the two you want to do.
If you still are more comfortable in Oracle, it would not surprise me if your DBA would grant you access to create tables in your own schema. They would need to change your privilges but I have rarely seen this denied.
Debbie
Thanks for that information. I’ve started changing over my code to be the cross-connection query, which so far seems to work fine. I considered the first possibility you mentioned, but I was unsure if that would be the best route to take. Currently my excel list has ~8300 rows and will only grow (>50,000+) if it continues to be contained in excel; however if the numbers are that big hopefully the data will be in Oracle instead of excel.
Thanks so much for all of your help!
Amanda
