生成的sqlplus文件中的字符不可读 [英] Unreadable character in generated sqlplus file

问题描述

我有一个带有一个命令行的shell，该命令行打开sql plus并调用一个sql文件

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

谢谢

推荐答案

从描述中听起来您有login.sql或glogin.sql

From the description it sounds like you have a login.sql or glogin.sql user profile that is issuing set newpage 0:

设置从每页顶部到顶部标题要打印的空白行数. 零值会将换页符放置在每页的开头(包括第一页)，并清除大多数终端上的屏幕.如果将NEWPAGE设置为NONE，则SQL * Plus不会在报告页面之间打印空白行或换页.

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.

在Excel中，它确实显示为不可打印的字符，并在小方块中带有问号；在Notepad ++中，它似乎以FF的反色显示；并且在某些编辑器(例如Vim)中显示为^L.这是 ASCII换行符字符，十进制12或0xC.

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.

您可以使用set newpage none重置该值，或者使用set pagesize 0使其不相关，这具有删除列标题和分隔符的便捷副作用.如果您还没有的话，可能还想set feedback off.

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.

具有对比设置的样品:

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.

还有

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

这篇关于生成的sqlplus文件中的字符不可读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！