Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: Spooling data from long file: Urgent

    Hi,
    I am trying to spool a numeric field and a long field from a table with a pipe seperator and I am getting an error message "In consistent data types".I have tried to get this done using a stored procedure but I am getting error messages with DBMS_OUTPUT package.

    Can anyone please help on how to get this done?

    Thanks
    Manivannan

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    Long columns cannot be cancatenated.
    if u really want it done with the || (pipes), you will have to use PL/SQL and read the long value into a varchar2 variable. The only problem is that u will have to work around the length limitation of the varchar2 of 32676 characters. You can do this in a loop, cut the long in pieces of then write it to file. Better to file then using DBMS_OUTPUT which has a limited buffer of 1000000 characters.

    Using SQL you might wanna try selecting two separate columns the number and the long and use an alias for the columns. Also set pages to 0 and turn of colun heading and be sure to set long = (an appropriate value), otherwise it might not show all text in the long column.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    Thank you for replying.I used the UTL_FILE package and was able to get the job done.But UTL_file package writes the data to the server and it needs a change in the init.ora file and an instance restart.Is there anyway to write the data to the client directory from SQL Plus

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    the only way to 'write' from sqlplus to a local file, is to use spool and dbms_output with all limitations.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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