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,EXECQTY,
AMOUNT, BALINVQTY, ADDUID, ADDUIDTIME, EDITUID, EDITUIDTIME, CNFMUID,
CNFMUIDTIME, STATUS, RETURNQTY,SOSKUDTLSERIAL, MAINSTOCKBAL,
LASTCHANGEDRATE, LASTCHANGEDAMT, BATCHKEY, CHARGEAMT, FILLTYPE,
BALSTNQTY, SCHEDDATE,TOTITRQTY, ALLOCKEY, ALLOCQTY, TD, EXCISE,
TDPERC, EXCISERATETYPE, EXCISERATE, EXCISEINCL, NEWEXCISEAMT,
NEWEXCISERATE,NEWTDAMT, NEWTDPERC from WHSISKUDTL where rownum <
1000',',','l:\oracle\oradata\fostest\Testing','tes t1.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]

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

ha*******@yahoo.com (Mahesh Hardikar) wrote in message news:<4a**************************@posting.google. 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,EXECQTY,
AMOUNT, BALINVQTY, ADDUID, ADDUIDTIME, EDITUID, EDITUIDTIME, CNFMUID,
CNFMUIDTIME, STATUS, RETURNQTY,SOSKUDTLSERIAL, MAINSTOCKBAL,
LASTCHANGEDRATE, LASTCHANGEDAMT, BATCHKEY, CHARGEAMT, FILLTYPE,
BALSTNQTY, SCHEDDATE,TOTITRQTY, ALLOCKEY, ALLOCQTY, TD, EXCISE,
TDPERC, EXCISERATETYPE, EXCISERATE, EXCISEINCL, NEWEXCISEAMT,
NEWEXCISERATE,NEWTDAMT, NEWTDPERC from WHSISKUDTL where rownum <
1000',',','l:\oracle\oradata\fostest\Testing','tes t1.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]

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_type;
l_theCursor integer default dbms_sql.open_cursor;
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.execute(l_theCursor);
loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 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_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);
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
rc***@panix.com (Robert C) wrote in message news:<25**************************@posting.google. com>...

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