Re: HELP with SP !!

I've seen this occur before, and for me the cause was that user informix didn't have the correct permissions to write the compilation errors to the file specified in the "with listing in" line. With the where clause the way you have it, you will get a compilation warning on the local variable IN_SBM_PROC_CNTR. There's nothing wrong, and the code will work, but Informix needs to be able to write that warning. It's alittle deceiving, because one would think that as long as the user creating the procedure had permission in that directory one should be ok. This is not the case. When you change the where cause variable to a number, then the warning goes away and the procedure compiles because there is no attempt to write to that file. Since user informix is normally in it's own group, be careful to check the permissions for other in that directory path. I hope this helps! Tracy W. Nedd National Weather Service - DBA vball@skipper.ssmc.noaa.gov } } I am receiving error -229: Could not open or create a temporary file } on the "where" clause for a SP that follows: } } ******************************************************************************* } } drop procedure chk_usid; } -- } create procedure chk_usid } (IN_SBM_PROC_CNTR smallint, } IN_YEAR_NUMBER smallint, } IN_SCN_JULIAN_DT smallint, } IN_CAP_CMP_CD smallint, } IN_CONTAINER_NUM int, } IN_SEQUENCE_NUM smallint) } -- } define p_cnt int; } define sql_err_num int; } define sql_err_txt int; } -- } on exception in (-691) } set sql_err_num,sql_err_txt } end exception } -- } select count(*) } into p_cnt } from submission } where SBM_PROC_CNTR_num = IN_SBM_PROC_CNTR; } } if p_cnt > 0 then } raise exception -691,0, } 'related_tin insert: no matching record found in submission; RI violation.'; } end if } end procedure } document } 'Usage: sbm_proc_cntr_num, year_number, scanned_julian_dt, capture_comp_cd, con } tainer_seq_num, sequence_number' } with listing in '/tmp/SPL/chk_usid.warnings'; } } ******************************************************************************* } } If I change the where clause to "where SBM_PROC_CNTR_num = 1", the procedure } is created. But that's not what I want, I want to have a insert trigger calls } this procedure to verify the value of IN_SBM_PROC_CNTR exists in table } submission (RI stuff). } } Please response and thanks in advance ! } } Diana Li (li@lfs.loral.com) }