Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE errors
UTL_FILE errors [message #10121] Tue, 06 January 2004 01:01 Go to next message
Ken Jones
Messages: 70
Registered: January 2004
Member
Hi,

OS:SunOS haddock 5.6 Generic_105181-35 sun4u sparc SUNW,Ultra-Enterprise
Oracle Software: Enterprise Edition 8.1.7.4.0

I am receiving buffer overflow messages (ORU-10027: buffer overflow, limit of 1000000 bytes) using DBMS_OUTPUT.PUT_LINE to write to a .csv file on my server. Therefore I have tried to rewrite the code to utilise UTL_FILE (no buffer limits) to write to /batch_jobs/journals/snow_ua_commit_timesheet.csv. I have an init.ora parameter set as:-

UTL_FILE_DIR = '/batch_jobs/journals'

The code is:-

CREATE OR REPLACE PROCEDURE ANC43.ANC_REPORT_INTERFACE_RECORDS
AS
BEGIN
UTL_FILE.FOPEN('/batch_jobs/journals', 'snow_ua_commit_timesheet.csv', 'W');
UTL_FILE.PUT_LINE('COST_CENTRE,'||
'HISTORY_DATE,'||
'REF_NO,'||
'FIELD_NAME,'||
'VALUE,'||
'DATA_TYPE,'||
'CHANGE_DATE,'||
'INT_REF');
FOR recInterface IN
(
SELECT E.COSTCENTRE,
I.REFNO,
TO_CHAR(I.HISTORYDATE,'DD/MM/YYYY HH24:MI') AS HDATE,
I.FIELDNAME,
I.VALUE,
I.DATATYPE,
TO_CHAR(I.CHANGEDATE,'DD/MM/YYYY HH24:MI') AS CDATE,
I.SNOWDROP_INTERFACE_REF
FROM SNOWDROP_INTERFACE I,
EMPLOYEE E
WHERE E.EMPLOY_REF = I.REFNO
GROUP BY E.COSTCENTRE,
I.REFNO,
I.HISTORYDATE,
I.FIELDNAME,
I.VALUE,
I.DATATYPE,
I.CHANGEDATE,
I.SNOWDROP_INTERFACE_REF
ORDER BY E.COSTCENTRE,
I.REFNO
)
LOOP
UTL_FILE.PUT_LINE(recInterface.COSTCENTRE||','||
recInterface.HDATE||','||
recInterface.REFNO||','||
recInterface.FIELDNAME||','||
recInterface.VALUE||','||
recInterface.DATATYPE||','||
recInterface.CDATE||','||
recInterface.SNOWDROP_INTERFACE_REF);
END LOOP;
UTL_FILE.fclose();
END;
/

When I try to compile it I get the following error:-
PLS-00306: wrong number or types of arguments in call to 'FOPEN'.

I haven't used UTL_FILE before so any help wuld be greatly appreciated.

Thanks in advance,

Ken.

Report message to a moderator

Re: UTL_FILE errors [message #10122 is a reply to message #10121] Tue, 06 January 2004 01:05 Go to previous messageGo to next message
Ken Jones
Messages: 70
Registered: January 2004
Member
Maybe it is something to do with not having a file handler.e.g.

l_file UTL_FILE.file_Type;

in declaration section of PL/SQL block.????

Report message to a moderator

Re: UTL_FILE errors [message #10123 is a reply to message #10122] Tue, 06 January 2004 01:22 Go to previous messageGo to next message
Ken Jones
Messages: 70
Registered: January 2004
Member
All,

I have changed the code to include a file handler and also some exception handling.:-

CREATE OR REPLACE PROCEDURE ANC43.ANC_REPORT_INTERFACE_RECORDS
AS
output_file utl_file.file_type;
BEGIN
output_file:=UTL_FILE.FOPEN('/batch_jobs/journals', 'snow_ua_commit_timesheet.csv', 'W');
UTL_FILE.PUT_LINE('COST_CENTRE,'||
'HISTORY_DATE,'||
'REF_NO,'||
'FIELD_NAME,'||
'VALUE,'||
'DATA_TYPE,'||
'CHANGE_DATE,'||
'INT_REF');
FOR recInterface IN
(
SELECT E.COSTCENTRE,
I.REFNO,
TO_CHAR(I.HISTORYDATE,'DD/MM/YYYY HH24:MI') AS HDATE,
I.FIELDNAME,
I.VALUE,
I.DATATYPE,
TO_CHAR(I.CHANGEDATE,'DD/MM/YYYY HH24:MI') AS CDATE,
I.SNOWDROP_INTERFACE_REF
FROM SNOWDROP_INTERFACE I,
EMPLOYEE E
WHERE E.EMPLOY_REF = I.REFNO
GROUP BY E.COSTCENTRE,
I.REFNO,
I.HISTORYDATE,
I.FIELDNAME,
I.VALUE,
I.DATATYPE,
I.CHANGEDATE,
I.SNOWDROP_INTERFACE_REF
ORDER BY E.COSTCENTRE,
I.REFNO
)
LOOP
UTL_FILE.PUT_LINE(recInterface.COSTCENTRE||','||
recInterface.HDATE||','||
recInterface.REFNO||','||
recInterface.FIELDNAME||','||
recInterface.VALUE||','||
recInterface.DATATYPE||','||
recInterface.CDATE||','||
recInterface.SNOWDROP_INTERFACE_REF);
END LOOP;
UTL_FILE.fclose();
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
END;
/

I now get the error 'PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'' in line 6.

Any ideas?

Ken.

Report message to a moderator

Re: UTL_FILE errors [message #10124 is a reply to message #10123] Tue, 06 January 2004 01:26 Go to previous messageGo to next message
Ken Jones
Messages: 70
Registered: January 2004
Member
All,

I may have cracked it!! I put file handler in fopen and fclose statements and the procedure compiled. I haven't run it yet so fingers crossed. Here is the code:-

CREATE OR REPLACE PROCEDURE ANC43.ANC_REPORT_INTERFACE_RECORDS
AS
output_file utl_file.file_type;
BEGIN
output_file:=UTL_FILE.FOPEN('/batch_jobs/journals', 'snow_ua_commit_timesheet.csv', 'W');
UTL_FILE.PUT_LINE(output_file,'COST_CENTRE,'||
'HISTORY_DATE,'||
'REF_NO,'||
'FIELD_NAME,'||
'VALUE,'||
'DATA_TYPE,'||
'CHANGE_DATE,'||
'INT_REF');
FOR recInterface IN
(
SELECT E.COSTCENTRE,
I.REFNO,
TO_CHAR(I.HISTORYDATE,'DD/MM/YYYY HH24:MI') AS HDATE,
I.FIELDNAME,
I.VALUE,
I.DATATYPE,
TO_CHAR(I.CHANGEDATE,'DD/MM/YYYY HH24:MI') AS CDATE,
I.SNOWDROP_INTERFACE_REF
FROM SNOWDROP_INTERFACE I,
EMPLOYEE E
WHERE E.EMPLOY_REF = I.REFNO
GROUP BY E.COSTCENTRE,
I.REFNO,
I.HISTORYDATE,
I.FIELDNAME,
I.VALUE,
I.DATATYPE,
I.CHANGEDATE,
I.SNOWDROP_INTERFACE_REF
ORDER BY E.COSTCENTRE,
I.REFNO
)
LOOP
UTL_FILE.PUT_LINE(output_file,recInterface.COSTCENTRE||','||
recInterface.HDATE||','||
recInterface.REFNO||','||
recInterface.FIELDNAME||','||
recInterface.VALUE||','||
recInterface.DATATYPE||','||
recInterface.CDATE||','||
recInterface.SNOWDROP_INTERFACE_REF);
END LOOP;
UTL_FILE.fclose(output_file);
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
END;
/

Cheers,

Ken.

Report message to a moderator

Re: UTL_FILE errors [message #10133 is a reply to message #10124] Tue, 06 January 2004 03:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
Your procedure looks good now, assuming that you re-started your database after setting the utl_file_dir parameter in the init.ora file. In case you ever need such a thing again, you might be interested in Tom Kyte's generic dump_csv function that accepts any query, such as the one used in your for loop, and outputs the result to a csv file. PLease click on the link below:

Report message to a moderator

Re: UTL_FILE errors [message #10164 is a reply to message #10133] Tue, 06 January 2004 23:52 Go to previous message
Ken Jones
Messages: 70
Registered: January 2004
Member
Thanks Barbara!!

Report message to a moderator

Previous Topic: Foreign /primary key
Next Topic: ORA-04031: unable to allocate 4096 bytes of shared memory
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 01:26:33 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.