Forum Stats

  • 3,828,121 Users
  • 2,260,865 Discussions
  • 7,897,483 Comments

Discussions

SQLPLUS on Windows generates special character every 14 lines

User_1DB6D
User_1DB6D Member Posts: 2 Green Ribbon

Hi All,

I have a sqlplus script to spool ouput to file, but it generates a special character as in the screenshot uploaded. This is occurring in the beginning of every 14 lines. Any idea what could be the cause and how to get rid of this while the file is generated? I have below lines in the sql file that generates the file.

SET TRIMSPOOL ON

SET PAGESIZE 100000

SET LINESIZE 400

SET NEWPAGE 0

SET SPACE 0

SET ECHO OFF

SET FEEDBACK OFF

SET HEADING OFF

SPOOL file1.csv


<SQL STATEMENT>

/


SPOOL OFF


Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond
    Answer ✓

    Why every 14 rows: because your SET PAGESIZE command is ignored; the maximum valid size is 50000. 14 is the default pagesize, which is then used in your script (since your attempt to change it fails).

    SET NEWPAGE 0 inserts a formfeed character (decimal ASCII code 12) at the beginning of every page. That seems to be what you are seeing there. The fix is to SET NEWPAGE NONE (not 0).

    You may also want to experiment with SET PAGESIZE 0 (instead of some very large number) - that automatically turns off some things that you turn off with separate commands in your script.

    Jonathan LewisKayK

Answers

  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond
    Answer ✓

    Why every 14 rows: because your SET PAGESIZE command is ignored; the maximum valid size is 50000. 14 is the default pagesize, which is then used in your script (since your attempt to change it fails).

    SET NEWPAGE 0 inserts a formfeed character (decimal ASCII code 12) at the beginning of every page. That seems to be what you are seeing there. The fix is to SET NEWPAGE NONE (not 0).

    You may also want to experiment with SET PAGESIZE 0 (instead of some very large number) - that automatically turns off some things that you turn off with separate commands in your script.

    Jonathan LewisKayK
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,982 Blue Diamond

    Just a quick note to head off in a different direction from @mathguy

    I see you've given your spool file a suffix of ".csv", if you're run a recent version of Oracle you may want to check the SQL*Plus manual for the command: "set markup csv [options]".


    Regards

    Jonathan Lewis

    NextName
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    Of course, generating structured data files through a user interface always has risks especially when those interfaces can do their own "formatting" of output on top of what you try and do with your SQL statements.

    When I generate CSV data, I generate it inside a CLOB in the database and then write that CLOB out to a file in one go using something like DBMS_LOB.CLOB2FILE (if you're using an old version of Oracle that doesn't have CLOB2FILE in the DBMS_LOB package, then use the one in DBMS_XSLPROCESSOR package instead).

    That way, whatever you generate in the CLOB is what you get in the file, without any interface interfering with the formatting.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,982 Blue Diamond

    @BluShadow

    Horses for courses, of course, but that sounds very inefficient and more likely to introduce errors since (presumably) you have to include code to format the data that you're putting into the CLOB.

    Regards

    Jonathan Lewis

  • User_1DB6D
    User_1DB6D Member Posts: 2 Green Ribbon

    Thank you very much, I changed the settings as suggested and it worked as expected. 👍️

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond


    Not really, it eliminates any possibility of different interfaces doing their own things with formatting, and puts all the formatting control in my hands as the developer.

    I've never found it inefficient to build up my data in a CLOB and the write it in a single go. Seems to work faster than transferring the data over a client connection and spooling it to a file as it goes, from my experience at least.

    I have a dbms_sql based procedure that takes a query, determines the datatypes and writes the details out to a CSV with known date format, and double quoting text... so it's consistent for any CSV I need to write out and, now it's written, isn't prone to errors 😉

    Far better than trying to figure out what page sizes and line sizes etc. are needed in SQL*Plus which can introduce some really odd output (not withstanding the newer markup csv option of course)

    It also means my data remains on the server as it's generated as a file on there, which is more secure and doesn't break data protection laws, as the data isn't being created on a client machine where it can be inadvertently copied, transferred, left lying around for someone else to see etc. These are all things to consider when extracting data from a secure database to a o/s based file that doesn't have the same security.

    NextName
  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    I've never found it inefficient to build up my data in a CLOB and the write it in a single go. Seems to work faster than transferring the data over a client connection and spooling it to a file as it goes, from my experience at least.

    If the file is needed by the client, it still needs to be downloaded from the server. If we discard the network transfer, sqlplus formats csv and writes to a file faster than writing to CLOB. In example the difference is 2.5 times.

    SQL> set timing on
    SQL> declare
      2    cl clob;
      3    s varchar2(200 char);
      4  begin
      5    cl := 'N|S'||chr(10);
      6    for c in (select level n, rpad(level, 100, level) s from dual connect by level<=1e5) loop
      7      s := to_char(c.n) || ',' || c.s || chr(10);
      8      dbms_lob.writeappend(cl, length2(s), s);
      9    end loop;
     10    dbms_output.put_line('l='||dbms_lob.getlength(cl));
     11    dbms_lob.freetemporary(cl);
     12  end loop;
     13  /
    l=10688899
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:02.48
    SQL> set termout off
    SQL> host type 1.sql
    set arraysize 5000
    set markup csv on delimiter , quote off
    spool 1.csv
    select level n, rpad(level, 100, level) s from dual connect by level<=1e5;
    spool off
    
    SQL> @1
    SQL> host findstr Elapsed 1.csv
    Elapsed: 00:00:00.95
    
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    If we discard the network transfer

    Well, of course if you have a console on the server and are spooling direct on the server, then that's different.

    Most people "running SQL*Plus" will be doing so on a client, so network transfer does usually play a significant factor.

    If the file is needed by the client, it still needs to be downloaded from the server

    Well, of course that's the case, but often CSV files are produced to transfer data between servers for different processes/applications to interface with each other (where database links etc. aren't appropriate/possible). When it comes to matters of security it's better for the file to be generated on the server and then transferred where it's actually needed rather than someone generating it on their client to then copy it over to some destination, often leaving copies of the file lying around on their client.

    My point was that there's more to be concerned about than just getting SQL*Plus to be able to format CSV files without corrupting the output. Protection of data is very important when it comes to taking data out of the database.