flat file

Hi,
I have a control file, did use sqlldr to load my temp table. Do not know how to create the flat file nor the steps to take in order to distribute the temp's table data to the apropriate tables. Any help will be greatly appriciated.

Nedpan.

nedpan,

I'll gladly help you, but I'll need more information. I'm not sure what you need help with.

1. You state that you have a control file and did use sqlldr to load your temp table. At this point, I assume all you data from you external data source is in a table in your Oracle database.

2. You then state that you do not know how to create the flat file. This implies you have not yet created a text file to upload using sqlldr. Thus it appears this statement is in direct conflict with #1.

3. You finally state that you do not know the steps to distribute the temp table data to the appropriate tables. Again, this seems to make sense with statement #1, but be in conflict with statement #2.

So, my questions are:

Have you loaded data into your Oracle database using sqlldr?

If so, what do you want help with?

If not, how is the "old" data stored now? Do you know how to output it to a text file? What is the data structure of the old data? Do you want to maintain an identical structure in the Oracle database? If not, what is the desired new structure?

I have a temp table called "X" that is loaded with data using sqlldr. My problem is the distribution of the data in table "X" to all the other tables where it is supposed to go. Also on weekly basis I will have to get data from database "A" using sqlldr and put it in database "B". I was thinking of using SQLLDR for that. One last thing, what I do not understand is the process from [a] to [z]. The steps I need to take to have the import/export between the databases, tables, etc.. automized.

Nedpan.

Hi Nedpan,
Still it's confusing us! Any way i will give u some steps how to complete a to z steps.
The first one!

Flat file:

Flat file is nothing but a text file which data contains in one format.

For ex:

empno ename job sal or
empno|ename|job|sal or
empno,ename,job,sal

When somebody sending the flat file they will tell you the
table details
data format details.

bases on that you will create a control file and you will load data into temp table. Once you loaded into the temp table you will do appropriate changes to your table data and load into one production table or multiple production tables.

This is the process. I am not understanding exatly what you are doing. If you tell us what you are planing to do then i can send u the scripts.

The next one:
Weekly basis if you get data from database a and if you want to load data into database b, Then you can create a database link and you can complete your job. If you have problem with that and if you want to use sqlldr then you have to follow these steps.

1. Pull data from database A by using dynamic sql statements into text file.
for ex:
set heading off
spool temp.dat
select empno||'|'||ename||'|'||job||'|'||sal from emp;
spool off;

In this statement we are using "|" as a delimiter. If you want you can change this one. Now you have data in temp.dat .

2. write a control file and use temp.dat infile and use fields terminated by "|". The first four lines will go like this
LOAD DATA
INFILE 'temp.dat'
INTO TABLE temp_emp
fields terminated by "|"
following by field names.

3. Now data is there in database B. You can write a sql statement and copy data from temp_emp to production table or tables.

This is the process. If you give me more details then i can give u some more information. Good Luck

nedpan,

sree_sri is exactly right. Use a database link to connect the two databases, then transfer the data within SQL*PLUS rather than sqlldr.

However, if you cannot link the databases for some reason, post your data structures, Oracle table names and some example data and we'll help you.

Thank you very much for your help. The remaining problems that I am havin are as follows:

1. Once I've loaded data into the temp table I do not know how to do appropriate changes to my table data and load into one production table or multiple production tables.

2. Do not know how to write a sql statement and copy data from temp table to production tables.

Thank you in advance for your help.

Nedpan.

Assuming you have the data loaded into Oracle, and you wish to move the data into one or more other tables:

let's assume it is not normalized, and has a structure as:

id number(10)
name
phone1
phone2
phone3
birthday

let's assume your desired structure is:

MASTER_TABLE:
id number(10)
name
birthday

DETAIL_TABLE:
id number(10) foreign key
phone

if the MASTER_TABLE and DETAIL_TABLE DO NOT already exist:

create table MASTER_TABLE as select id, name, birthday from upload_table;

create table DETAIL_TABLE as select id, phone1 phone from upload_table;
insert into detail table select id, phone2 from upload_table;
insert into detail table select id, phone3 from upload_table;


if the MASTER_TABLE and DETAIL_TABLE DO already exist:

insert into MASTER_TABLE select id, name, birthday from upload_table;

insert into detail table select id, phone1 phone from upload_table;
insert into detail table select id, phone2 phone from upload_table;
insert into detail table select id, phone3 phone from upload_table;


You stated that you don't know how to "do appropriate changes" to your table data. Do you mean you need help designing the structure of the production tables, or that there is an actual data conversion problem, such as date formats or case restrictions? What else do you need?

Hi again,

Thank you for the information.
I am still having some problems. Will give you the details again - maybe I missed something the first time.

I have 2 databases.
DB1 & DB2.
There is data that is going once a week into DB1. This data i have to capture and populate a table (table 'A') in DB2.
Once table 'A' is populated I need to store the old data into tables in DB2. For example:
given_name in table'A' used to be 'y', but after the update is now'x'. I have to take 'y' and put it into another table so that it is stored and accessible if needed.

What I have so far done:
I have a dblink between DB1 and DB2.
I have a temp table called 'B' that has the fields from the six tables in DB1 that I want to take the data from.

My problem:
I do not know how to extract ONLY the data I need so that I can populate table 'B' in DB1(my temp table).
For example;
CLIENT_ID NOT NULL VARCHAR2(9)
DATE_OF_BIRTH NOT NULL DATE
SEX NOT NULL VARCHAR2(1)
MARITAL_STATUS VARCHAR2(1)
SIN VARCHAR2(9)
REGION_CODE NOT NULL VARCHAR2(2)
ZONE_CODE VARCHAR2(1)
BASE_FACILITY_CODE VARCHAR2(2)
COMM_CODE VARCHAR2(2)
ADDRESS_CITY VARCHAR2(30)
ADDRESS_STREET VARCHAR2(30)
PROVINCE VARCHAR2(2)
POSTAL_CODE VARCHAR2(6)
CLIENT_TYPE NOT NULL VARCHAR2(1)
BC_SENT_FLAG VARCHAR2(1)
TRANS_FLAG VARCHAR2(1)
UPDATE_DATE DATE
UPDATE_ID VARCHAR2(30)
API VARCHAR2(1)
INNU_NUMBER VARCHAR2(5)
INSERT_DATE NOT NULL VARCHAR2(14)
PILOT_ID VARCHAR2(3)
NWT_COMM_CODE VARCHAR2(4)

Out of the above table I only need to get client_id, sex,region_code,province,client_type, api,pilot_id.
I have five more tables similar to the one above that I need to do the same thing with.
If I get pass this problem then the only other one is once my temp table ('B') is populated how do I transfer the data from table 'B' over to table'A'. Keep in mind 'A' is in DB2 and 'B' is in DB1.

I trully hope you can assist me in completing my task. Also to what extend can I use EZSQL to complete the above requirements.

Once again thank you very much for you time.