Home » SQL & PL/SQL » SQL & PL/SQL » code to load data from text to table ? (Oracle SQL Developer 3.1 on XP)
icon5.gif  code to load data from text to table ? [message #568571] Sun, 14 October 2012 14:37 Go to next message
vanQuisher
Messages: 7
Registered: October 2012
Junior Member
What is the best way to load the data from text file to the table in PL/SQL
How can i write a program for that ?
-data is separated by ',' in text file for each columns in table
Re: code to load data from text to table ? [message #568572 is a reply to message #568571] Sun, 14 October 2012 14:49 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL*Loader would be my choice; possibly, external table. PL/SQL? Only if there's no other way or the inserting process is very complex.
Re: code to load data from text to table ? [message #568573 is a reply to message #568572] Sun, 14 October 2012 16:03 Go to previous messageGo to next message
vanQuisher
Messages: 7
Registered: October 2012
Junior Member
how to do that ?

i have a table dept with 3 columns in the database, now i want to write a program to load the data from text file into the table
Re: code to load data from text to table ? [message #568575 is a reply to message #568573] Sun, 14 October 2012 16:39 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
If text file resides on DB Server system, it is better to utilize External Table.

Re: code to load data from text to table ? [message #568576 is a reply to message #568575] Sun, 14 October 2012 16:55 Go to previous messageGo to next message
vanQuisher
Messages: 7
Registered: October 2012
Junior Member
im new to PL/SQL buddy thats y im asking the code for this program Smile
Re: code to load data from text to table ? [message #568577 is a reply to message #568576] Sun, 14 October 2012 18:12 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
Are you new to GOOGLE also?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

when all else fails Read The Fine Manual

http://docs.oracle.com

[Updated on: Sun, 14 October 2012 18:21]

Report message to a moderator

Re: code to load data from text to table ? [message #568578 is a reply to message #568577] Sun, 14 October 2012 18:55 Go to previous messageGo to next message
vanQuisher
Messages: 7
Registered: October 2012
Junior Member
y i post here if i found it on google, senior Smile
Re: code to load data from text to table ? [message #568579 is a reply to message #568578] Sun, 14 October 2012 19:22 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
are unwilling or incapable to comply with Posting Guidelines?
Re: code to load data from text to table ? [message #568590 is a reply to message #568578] Sun, 14 October 2012 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vanQuisher wrote on Mon, 15 October 2012 01:55
y i post here if i found it on google, senior Smile


Why should we repeat what has already been posted many times? Smile

external table

Regards
Michel
Re: code to load data from text to table ? [message #568594 is a reply to message #568590] Mon, 15 October 2012 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First things first.

Where is the database located? Is it installed on your own PC or do you access it through the network (so it is installed on a database server)?
Where is the file located? Is it on your own PC or (if using a database server) somewhere else (for example, on the database server)?
Re: code to load data from text to table ? [message #568670 is a reply to message #568594] Mon, 15 October 2012 12:55 Go to previous messageGo to next message
vanQuisher
Messages: 7
Registered: October 2012
Junior Member
the database and file both are on my PC
Re: code to load data from text to table ? [message #568674 is a reply to message #568670] Mon, 15 October 2012 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#sthref5181

URL provided for SEARCH challenged poster
Re: code to load data from text to table ? [message #568676 is a reply to message #568674] Mon, 15 October 2012 13:23 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If everything is on your PC then - if you could post CREATE TABLE statement and several records from the input file, we can try to compose a solution for you.
Re: code to load data from text to table ? [message #568686 is a reply to message #568676] Mon, 15 October 2012 16:17 Go to previous messageGo to next message
vanQuisher
Messages: 7
Registered: October 2012
Junior Member
Thanks Little foot.

i have table dept with columns deptno, dname, loc
and here is the program i wrote to write all the data in dept table to a text file

DECLARE
  a utl_file.file_type;
  CURSOR c
  IS
    SELECT * FROM dept;
  i c%rowtype;
  dis VARCHAR2(4000);
BEGIN
  a := utl_file.fopen('FOLDER','dept.txt','W');
  IF utl_file.is_open(a) THEN
    OPEN c;
    LOOP
      FETCH c INTO i;
      EXIT
    WHEN c%NOTFOUND;
      line := i.Deptno || '|' ||i.dname ||'|' || i.loc;
      utl_file.put_line(a,dis);
    END LOOP;
    CLOSE c;
  END IF;
  utl_file.fclose(a);
END;

--here is the output for that program  in dept.txt

10  | ACCOUNTING  | NEW YORK
20  | RESEARCH  | DALLAS
30  | SALES  | CHICAGO
40  | OPERATIONS  | BOSTON



now how can i feed this data in text file to another table named dept2 with same columns
the data in the text is seperated by '|'
Re: code to load data from text to table ? [message #568688 is a reply to message #568686] Mon, 15 October 2012 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
>now how can i feed this data in text file to another table named dept2 with same columns


INSERT INTO DEPT2 SELECT * FROM DEPT;
Re: code to load data from text to table ? [message #568693 is a reply to message #568688] Mon, 15 October 2012 18:12 Go to previous messageGo to next message
vanQuisher
Messages: 7
Registered: October 2012
Junior Member
dude i want to load the data from text file to dept2
Re: code to load data from text to table ? [message #568696 is a reply to message #568693] Mon, 15 October 2012 23:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you didn't click on the link I provided you.
You just want we make your homework.

Regards
Michel
Re: code to load data from text to table ? [message #568697 is a reply to message #568696] Tue, 16 October 2012 00:19 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, a SQL*Loader solution.

Does the DEPT.TXT file exist?
M:\>dir dept.txt
 Volume in drive M is file sharing 1
 Volume Serial Number is 98A9-A7AE

 Directory of M:\

16.10.2012.  07:07               106 dept.txt
               1 File(s)            106 bytes
               0 Dir(s)     426.070.016 bytes free

A control file - can't be simpler:
load data
INFILE 'dept.txt'

into table dept2
replace
fields terminated by '|'
  (deptno,
   dname,
   loc
  )

Let's create an empty DEPT2 table:
SQL> show user
USER is "MIKE"
SQL> create table dept2 as select * from dept where 1 = 2;

Table created.

SQL>

Loading session and the result:
SQL> $sqlldr mike/lion@ora10 control=test6.ctl log=test6.log

SQL*Loader: Release 11.2.0.2.0 - Production on Uto Lis 16 07:13:40 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3
Commit point reached - logical record count 4

SQL> select * from dept2;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10  ACCOUNTING     NEW YORK
        20  RESEARCH       DALLAS
        30  SALES          CHICAGO
        40  OPERATIONS     BOSTON

SQL>


The above solution works fine as SQL*Loader allows you to load files which are stored on your own PC, while the database can be a local one (as you have it) or somewhere on the network.

An external table solution Michel suggested isn't that flexible as it requires that the input file is located on the database server, which can be tricky if you don't have access to the server - then you have to ask someone (a DBA) to create a directory (Oracle object which points to a "real" directory on database server's file system) and grant you privileges to use it from Oracle tools. However, DBA doesn't have to be a nice person and won't let you put files onto that server so you'd have to ask him/her to do that for you every time.

However, as you are a DBA of your own database, I'd suggest you to read the documentation and find a way to do the same job, but this time using the external tables feature.
Re: code to load data from text to table ? [message #568705 is a reply to message #568697] Tue, 16 October 2012 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
An external table solution Michel suggested isn't that flexible as it requires that the input file is located on the database server


If ÖP is able to write his file on the database server using UTL_FILE then I think he is able to read it using an external table. Wink

Regards
Michel
Re: code to load data from text to table ? [message #568727 is a reply to message #568705] Tue, 16 October 2012 02:13 Go to previous message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right! But this case is a special case as he is using a database installed on his own PC so ... no problem in doing anything he wants. Some day, in the future, that might not be the case. I admit, my thoughts may be irrelevant.
Previous Topic: Date
Next Topic: Generate dynamic Unpivot
Goto Forum:
  


Current Time: Sun Sep 21 19:22:46 CDT 2014

Total time taken to generate the page: 0.06610 seconds