Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re:Import excelfile into Oracle table
I heard , ( I think in the same list ) ... FILE PATH can be a path to your
NT local machine too... ( even can have multiple UTL_FILE_DIR locations on
NT machine )
So we should be able to read a file on NT machine to load the data to the Oracle using UTL_FILE package.
--Madhu
-----Original Message-----
Sent: Thursday, April 18, 2002 5:13 AM
To: Multiple recipients of list ORACLE-L
Here is an example. As far as I know, utl_file package reads data from unix box. So the file path below should be on Unix and be careful about your rights writing or reading from this path.
DECLARE
outfile_handle UTL_FILE.FILE_TYPE;
v_test VARCHAR2(1000) ;
BEGIN outfile_handle := UTL_FILE.FOPEN('FILE PATH','file_name','A');
v_test := 'This is a Test ' ;
UTL_FILE.PUT_LINE(outfile_handle, v_test) ;
UTL_FILE.FCLOSE (outfile_handle) ;
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN
DBMS_OUTPUT.PUT_LINE('Invalid File Handle');
UTL_FILE.FCLOSE_ALL;
WHEN UTL_FILE.INVALID_MODE
THEN
UTL_FILE.FCLOSE_ALL;
DBMS_OUTPUT.PUT_LINE('Invalid Mode');
WHEN UTL_FILE.INTERNAL_ERROR
THEN
UTL_FILE.FCLOSE_ALL;
DBMS_OUTPUT.PUT_LINE('Internal Error');
WHEN UTL_FILE.INVALID_OPERATION
THEN
UTL_FILE.FCLOSE_ALL;
DBMS_OUTPUT.PUT_LINE('Invalid Operation');
WHEN UTL_FILE.INVALID_PATH
THEN
UTL_FILE.FCLOSE_ALL;
DBMS_OUTPUT.PUT_LINE('Invalid Path');
WHEN UTL_FILE.READ_ERROR
THEN
UTL_FILE.FCLOSE_ALL;
DBMS_OUTPUT.PUT_LINE('Read Error');
WHEN UTL_FILE.WRITE_ERROR
THEN
UTL_FILE.FCLOSE_ALL;
DBMS_OUTPUT.PUT_LINE('Write Error');
WHEN NO_DATA_FOUND
THEN
UTL_FILE.FCLOSE_ALL;
DBMS_OUTPUT.PUT_LINE('No Data Found');
WHEN VALUE_ERROR
THEN
UTL_FILE.FCLOSE_ALL;
DBMS_OUTPUT.PUT_LINE('Value Error' || step || ' ' || SUBSTR(V_BUFF,25,7) ||
step);
WHEN OTHERS
THEN
UTL_FILE.FCLOSE_ALL; DBMS_OUTPUT.PUT_LINE('Error!' || substr(sqlerrm,1,75) || step); utl_file.fclose_all;
Roland.Skoldblom_at_ica.se
ica.se
Sent by: root_at_fatcity.com
18.04.02 11:38
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Ok, thanks can you give me a good example on how to write the pl/sql code?
Thanks in advance.
Roland
emre.hancioglu_at_eu.effem.com_at_fatcity.com den 2002-04-18 01:10 PST
Sänd svar till ORACLE-L_at_fatcity.com
Sänt av: root_at_fatcity.com
Till: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Kopia:
Hi,
You'd better do the following:
* Convert the excel file to .csv file.
* Use utl_file package to read the data and insert to Oracle Tables.
M.Emre HANCIOGLU
Masterfoods GmbH
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Roland.Skoldblom_at_ica.se
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reddy, Madhusudana
INET: Madhusudana.Reddy_at_bestbuy.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Apr 18 2002 - 10:28:28 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
![]() |
![]() |