Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL help with pulling an environment variable into a default

RE: SQL help with pulling an environment variable into a default

From: Brian MacLean <BMacLean_at_Homebid.com>
Date: Mon, 29 Jan 2001 16:17:47 -0700
Message-Id: <10756.127760@fatcity.com> 





This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.



------_=_NextPart_001_01C08A49.B1658500


Content-Type: text/plain;


        charset="iso-8859-1"



Hey Jeff;



c:\> set SVDIR=C:
c:\> sqlplus username/password_at_mydb
SQL> select * from test;





RPT_FILE_PATH





\alm\reports\



SQL> col v_svdir new_value nv_svdir


SQL> host echo select '%SVDIR%' v_svdir from dual; >work.sql
Command not success.


SQL> @work.sql



'C


--
C:

SQL> host del work.sql
Command not success.
SQL> select '&nv_svdir' || rpt_file_path from test;
old   1: select '&nv_svdir' || rpt_file_path from test
new   1: select 'C:' || rpt_file_path from test



'C:'||RPT_FILE_PATH

----------------------------------------------------
C:\alm\reports\

SQL>

There are ways to shut off all the clutter from the statements but I'll
leave that up to you to figure out.  

BOL

Brian P. Mac Lean
Senior Oracle Database Administrator
OCPv8/Oracle Master
HomeBid.Com
8700 N. Gainey Center Drive
Suite 150
Scottsdale, AZ  85258
Tel:480.609.4624
Net:brian.maclean_at_homebid.com
http://www.homebid.com










-----Original Message-----
From: Jeff Cox [mailto:jeff.cox_at_ips-sendero.com]
Sent: Thursday, January 25, 2001 9:46 AM
To: Multiple recipients of list ORACLE-L
Subject: SQL help with pulling an environment variable into a default
value


Hello SQL fanatics,

I am having no luck with trying what I wish to do (Oracle 7.3.4 on NT) and
cannot seem to find it in any documentation.  Hope somebody from this list
can either tell me how to do it or tell me that it cannot be done.

I am trying to add a column to the test table so the default value of the
column will pull the %SVDIR% environment variable (Which is a drive letter,
usually C: or D:) and then add '\FTP\' after the drive letter.


**** This is what I tried, and the output is NOT what I want ****
ALTER TABLE test
ADD (rpt_file_path VARCHAR2(256) DEFAULT '%SVDIR%\FTP\');

SVRMGR> SELECT rpt_file_path FROM test;
RPT_FILE_PATH
------------------------------------------------------------
%SVDIR%\FTP\


**** This IS the output that I desire (The SVDIR environment variable is set
to D:)****
SVRMGR> SELECT rpt_file_path FROM test;
RPT_FILE_PATH
------------------------------------------------------------
D:\FTP\



TIA,

Jeff Cox
IPS-Sendero
Scottsdale, AZ

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeff Cox
  INET: jeff.cox_at_ips-sendero.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C08A49.B1658500
Content-Type: text/html;
	charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12">
<TITLE>RE: SQL help with pulling an environment variable into a default value</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=2>Hey Jeff;</FONT>
</P>
<BR>

<P><FONT SIZE=2>c:\&gt; set SVDIR=C:</FONT>
<BR><FONT SIZE=2>c:\&gt; sqlplus username/password_at_mydb</FONT>
<BR><FONT SIZE=2>SQL&gt; select * from test;</FONT>
</P>

<P><FONT SIZE=2>RPT_FILE_PATH</FONT>
<BR><FONT SIZE=2>--------------------------------------------------</FONT>
<BR><FONT SIZE=2>\alm\reports\</FONT>
</P>

<P><FONT SIZE=2>SQL&gt; col v_svdir new_value nv_svdir</FONT>
<BR><FONT SIZE=2>SQL&gt; host echo select '%SVDIR%' v_svdir from dual; &gt;work.sql</FONT>
<BR><FONT SIZE=2>Command not success.</FONT>
<BR><FONT SIZE=2>SQL&gt; @work.sql</FONT>
</P>

<P><FONT SIZE=2>'C</FONT>
<BR><FONT SIZE=2>--</FONT>
<BR><FONT SIZE=2>C:</FONT>
</P>

<P><FONT SIZE=2>SQL&gt; host del work.sql</FONT>
<BR><FONT SIZE=2>Command not success.</FONT>
<BR><FONT SIZE=2>SQL&gt; select '&amp;nv_svdir' || rpt_file_path from test;</FONT>
<BR><FONT SIZE=2>old&nbsp;&nbsp; 1: select '&amp;nv_svdir' || rpt_file_path from test</FONT>
<BR><FONT SIZE=2>new&nbsp;&nbsp; 1: select 'C:' || rpt_file_path from test</FONT>
</P>

<P><FONT SIZE=2>'C:'||RPT_FILE_PATH</FONT>
<BR><FONT SIZE=2>----------------------------------------------------</FONT>
<BR><FONT SIZE=2>C:\alm\reports\</FONT>
</P>

<P><FONT SIZE=2>SQL&gt;</FONT>
</P>

<P><FONT SIZE=2>There are ways to shut off all the clutter from the statements but I'll leave that up to you to figure out.&nbsp; </FONT>
</P>

<P><FONT SIZE=2>BOL</FONT>
</P>

<P><FONT SIZE=2>Brian P. Mac Lean</FONT>
<BR><FONT SIZE=2>Senior Oracle Database Administrator</FONT>
<BR><FONT SIZE=2>OCPv8/Oracle Master</FONT>
<BR><FONT SIZE=2>HomeBid.Com</FONT>
<BR><FONT SIZE=2>8700 N. Gainey Center Drive</FONT>
<BR><FONT SIZE=2>Suite 150</FONT>
<BR><FONT SIZE=2>Scottsdale, AZ&nbsp; 85258</FONT>
<BR><FONT SIZE=2>Tel:480.609.4624</FONT>
<BR><FONT SIZE=2>Net:brian.maclean_at_homebid.com</FONT>
<BR><FONT SIZE=2>http://www.homebid.com</FONT>
</P>
<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
<BR>

<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Jeff Cox [<A HREF="mailto:jeff.cox_at_ips-sendero.com">mailto:jeff.cox_at_ips-sendero.com</A>]</FONT>
<BR><FONT SIZE=2>Sent: Thursday, January 25, 2001 9:46 AM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=2>Subject: SQL help with pulling an environment variable into a default</FONT>
<BR><FONT SIZE=2>value</FONT>
</P>
<BR>

<P><FONT SIZE=2>Hello SQL fanatics,</FONT>
</P>

<P><FONT SIZE=2>I am having no luck with trying what I wish to do (Oracle 7.3.4 on NT) and</FONT>
<BR><FONT SIZE=2>cannot seem to find it in any documentation.&nbsp; Hope somebody from this list</FONT>
<BR><FONT SIZE=2>can either tell me how to do it or tell me that it cannot be done.</FONT>
</P>

<P><FONT SIZE=2>I am trying to add a column to the test table so the default value of the</FONT>
<BR><FONT SIZE=2>column will pull the %SVDIR% environment variable (Which is a drive letter,</FONT>
<BR><FONT SIZE=2>usually C: or D:) and then add '\FTP\' after the drive letter.</FONT>
</P>
<BR>

<P><FONT SIZE=2>**** This is what I tried, and the output is NOT what I want ****</FONT>
<BR><FONT SIZE=2>ALTER TABLE test</FONT>
<BR><FONT SIZE=2>ADD (rpt_file_path VARCHAR2(256) DEFAULT '%SVDIR%\FTP\');</FONT>
</P>

<P><FONT SIZE=2>SVRMGR&gt; SELECT rpt_file_path FROM test;</FONT>
<BR><FONT SIZE=2>RPT_FILE_PATH</FONT>
<BR><FONT SIZE=2>------------------------------------------------------------</FONT>
<BR><FONT SIZE=2>%SVDIR%\FTP\</FONT>
</P>
<BR>

<P><FONT SIZE=2>**** This IS the output that I desire (The SVDIR environment variable is set</FONT>
<BR><FONT SIZE=2>to D:)****</FONT>
<BR><FONT SIZE=2>SVRMGR&gt; SELECT rpt_file_path FROM test;</FONT>
<BR><FONT SIZE=2>RPT_FILE_PATH</FONT>
<BR><FONT SIZE=2>------------------------------------------------------------</FONT>
<BR><FONT SIZE=2>D:\FTP\</FONT>
</P>
<BR>
<BR>

<P><FONT SIZE=2>TIA,</FONT>
</P>

<P><FONT SIZE=2>Jeff Cox</FONT>
<BR><FONT SIZE=2>IPS-Sendero</FONT>
<BR><FONT SIZE=2>Scottsdale, AZ</FONT>
</P>

<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Please see the official ORACLE-L FAQ: http://www.orafaq.com</FONT>
<BR><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: Jeff Cox</FONT>
<BR><FONT SIZE=2>&nbsp; INET: jeff.cox_at_ips-sendero.com</FONT>
</P>

<P><FONT SIZE=2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=2>San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet access / Mailing Lists</FONT>
<BR><FONT SIZE=2>--------------------------------------------------------------------</FONT>
<BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT>
<BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT>
<BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT>


Received on Mon Jan 29 2001 - 17:17:47 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US