oracle 假脱机查询

发布于 2024-08-02 01:50:20 字数 745 浏览 3 评论 0原文

我已经在 unix 机器上按名称 cashload.txt 编写了 sql，并将其保存在 unix 机器上的以下位置：

exit |sqlplus -s batch/password@SW_TEST @/soft/checkfree/AccurateBXG/scripts/cashload.txt

在 cashload.txt 中写入了以下代码：

spool /Detail/reports/inner/SW/Rep_OIbyAccount_$DATE_FILE.csv
select accountnumber||','||accountname||','||X from HSBC_Cash_OIbyAccount_v;
spool off

但它没有假脱机结果集在上述路径上。但是，当我给出保存脚本的路径时，它在该位置假脱机。我不明白为什么？它在 cashload.txt< 的以下路径假脱机/strong>（sql脚本）被保留：

**spool /soft/checkfree/AccurateNXG/scripts/Rep_OIbyAccount.csv**
select accountnumber||','||accountname||','||X from HSBC_Cash_OIbyAccount_v;
spool off

请查看上面的查询并帮助我。提前致谢！！！

原文

I have written the sql by name cashload.txt on unix box and kept it on the following location on unix box:

exit |sqlplus -s batch/password@SW_TEST @/soft/checkfree/AccurateBXG/scripts/cashload.txt

In the cashload.txt the below code is written:

spool /Detail/reports/inner/SW/Rep_OIbyAccount_$DATE_FILE.csv
select accountnumber||','||accountname||','||X from HSBC_Cash_OIbyAccount_v;
spool off

But it is not spooling the result set on the above mentioned path.However,when I am giving the path where the script is kept,It is spooling at that location.I don't understand why?It is spooling at the below path where the cashload.txt(sql script) is kept:

**spool /soft/checkfree/AccurateNXG/scripts/Rep_OIbyAccount.csv**
select accountnumber||','||accountname||','||X from HSBC_Cash_OIbyAccount_v;
spool off

Please look into the above query and help me out.
Thanks in advance!!!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

缱倦旧时光 2024-08-09 01:50:20

如果我理解正确的话，DATE_FILE 是一个您想要替换为假脱机文件名的 shell 变量。这可能是您问题的一部分或全部。我认为您不能直接从 SQLPlus 引用它。您需要将其作为参数传递给脚本，然后将其作为 SQLPlus 替换变量引用。

因此，命令行将是：

sqlplus -s batch/password@SW_TEST @/soft/checkfree/AccurateBXG/scripts/cashload.txt $DATE_FILE

脚本中的 spool 命令将是：

spool /Detail/reports/inner/SW/Rep_OIbyAccount_&1..csv

（额外的句点是必要的，因为它充当替换变量名称的终止符。）

If I understand this correctly, DATE_FILE is a shell variable that you want to substitute into the spool file name. This might be part or all of your problem. I don't think you can reference this directly from SQLPlus. You would need to pass that in as a parameter to the script then reference it as a SQLPlus substitution variable.

So the command line would be:

sqlplus -s batch/password@SW_TEST @/soft/checkfree/AccurateBXG/scripts/cashload.txt $DATE_FILE

And the spool command in the script would be:

spool /Detail/reports/inner/SW/Rep_OIbyAccount_&1..csv

(The extra period is necessary because it serves as a terminator to the substition variable name.)

回复收藏 0 原文

那伤。 2024-08-09 01:50:20

几乎可以肯定这是文件权限问题。请记住，当我们从数据库内部与操作系统交互时，我们使用的是 oracle 帐户，而不是我们连接的帐户。

那么oracle帐户是否具有 /Detail/reports/inner 的写入权限？

回复收藏 0 原文

别念他 2024-08-09 01:50:20

您可以回显“/Detail/reports/inner/SW/Rep_OIbyAccount_$DATE_FILE.csv”的值吗？

我能想到的唯一原因是
a) 上述位置不是有效路径
b) 您没有写入该位置的权限。

在第一种情况下您是否会遇到错误，或者根本没有发生任何事情？

回复收藏 0 原文

锦爱 2024-08-09 01:50:20

不使用静默 (-s) 选项运行是否会提供更多详细信息？

回复收藏 0 原文

甜心 2024-08-09 01:50:20

您提到的路径有一个 $ 符号，这导致了问题。为了避免这种情况，只需在 " " 中给出路径和文件名。这应该可以解决问题：

SPOOL "/Detail/reports/inner/SW/Rep_OIbyAccount_$DATE_FILE.csv "

The path mentioned by you has a $ symbol which is causing the problem. To avoid that just give the path and file name in " ". That should solve the problem:

SPOOL "/Detail/reports/inner/SW/Rep_OIbyAccount_$DATE_FILE.csv "

回复收藏 0 原文

只是在用心讲痛 2024-08-09 01:50:20

也许您可以尝试以下操作，看看它是否会生成所需的输出。它将放入您当前所在的目录中。

set termout  off
set echo     off
set linesize 140
set feedback off
set pagesize 0

spool DATE_FILE.csv
select accountnumber
       ||','
       ||accountname
       ||','
       || X 
  from HSBC_Cash_OIbyAccount_v;
spool off
ed DATE_FILE.csv

Maybe you can try the following to see if it generats the desired output. It will put in the directory which you are currently in.

set termout  off
set echo     off
set linesize 140
set feedback off
set pagesize 0

spool DATE_FILE.csv
select accountnumber
       ||','
       ||accountname
       ||','
       || X 
  from HSBC_Cash_OIbyAccount_v;
spool off
ed DATE_FILE.csv

回复收藏 0 原文

~没有更多了~