DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: DBMS_OUTPUT.PUT_LINE buffer size exceeds

  1. #1
    Join Date
    Aug 2000
    Posts
    32

    Unhappy

    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

  2. #2
    Join Date
    Aug 2001
    Posts
    184
    utl_file
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  4. #4
    Join Date
    Aug 2000
    Posts
    32

    Angry

    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

  5. #5
    Join Date
    Feb 2001
    Posts
    5

    Smile

    UTL_FILE - For writing to a flat file on server side

    TEXT_IO - For writing to a flat file on client side


  6. #6
    Join Date
    Aug 2000
    Posts
    32
    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.




  7. #7
    Join Date
    Feb 2001
    Posts
    5
    UTL_FILE_DIR be set in init.ora file

  8. #8
    Join Date
    Aug 2000
    Posts
    32

    Unhappy

    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

  9. #9
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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 ...


  10. #10
    Join Date
    Aug 2000
    Posts
    32

    Smile

    Thank you very much Shestakov,

    Vimala

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width