4 Replies Latest reply: Jan 11, 2013 7:57 AM by EdStevens RSS

    Change default spool extension

    971526
      Hi,

      I am trying to write piece of code that would spool a file directly in .csv format. However, when I try to do it the .csv file is further appended with .lst and this does not serve my purpose. Is there a way to work around this.

      My existing code for your reference:

      SET ECHO OFF;
      SET FEEDBACK OFF;
      SET SERVEROUTPUT ON;
      SET VERIFY OFF;
      SET PAGES 0;
      SET HEAD OFF;
      set trimspool on;

      column datecol new_value curr_date noprint
      select to_char(sysdate,'YYYYMMDD') datecol from dual;

      SPOOL /home/qqtdrv1/EMP_INACTIVE_abc_&curr_date.csv APPEND;
      SELECT *
      FROM emp;

      SPOOL OFF;
      COMMIT;

      the result should be EMP_INACTIVE_abc_10130111.csv
      bt its coming like EMP_INACTIVE_abc_10130111csv.lst

      Thanks
        • 1. Re: Change default spool extension
          Mihael
          SPOOL /home/qqtdrv1/EMP_INACTIVE_abc_&curr_date.csv APPEND;
          use two dots :

          SPOOL /home/qqtdrv1/EMP_INACTIVE_abc_&curr_date..csv APPEND;
          • 2. Re: Change default spool extension
            Niket Kumar
            have you seen the file name

            EMP_INACTIVE_abc_10130111csv.lst

            if you use .csv then it will give .csv as you forget '.' in it it uses .lst because no file extension is there,

            SQL> spool new.csv
            SQL> spool off
            SQL> exit
            Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options

            /home/xxxxx> ls -l|grep new.csv
            -rw------- 1 xxxxx xxxxx 15 Jan 11 16:05 new.csv
            • 3. Re: Change default spool extension
              Rob_J
              I think Mihael is right, when you have used a variable such as has been used in the original example the two dots are required for some reason. I've had the same issue and that's how we got around it. It's the same if you just want a dot to appear, such as when creating a database link with a domain at the end and you have a variable used for the database name.
              • 4. Re: Change default spool extension
                EdStevens
                Rob_J wrote:
                I think Mihael is right, when you have used a variable such as has been used in the original example the two dots are required for some reason.
                The 'for some reason' is simple enough. When parsing out a variable name, sqlplus takes that first 'dot' as the delimiter of the name, and nothing more.
                I've had the same issue and that's how we got around it. It's the same if you just want a dot to appear, such as when creating a database link with a domain at the end and you have a variable used for the database name.