oracle 将 sqlplus 变量的值从一个脚本传递到另一个脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2641312/
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
passing value of sqlplus variable from one script to another
提问by FrustratedWithFormsDesigner
I have a script that gets the current time, and must pass it to another script.
我有一个获取当前时间的脚本，并且必须将它传递给另一个脚本。
variable v_s_time varchar2(30);
exec :v_s_time := to_char(sysdate,'YYYY-MM-DD HH:MI:SS AM');
--Lots of unrelated code here
variable v_e_time varchar2(30);
exec :v_e_time := to_char(sysdate,'YYYY-MM-DD HH:MI:SS AM');
@"test report script.sql" :v_s_time :v_e_time; --yes, I also tried ":v_s_time", didn't seem to do anything.
This does not work, it seems that the literal :v_s_timeis passed to the script, instead of what I want: "2010-04-14 05:50:01 PM".
这不起作用，似乎文字:v_s_time被传递给脚本，而不是我想要的："2010-04-14 05:50:01 PM"。
To execute this manually, I could enter:
要手动执行此操作，我可以输入：
@"test report script.sql" "2010-04-14 05:50:01 PM" "2010-04-14 05:57:34 PM"
I found that what does work is this:
我发现有效的是这样的：
define v_s_time = "2010-04-14 05:50:01 PM"
--Lots of unrelated code here
define v_e_time = "2010-04-14 05:57:34 PM"
@"test report script.sql" "&&v_s_time" "&&v_e_time";
But it is unrealistic to hardcode the datetimes. Anyone know how to handle this?
但是对日期时间进行硬编码是不现实的。有谁知道如何处理这个？
(Oracle 10g)
（甲骨文 10g）
回答by Vincent Malgrat
You can use the NEW_VALUE clause of the COL commandto dynamically retrieve a value into a substitute variable:
您可以使用COL 命令的NEW_VALUE 子句将值动态检索到替代变量中：
SQL> /*Declare that the "dat" column will be stored in the v_date variable*/
SQL> COL dat NEW_VALUE v_date
SQL> SELECT to_char(sysdate,'YYYY-MM-DD HH:MI:SS AM') dat FROM dual;
DAT
----------------------
2010-04-15 09:54:29 AM
SQL> select '&&v_date' from dual;
'2010-04-1509:54:29AM'
----------------------
2010-04-15 09:54:29 AM
You can then call your script with this substitute variable:
然后，您可以使用此替代变量调用您的脚本：
@"test report script.sql" &&v_date
回答by DCookie
Can you just reference the bind variables in the "test report script.sql" script? In other words, in "test report script.sql", you have a direct reference to v_s_time and v_e_time, skipping the SQLPlus define variable. There doesn't appear to be any really elegant way to convert from bind variables to SQLPlus DEFINE'd variables.
您可以在“测试报告脚本.sql”脚本中引用绑定变量吗？换句话说，在“测试报告脚本.sql”中，您可以直接引用 v_s_time 和 v_e_time，跳过 SQL Plus 定义变量。似乎没有任何真正优雅的方法可以将绑定变量转换为 SQLPlus DEFINE 变量。
