I have a script that takes a table name and generates a control file by querying all the columns/rows the table. This works fine for numeric and character data but fails on timestamp data so I need to adjust the script to output the timestamp data into the control in such a way that it can be read in properly. So essentially, my questi...
The SQL-LDR documentation states that you need to do a convetional Path Load: When you want to apply SQL functions to data fields. SQL functions are not available during a direct path load I have TimeStamp data stored in a CSV file that I'm loading with SQL-LDR by describing the fields as such: STARTTIME "To_TimeStamp(:S...
How would I disable and later enable all indexes in a given schema/database in Oracle? Note: This is to make sqlldr run faster. ...
Sqlldr is corrupting my primary key index after the first commit in my ctl file. After the first, no matter what I set the rows value to in my control file, I get: ORA-39776: fatal Direct Path API error loading table PE_OWNER.CLINICAL_CODE ORA-01502: index 'PE_OWNER.CODE_PK' or partition of such index is in unusable state SQL*Loader-20...
Hello, I have to import dates into oracle with this type of format 03JUN2008 The only example I've seen this done before has dashes and this does not. Should I just bring it in as varchar and manipulate or is there another way? Thanks ...
In order to load data (from a CSV file) into an Oracle database, I use SQL*Loader. In the table that receives these data, there is a varchar2(500) column, called COMMENTS. For some reasons, I want to ignore this information from the CSV file. Thus, I wrote this control file: Options (BindSize=10000000,Readsize=10000000,Rows=5000,Errors...
Hello. FIRST: Yes, I know about CONCATENATE and CONTINUEIF, but I might not be smart enough to understand the documentation. I don't "get" how they solve my problem. Thank you... I am in the position of having to create a file for importing into an Oracle DB by way of sqlldr. The columns are required to be delimited by "~" (Don't ask....
Hello, I am a front-end developer writing a C# application to export data from an oracle stored procedure into a text file for later importing into a different oracle database. The data will be loaded with SQL Loader (sqlldr) and I am trying to understand that tool's abilities so that I can output the file in a format that will be easy ...
During our build process, we run sqlldr to populate our database with some sample data. On every table that sqlldr touches, the foreign keys are disabled after sqlldr runs. According to this Ask Tom posting: sqlldr will only disable constraints that relate to other tables (eg: foreign keys) NOT the primary key. SQLLDR wil...
Hi, I am trying to import NetBackup statistics into Oracle. One field I really want is ACTIVE START, but it isn't available from bpdbjobs -report. What is available is ACTIVEELAPSED, so I want to subtract that from the END TIME on import as follows: END TIME Format: 26/09/2009 10:46:20 PM ELAPSED TIME Format: 028:32:35 (which is hours...
I'm trying to export from SQL Server to Oracle a table that consists of an ID and a really long XML string stored as nvarchar(max). SQL Table -- CREATE TABLE MyStuff (MyID int, MyText nvarchar(max)) Oracle Table -- CREATE TABLE MyStuffImported (MyID int, MyText NCLOB) I'm using the SQL Server bcp utility to export to a text file (MyStu...
I have a table with a Unique Index that I need to load 800,000 rows into. I altered the index and made it unuseable. I added 'options( rows=4000, bindsize=512000, skip_unusable_indexes=TRUE )' to my control file. I'm still getting errors of the form: ORA-01502: index 'x_Unique' or partition of such index is in unusable state Sugges...
I have a table with a field called [SEQUENCE] and another called [REFERENCE] I can define the field in SQLLDR as SEQUENCE NULLIF ( SEQUENCE=" ") It complains that SEQUENCE is A SQLLDR reserved word. I have tried "SEQUENCE" [SEQUENCE] \"SEQUENCE\" None seem to work Would prefer not to call SQL, because that would preclude direct...
Hi - I'm struggling to get sqlldr to import a csv data file into my table, specifically with the field that is a timestamp. The data in my csv file is in this format: 16-NOV-09 01.57.48.001000 PM I've tried all manner of combinations in my control file and am going around in circles. I can't find anything online - not even the Oracle...
I have a rather huge file (50mb) where the first field is a unix timestamp. However, I don't want to load the whole file, I need only half of it where timestamp > some_parameterized_timestamp. It would seem sqlldr only allows = and <> conditions in the when clause. It takes too long to load the file and then delete records that are not ...
I'm trying to load some data to an Oracle database using SQL Loader. Is it possible to invoke it with specifying the server to load the data into using a DSN instead of a TNS? Right now my command line looks like this: sqlldr uesr/password@tns_id..., I'd like to replace tns_id with a DSN that points to tns_id. Can SQL Loader figure out ...
I have a dump of several Postgresql Tables in a selfcontained CSV file which I want to import into an Oracle Database with a matching schema. I found several posts on how to distribute data from one CSV "table" to multiple Oracle tables, but my problem is several DIFFERENT CVS "tables" in the same file. Is it possible to specify table s...
how to increase the limit of exceptions in oracle ? i have a excel sheet in which their are about 900 records to be appended .so i converted the excel to dat file and wrote a batch file that read from the dat file and appends it to the concern table but the batch file stop execution once the exceptions reach 51(all integrity constrain...
In windows xp, I start sqlloader.exe to load data onto Oracle. This works great, until my password contains an @-sign, that is also used by sqlloader parameters to determine the database to connect to: sqlldr.exe userid/password@database control=ctrlfile.ctl How can I make sqlldr.exe accept a password like p@ssword? I tried with single...
This should be simple, but what is the correct syntax for an Oracle sqlldr control file in order to specify tab delimited/separated data? FWIW, I found the file was UTF16 and not UTF8, and was editing fine but would introduce null bytes as Oracle read the control file. Can't even replicate today. ...