Stack Overflow
Stack Overflow

Questions tagged [controlfile]

46 questions
6
votes
2 answers

SQL Loader script help with adding SYSDATE, USER

I'm trying to load data from a file and I want to set CREATED_DATE and UPDATED_DATE to SYSDATE and CREATE_BY and UPDATED_BY to USER Here the table that I'm working with: CREATE TABLE CATALOG (CNO NUMBER, CTITLE VARCHAR2(25), CREATED_BY VARCHAR2(10)…
relyt
  • 679
  • 6
  • 14
  • 26
6
votes
2 answers

Can variables be passed to a SQL*Loader control file via sqlldr command?

Below is my Control file example : OPTIONS (skip=1,errors=1000,direct=true,rows=10000) load data append into table TABLE_NAME fields terminated by ',' OPTIONALLY ENCLOSED BY '"' trailing nullcols( DATE_ID …
arul.k
  • 69
  • 1
  • 3
  • 7
4
votes
1 answer

What tool do I use to make sure my debian/control file is valid?

I'm looking into a fast way to verify that the debian/control files of my projects are syntactically valid before I send them to the build server. (i.e. an equivalent of apache2ctl configtest but for debian control files.) For example, once in a…
Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
4
votes
1 answer

storing date and time value through SQL LOADER

I am trying to write a control file for a sql loader. The source file to which is in fixed length format. I have date and time present in the source file like say , from position 17 to position 24 for date in CCYYMMDD format and from position 25 to…
Nitin_Sen
  • 331
  • 3
  • 5
  • 10
2
votes
2 answers

Dynamic SQL-Loader control file

I have 20 tables that are temp-tables where we load and validate data constantly and I have a control file for each table. How can I have a unique control file that just changes the table the data is loaded into? Any suggestion? Thanks in…
BRabbit27
  • 6,333
  • 17
  • 90
  • 161
1
vote
1 answer

ALTER EXTENSION ERROR : extension "my_extension" has no update path from version "1.0.0" to version "1.0.1"

Trying to implement ALTER EXTENSION on custom extension my_extension. Made the following changes: Created a new file my_extension--1.0.1.sql I changed the code of the control file: default_version = '1.0.0' to default_version = '1.0.1' Changed…
Ishaan Adarsh
  • 197
  • 1
  • 11
1
vote
1 answer

sqlloader skips characters during data load into table

using the control file below to load data LOAD DATA INFILE '/c/Transaction.txt' INTO TABLE tab1 APPEND WHEN (1:1) = 'D' (RUN_ID "RUN_ID_SEQ.NEXTVAL" ,RUN_DATE_TIME "SYSDATE" ) INTO TABLE tab2 TRUNCATE WHEN(1:1)…
sree
  • 13
  • 3
1
vote
0 answers

Default value in an Oracle SQL*Loader control file

I have an SQL*Loader control file which is used to transfer data from an XML file to an Oracle database. My control file is as follows: options (errors=100,silent=feedback) load data infile "dest/TMP-TMP_Employee.xml" "str ''" replace into…
refresh
  • 1,319
  • 2
  • 20
  • 71
1
vote
2 answers

Oracle 12c - SQL * Loader conditional load

I'm trying to use SQL Loader and while inserting the data into the tables I need to check some conditions and insert the data. Example: CASE COLUMNA WHEN 'NULL' -- INSERT NULL VALUE IN IT INSTEAD OF STRING 'NULL' ELSE -- INSERT THE…
Koushik Ravulapelli
  • 1,140
  • 11
  • 30
1
vote
2 answers

How to recreate an oracle 9i database from backup files (ora files)

I'm a developer so I'm a little lost in the DBA world. Our systems guys have given me a backup of an Oracle 9i database. I have installed oracle 9i on my pc and am now trying to 'import' the backup files so I have a normal database to work…
user38341
  • 11
  • 1
  • 2
1
vote
1 answer

Can Oracle SQL*Loader work without a control file

I am using SQL*Loader to push lot of CSV files to Oracle database. I am building intelligence using JAVA to create control file's content. Reason is That the nature of data is such that the control file's content changes with every CSV file i get.…
maverick
  • 549
  • 2
  • 10
  • 18
0
votes
1 answer

How to allow blank spaces in an attribute assigned as primary key?

I am trying to enter few records from a tape file to a table residing in Oracle database. I am trying to do this with the help of control file. However, in the tape file there are few spaces which are targeted to a primary key attribute. While…
Andrews R. Dean
  • 23
  • 1
  • 5
0
votes
1 answer

TimeStamp in Control File

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…
Mark Roddy
  • 27,122
  • 19
  • 67
  • 71
0
votes
1 answer

USING CASE STATEMENT IN SQL LOADER CONTROL FILE FIXED WIDTH LENGTH

I have a control file that loads text to oracle but i am trying to populate field C_TIPO_PRATICA with a case statement, please see below control file and the error I receive. what should be the syntax? ctl file: LOAD DATA INFILE * APPEND INTO TABLE…
agium
  • 1
  • 1
0
votes
1 answer

How can I use a '>' or '<' in the when clause of a control file?

LOAD DATA INFILE 'Sample2.dat' APPEND INTO TABLE EMP_LEAVE WHEN REQUEST_DATE > SYSDATE --The problem lies here FIELDS TERMINATED BY "," (REQUEST_NO, EMPNO, REQUEST_DATE DATE "DD-MM-YYYY", START_DATE DATE "DD-MM-YYYY", END_DATE DATE…
John Joy
  • 7
  • 1
1
2 3 4