Home » SQL & PL/SQL » Client Tools » How to generate Control File Definition in Oracle SQL Developer (Oracle PLSQL Developer)
How to generate Control File Definition in Oracle SQL Developer [message #630586] Tue, 30 December 2014 22:13 Go to next message
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 #630589 is a reply to message #630586] Tue, 30 December 2014 23:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
What did you try so far? Did you do any research on it? Did you read the documentation? If not, then here is a good start, you will find everything you need, SQL*Loader Control File Reference.

And also, please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
Re: How to generate Control File Definition in Oracle SQL Developer [message #630591 is a reply to message #630586] Wed, 31 December 2014 00:06 Go to previous messageGo to next message
mist598
Messages: 1161
Registered: February 2013
Location: Hyderabad
Senior Member
Hi , you can do this by using UTL_FILE api
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#BABGGEDF
Re: How to generate Control File Definition in Oracle SQL Developer [message #630592 is a reply to message #630591] Wed, 31 December 2014 00:13 Go to previous messageGo to next message
isteri143
Messages: 11
Registered: December 2014
Location: World wide web
Junior Member
Thanks
Re: How to generate Control File Definition in Oracle SQL Developer [message #630594 is a reply to message #630591] Wed, 31 December 2014 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 21364
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
API? SQL Developer? To create a control file? I have never ever thought of that. I created hundreds of control files, and every time I used a text editor.
Re: How to generate Control File Definition in Oracle SQL Developer [message #630596 is a reply to message #630594] Wed, 31 December 2014 00:29 Go to previous messageGo to next message
mist598
Messages: 1161
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
API? SQL Developer? To create a control file? I have never ever thought of that. I created hundreds of control files, and every time I used a text editor.

Yes you right, most of the cases we used text editor ,but i mean as like below code is this not possible?
CREATE OR REPLACE PACKAGE XXC_CTL_FILE 
BODY 
IS
  v_file UTL_FILE.FILE_TYPE;
  BEGIN
       v_file := UTL_FILE.FOPEN('C:\','HELLO.CTL','a');
       UTL_FILE.FCLOSE(v_file);
 EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);
           NULL; 
 END; 

Re: How to generate Control File Definition in Oracle SQL Developer [message #630597 is a reply to message #630596] Wed, 31 December 2014 00:33 Go to previous messageGo to next message
Littlefoot
Messages: 21364
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course, it is possible, but that's kind of stupid unless you have a VERY, VERY good reason to do it that way.
Re: How to generate Control File Definition in Oracle SQL Developer [message #630602 is a reply to message #630596] Wed, 31 December 2014 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);
           NULL; 


Once again this is really STUPID.
Stop posting such things.
WHEN OTHERS

[Updated on: Wed, 31 December 2014 01:20]

Report message to a moderator

Re: How to generate Control File Definition in Oracle SQL Developer [message #630603 is a reply to message #630602] Wed, 31 December 2014 01:42 Go to previous messageGo to next message
isteri143
Messages: 11
Registered: December 2014
Location: World wide web
Junior Member
Hi All,


Could you please assist me step by step Implimentation of Control File Definition in Oracle SQL Developer.

Thanks
Re: How to generate Control File Definition in Oracle SQL Developer [message #630604 is a reply to message #630603] Wed, 31 December 2014 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The control file will contain ONE only record.

Explain what is a record of a control file.

Quote:
The control record will hold details about the delivered data file.

What does this mean? Explain.

Quote:
The control record will hold total 26 byte record length.

What does this mean? Explain.

[Updated on: Wed, 31 December 2014 01:45]

Report message to a moderator

Re: How to generate Control File Definition in Oracle SQL Developer [message #630605 is a reply to message #630604] Wed, 31 December 2014 01:55 Go to previous messageGo to next message
isteri143
Messages: 11
Registered: December 2014
Location: World wide web
Junior Member
Sample control file record :
2011201419112014 350
Re: How to generate Control File Definition in Oracle SQL Developer [message #630606 is a reply to message #630605] Wed, 31 December 2014 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is a control file for you?

Re: How to generate Control File Definition in Oracle SQL Developer [message #630607 is a reply to message #630606] Wed, 31 December 2014 02:29 Go to previous messageGo to next message
isteri143
Messages: 11
Registered: December 2014
Location: World wide web
Junior Member
Control file Definition means

It can handle to generate FileDate, DataDate and Number OF Records it should be Calculated.

Thanks
Re: How to generate Control File Definition in Oracle SQL Developer [message #630608 is a reply to message #630607] Wed, 31 December 2014 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It can handle where? It can handle how? It can handle in which environement? It can handle with which tool?
"It can handle" is not sufficient to explain what is a control file for you.

What does mean "generate FileDate, DataDate and Number OF Records it should be Calculated"?

Re: How to generate Control File Definition in Oracle SQL Developer [message #630609 is a reply to message #630607] Wed, 31 December 2014 02:40 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
isteri143 wrote on Wed, 31 December 2014 13:59
Control file Definition means

It can handle to generate FileDate, DataDate and Number OF Records it should be Calculated.


Did you bother to read the link I provided you?
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 Go to previous messageGo to next message
Littlefoot
Messages: 21364
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 #630621 is a reply to message #630586] Wed, 31 December 2014 08:24 Go to previous messageGo to next message
EdStevens
Messages: 902
Registered: September 2013
Senior Member
https://community.oracle.com/thread/3644592
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 Go to previous message
thatjeffsmith
Messages: 71
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

Previous Topic: Extract file format in toad
Next Topic: read multiple text files
Goto Forum:
  


Current Time: Wed Feb 21 06:09:23 CST 2018

Total time taken to generate the page: 0.02501 seconds