Strange Utl_File Error on NT

[Mahesh Hardikar]

Hi ,

Oracle EE 8.1.7.0.0 on MS WIndows Advanced Server 2000 SP3

We have a proc which is using utl_file .

procedure test_dump_csv
as
l_rows number;
begin
l_rows := SK_dump_csv(
'select SIKEY, SERIAL, SKUCODE, BATCHCODE, BATCHDATE, BATCHEXPIRYDATE ,
SKUSERIAL, UOMCODE, WHCATCODE, WHAREACODE, RATE, ORDERQTY,EXECQT Y,
AMOUNT, BALINVQTY, ADDUID, ADDUIDTIME, EDITUID, EDITUIDTIME, CNFMUID,
CNFMUIDTIME, STATUS, RETURNQTY,SOSKU DTLSERIAL, MAINSTOCKBAL,
LASTCHANGEDRATE , LASTCHANGEDAMT, BATCHKEY, CHARGEAMT, FILLTYPE,
BALSTNQTY, SCHEDDATE,TOTIT RQTY, ALLOCKEY, ALLOCQTY, TD, EXCISE,
TDPERC, EXCISERATETYPE, EXCISERATE, EXCISEINCL, NEWEXCISEAMT,
NEWEXCISERATE,N EWTDAMT, NEWTDPERC from WHSISKUDTL where rownum <
1000',',','l:\o racle\oradata\f ostest\Testing' ,'test1.csv');
end;

Here we are finding 1000 rows using function SK_Dump_CSV. This works
fine. But when I use more than 1000 rows in procedure , it gives me
error ORA-06510 & 6512
Proc gets compiled for rownum < 2001 but execution gives problem.

I tried compiling & running proc in another instance which runs on
HP-UX 11.0. There it gives no problem for rownum 1000 .

WHat can be the issue on NT ?

Any help/pointers in this is highly appreciated ...

Regards,
Mahesh Hardikar

[Robert C]

Re: Strange Utl_File Error on NT

Well, I am guessing you're calling DBMS_SQL - did you verify DBMS_SQL execution
is OK ?



hardikarm@yahoo .com (Mahesh Hardikar) wrote in message news:<4a1c57c2. 0308202228.6929 c626@posting.go ogle.com>...

Hi ,
>
Oracle EE 8.1.7.0.0 on MS WIndows Advanced Server 2000 SP3
>
We have a proc which is using utl_file .
>
procedure test_dump_csv
as
l_rows number;
begin
l_rows := SK_dump_csv(
'select SIKEY, SERIAL, SKUCODE, BATCHCODE, BATCHDATE, BATCHEXPIRYDATE ,
SKUSERIAL, UOMCODE, WHCATCODE, WHAREACODE, RATE, ORDERQTY,EXECQT Y,
AMOUNT, BALINVQTY, ADDUID, ADDUIDTIME, EDITUID, EDITUIDTIME, CNFMUID,
CNFMUIDTIME, STATUS, RETURNQTY,SOSKU DTLSERIAL, MAINSTOCKBAL,
LASTCHANGEDRATE , LASTCHANGEDAMT, BATCHKEY, CHARGEAMT, FILLTYPE,
BALSTNQTY, SCHEDDATE,TOTIT RQTY, ALLOCKEY, ALLOCQTY, TD, EXCISE,
TDPERC, EXCISERATETYPE, EXCISERATE, EXCISEINCL, NEWEXCISEAMT,
NEWEXCISERATE,N EWTDAMT, NEWTDPERC from WHSISKUDTL where rownum <
1000',',','l:\o racle\oradata\f ostest\Testing' ,'test1.csv');
end;
>
Here we are finding 1000 rows using function SK_Dump_CSV. This works
fine. But when I use more than 1000 rows in procedure , it gives me
error ORA-06510 & 6512
Proc gets compiled for rownum < 2001 but execution gives problem.
>
I tried compiling & running proc in another instance which runs on
HP-UX 11.0. There it gives no problem for rownum 1000 .
>
WHat can be the issue on NT ?
>
Any help/pointers in this is highly appreciated ...
>
Regards,
Mahesh Hardikar

[Mahesh Hardikar]

Re: Strange Utl_File Error on NT

Well ,

We have a function

CREATE OR REPLACE function SK_dump_csv( p_query in varchar2,
p_separator in varchar2 default
',',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
is
l_output utl_file.file_t ype;
l_theCursor integer default dbms_sql.open_c ursor;
l_columnValue varchar2(2001);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
dbms_output.put _line(p_dir || ' - ' || p_filename);
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
for i in 1 .. 255 loop
begin
dbms_sql.define _column( l_theCursor, i, l_columnValue,
3000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;
dbms_sql.define _column( l_theCursor, 1, l_columnValue, 3000 );
l_status := dbms_sql.execut e(l_theCursor);
loop
exit when ( dbms_sql.fetch_ rows(l_theCurso r) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column _value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_li ne( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_ cursor(l_theCur sor);
utl_file.fclose ( l_output );
return l_cnt;
end SK_dump_csv;

Procedure is calling this function .

Also , the proc runs for rownum < 2000 but o/p is not proper on NT.
When we give rownum < 2001 , procedure does not run only giving
ORA-6510 - 6512 errors.

Mahesh


rchin@panix.com (Robert C) wrote in message news:<2541279b. 0308210808.5226 c165@posting.go ogle.com>...

Well, I am guessing you're calling DBMS_SQL - did you verify DBMS_SQL execution
is OK ?