Need help for Usage of "UTL_FILE_GET_LINE"
Gerd Kaluzinski got error -937 (EXC04, message not found) from UTL_FILE_GET_LINE on 15.0.0.2 with an lvarchar(32672) buffer. Art Kagel advised a support case; Paul Watson suspected a parameter type mismatch. Andreas Legner found it also fails on 14.10 and that a wide initial value or a CHAR(n) variable avoids it. Gerd confirmed CHAR(32672) solved it.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello,
When I use
UTL_FILE_GET_LINE
I always get the error:
(EXC04) - Message cannot be found.
Version 15.0.0.2 on Ubuntu.
Trace shows, that the file is open:
execute function informix.utl_file_is_open[procid=613] ( arg[0] = 0 )expression:
(select (<procedure> utl_file_is_open, logfile)
from systables
where (= tabid, 1))
evaluates to t ;
let x = t
expression:(not-null txt)
evaluates to t
execute (<procedure> utl_file_get_line, logfile, txt);
execute procedure informix.utl_file_get_line[procid=615] ( arg[0] = 0 , arg[1] = )exception : looking for handler
SQL error = -937 ISAM error = 0 error string = = "utl_file_get_line (EXC04 - Message cannot be found.)"exception : no appropriate handler
Writing to file and append works fine.
Here a repro:
database stores;create or replace procedure read_file()
returning varchar(255);
define logfile utl_file_file_type;
define txt lvarchar(32672);
define x boolean;
set debug file to "kalu.out";
trace on;
let txt='';
select
UTL_FILE_FOPEN
("/tmp","IFX_Text_Output.txt","r")
into logfile
from systables where tabid = 1;
select
UTL_FILE_IS_OPEN
(logfile)
into x
from systables where tabid = 1;
while txt is not null
call UTL_FILE_GET_LINE(logfile,txt);
return txt with resume;
end while
call UTL_FILE_FCLOSE(logfile);
end procedure;
Mit freundlichen Grüßen / Kind regards
Gerd Kaluzinski
Delivery Consultant Data
IBM Technology Expert Labs
Phone: +49 175 228 1983 IBM Deutschland GmbH
Email:
gerd.kaluzinski@de.ibm.com
Mies-van-der-Rohe-Straße 6, 80807 München
IBM Deutschland GmbH
Vorsitzender des Aufsichtsrats: Ivo Körner
Geschäftsführung: Wolfgang Wendt (Vorsitzender), Dr. Andreas Buchelt, Dr. Frank Kohls, Christine Rupp
Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562
Gerd: I would recommend that you open a support case. Since this is in v15.0 this might be a case where the datablade function has to rewritten to better support v15.0! Support needs to look into this one. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------
Double check the signatures, the functions are obviously there with the passing signatures. Normally, this is just bad parameters or bad parameter types Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
try changing define txt lvarchar(32672); to varchar From manual .... file An input argument of type UTL_FILE.FILE_TYPE that contains the file handle of the opened file. buffer An output argument of type VARCHAR(32672) that contains a line of text from the file. On 11/26/2025 6:42 AM, Art Kagel via IBM TechXchange Community wrote: 0100019ac02f98d7-a9c2ed3a-9b3d-4606-b39f-215f0e672fdb-000000@email.amazonses.com"> Gerd: I would recommend that you open a support case. Since this is in v15.0 this might be a case where the datablade function has to rewritten... -posted to the "Informix" group
When I look at the definition of the function, the second parameter is LVARCHAR (and VARCHAR cannot exceed the size of 255). So the reason for the error is somewhere in the C-Function. Mit freundlichen Grüßen / Kind regards Gerd Kaluzinski Delivery Consultant Data IBM Technology Expert Labs Phone: +49 175 228 1983 IBM Deutschland GmbH Email: gerd.kaluzinski@de.ibm.com Mies-van-der-Rohe-Straße 6, 80807 München IBM Deutschland GmbH Vorsitzender des Aufsichtsrats: Ivo Körner Geschäftsführung: Wolfgang Wendt (Vorsitzender), Dr. Andreas Buchelt, Dr. Frank Kohls, Christine Rupp Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562
Hi Gerd, This equally doesn't work with 14.10. The message you should be getting there, for error code EXC04, is "Invalid operation : File could not be opened or operated on as requested." - not sure why it's not found on your system. The problem goes away if you either initialize your txt to a wide value (wider than any line in your file) or if you declare txt as a char(n) (again wide enough). This doesn't make sense, though, in my view... From the code, it should support lvarchar, but it cannot guess that variable's length other than from its value's length. Hence The documentation is poor (what's a VARCHAR(32672)?? And an example would be really useful), but where it seems to be right is that you need to catch the NO_DATA_FOUND exception rather than testing your output variable for NULL. Again, an example for this would help. HTH, and yes, if you want this to be improved, a support case would be due. Andreas ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------
@Andreas Thank you very much for your help. Using CHAR(32672) as definition for the parameter (defined as lvarchar) solves the problem. The official documentation is not very useful, so I will provide a documentation with examples in the INFORMIX Newsletter 2026_Q1, which can then be found like all other Newsletters (since 2016) at: https://iug.de/informix-newsletter/ Older issues (since 2006 until the latest one) can be found at https://www.cursor-distribution.de/de/informix-fuer-administratoren/informix-newsletter-ibm/category/434-informix-newsletter Mit freundlichen Grüßen / Kind regards Gerd Kaluzinski Delivery Consultant Data IBM Technology Expert Labs Phone: +49 175 228 1983 IBM Deutschland GmbH Email: gerd.kaluzinski@de.ibm.com Mies-van-der-Rohe-Straße 6, 80807 München IBM Deutschland GmbH Vorsitzender des Aufsichtsrats: Ivo Körner Geschäftsführung: Wolfgang Wendt (Vorsitzender), Dr. Andreas Buchelt, Dr. Frank Kohls, Christine Rupp Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562
Gerd: Please submit the article to the IIUG Insider as well. You can just email me the article. I'll even translate it of you want. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
No suprise the manual is wrong, the like you said the definition is lvarchar. Still smells like a parameter type mismatch On 11/27/2025 4:10 AM, Gerd Kaluzinski via IBM TechXchange Community wrote: 0100019ac4ca9081-4d36e45d-1730-479e-8082-64dce29e0a88-000000@email.amazonses.com"> When I look at the definition of the function, the second parameter is LVARCHAR (and VARCHAR cannot exceed the size of 255). So the reason for... -posted to the "Informix" group