DBMS_OUTPUT.PUT_LINE buffer size exceeds

[Vimala]

Is there any alternative for DBMS_OUTPUT.PUT_LINE?

I have to get the data from tables into a flatfile through a stored procedure. I could just run a query and spool the data into flat file. But a single query is not enough to get my result, hence I have to use the stored procedure. From stored procedure, is DBMS_OUTPUT.PUT_LINE the only way to get the result? I am getting an error of buffer 1000000 exceeding for DBMS_OUTPUT.PUT_LINE. My data is exceeding 10,00,000. Is there any other way that i could spool the result.

I would appreciate your help.

Thank you,
Vimala

[ocpwannabe]

utl_file

[Shestakov]

This is not simple question.

If u going AND CAN write ur flat file on oracle server box (or some box,
that mapping as mount point on oracle server box) best decision --> UTL_FILE.

If u need place flat file on ANY client box, that execute SQLPLUS, u can't use UTL_FILE.
In this case u probably have to write some tools manually.

[Vimala]

I can write my output on oracle box.

But for now i am trying on client box, and i am getting the below error.

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 "VIMALA.SP_FLATFILE", line 39
ORA-06512: at line 1

How do i overcome this error?

Thanks,
Vimala

[bethis]

UTL_FILE - For writing to a flat file on server side

TEXT_IO - For writing to a flat file on client side

[Vimala]

Now i am trying to write to a flat file on server side.

Is it must to have UTL_FILE_DIR in init.ora file?

I can not afford to restart the database.

[bethis]

UTL_FILE_DIR be set in init.ora file

[Vimala]

Hi,

When I use TEXT/IO, I am getting the below error.

5/11 PLS-00201: identifier 'TEXT_IO.FILE_TYPE' must be declared
5/11 PL/SQL: Item ignored

Do I have to run/install any utility file in order to use TEXT_IO package?

Please let me know. Thanks for your help,

Vimala

[Shestakov]

text_io package is part of Oracle forms, this isn't stored package.
U can't use this package in sqlplus or in any store procedures.

If u going to use text_io then u should:
-- create some Oracle forms application
-- us functions/procedures from text_io package into this application only.

If u going to use utl_file u must setup in init.ora:

UTL_FILE_DIR = *
(or some path, that can be mapping from oracle server box)
for example:
UTL_FILE_DIR = /data/io_from_database ...

[Vimala]

Thank you very much Shestakov,

Vimala