I have a shell with one command line opening sql plus and calling a sql file
$ORACLE_HOME/bin/sqlplus id/psw @SQL_DIRECTORY/myfile.sql
in this file I write this :
spool myfile.csv
select 'column 01', ';', 'column 02' from dual;
select myColumn1, ';', mycolum2 from mytable
spool off
It works good BUT on the first line of the the first column of each select there is a special character.
Why is that? how can I get rid of this
Thanks
From the description it sounds like you have a login.sql or glogin.sql user profile that is issuing set newpage 0:
Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.
In Excel that does show up as an unprintable character with a question mark in a small square; in Notepad++ it seems to show as FF in reverse colouring; and in some editors (e.g. Vim) it shows as ^L. This is the ASCII form feed character, decimal 12 or 0xC.
You can either reset that with set newpage none, or make it irrelevant with set pagesize 0, which has the convenient side effect of removing the column headers and separators. You may also want to set feedback off if you aren't already.
Samples with contrasting settings:
set newpage 0;
set feedback on;
set pagesize 100;
spool ctrl.csv
select 'head 1;head 2' from dual;
select sysdate, ';', systimestamp from dual;
spool off
^L'HEAD1;HEAD2'
-------------
head 1;head 2
1 row selected.
^LSYSDATE ' SYSTIMESTAMP
------------------- - ---------------------------------------------------------------------------
2015-09-16 15:45:42 ; 16-SEP-15 15.45.42.333627 +01:00
1 row selected.
And
set newpage none;
set feedback off;
set pagesize 0;
spool ctrl.csv
select 'head 1;head 2' from dual;
select sysdate, ';', systimestamp from dual;
spool off
head 1;head 2
2015-09-16 15:46:11 ; 16-SEP-15 15.46.11.274863 +01:00
