oracle 创建一个 bat 文件来运行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4436876/
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
to Create a bat file to run a query
提问by Remya
I need to create a bat file which include a query to run a package. I use plsql developer to develop the package. its username,password and database is user,pswd,db1 respectively. The query to run the package is:
我需要创建一个 bat 文件，其中包含运行包的查询。我使用 plsql developer 来开发包。它的用户名、密码和数据库分别是 user、pswd、db1。运行包的查询是：
SELECT
COLUMN1 AS "LAST NAME",
COLUMN2 AS "FIRST NAME",
COLUMN3 AS "LOCATION"
FROM TABLE(PKG.GET_SUM('09-NOV-2010','12-NOV-2010'))
can anyone help me what code shud I write to create a bat file
谁能帮我写什么代码来创建一个bat文件
Thanks in advance
提前致谢
This is my code
这是我的代码
connect usr/pswd@db1
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 100
COLUMN COLUMN1 HEADING 'LAST NAME'
COLUMN COLUMN2 HEADING 'FIRST NAME'
COLUMN COLUMN3 HEADING 'LOCATION'
spool c:\temp\get_sums.csv
SELECT COLUMN1
,COLUMN2
,COLUMN3
,COLUMN4
,COLUMN5
, COLUMN6
,COLUMN7,
COLUMN8
,COLUMN9
FROM TABLE(ACTY_SUMM('09-NOV-2010','12-NOV-2010'))
/
spool off
exit;
I changed the line size and pagesize. But not able to get the desirable format. I have 12 columns. I need it in a report format.
我更改了行大小和页面大小。但无法获得理想的格式。我有 12 列。我需要它的报告格式。
Hi I tried to change the Linesize,but still I am not getting the desired format. I want the report in the following format
嗨，我尝试更改 Linesize，但仍然没有获得所需的格式。我想要以下格式的报告
FirstNAME LASTNAME LOCATION A B C D E F G H I
NAME1 LNAME1 LOC1 A1 B1 C1 D1 E1 F1 G1 H1 I1
NAME2 LNAME2 LOC1 A2 B2 C2 D2 E2 F2 G2 H2 I2
LOCTOT
NAME3 LNAME3 LOC2 A3 B3 C3 D3 E3 F3 G3 H3 I3
LOCTOT
I need all the column in one row and their corresponding values of each person under each column and after each location there will be location total and at the end there will be grant total. I tried with the linesize,page size and all. still no result. Can anyone help me to get the report in this format
我需要一行中的所有列以及每列下每个人的相应值，并且在每个位置之后会有位置总数，最后会有总拨款。我尝试了 linesize、page size 和所有。仍然没有结果。谁能帮我得到这种格式的报告
Hi
你好
As of now client is ok with the csv format. But the challenge is I am not getting the heading. This is my code in sql script.
截至目前，客户可以使用 csv 格式。但挑战是我没有得到标题。这是我在 sql 脚本中的代码。
connect usr/pwd@db1
SET NEWPAGE 0
SET LINESIZE 100
SET PAGESIZE 0
spool c:\temp\q1.csv
COLUMN COLUMN1 HEADING 'LAST NAME'
COLUMN COLUMN2 HEADING 'FIRST NAME'
COLUMN COLUMN3 HEADING 'LOCATION'
COLUMN COLUMN4 HEADING 'A'
COLUMN COLUMN5 HEADING 'B'
COLUMN COLUMN6 HEADING 'C'
COLUMN COLUMN7 HEADING 'D'
COLUMN COLUMN8 HEADING 'E'
COLUMN COLUMN9 HEADING 'F'
COLUMN COLUMN10 HEADING 'G'
COLUMN COLUMN11 HEADING 'H'
COLUMN COLUMN12 HEADING 'I'
SELECT
'"'||COLUMN1
||'","'|| COLUMN2
||'","'|| COLUMN3
||'","'|| COLUMN4
||'","'|| COLUMN5
||'","'|| COLUMN6
||'","'|| COLUMN7
||'","'|| COLUMN8
||'","'|| COLUMN9
||'","'|| COLUMN10
||'","'|| COLUMN11
||'","'|| COLUMN12||'"'
FROM
TABLE(ACTY_SUM('09-NOV-2010','12-NOV-2010'))
/
spool off
exit;
Can you please suggest on this issue
你能就这个问题提出建议吗
回答by APC
First create a script to run the query. You'll want to capture the output to a file, hence the SPOOL command.
首先创建一个脚本来运行查询。您需要将输出捕获到文件中，因此使用 SPOOL 命令。
connect usr/pswd@db1
spool c:\temp`get_sums.lst
SELECT
COLUMN1 AS "LAST NAME",
COLUMN2 AS "FIRST NAME",
COLUMN3 AS "LOCATION"
FROM TABLE(PKG.GET_SUM('09-NOV-2010','12-NOV-2010'))
/
spool off
exit;
save that to a file called get_sums.sql . Then you need a batch file get_sums.bat like this:
将其保存到名为 get_sums.sql 的文件中。然后你需要一个像这样的批处理文件 get_sums.bat：
sqlplus /nolog @C:\get_sums.sql
There are various SQL*Plus commands you can include in the .sql file t format the output. Find out more.
您可以在 .sql 文件中包含各种 SQL*Plus 命令以格式化输出。 了解更多。
Apparently a link to the formatting documentation is not sufficient.
显然，指向格式文档的链接是不够的。
There is no point in setting LINESiZE to 80, that is the default. If you are selecting nine columns and you want all nine values to appear on one line you need to set the LINESIZE so that it is long enough to accomodate all the columns. This means you need to set LINESIZE to the sum of all the columns' widths plus eight (the number of interstitial spaces between ninbe columns).
将 LINESiZE 设置为 80 没有任何意义，这是默认值。如果您选择九列并且希望所有九个值都出现在一行上，您需要设置 LINESIZE 以便它足够长以容纳所有列。这意味着您需要将 LINESIZE 设置为所有列的宽度之和加上八（ninbe 列之间的间隙数）。
Although I see you have tried
虽然我看到你已经尝试过
SET SPACE 0
This causes all the selected columns to run together in one long line, which is a highly unusual way of laying out a report. But if that's really what you want then ignore the interstitial spaces when calculating the correct value for LINESIZE.
这会导致所有选定的列一起排成一行，这是一种非常不寻常的报表布局方式。但是，如果这确实是您想要的，那么在计算 LINESIZE 的正确值时忽略间隙空间。
Having formatted you posted code I see you are outputting to .csv. Why didn't you say you wanted to export comma separated values in the first place?
格式化您发布的代码后，我看到您正在输出到.csv. 为什么你一开始不说要导出逗号分隔的值？
There are several ways of doing this. The most straightforward is to concatenate your query's projection with commas:
有几种方法可以做到这一点。最直接的是用逗号连接查询的投影：
SELECT
'"'||COLUMN1
||'","'|| COLUMN2
||'","'|| COLUMN3
||'","'|| COLUMN4
||'","'|| COLUMN5
||'","'|| COLUMN6
||'","'|| COLUMN7
||'","'|| COLUMN8
||'","'|| COLUMN9 ||'"'
FROM TABLE(ACTY_SUMM('09-NOV-2010','12-NOV-2010'))
/
The double-quotes are the optional field terminators, which will handle any string columns which contain commas.
双引号是可选的字段终止符，它将处理任何包含逗号的字符串列。
"I dont want coma seperated values. I edited my post. Please suggest "
“我不想要昏迷分隔值。我编辑了我的帖子。请提出建议”
Here is some test data:
下面是一些测试数据：
SQL> select * from t23
2 /
♀FIRSTNAME LASTNAME LOCATION A B C D E F
-------------------- -------------------- ---------- -- -- -- -- -- --
G H I
-- -- --
NAME1 LNAME1 LOC1 A1 B1 C1 D1 E1 F1
G1 H1 I1
NAME2 LNAME2 LOC1 A2 B2 C2 D2 E2 F2
G2 H2 I2
NAME3 LNAME3 LOC2 A3 B3 C3 D3 E3 F3
G3 H3 I3
SQL>
I can produce the basic layout you want using SQL*Plus formatting commands
我可以使用 SQL*Plus 格式化命令生成您想要的基本布局
SQL> set linesize 100
SQL> break on location
SQL> compute count of location on location
SQL> compute count of location on report
SQL> r
1* select * from t23
♀FIRSTNAME LASTNAME LOCATION A B C D E F G H I
-------------------- -------------------- ---------- -- -- -- -- -- -- -- -- --
NAME1 LNAME1 LOC1 A1 B1 C1 D1 E1 F1 G1 H1 I1
NAME2 LNAME2 A2 B2 C2 D2 E2 F2 G2 H2 I2
----------
2
NAME3 LNAME3 LOC2 A3 B3 C3 D3 E3 F3 G3 H3 I3
----------
1
SQL>
