使用批处理脚本连接到 oracle db 运行选择命令并将所有数据输出到平面文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10000165/
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
Using Batch Script connect to oracle db run a select command and output all data to a flat file
提问by ak77
I am trying to create a batch file for windows (XP) which will have several sqls and when its run it would 1. connect with oracle 2. set the userid/password/schema 3. run each sql in the loop and 4. output each sql outputs to its own flat file.
我正在尝试为 Windows (XP) 创建一个批处理文件，它将有几个 sqls，当它运行时它将 1. 与 oracle 连接 2. 设置用户 ID/密码/模式 3. 在循环中运行每个 sql 和 4. 输出每个 sql 输出到它自己的平面文件。
I have started the script
我已经启动了脚本
@ECHO off
SET STATE=fl
TABLE1=AGENCY
set SQL1="SELECT Column_ID||CHR(31)||column_ENTITY_CD||CHR(31) FROM AGENCY"
set TABLE2=FIRM
set SQL2="SELECT Column_ID||CHR(31)||Column_NM||CHR(31) FROM FIRM"
set TABLE3=FL_CO_LOB
Set SQL3="SELECT Column_ID||CHR(31)||Column_LOB_CODE||CHR(31) FROM FL_CO_LOB"
...
SET NumberOfTables=19
SETLOCAL ENABLEDELAYEDEXPANSION
FOR /l %%A IN (1,1,%NumberOfTables%) DO (
echo !SQL%%A!
)
endlocal
I can get the SQL out of the variable but don't have any clue how to connect to oracle and run the sql and get the output to a defined file.
我可以从变量中获取 SQL，但不知道如何连接到 oracle 并运行 sql 并将输出获取到定义的文件。
please give me some direction.
请给我一些方向。
one thing to notice that the echo is printing including the double quote. but If i don't have them then it just print the 1st word not the whole query.
要注意的一件事是，回声正在打印，包括双引号。但如果我没有它们，那么它只打印第一个单词而不是整个查询。
Thanks
谢谢
回答by RobW
If you've installed the oracle client on your workstation, you have SQLPlus.exe for command line work with the database. Your TNSNAMES.ORA file needs to be up to date, and tnsping needs to be able to locate your service.
如果您在工作站上安装了 oracle 客户端，则您有 SQLPlus.exe 用于与数据库的命令行工作。您的 TNSNAMES.ORA 文件需要是最新的，并且 tnsping 需要能够定位您的服务。
I think you'll need to change your approach. The scripts you want to run will need to be fed to the SQLPlus.exe program, rather than being delivered semi-interactively.
我认为你需要改变你的方法。您想要运行的脚本需要提供给 SQLPlus.exe 程序，而不是半交互式地交付。
I suggest creating a .sql script instead of creating evnironment variables
我建议创建一个 .sql 脚本而不是创建 evnironment 变量
Your batch file line might look like:
您的批处理文件行可能如下所示：
@ECHO off
SET STATE=fl
set TABLE1=AGENCY
set TABLE2=FIRM
echo SELECT Column_ID^|^|CHR(31)^|^|column_ENTITY_CD^|^|CHR(31) FROM %TABLE1% > tablecommands.sql
echo SELECT Column_ID^|^|CHR(31)^|^|Column_NM^|^|CHR(31) FROM %TABLE2% >> tablecommands.sql
echo SELECT Column_ID^|^|CHR(31)^|^|Column_LOB_CODE^|^|CHR(31) FROM FL_CO_LOB >> tablecommands.sql
...
<until your 19 SQL statements are declared>
SQLPlus User/Password@Database @tablecommands.sql
回答by AVA
Add the sqlplus login command before your sql commands in your batch file. It executes and writes to logs file where the batch file resides
在批处理文件中的 sql 命令之前添加 sqlplus login 命令。它执行并写入批处理文件所在的日志文件
syntax : sqlplus -s ora_user_name/ora_user_password[as sysdba]@ora_sid@"path_to_sql_file" > output.log
语法：sqlplus -s ora_user_name/ ora_user_password[as sysdba] @ora_sid@" path_to_sql_file" > output.log
ex. sqlplus -s scott/tiger@xe @"D:\Oralcle\scripts\sql_file.sql" > output.txt
前任。sqlplus -s scott/tiger@xe @"D:\Oralcle\scripts\sql_file.sql" > output.txt
