Home » SQL & PL/SQL » SQL & PL/SQL » UTL_File Buffer size
| UTL_File Buffer size [message #182991] |
Wed, 19 July 2006 02:32  |
sanjit
Messages: 65
Registered: November 2001
|
Member |
|
|
I have to create a ASCII file from oracle using PL/SQL.
I have written procedure and I am builting a string that suppose to write all data into one string, like row1|row2|row3|.....row n
Now I found the data type which I declare for capturing all string is havinf maximum limit of 32767 whereas in few cases there are thousnads of rows in the table.
It works fine with few hundereds of rows but byond certain number I am getting error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I have tried with Long datatype but seems this too has limitation of 32k byte.
What would be best way to handle this.
regards
anand
|
|
|
|
|
|
| Re: UTL_File Buffer size [message #183012 is a reply to message #182991] |
Wed, 19 July 2006 03:07  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What are you planning to do with this string when you have it?
If you're going to write it out to disc, just use UTL_FILE to write out each row as you get it, using UTL_FILE.PUT rather than UTL_FILE.PUT_LINE.
Otherwise, you want to use a CLOB datatype.
SQL> set serveroutput on size 10000
SQL> declare
2 v_clob clob;
3 v_vc2 varchar2(32767);
4 begin
5 v_vc2 := rpad('A',32767,'B');
6
7 for i in 1..10 loop
8 v_clob := v_clob||v_vc2;
9 end loop;
10
11 dbms_output.put_line('Clob Length = '||DBMS_LOB.getlength(v_clob));
12 end;
13 /
Clob Length = 327670
PL/SQL procedure successfully completed.
|
|
|
|
| Re: UTL_File Buffer size [message #183041 is a reply to message #183012] |
Wed, 19 July 2006 04:34 |
sanjit
Messages: 65
Registered: November 2001
|
Member |
|
|
I need to built a string with all rows which is fetch in cursor.
I have tried with LONG which have limitation of 2 GB data byte but cound no luck.
here is piece of package
Will utl_file.put_raw works
pl advice
thanks in advance
regards
sanjit
--------------
CREATE OR REPLACE PACKAGE BODY XX_CITIDIRECT_EXP_PKG
AS
PROCEDURE main_proc(
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
p_period IN VARCHAR2
)
IS
-- Variables
lv_file_handle UTL_FILE.FILE_TYPE;
lv_file_name_txt VARCHAR2(25) ;
lv_file_location_txt VARCHAR2(100) :='/dv1/gfp/ora01/dv1gfpcomn/temp';
v_error_code NUMBER;
v_error_text VARCHAR2(200);
lv_cursor_rowcnt NUMBER := 0;
lv_row_data VARCHAR2(32767);
-- lv_row_tran_data VARCHAR2(32767);
lv_row_tran_data LONG;
lv_row_head_data LONG;
lv_dept_employees LONG;
-- lv_dept_employees VARCHAR2(32767);
-- lv_row_head_data VARCHAR2(32767);
lv_transaction_number VARCHAR2(1000);
lv_header_id NUMBER;
lv_amount NUMBER;
lv_count NUMBER;
lv_deliminator VARCHAR2(10);
lv_period VARCHAR2(100);
prev_dept NUMBER;
l_temp VARCHAR2(32767);
prev_data NUMBER;
lv_invoice_reference VARCHAR2(200);
---
file_cnt NUMBER := 1; -- Number of files written so far
row_cnt NUMBER := 0; -- Total lines written to latest file
max_rows NUMBER := 50; -- Number of lines allowed in any file
----now for raw
my_vr RAW(32000);
vblob BLOB;
lv_cursor_tran_rowcnt NUMBER := 0;
--
CURSOR c_emp(p_period varchar2--,p_dept number
) IS
SELECT
b.segment1 participant_id,
b.segment6 counterparty_id,
c.currency_code ccy,
( -nvl(a.entered_dr,0) + nvl( a.entered_cr,0)) amount ,
c. default_effective_date maturity_date,
null invoice_date,
-- (b.segment1||c.DOC_SEQUENCE_VALUE||b.segment6) invoice_reference,
c.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
a.jgzz_recon_ref invoice_reference,
null po_reference,
a.je_line_num line_num,
c.je_header_id original_number_oracle,
DECODE(b.segment2,680000,'P',780000,'R',b.segment2) account,
NULL comments,
null bill_period
from gl_je_lines a
,gl_code_combinations b
,gl_je_headers c
WHERE a.je_header_id=c.je_header_id
AND a.code_combination_id=b.code_combination_id
AND b.segment6!='0000'
AND b.segment2 IN('680000','780000')
-- AND C.PERIOD_NAME IN ('FEB-06')
AND C.PERIOD_NAME =p_period
AND C.JE_SOURCE IN ('102','2','63','AP Translator','Consolidation','Intercompany','Manual','Payables','Payroll','Receivables','Spreadsheet')
-- AND c.je_header_id=p_dept
AND B.SeGMENT1!=B.SeGMENT6
and ( -nvl(a.entered_dr,0) + nvl( a.entered_cr,0))!=0
AND ROWNUM<300
ORDER BY 1,2 ,10 desc;
--->>> cursor for identifying the records
BEGIN
--->>
lv_file_handle := UTL_FILE.FOPEN(lv_file_location_txt, lv_file_name_txt, 'w','32767');
lv_transaction_number:=XX_GL_Utils_Pkg.get_transaction_number;
lv_row_tran_data := NULL;
lv_row_head_data := NULL;
lv_dept_employees := '';
FOR r_emp IN c_emp (p_period) --,r_dept.dept)
LOOP
--
lv_cursor_rowcnt := lv_cursor_rowcnt + 1;
lv_cursor_tran_rowcnt := lv_cursor_tran_rowcnt + 1;
lv_invoice_reference := r_emp.participant_id||lpad(r_emp.DOC_SEQUENCE_VALUE,11,'0')||lpad(r_emp.line_num,5,'0')||r_emp.counterparty_id;
lv_row_head_data := 'HED';
lv_dept_employees := lv_dept_employees
||'''TRN'|| '+'
||rpad(nvl(substr('98765432',1, ,'') ,8,' ') || '+'
|| lv_transaction_number||'+'
|| rpad(nvl(substr('01',1,2),'') ,2,' ') ||'+'
|| rpad(nvl(substr(r_emp.participant_id,1,4),' ') ,4,' ') || '+'
|| rpad(nvl(substr(r_emp.ccy,1,3),' ') ,3,' ') || '+'
|| Lpad(nvl(substr(r_emp.amount,1,15),' ') ,15,'0') || '+'
|| rpad(nvl(substr(r_emp.counterparty_id,1,4),' ') ,4,' ') || '+'
|| rpad(nvl(substr(to_char(r_emp.maturity_date,'yyyymmdd'),1,,'') ,8,' ') || '+'
|| rpad(nvl(substr(to_char(r_emp.invoice_date,'yyyymmdd'),1,,'') ,8,' ') || '+'
|| rpad(nvl(substr(r_emp.account ,1,1),'') ,1,'') || '+'
|| lv_invoice_reference || '+'
|| r_emp.po_reference || '+'
|| r_emp.comments|| '+'
|| rpad(nvl(substr(r_emp.bill_period ,1,4),'') ,4,'')|| '+'
|| 'N'|| '+'
|| 'N';
END IF;
END LOOP;
lv_dept_employees := lv_dept_employees || '''END''';
lv_row_tran_data :=lv_row_head_data ||lv_dept_employees;
xx_gl_utils_pkg.write_out (length(lv_row_tran_data));
UTL_FILE.PUT(lv_file_handle,lv_dept_employees,TRUE);
utl_file.fflush(lv_file_handle);
UTL_FILE.FCLOSE(lv_file_handle);
EXCEPTION
WHEN OTHERS
THEN
v_error_code := SQLCODE;
v_error_text := SQLERRM;
retcode := 2;
xx_gl_utils_pkg.write_out ('Error :' || v_error_code || '- ' || v_error_text );
-- dbms_output.put_line (SQLERRM);
UTL_FILE.FCLOSE_ALL();
END
|
|
|
|
| Re: UTL_File Buffer size [message #183053 is a reply to message #183041] |
Wed, 19 July 2006 04:49 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
In SQL, the LONG datatype has a 2Gb limit.
In Pl/Sql it has a 32760 byte limit.
BUT - you are trying to solve the wrong problem here.
You don't need to work out how to load an arbitrarily large number of rows into a single variable and then write that to disk, particularly as the maximum amount of data you can pass to UTL_FILE before a buffer flush is 32767 bytes.
Just do a UTL_FILE.PUT after each line you read from the cursor.
Also - you don't need to use
rpad(nvl(substr(<field>,1,4),' ') ,4,' ')
Rpad will truncate long strings as well as padding short ones.
Just use
rpad(nvl(<field>,' '),4,' ')
(Hint - Use the CODE tags to wrap your code in to prevent that irritating Smiley effect you're getting.)
|
|
|
|
| Re: UTL_File Buffer size [message #183058 is a reply to message #182991] |
Wed, 19 July 2006 04:52 |
sanjit
Messages: 65
Registered: November 2001
|
Member |
|
|
I am builting a string based out of all the rows from the fetch data.
the string should have this structure
like
START'tx'row1'tx'row2'tx'row3'tx'.......'tx'rown'END
It means row1..rown are the data from row sepearted by deliminator like
row1 =col1+col2.......coln
....
....
rown =Col1n+col2n.....colnn
and tr is delimiator between on string of row
END comes at the end of rown
in my code
I have written
lv_dept_employees as row data
and this is within the loop
and after the loop
I have made
lv_dept_employees := lv_dept_employees || '''END''';
but problem here is
the varibale which holding the value once reached 32767 get short circuited.....
I have tried with LONG data type which hold the string varibale but no luck
any thoughts/advice
thanks in Advance
regards
anand
|
|
|
|
| Re: UTL_File Buffer size [message #183060 is a reply to message #183053] |
Wed, 19 July 2006 04:59 |
sanjit
Messages: 65
Registered: November 2001
|
Member |
|
|
thanks
but could you point out where adjectly i should modify the code ,I am struggling a lot where adjectly should modify...
please advice /suggest
thanks a ton in advance
anand
|
|
|
|
| Re: UTL_File Buffer size [message #183067 is a reply to message #183060] |
Wed, 19 July 2006 05:15 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The basic structure that you need is:
PROCDURE main_proc (<parameter>) IS
<variable definition>
BEGIN
<Open file using UTL_FILE>
<Write Start marker to file>
FOR r_emp IN c_emp LOOP
<put all data for this r_emp record into a string>
<put record start and end markers on the string>
<Write this string out using UTL_FILE.PUT>
END LOOP;
<Write End marker to file>
<Close File>
END;
In your code you can achieve something like this by moving this code UTL_FILE.PUT(lv_file_handle,lv_dept_employees,TRUE);
utl_file.fflush(lv_file_handle);
You should probably add a line after that to set lv_dept_employees to null.
|
|
|
|
|
|
| Re: UTL_File Buffer size [message #183078 is a reply to message #183070] |
Wed, 19 July 2006 05:34 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
In this case, using a CLOB won't help, as the maximum amount of data that you can write out using UTL_FILE is 32767 bytes.
Just to check, you have changed the code in your loop so that it isn't appending to lv_dept_employees each time you go round the loop?
What size is lv_dept_employees defined as?
Report your code, and show us exactly what error mesage you're getting.
I don't think it's erroring on the UTL_FILE.PUT, I think it's erroring somewhere else.
|
|
|
|
 Re: UTL_File Buffer size [message #183093 is a reply to message #183078] |
Wed, 19 July 2006 06:06 |
sanjit
Messages: 65
Registered: November 2001
|
Member |
|
|
hello J,
lv_dept_employees initally was 32676 but i have changed to long which has similar byte.
There are about 2000 records fetching from cursor and i have restricted through rownum at 250.At 249 row the i found
length(lv_row_tran_data)=30211, after that the program get short circuited....
251th row the error message is same ORA-06502: PL/SQL: numeric or value error: character string buffer too small
so I think there is some catch at this point only.
please advice..thanks a ton in advance.
anand
==============================================================
CREATE OR REPLACE PACKAGE BODY XX_CITIDIRECT_EXP_PKG
AS
PROCEDURE main_proc(
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
p_period IN VARCHAR2
)
IS
-- Variables
lv_file_handle UTL_FILE.FILE_TYPE;
lv_file_name_txt VARCHAR2(25) ;
lv_file_location_txt VARCHAR2(100) :='/dv1/gfp/ora01/dv1gfpcomn/temp';
v_error_code NUMBER;
v_error_text VARCHAR2(200);
lv_cursor_rowcnt NUMBER := 0;
lv_row_data VARCHAR2(32767);
-- lv_row_tran_data VARCHAR2(32767);
lv_row_tran_data LONG;
lv_row_head_data LONG;
lv_dept_employees LONG;
-- lv_dept_employees VARCHAR2(32767);
-- lv_row_head_data VARCHAR2(32767);
lv_transaction_number VARCHAR2(1000);
lv_header_id NUMBER;
lv_amount NUMBER;
lv_count NUMBER;
lv_deliminator VARCHAR2(10);
lv_period VARCHAR2(100);
prev_dept NUMBER;
l_temp VARCHAR2(32767);
prev_data NUMBER;
lv_invoice_reference VARCHAR2(200);
lv_cursor_tran_rowcnt NUMBER := 0;
CURSOR c_emp(p_period varchar2--,p_dept number
) IS
SELECT
b.segment1 participant_id,
b.segment6 counterparty_id,
c.currency_code ccy,
( -nvl(a.entered_dr,0) + nvl( a.entered_cr,0)) amount ,
c. default_effective_date maturity_date,
null invoice_date,
-- (b.segment1||c.DOC_SEQUENCE_VALUE||b.segment6) invoice_reference,
c.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
a.jgzz_recon_ref invoice_reference,
null po_reference,
a.je_line_num line_num,
c.je_header_id original_number_oracle,
DECODE(b.segment2,680000,'P',780000,'R',b.segment2) account,
NULL comments,
null bill_period
from gl_je_lines a
,gl_code_combinations b
,gl_je_headers c
WHERE a.je_header_id=c.je_header_id
AND a.code_combination_id=b.code_combination_id
AND b.segment6!='0000'
AND b.segment2 IN('680000','780000')
-- AND C.PERIOD_NAME IN ('FEB-06')
AND C.PERIOD_NAME =p_period
AND C.JE_SOURCE IN ('102','2','63','AP Translator','Consolidation','Intercompany','Manual','Payables','Payroll','Receivables','Spreadsheet')
AND B.SeGMENT1!=B.SeGMENT6
and ( -nvl(a.entered_dr,0) + nvl( a.entered_cr,0))!=0
AND ROWNUM<250
ORDER BY 1,2 ,10 desc;
--->>> cursor for identifying the records
BEGIN
--->>
lv_file_name_txt := 'citi'||TO_CHAR (SYSDATE, 'DDMMYYYY')||'.trn';
lv_period :=p_period;
lv_deliminator :=',';
lv_file_handle := UTL_FILE.FOPEN(lv_file_location_txt, lv_file_name_txt, 'w','32767');
lv_transaction_number:=XX_GL_Utils_Pkg.get_transaction_number;
lv_row_tran_data := NULL;
lv_row_head_data := NULL;
lv_dept_employees := '';
FOR r_emp IN c_emp (p_period)
LOOP
lv_cursor_rowcnt := lv_cursor_rowcnt + 1;
lv_cursor_tran_rowcnt := lv_cursor_tran_rowcnt + 1;
lv_invoice_reference := r_emp.participant_id||lpad(r_emp.DOC_SEQUENCE_VALUE,11,'0')||lpad(r_emp.line_num,5,'0')||r_emp.counterparty_id;
lv_row_head_data := 'HED';
lv_dept_employees := lv_dept_employees
||'''TRN'|| '+'
||rpad(nvl(substr('98765432',1,8),'') ,8,' ') || '+'
|| lv_transaction_number||'+'
|| rpad(nvl(substr('01',1,2),'') ,2,' ') ||'+'
|| rpad(nvl(substr(r_emp.participant_id,1,4),' ') ,4,' ') || '+'
|| rpad(nvl(substr(r_emp.ccy,1,3),' ') ,3,' ') || '+'
|| Lpad(nvl(substr(r_emp.amount,1,15),' ') ,15,'0') || '+'
|| rpad(nvl(substr(r_emp.counterparty_id,1,4),' ') ,4,' ') || '+'
|| rpad(nvl(substr(to_char(r_emp.maturity_date,'yyyymmdd'),1,8),'') ,8,' ') || '+'
|| rpad(nvl(substr(to_char(r_emp.invoice_date,'yyyymmdd'),1,8),'') ,8,' ') || '+'
|| rpad(nvl(substr(r_emp.account ,1,1),'') ,1,'') || '+'
|| lv_invoice_reference || '+'
|| r_emp.po_reference || '+'
|| r_emp.comments|| '+'
|| rpad(nvl(r_emp.bill_period,' '),4,' ') || '+'
|| 'N'|| '+'
|| 'N';
UTL_FILE.PUT(lv_file_handle,lv_dept_employees);
utl_file.fflush(lv_file_handle);
END LOOP;
lv_dept_employees := lv_dept_employees || '''END''';
lv_row_tran_data :=lv_row_head_data ||lv_dept_employees;
UTL_FILE.FCLOSE(lv_file_handle);
dbms_output.put_line(length(lv_row_tran_data));
EXCEPTION
WHEN OTHERS
THEN
v_error_code := SQLCODE;
v_error_text := SQLERRM;
retcode := 2;
dbms_output.put_line (SQLERRM);
UTL_FILE.FCLOSE_ALL();
END;
|
|
|
|
| Re: UTL_File Buffer size [message #183098 is a reply to message #183093] |
Wed, 19 July 2006 06:19 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Don't take this the wrong way, but are you actually reading my posts to try and solve the problem yourself, or just to see what I've asked for in the hope I'll fix the problems for you?
In my last post, I asked:
| Quote: |
Just to check, you have changed the code in your loop so that it isn't appending to lv_dept_employees each time you go round the loop?
|
Looking at your code, what do we see?
You set lv_dept_employees to null before the loop, and then every time you go round the loop, you run this command:
lv_dept_employees := lv_dept_employees||'''TRN'|| '+'.....
Which is doing EXACTLY what I asked you to check that you weren't doing.
The effect of this is that every time you go through the loop, the string lv_dept_employees gets longer and longer and longer. Sooner or later (and it's at around record 250) it hits the 32767 limit and goes bang.
(I also mentioned that you might want to consider clearing down lv_dept_employees inside the loop in the post before that.)
I'm quite willing to help people with their problems, but it would be nice if you could try fixing it yourself as well.
So, here's what you need to do:
1) Get rid of the LONG entirely. They're an obsolete data type, and very unfriendly to use. Switch back to a varchar2(32767).
2) Replace these lines inside the loop
UTL_FILE.PUT(lv_file_handle,lv_dept_employees);
utl_file.fflush(lv_file_handle);
with these lines
UTL_FILE.PUT(lv_file_handle,lv_dept_employees);
utl_file.fflush(lv_file_handle);
lv_dept_employees := null;
|
|
|
|
| Re: UTL_File Buffer size [message #183119 is a reply to message #183098] |
Wed, 19 July 2006 07:33 |
sanjit
Messages: 65
Registered: November 2001
|
Member |
|
|
hey J,
I am really apologies.
I have tried and again its does not come at the end.I guess
UTL_File.Put does have limitation of 32676 of all sums.
It again get errored if we exceed the rownum more 300.
here is from documrentation:
PUT writes the text string stored in the buffer parameter to the open file identified by the file handle.
The file must be open for write operations. No line terminator is appended by PUT; use NEW_LINE to terminate the line or use PUT_LINE to write a complete line with a line terminator. See also "PUT_NCHAR Procedure".
Syntax
UTL_FILE.PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);
Parameters
Table 167-19 PUT Procedure Parameters
Parameters Description
file :Active file handle returned by an FOPEN_NCHAR call. The file must be open for writing.
buffer: Buffer that contains the text to be written to the file.
You must have opened the file using mode w or mode a; otherwise, an INVALID_OPERATION exception is raised.
Usage Notes
The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN.
If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.
could you please help me what and where goes wrong.
I am really struggling in getting the string with all rows
I once again thank you for all your much needed help.
regards
sanjit
i am just putting the piece of code .once again for your reference
=================================================
BEGIN
--->>
lv_file_handle := UTL_FILE.FOPEN(lv_file_location_txt, lv_file_name_txt, 'w','32767');
lv_transaction_number:=XX_GL_Utils_Pkg.get_transaction_number;
lv_row_tran_data := NULL;
lv_row_head_data := NULL;
lv_dept_employees := '';
lv_row_head_data := 'HED';
UTL_FILE.PUT(lv_file_handle,lv_row_head_data);
lv_dept_employees := '';
FOR r_emp IN c_emp (p_period)
LOOP
lv_cursor_rowcnt := lv_cursor_rowcnt + 1;
lv_cursor_tran_rowcnt := lv_cursor_tran_rowcnt + 1;
lv_invoice_reference := r_emp.participant_id||lpad(r_emp.DOC_SEQUENCE_VALUE,11,'0')||lpad(r_emp.line_num,5,'0')||r_emp.counterparty_id;
lv_dept_employees :='''TRN'|| '+'
||rpad(nvl(substr('98765432',1,8),'') ,8,' ') || '+'
|| lv_transaction_number||'+'
|| rpad(nvl(substr('01',1,2),'') ,2,' ') ||'+'
|| rpad(nvl(substr(r_emp.participant_id,1,4),' ') ,4,' ') || '+'
|| rpad(nvl(substr(r_emp.ccy,1,3),' ') ,3,' ') || '+'
|| Lpad(nvl(substr(r_emp.amount,1,15),' ') ,15,'0') || '+'
|| rpad(nvl(substr(r_emp.counterparty_id,1,4),' ') ,4,' ') || '+'
|| rpad(nvl(substr(to_char(r_emp.maturity_date,'yyyymmdd'),1,8),'') ,8,' ') || '+'
|| rpad(nvl(substr(to_char(r_emp.invoice_date,'yyyymmdd'),1,8),'') ,8,' ') || '+'
|| rpad(nvl(substr(r_emp.account ,1,1),'') ,1,'') || '+'
|| lv_invoice_reference || '+'
|| r_emp.po_reference || '+'
|| r_emp.comments|| '+'
|| rpad(nvl(substr(r_emp.bill_period ,1,4),'') ,4,'')|| '+'
|| 'N'|| '+'
|| 'N';
END IF;
UTL_FILE.PUT(lv_file_handle,lv_dept_employees);
utl_file.fflush(lv_file_handle);
lv_dept_employees := null;
END LOOP;
lv_dept_employees := lv_dept_employees || '''END''';
lv_row_tran_data :=lv_row_head_data ||lv_dept_employees;
xx_gl_utils_pkg.write_out (length(lv_row_tran_data));
UTL_FILE.PUT(lv_file_handle,lv_dept_employees,TRUE);
utl_file.fflush(lv_file_handle);
UTL_FILE.FCLOSE(lv_file_handle);
EXCEPTION
WHEN OTHERS
THEN
v_error_code := SQLCODE;
v_error_text := SQLERRM;
retcode := 2;
xx_gl_utils_pkg.write_out ('Error :' || v_error_code || '- ' || v_error_text );
-- dbms_output.put_line (SQLERRM);
UTL_FILE.FCLOSE_ALL();
END
================================
|
|
|
|
| Re: UTL_File Buffer size [message #183126 is a reply to message #183119] |
Wed, 19 July 2006 08:23 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Could you post the ACTUAL code, not some edited version of it.
I know that the code you just posted isn't the code you're running because you have an END IF inside the loop, just before the UTL_FILE.PUT, and there's no matching IF statement.
Are you still geting the same error, or a different one?
If it's definitely the same error, we need to find out what line it's happening at, because I can't see where it could be in the code you've posted.
It's not Utl_file that's causing the problem as:
1) you're doing a FLUSH after each PUT to avoid the size limit
2) UTL_FILE errors would show up in your code as 'User Defined Exception'
So, either
1) Add a local variable lv_debug, of type number, add lots of code to your procedure setting lv_debug to distinct values after every statement, and then ensure that lv_debug gets reported out in the When Others block
Or
2) Comment out the when others block, and call this procedure directly from SQL*Plus. This will give you the entire error stack, including line numbers. You can then look in the USER_SOURCE view for actual code on that line.
|
|
|
|
| Re: UTL_File Buffer size [message #183193 is a reply to message #183126] |
Wed, 19 July 2006 20:35 |
sanjit
Messages: 65
Registered: November 2001
|
Member |
|
|
Hey J,
Greeting 
Thanks for your suggestion and help.
After debuging I found that the error encounter is beacuse of UTL_FILE.WRITE_ERROR ...
| Quote: |
'An operating system error occurred during the write operation.
|
just after row 307, where the utl_file.put buffer reaches the limit of 32767. Also we have set UTL_File.Fopen at 32767 and this is one reason which causing the problem.
| Quote: |
lv_file_handle := UTL_FILE.FOPEN(lv_file_location_txt, lv_file_name_txt, 'w','32767');
|
I didnot understand why utl_file.fflush is not releasing ....
I gone through documentation and found
| Quote: |
The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.
|
Could you suggest where is catch.
thanks a ton once again in advance.
anan
======
CREATE OR REPLACE PACKAGE BODY XX_CITIDIRECT_EXP_PKG
AS
PROCEDURE main_proc(
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
p_period IN VARCHAR2
)
IS
-- Variables
lv_file_handle UTL_FILE.FILE_TYPE;
lv_file_name_txt VARCHAR2(25) ;
lv_file_location_txt VARCHAR2(100) :='/dv1/gfp/ora01/dv1gfpcomn/temp';
v_error_code NUMBER;
v_error_text VARCHAR2(200);
lv_cursor_rowcnt NUMBER := 0;
lv_row_data VARCHAR2(32767);
lv_row_tran_data VARCHAR2(32767);
-- lv_row_tran_data LONG;
-- lv_row_head_data LONG;
-- lv_dept_employees LONG;
lv_dept_employees VARCHAR2(32767);
lv_row_head_data VARCHAR2(32767);
lv_transaction_number VARCHAR2(1000);
lv_header_id NUMBER;
lv_amount NUMBER;
lv_count NUMBER;
lv_deliminator VARCHAR2(10);
lv_period VARCHAR2(100);
prev_dept NUMBER;
l_temp VARCHAR2(32767);
prev_data NUMBER;
lv_invoice_reference VARCHAR2(200);
lv_cursor_tran_rowcnt NUMBER := 0;
CURSOR c_emp(p_period varchar2) IS
SELECT
b.segment1 participant_id,
b.segment6 counterparty_id,
c.currency_code ccy,
( -nvl(a.entered_dr,0) + nvl( a.entered_cr,0)) amount ,
c. default_effective_date maturity_date,
null invoice_date,
-- (b.segment1||c.DOC_SEQUENCE_VALUE||b.segment6) invoice_reference,
c.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
a.jgzz_recon_ref invoice_reference,
null po_reference,
a.je_line_num line_num,
c.je_header_id original_number_oracle,
DECODE(b.segment2,680000,'P',780000,'R',b.segment2) account,
NULL comments,
null bill_period
from gl_je_lines a
,gl_code_combinations b
,gl_je_headers c
WHERE a.je_header_id=c.je_header_id
AND a.code_combination_id=b.code_combination_id
AND b.segment6!='0000'
AND b.segment2 IN('680000','780000')
-- AND C.PERIOD_NAME IN ('FEB-06')
AND C.PERIOD_NAME =p_period
AND C.JE_SOURCE IN ('102','2','63','AP Translator','Consolidation','Intercompany','Manual','Payables','Payroll','Receivables','Spreadsheet')
-- AND c.je_header_id=p_dept
AND B.SeGMENT1!=B.SeGMENT6
and ( -nvl(a.entered_dr,0) + nvl( a.entered_cr,0))!=0
AND ROWNUM<308
ORDER BY 1,2 ,10 desc;
BEGIN
--->>
lv_file_name_txt := 'citi'||TO_CHAR (SYSDATE, 'DDMMYYYY')||'.trn';
lv_period :=p_period;
lv_deliminator :=',';
lv_file_handle := UTL_FILE.FOPEN(lv_file_location_txt, lv_file_name_txt, 'w','32767');
lv_transaction_number:=XX_GL_Utils_Pkg.get_transaction_number;
lv_row_tran_data := NULL;
lv_row_head_data := NULL;
lv_row_head_data := 'HED';
UTL_FILE.PUT(lv_file_handle,lv_row_head_data);
lv_dept_employees := '';
FOR r_emp IN c_emp (p_period)
LOOP
lv_cursor_rowcnt := lv_cursor_rowcnt + 1;
lv_cursor_tran_rowcnt := lv_cursor_tran_rowcnt + 1;
lv_invoice_reference := r_emp.participant_id||lpad(r_emp.DOC_SEQUENCE_VALUE,11,'0')||lpad(r_emp.line_num,5,'0')||r_emp.counterparty_id;
lv_dept_employees := '''TRN'|| '+'
||rpad(nvl(substr('98765432',1,8),'') ,8,' ') || '+'
|| lv_transaction_number||'+'
|| rpad(nvl(substr('01',1,2),'') ,2,' ') ||'+'
|| rpad(nvl(substr(r_emp.participant_id,1,4),' ') ,4,' ') || '+'
|| rpad(nvl(substr(r_emp.ccy,1,3),' ') ,3,' ') || '+'
|| Lpad(nvl(substr(r_emp.amount,1,15),' ') ,15,'0') || '+'
|| rpad(nvl(substr(r_emp.counterparty_id,1,4),' ') ,4,' ') || '+'
|| rpad(nvl(substr(to_char(r_emp.maturity_date,'yyyymmdd'),1,8),'') ,8,' ') || '+'
|| rpad(nvl(substr(to_char(r_emp.invoice_date,'yyyymmdd'),1,8),'') ,8,' ') || '+'
|| rpad(nvl(substr(r_emp.account ,1,1),'') ,1,'') || '+'
|| lv_invoice_reference || '+'
|| r_emp.po_reference || '+'
|| r_emp.comments|| '+'
|| rpad(nvl(r_emp.bill_period,' '),4,' ') || '+'
|| 'N'|| '+'
|| 'N';
--- xx_gl_utils_pkg.write_out (lv_cursor_rowcnt||'==>'||lv_dept_employees); ----debug
UTL_FILE.PUT(lv_file_handle,lv_dept_employees);
utl_file.fflush(lv_file_handle);
lv_dept_employees := null;
END LOOP;
lv_dept_employees := lv_dept_employees || '''END''';
UTL_FILE.PUT(lv_file_handle,lv_dept_employees);
lv_row_tran_data :=lv_row_head_data ||lv_dept_employees;
IF lv_cursor_tran_rowcnt = 0 THEN
xx_gl_utils_pkg.write_out ('NO Transaction found ');
END IF;
UTL_FILE.FCLOSE(lv_file_handle);
EXCEPTION
WHEN UTL_FILE.invalid_operation
THEN
xx_gl_utils_pkg.write_log ('The file could not be opened or operated on as requested. ' );
xx_gl_utils_pkg.write_out ('The file could not be opened or operated on as requested. ' );
UTL_FILE.fclose_all;
retcode := 2;
WHEN UTL_FILE.invalid_maxlinesize
THEN
xx_gl_utils_pkg.write_log ('The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767' );
xx_gl_utils_pkg.write_out ('The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767 ' );
UTL_FILE.fclose_all;
retcode := 2;
WHEN UTL_FILE.invalid_path
THEN
xx_gl_utils_pkg.write_log ('File location or filename was invalid.');
xx_gl_utils_pkg.write_out ('File location or filename was invalid.');
UTL_FILE.fclose_all;
retcode := 2;
WHEN UTL_FILE.invalid_mode
THEN
xx_gl_utils_pkg.write_log ('The open_mode parameter in FOPEN was invalid. ' );
xx_gl_utils_pkg.write_out ('The open_mode parameter in FOPEN was invalid. ' );
UTL_FILE.fclose_all;
retcode := 2;
WHEN UTL_FILE.invalid_filehandle
THEN
xx_gl_utils_pkg.write_log ('The file handle was invalid. ');
xx_gl_utils_pkg.write_out ('The file handle was invalid. ');
UTL_FILE.fclose_all;
retcode := 2;
WHEN UTL_FILE.read_error
THEN
xx_gl_utils_pkg.write_log ('An operating system error occurred during the read operation.' );
xx_gl_utils_pkg.write_out ('An operating system error occurred during the read operation.' );
UTL_FILE.fclose_all;
retcode := 2;
WHEN UTL_FILE.write_error
THEN
xx_gl_utils_pkg.write_log ('An operating system error occurred during the write operation. ' );
xx_gl_utils_pkg.write_out ('An operating system error occurred during the write operation. ' );
UTL_FILE.fclose_all;
retcode := 2;
WHEN UTL_FILE.internal_error
THEN
xx_gl_utils_pkg.write_log ('An unspecified error in PL/SQL.');
xx_gl_utils_pkg.write_out ('An unspecified error in PL/SQL.');
UTL_FILE.fclose_all;
retcode := 2;
WHEN OTHERS
THEN
v_error_code := SQLCODE;
v_error_text := SQLERRM;
retcode := 2;
xx_gl_utils_pkg.write_out ('Error :' || v_error_code || '- ' || v_error_text );
-- dbms_output.put_line (SQLERRM);
UTL_FILE.FCLOSE_ALL();
END;
PROCEDURE write_log (p_text_in IN VARCHAR2)
IS
BEGIN
Fnd_File.put_line (Fnd_File.LOG, p_text_in);
EXCEPTION
WHEN OTHERS THEN
RAISE;
--
END write_log;
PROCEDURE write_out (p_text_in IN VARCHAR2)
IS
BEGIN
Fnd_File.put_line (Fnd_File.output, p_text_in);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END write_out;
END XX_CITIDIRECT_EXP_PKG;
|
|
|
|
| Re: UTL_File Buffer size [message #183239 is a reply to message #183193] |
Thu, 20 July 2006 02:21 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Doing a quick check on the doc for FFLUSH, the problem is this:
| Quote: |
FFLUSH Procedure
FFLUSH physically writes pending data to the file identified by the file handle.
Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file.
The data must be terminated with a newline character.
|
In effect, there is a 32kb maximum line size to UTL_FILE
You are trying to write all the data out as a single line with no cr/lf characters, and so you're hitting this limit.
Solutions:
1) Use PUT_LINE rather than PUT, and have a file with multiple lines in it
2) Try using PUT_RAW - this has an option that allows you to auto-flush the buffer, and given that it's raw data, it probably won't have a 32k linesize limit.
[Updated on: Thu, 20 July 2006 02:22] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed Apr 24 17:36:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
