 Need help in Using UTL_FILE package [message #211473] |
Thu, 28 December 2006 20:31  |
yugamore
Messages: 23
Registered: December 2006
|
Junior Member |
|
|
Hi Friends,
I have a table which consists of column of CLOB datatype.
I need to read this data and put it in Target table selectively(i mean not all the rows from source table). However I could not directly query CLOB data from table,it gives error.So i have written a code which reads data from this clob column and puts it in File. I'm using UTL_FILE package to put it in the file.
I want to know :
1) Can I put data from flat file into table Using UTL_FILE?
2) If yes, Can I format it?
3) Can i SELECTIVELY put data from UTL_FILE in table?
OR do I have to first insert data from file into the Temp table and then query this Temp table to put data in the Target table?
4) No of rows I want to insert in the Target table is vey less,say around 5000. Wouldn't it be a good idea to read data from CLOB and put it in Temp table. Select it from Temp table and finally put it in Target table. This will avoid use of text file.
Please note that I don't have access to UNIX server on which the Database is residing. So handling file is going to be cumbersome.
Can anyone suggest a better way to deal with this?
Reagrds
Yuga
[Updated on: Thu, 28 December 2006 20:35] Report message to a moderator |
|
|
|
|
|
| Re: Need help in Using UTL_FILE package [message #211493 is a reply to message #211474] |
Thu, 28 December 2006 23:53  |
yugamore
Messages: 23
Registered: December 2006
|
Junior Member |
|
|
Hi,
Basically I need to query some data from table having CLOB column and upload it to some other table.
Please note that because the table has a column with the data type CLOB I can not directly select from this table.
select * from actual_table;
would give an error.
To solve this I have fetched the CLOB data from actual table into a flat file, with the help of following code.
CREATE OR REPLACE procedure read_clob as
v_filehandle UTL_FILE.FILE_TYPE;
clobColumn CLOB;
iClobSize INTEGER;
rBuffer RAW(32767);
vBuffer VARCHAR2(32767);
nOffset NUMBER := 1;
iMaxSize INTEGER := 32767;
iChunkSize INTEGER;
iLeftOver INTEGER;
v_id NUMBER;
v_name VARCHAR2(50);
v_sector VARCHAR2(50);
v_country VARCHAR2(50);
CURSOR C1 is
SELECT company_id,company_name,sector,country,description
FROM pli2_t_company_master
WHERE company_id BETWEEN 1 AND 179;
BEGIN
OPEN c1;
v_filehandle := UTL_FILE.FOPEN('MCD','CMP_Master.txt','w');
LOOP
FETCH c1 into v_id,v_name,v_sector,v_country,clobColumn;
EXIT WHEN c1%NOTFOUND;
iClobSize := dbms_lob.getlength(clobColumn);
IF iclobsize = 0
THEN
dbms_output.put_line(v_id ||' ,'||v_name ||' ,'||v_sector||' ,'||v_country||' ,'||'EMPTY DESCRIPTION');
UTL_FILE.PUTF(v_filehandle,'%s %s %s %s %s',v_id v_name v_sector v_country 'EMPTY DESCRIPTION');
END IF;
IF iClobSize > 0
THEN
LOOP
iLeftOver := (iClobSize - nOffset + 1);
IF iLeftOver <= iMaxSize
THEN
iChunkSize := iLeftOver;
ELSE
iChunkSize := iMaxSize;
END IF;
dbms_lob.READ(clobColumn, iChunkSize, nOffset, vBuffer);
dbms_output.put_line(v_id ||' ,'||v_name ||' ,'||v_sector||' ,'||v_country||' ,'||vBuffer);
UTL_FILE.PUTF(v_filehandle,'%s %s %s %s %s',v_id v_name v_sector v_country vBuffer);
nOffset := nOffset + iMaxSize;
IF nOffset > iClobSize
THEN
EXIT;
END IF;
END LOOP;
END IF;
nOffset := 1;
END LOOP;
UTL_FILE.FCLOSE(v_filehandle);
CLOSE c1;
END;
Now my Problem is:
I DON'T want all the data from flat file and want to select some of it and upload into Final target Table.
So I have two options
Option 1) One to read the flat file and put all the data in temp table. And later query this temp table.
Option 2) In the ABOVE code I directly put the data from CLOB into a temp table(Temp table will hold in varchar2() format). And later query this tamp table.
My Questions are:
1) If I choose Option 1, i.e. Flat file, Can i select data from flat file by some method and put it directly in the Final target table?
Or Do I have to put it in Temp table first? and then query it?
Note that I don't have access to UNIX server on which data base is residing. So I can't write any Pl/SQL loader to write data from flat file into Temp table.
2) I would like to know if we can Load data from Flat file into table using UTL_FILE package?
3) Or Do you think using option 2 is better since I don't have to use flat file?
[Updated on: Thu, 28 December 2006 23:57] Report message to a moderator |
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
