Re: Pass a PL/SQL variable into SQL DEFINE

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 30 Apr 2002 18:00:51 -0700
Message-ID: <aaneo30mi5@drn.newsguy.com>

In article <a2bc9497.0204301614.63b73608_at_posting.google.com>, yuanjeff_at_yahoo.com says...
>
>I want to pass variable wkday from a PL/SQL function into a SQL
>variavle, but can not make it work. I need the result 'dir =
>h:\Backup\WED'. Do you know how to do it?
>Thanks a lot.
>-------------------------------------
>variable wkday varchar2(3)
>exec :wkday := weekday;
>print wkday
>
>define kk = :wkday
>
>define dir = 'h:\Backup\&kk'
>prompt &dir
>
>---------------------------------
>The function is
>create or replace FUNCTION weekday return varchar2 IS
>wkday varchar2(3);
>BEGIN
> select to_char(sysdate, 'DY') into wkday from dual;
>return wkday;
>END;
You are making this waaayyy harder then it should be:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> column dir new_value dir

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select 'h:\backup\' || to_char(sysdate,'dy') dir from dual;

DIR

---

h:\backup\tue

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> prompt &dir h:\backup\tue

that is all you need to do. You can "set termout off" "set termout on" before/after the select to surpress it's display if you like.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Apr 30 2002 - 20:00:51 CDT