Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Mohammad.

Asked: June 21, 2000 - 12:31 pm UTC

Last updated: November 17, 2004 - 10:39 am UTC

Version: ORACLE 8.0.5

Viewed 10K+ times! This question is

You Asked

Hi...
I am trying to create a file using UTL_FILE package. I have defined UTL_FILE_DIR parameter in my initSID.ora file at the server and rerun Oracle again. But when I run the program it is giving me error as INVALID_OPERATION (user defined exception.
The code looks like :

SET SERVEROUTPUT ON;
DECLARE
d_file1 UTL_FILE.FILE_TYPE;
d_directory1 varchar2(80):= '/export/home/clarify/aperdata';

BEGIN
dbms_output.put_line('1st...');
d_file1 :=UTL_FILE.FOPEN(d_directory1,'test.txt','W');
dbms_output.put_line('2nd ...');
IF UTL_FILE.IS_OPEN(d_file1) = FALSE
THEN
dbms_output.put_line('File is not open');
END IF;

/*** Document Headers ***/
UTL_FILE.PUT_LINE(d_file1,'Name');
UTL_FILE.PUT_LINE(d_file1,'Address');
UTL_FILE.PUT_LINE(d_file1,'');

UTL_FILE.FCLOSE(d_file1);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.put_line('Error : Invalid Path (' || d_directory1 ||') ') ;

WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.put_line('Error : Invalid File Operation');
IF UTL_FILE.IS_OPEN(d_file1) = TRUE Then
UTL_FILE.FCLOSE(d_file1);
END IF;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.Put_line('Error : Invalid Mode ') ;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('ERROR: ' || SQLCODE || '- '|| SQLERRM);

END;
/
exit;

Basically my program giving me message as "1st...." but not "2nd......" (I put as test purpose).

Any help in this regard is highly appricieted.

Thanx in advance.

Arif


and Tom said...

I would hazzard a guess that '/export/home/clarify/aperdata' is your home directory and the permissions are such that the Oracle account (which is running the server - which is doing the UTL_FILE call) does not have write permission on your home directory.

Remember -- utl_file files will be read/written with the ownership of the Oracle software owner. He who starts the database -- writes the files.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

help import file

adry, November 17, 2004 - 5:45 am UTC

helo,
my procedure gives the following error to me
why?

CREATE OR REPLACE PROCEDURE inserimento AS




V_TIPO_RECORD CHAR(1);
V_TIPO_ELEMENTO CHAR(1);
V_POLO_CREDITO CHAR(1);
V_PROG_RIMB CHAR(19);
V_COD_ESITO CHAR(6);
V_DESC_ESITO CHAR(30);
V_DATA_ESITO CHAR(8);
V_COD_ALL CHAR(35);
V_CODE_CT CHAR(4);
V_COGNOME CHAR(30);
V_INDIRIZZO CHAR(30);
V_CITTA CHAR(21);
V_PROVINCIA CHAR(2);
V_CAP CHAR(5);
V_IBAN CHAR(4);
V_CIN CHAR(1);
V_ABI CHAR(5);
V_CAB CHAR(5);
V_NUM_CC CHAR(12);
V_IMPT_RIMB_A CHAR(13);
V_IMPT_RIMB_B CHAR(2);
V_PROG_RIMB_B CHAR(8);
V_PART_IVA CHAR(16);


v_file UTL_FILE.FILE_TYPE;
v_text VARCHAR2(1000);
v_fld CHAR(5);
v_sql VARCHAR2(1000);
d_directory1 varchar2(80):= 'c:/';

BEGIN

v_file := UTL_FILE.FOPEN(d_directory1,'xx.txt','r',2000);
--v_ename := '';
--V_TIPO_RECORD :=1;
v_sql := 'INSERT INTO appo_cb VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23 )';


UTL_FILE.FCLOSE(v_file);
COMMIT;

/*LOOP
UTL_FILE.GET_LINE(v_file, v_text);
IF V_PROG_RIMB <> 0 THEN
UTL_FILE.FCLOSE(v_file);
COMMIT;
EXIT;

*/

/*END IF;
v_fld := SUBSTR(v_text,1,5);
IF v_fld = 'ename' AND NOT v_ename = '' THEN
IF v_commi = NULL THEN
v_commi := 0;
END IF; */

/* EXECUTE IMMEDIATE v_sql USING V_TIPO_RECORD,
V_TIPO_ELEMENTO,
V_POLO_CREDITO,
V_PROG_RIMB,
V_COD_ESITO ,
V_DESC_ESITO ,
V_DATA_ESITO ,
V_COD_ALL ,
V_CODE_CT ,
V_COGNOME,
V_INDIRIZZO ,
V_CITTA ,
V_PROVINCIA ,
V_CAP ,
V_IBAN ,
V_CIN ,
V_ABI ,
V_CAB ,
V_NUM_CC ,
V_IMPT_RIMB_A ,
V_IMPT_RIMB_B ,
V_PROG_RIMB_B ,
V_PART_IVA
;

*/

-- variabile := '';
/* END IF;
END LOOP;*/
/*CASE v_fld
WHEN 'empno' THEN v_empno := TO_NUMBER(SUBSTR(v_text, 8));
WHEN 'ename' THEN v_ename := SUBSTR(v_text, 8);
WHEN 'jobid' THEN v_jobid := SUBSTR(v_text, 8);
WHEN 'manag' THEN v_manag := TO_NUMBER(SUBSTR(v_text, 8));
WHEN 'hired' THEN v_hired := TO_DATE(SUBSTR(v_text, 8));
WHEN 'salar' THEN v_salar := TO_NUMBER(SUBSTR(v_text, 8));
WHEN 'commi' THEN v_commi := TO_NUMBER(SUBSTR(v_text, 8));
WHEN 'depno' THEN v_depno := TO_NUMBER(SUBSTR(v_text, 8)); */


EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No_data_found');
UTL_FILE.FCLOSE(v_file);
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
UTL_FILE.FCLOSE(v_file);
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.READ_ERROR');
UTL_FILE.FCLOSE(v_file);
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
UTL_FILE.FCLOSE(v_file);
when utl_file.invalid_mode then
raise_application_error(-20002,
'INVALID_MODE: The open_mode parameter in FOPEN was
invalid.');
when utl_file.invalid_filehandle then
raise_application_error(-20002,
'INVALID_FILEHANDLE: The file handle was invalid.');
when utl_file.invalid_operation then
raise_application_error(-20003,
'INVALID_OPERATION: The file could not be opened or
operated on as requested.');
when utl_file.internal_error then
raise_application_error(-20006,
'INTERNAL_ERROR: An unspecified error in PL/SQL.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('No file');


UTL_FILE.FCLOSE(v_file);


END;
/


oppssss

adry, November 17, 2004 - 5:47 am UTC

error :

ERRORE alla riga 1:
ORA-20003: INVALID_OPERATION: The file could not be opened or
operated on as requested.
ORA-06512: at "PJ07_ADM_CUT.INSERIMENTO", line 127
ORA-06512: at line 1

Tom Kyte
November 17, 2004 - 10:39 am UTC

you need all of that code to post that error? (when I encounter an error in any piece of code -- in the database or not - i try isolation as the first approach to figuring it out)

how about this, you chop down your example to just the part that is failing.

it'll be the fopen.

then you put together the entire test case such as:

o here is the setting for utl_file_dir, see with "show parameter" we can see it is set

o the c:\ drive is the c:\ drive of the SERVER....

o here we are logged into the server, dir shows the file exists

o but utl_file.fopen isn't working


My guesses -- c:\xx.txt either does not exist on the SERVER or the Oracle account the database software is running as does not have permission to read it.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library