How to generate Control File Definition in Oracle SQL Developer [message #630586] |
Tue, 30 December 2014 22:13 |
isteri143
Messages: 11 Registered: December 2014 Location: World wide web
|
Junior Member |
|
|
Hi All,
Here i need to create Control File Definition in my Current Package, Please guide me how to develop this Program.My Requirement as below.
Control File Definitions
The control file will be named similarly to the data file with an extension ".CTL"
The control file will contain ONE only record.
The control record will hold details about the delivered data file.
The control record will hold total 26 byte record length.
The format of the control record will be as follows:
Fieldname
Data Type and format
Definition
FileDate CHARacter 8 format 'YYYYMMDD' The date the file was created.
DataDate CHARacter 8 format 'YYYYMMDD' The date the data is for. Allows for data to be created post the date of the records.
NbrRecords CHARacter/Numeric Format 9(10)
No leading zeroes, numeric only, no decimal place, no commas etc. The NUMBER of records in the data file
Thanks
Isteri
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: How to generate Control File Definition in Oracle SQL Developer [message #630610 is a reply to message #630609] |
Wed, 31 December 2014 03:21 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Isteri,
when people (who use Oracle) hear "control file", a switch in their brain redirects thoughts to "SQL*Loader". It is a utility which enables you to load contents of a file (which is usually a CSV - comma separated values, and resides in a directory on your PC's hard disk) into a table (which is located in an Oracle schema).
In order to successfully load data, you have to create a control file. It tells SQL*Loader what to do, i.e. controls the whole process. It is just a file (such as any text document you might have, a Word document, picture of you and your friends ... a file).
Documentation contains information you need to know in order to complete the process. Until & unless you do that, you won't be able to load data. Therefore, follow a link Lalit provided previously; on the left side you'll see table of contents. Start with SQL*Loader Concepts, followed by Command-line reference, control file reference and field list reference chapters. Yes, it will take some time to read all of that, but that's the way it goes.
Reading your messages, it appears that "control file" is something different for you. Obviously, you don't create it in your "Current Package". "Control file definitions" you specified are more or less meaningless in context of what control file really is. It - usually - doesn't contain any records (although you can put them into the BEGINDATA control file's section). What you described is an input (CSV) file.
Furthermore, it seems that there are two input files in your case: one which contains only one record (with sample data you provided, "2011201419112014 350") and says that there is another input file which contains 350 records. These 350 records should be loaded into another table.
So, you might need two control files - one to load that "control record" ("2011201419112014 350"), and another one to load 350 "data" records.
If that's so, you'd better start reading.
Some people here might even create those control files for you, but you wouldn't learn much if they do so. Besides, you should provide CREATE TABLE statements (so that we'd know the target) as well as several sample input records (i.e. the source). Basically, you should put some initial effort and come back if you get errors you can't fix. Someone would certainly assist.
|
|
|
|
Re: How to generate Control File Definition in Oracle SQL Developer [message #630622 is a reply to message #630603] |
Wed, 31 December 2014 11:10 |
|
thatjeffsmith
Messages: 81 Registered: July 2009 Location: Raleigh, NC
|
Member |
|
|
The only UI interfaces to SQL*Loader in SQL Developer are:
+ for an existing database, be a query result or the content of a table/view. If you do an export, you can choose 'Loader' - this will create the SQL*Loader ctl file for that data
+ for an existing delimited file, doing an import via the wizard can be setup to be done via a SQL*Loader script that the tool will generate for you.
I talk about the latter scenario here.
[Updated on: Wed, 31 December 2014 11:10] Report message to a moderator
|
|
|