Hi ,
assume a flat file name emp.txt contains the following data
10000100001 narendranathreddy tallapireddy 01-01-2007 male
10000100002 ravindranathreddy tallapireddy 01-01-2008 male
10000100003 upendranathreddy tallapireddy 01-01-2008 male
10000100004 nagalakshmireddy tallapireddy 01-01-2007 male
10000100005 ruchithareddy tallapireddy 01-01-2007 female
10000100006 narendranathreddy tallapireddy 01-01-2009 male
10000100007 jyoshnareddy tallapireddy 01-01-2008 female
i want insert this data into emp table based on the date and no which are not in the emp table
i have writen this procedure : -
-
CREATE OR REPLACE Procedure data_export(file_name IN VARCHAR2) IS
-
test_file utl_file.file_type;
-
bufer_size VARCHAR2(1000);
-
S_no NUMBER(10);
-
s_first_name VARCHAR2(20);
-
s_last_name VARCHAR2(20);
-
s_date DATE;
-
s_gender VARCHAR2(6);
-
-
BEGIN
-
test_file:=utl_file.fopen('C:\narendra','emp.txt','r');
-
-
LOOP
-
-
BEGIN
-
-
utl_file.get_line(test_file,bufer_size);
-
dbms_output.put_line(bufer_size);
-
s_no:=SUBSTR(bufer_size,1,11);
-
s_first_name:=SUBSTR(bufer_size,12,20);
-
s_last_name:=SUBSTR(bufer_size,34,20);
-
s_date:=SUBSTR(bufer_size,55,10);
-
s_gender:=SUBSTR(bufer_size,66,5);
-
-
FOR i IN (SELECT e_no,e_first_name,e_last_name,e_join_date,e_gender FROM emp12
-
WHERE e_no=s_no
-
AND e_join_date=s_date)
-
-
LOOP
-
-
--IF i=0 THEN
-
INSERT INTO emp12 (e_no,e_first_name,e_last_name,e_join_date,e_gender) VALUES
-
(s_no,s_first_name,s_last_name,s_date,s_gender);
-
-
-
--END IF;
-
-
END LOOP;
-
-
-
EXCEPTION
-
-
WHEN NO_DATA_FOUND THEN
-
EXIT;
-
-
END;
-
-
END LOOP;
-
-
COMMIT;
-
-
utl_file.fclose(test_file);
-
-
END data_export;
-
while i try to execute the above procedure in the toad it compiled sucessfully but no data inserted in to the table emp12.
Please suggest me where i had done mistake.
Thanks
Narendra
5 5088
What is this FOR LOOP trying to do: -
FOR i IN (SELECT e_no,e_first_name,e_last_name,e_join_date,e_gender FROM emp12
-
WHERE e_no=s_no
-
AND e_join_date=s_date)
-
-
LOOP
-
-
--IF i=0 THEN
-
INSERT INTO emp12 (e_no,e_first_name,e_last_name,e_join_date,e_gende r) VALUES
-
(s_no,s_first_name,s_last_name,s_date,s_gender);
-
Why are you checking for the data that you are trying to insert in to emp12 table?
Im trying to elimante if there are records matching with e_no and date . if there are no records then i tried to insert these records.
Please give me the code where i can insert the data from flat files to the table.
Thanks ,
Narendra
Im trying to elimante if there are records matching with e_no and date . if there are no records then i tried to insert these records.
Please give me the code where i can insert the data from flat files to the table.
Thanks ,
Narendra
Then you need to do something like this: -
-
CREATE OR REPLACE PROCEDURE data_export(file_name IN VARCHAR2) IS
-
test_file utl_file.file_type;
-
bufer_size VARCHAR2(1000);
-
S_no NUMBER(10);
-
s_first_name VARCHAR2(20);
-
s_last_name VARCHAR2(20);
-
s_date DATE;
-
s_gender VARCHAR2(6);
-
cnt NUMBER := 0;
-
-
BEGIN
-
test_file:=utl_file.fopen('C:\narendra','emp.txt', 'r');
-
-
LOOP
-
-
BEGIN
-
-
utl_file.get_line(test_file,bufer_size);
-
dbms_output.put_line(bufer_size);
-
s_no:=SUBSTR(bufer_size,1,11);
-
s_first_name:=SUBSTR(bufer_size,12,20);
-
s_last_name:=SUBSTR(bufer_size,34,20);
-
s_date:=SUBSTR(bufer_size,55,10);
-
s_gender:=SUBSTR(bufer_size,66,5);
-
-
SELECT COUNT(*) INTO cnt FROM emp12 WHERE e_no=s_no
-
AND e_join_date=s_date;
-
-
IF (cnt = 0) THEN
-
INSERT INTO emp12 (e_no,e_first_name,e_last_name,e_join_date,e_gende r) VALUES
-
(s_no,s_first_name,s_last_name,s_date,s_gender);
-
ELSE
-
cnt:=0;
-
END IF;
-
EXCEPTION
-
WHEN NO_DATA_FOUND THEN
-
EXIT;
-
END;
-
END LOOP;
-
COMMIT;
-
utl_file.fclose(test_file);
-
END data_export;
-
-
if the file is a csv or fixed position and consistent then use an external table if you can. by attaching an external table definition to the file you can query it as though it were a table; no DML, just query.
but you can use this to perform a fast insert into a heap table to keep it using set operations.
It would be better to
1.Disable the primary key of the target table.
2.Use sql loader to load data from flat file into table.
3.Delete duplicate rows.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Pierre Maricq |
last post by:
Hi,
I am using Win2000 and Access2000.
I need to build build a macro or write a VBA in Access that would screen all
files contained in a directory on my C drive (files are structrured DAT...
|
by: Hi5 |
last post by:
Hi,
Any Idea how, I can make an Insert statement to insert data into 6
different tables, that are all holding all data of my database?
Is there any example?
I would be grateful if you could...
|
by: new |
last post by:
I have data for each week in a single table. I need to export this data to
a separate flat file for each week.
Any ideas?
DB2 SQL Query export to flat files as a function of data on each record
|
by: anuanusha29 |
last post by:
Hi,
I am kinda new to programming and am in the process of implementing an application which is expected to parse/read data from flat files and display errors to the user about missing/invalid...
|
by: dave32079 |
last post by:
I am moving data from Oracle to MS SQL 2000.
I have been given the Oracle table structures and about 130 CVS files to populate these structures, 1 file per table. I have converted the table...
|
by: =?Utf-8?B?TmF2YW5lZXRoLksuTg==?= |
last post by:
Hello
I am developing a system for user tracking. In this I am tracking all the
visitors that came to my website. i developed this using a HTTPModule and
storing the data in a SQL Server...
|
by: Stylus Studio |
last post by:
Dear Comp.Text.XML Community,
Attend a webinar event on how to modernize your EDI infrastructure and
exploit XML technologies.
Topic: Exploiting EDI and Flat Files in an XML World
Date &...
|
by: stephen |
last post by:
I had to use use ssis 2005 in a short project recently & had little
time to work it out. I was importing a whole bunch of flat files into
SQL Server tables with many derived columns and...
|
by: Tim |
last post by:
Hi Folks,
I'm used to a UNLOAD command that allows me to dump to a named flat
file the results of any SELECT statement. Hence one can build a single
SQL file which contains multiple SQL...
|
by: shivasusan |
last post by:
Hi!
I have two tables in my database (userinfo, logininfo)
- userinfo table has five fields (uname, pws, con_pws, squest, sans)
- logininfo table has then six fields (fname,lname,dob,add1,...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |