oracle 假脱机时删除前导空格和 SQL> 标记
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26778314/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove leading spaces and the SQL> tags when spooling
提问by Keithx
I m doing an export using SQL*Plus using the following code:
我正在使用以下代码使用 SQL*Plus 进行导出：
Set echo off
Set verify off
Set feedback off
Set heading off
Set termout on
Set pages 50000
Set linesize 1000
Set newpage none
Set colsep ","
spool Z:\ff\ss.csv
Select Client_ID, Cust_ID from cust;
spool off;
exit;
I get the correct output, but it doesn't have the best formatting.
我得到了正确的输出，但它没有最好的格式。
SQL> Select Client_ID, Cust_ID from cust;
100,200
200,300
300,400
400,500
SQL>spool off;
How can I get rid of the spaces before the values?
100,200
How can I get rid of the
SQL>statements in the output file?
如何去掉值之前的空格？
100,200
如何摆脱
SQL>输出文件中的语句？
回答by Gary_W
echo offonly works to remove the SQL> prompts when run from a file. Put your commands in a file called export_client_data.sql and run them using the @ command:
echo off仅适用于从文件运行时删除 SQL> 提示。将您的命令放在名为 export_client_data.sql 的文件中，并使用 @ 命令运行它们：
SQL>@export_client_data.sql
SQL*Plus, being a reporting tool, will format output to the column width as defined in the table. Using TRIM() doesn't always work as expected because of this. You will see a VARCHAR column padded on the right to the width for example.
SQL*Plus 作为一种报告工具，将输出格式化为表中定义的列宽。因此，使用 TRIM() 并不总是按预期工作。例如，您将看到一个 VARCHAR 列填充在宽度的右侧。
Typically to create an extract using SQL*Plus you would format the query like this and no need to TRIM anything:
通常要使用 SQL*Plus 创建数据提取，您可以像这样格式化查询，无需修剪任何内容：
Select Client_ID || ',' || Cust_ID from cust;
回答by Joro
Try this options : SET TRIMSPOOL ON otherwise every line in the spoolfile is filled up with blanks until the linesize is reached.
试试这个选项： SET TRIMSPOOL ON 否则 spoolfile 中的每一行都用空格填充，直到达到 linesize。
SET TRIMOUT ON otherwise every line in the output is filled up with blanks until the linesize is reached.
SET TRIMOUT ON 否则输出中的每一行都用空格填充，直到达到 linesize。
SET WRAP OFF Truncates the line if its is longer then LINESIZE. This should not happen if linesize is large enough.
SET WRAP OFF 如果该行比 LINESIZE 长，则截断该行。如果 linesize 足够大，则不应发生这种情况。
SET TERMOUT OFF suppresses the printing of the results to the output. The lines are still written to the spool file. This may accelerate the exectution time of a statement a lot.
SET TERMOUT OFF 禁止将结果打印到输出。这些行仍会写入假脱机文件。这可能会大大加快语句的执行时间。
回答by jarlh
General, non-Oracle answer:
一般的非 Oracle 答案：
Use ISO/ANSI SQL function
TRIM(Client_ID)to remove leading (and trailing) blanks. (Some DBMS's use LTRIM for leading.)The non-standard REPLACE function may work, i.e.
REPLACE(Client_ID, 'SQL> ', '').
使用 ISO/ANSI SQL 函数
TRIM(Client_ID)删除前导（和尾随）空格。（一些 DBMS 使用 LTRIM 进行引导。）非标准的 REPLACE 函数可能会起作用，即
REPLACE(Client_ID, 'SQL> ', '').
回答by Lalit Kumar B
How can I get rid of the spaces before the values?
如何去掉值之前的空格？
Use TRIM. For example,
使用修剪。例如，
SQL> SELECT TRIM(empno) eno FROM emp;
ENO
-------------------------------------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
14 rows selected.
SQL>
How can I get rid of the SQL> statements in the output file?
如何摆脱输出文件中的 SQL> 语句？
The best way is to use the SILENToption -s.
最好的方法是使用SILENT选项-s。
For example,
例如，
sqlplus -s scott/tiger@orcl
From documentation,
从文档，
SILENT Option
-S[ILENT]
Suppresses all
SQL*Plusinformation and prompt messages, including the command prompt, the echoing of commands, and the banner normally displayed when you startSQL*Plus. If you omit username or password,SQL*Plusprompts for them, but the prompts are not visible! Use SILENT to invokeSQL*Pluswithin another program so that the use ofSQL*Plusis invisible to the user.SILENT is a useful mode for creating reports for the web using the SQLPLUS -MARKUP command inside a CGI script or operating system script. The
SQL*Plusbanner and prompts are suppressed and do not appear in reports created using the SILENT option.
静音选项
-沉默的]
禁止所有
SQL*Plus信息和提示消息，包括命令提示符、命令的回显和启动时正常显示的横幅SQL*Plus。如果省略用户名或密码，SQL*Plus提示输入，但提示不可见！使用 SILENTSQL*Plus在另一个程序中调用，这样SQL*Plus用户就看不到 的使用。SILENT 是一种在 CGI 脚本或操作系统脚本中使用 SQLPLUS -MARKUP 命令创建 Web 报告的有用模式。该
SQL*Plus横幅和提示被抑制，不会出现在使用SILENT选项创建的报表。
Alternatively, you could also do:
或者，你也可以这样做：
set sqlprompt ''
For example,
例如，
SQL> set sqlprompt ''
select empno from emp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
14 rows selected.
So, the SQL>prompt is ignored.
因此，SQL>提示被忽略。
