Home » SQL & PL/SQL » SQL & PL/SQL » Passing variable in from select statement to host command?
Passing variable in from select statement to host command? [message #317116] Tue, 29 April 2008 10:35 Go to next message
bpeasey
Messages: 46
Registered: March 2005
Member
Hi,

I'm trying to dynamically update a host command in a .sql file. The host command runs an import session. Here's what I've tried:

declare 
DTM CHAR(6);
begin
SELECT TO_CHAR(trunc(SYSDATE), 'YYMMDD') INTO DTM FROM dual;
end;

@@Import.sql DTM
/
--contents of import.sql file
spool Import.OUT
prompt Import...

HOST imp *****/*****@***** fromuser=fpps touser=fpps file=..\PostSmoke.Export.&1.dmp log=FPPS.Import.&1.log
/
spool off;


DTM variable contains current date but when &1 get converted it is not the date but just 'DTM'.

Any suggestions greatly appreciated. Thx.

Brian
Re: Passing variable in from select statement to host command? [message #317122 is a reply to message #317116] Tue, 29 April 2008 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> @@Import.sql

--contents of import.sql file
col dtm new_value dtm 
SELECT TO_CHAR(trunc(SYSDATE), 'YYMMDD') DTM FROM dual
/
spool Import.OUT
prompt Import...

HOST imp *****/*****@***** fromuser=fpps touser=fpps file=..\PostSmoke.Export.&dtm.dmp log=FPPS.Import.&dtm.log
/
spool off;

Regards
Michel

[Updated on: Tue, 29 April 2008 11:00]

Report message to a moderator

Re: Passing variable in from select statement to host command? [message #317124 is a reply to message #317122] Tue, 29 April 2008 11:10 Go to previous message
bpeasey
Messages: 46
Registered: March 2005
Member
Thank you Michel Smile
Previous Topic: Trigger not working
Next Topic: ERROR PLS -00410 Duplicate fileds inRecord, Table or Argument list are not permitted
Goto Forum:
  


Current Time: Thu Dec 08 12:08:38 CST 2016

Total time taken to generate the page: 0.27870 seconds