Hi, I appreciate it very much if

[hasfari]

Hi,

I appreciate it very much if someone can tell me how to go around ORACLE spool restriction. I want to run a script that spools to a file using dbms_output.put_line function.

However, spool has a limit and it stopped throwing text to the file after sometime. I checked ORACLE environment and saw that trimspool is max set to 2000 char.

I tried using UTL_FILE package but with no avail since I have to set

utl_file_dir = 'C:\' in the INIT.ORA file, but I do not see this file on my machine and got stuck here.

 

[jee]

If you are running in the SQL*PLUS environment, you can increase the number of bytes that can be buffered using the SET SERVEROUTPUT ON command - just add "SIZE n" to the end, where n is the number of bytes to buffer (2000 is the default). For instance:

SET SERVEROUTPUT ON SIZE 4000

If you are using some other tool (TOAD, for instance), it likely has some method of changing the buffer size.

Are you running on a client machine, rather than on the database server? If so, you should be able to use the UTL_FILE package with no problem to write a file to your disk - as long as your permissions are set properly on the folder you are trying to write to.

If you are trying to use UTL_FILE from a trigger or stored procedure, then you will need to add the UTL_FILE_DIR parameter to the INITxxx.ORA file (the name of this file may contain the SID name of your database). Since you don't know where this file is, I am assuming you are not a DBA. Your DBA will need to take care of this.

 

[hasfari]

Thanks for the reply. You are right I am working on a client machine. I do not have access to the server but yet I am been asked to work as DBA!

Here is a smaple script thatt I wrote and the feedback from SQLPLUS. I believe the UTL_FILE package is installed on my client machine and do not how to solve this error. I do have access to C:\ drive since it is my machine drive. i believe that ORACLE goes through the package and that is why I am getting lines 98 and 157 errors.

SQL> ed
Wrote file afiedt.buf

1 declare
2 file_handle UTL_FILE.FILE_TYPE;
3 BEGIN
4 file_handle := UTL_FILE.FOPEN('C:\','TEMP.TXT','W');
5 UTL_FILE.PUTF (file_handle,'%s.\n', 'Test');
6 UTL_FILE.FCLOSE(file_handle);
7* END;
SQL> /
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at line 4

 

[jee]

Whoops, I misread the documentation. Apparently there is some sort of package called TEXT_IO that is similar to UTL_FILE that works on the client side. TEXT_IO apparently comes with the Procedure Builder and it looks like that is part of Developer 2000 - which is something I don't have. If you have this, perhaps you could nose around in its documentation and find out how to use it.

One thing that would probably work would be to add

UTL_FILE_DIR = *

to your INITxxx.ORA file (and then stop/restart the database). This is somewhat dangerous because it means you can write anywhere. I wouldn't do it on a production server, but it might be OK on a development machine.

Or maybe you should repost your question and someone who knows something about client-side file I/O might respond.

Sorry I misled you.

 

[sem]

Try to increase serveroutput size. But the better idea is to use dbms_output for debugging purposes ONLY, not for creating reports. As for utl_file - it works on server side. Text_io is used in Developer[2000] tools and I really doubt it can be called from sql*plus.