Passing the sqlplus variable value from one script to another
I have a script that gets the current time and has to 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.
It does not work, it seems that the letter :v_s_time
is transmitted script instead of what I want: "2010-04-14 05:50:01 PM"
.
To do this manually, I can enter:
@"test report script.sql" "2010-04-14 05:50:01 PM" "2010-04-14 05:57:34 PM"
I found this to work:
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's unrealistic to hardcode dates. Does anyone know how to handle this?
(Oracle 10g)
a source to share
You can use the NEW_VALUE clause of the COL command to dynamically retrieve a value into a replacement variable:
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
Then you can call the script with this replacement variable:
@"test report script.sql" &&v_date
a source to share
Can you just reference the bind variables in the "test report script.sql" script? In other words, in the "script.sql test report" you have a direct link to v_s_time and v_e_time, omitting the SQLPlus definition variable. There doesn't seem to be a really elegant way to convert from binding variables to SQLPlus DEFINE variables.
a source to share