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.
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.
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?
UTL_FILE - For writing to a flat file on server side
TEXT_IO - For writing to a flat file on client side
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.
UTL_FILE_DIR be set in init.ora file
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,
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)
UTL_FILE_DIR = /data/io_from_database ...
Thank you very much Shestakov,