Skip to Main Content
  • Questions
  • How to use environment variables inside of a pl/sql block.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sharon.

Asked: February 02, 2001 - 5:55 pm UTC

Last updated: March 10, 2021 - 9:11 am UTC

Version: oracle 8.1.6.00 for Win NT

Viewed 10K+ times! This question is

You Asked

- platform = oracle 8.1.6 installed on Windows 98

I have a master script (script1.cmd) that sets a bunch of environment variables and then calls an sql script(script2.sql).
Inside of script2.sql, there are pl/sql blocks which I
would like to use the ENVIRONMENT variables in. How would I
do that?

EG. script1.cmd contents:
=========================
setlocal

set ORACLE_SID=TESTDB
set ORACLE_HOME=C:\ORACLE\ORA81
set KEYWORD=VALID_HOTBKUP_19990131
set SYS_PASS=TEST/TESTPW
start %ORACLE_HOME%\bin\sqlplus %SYS_PASS% @C:\TESTSCRIPTS\script2.sql

endlocal

EG. script2.sql contents:
=========================
-- called from script1.cmd
-- purpose - to generate a sql script with all the
-- 'alter tablespace <tbs name> begin backup;' commands.

-- Inside of this script, I have a spool <filename> and a
-- spool off command.
-- As part of the spool file name, I would like to use
-- the environment variable (KEYWORD=VALID_HOTBKUP_19990131)
-- that I had set in the calling program, script1.cmd.

set serveroutput on size 100000
set trimspool on
set line 500
set head off
set feed off

--BUILD 'ALTER TABLESPACE ... BEGIN BACKUP' CMD SCRIPT

spool D:\TESTSCRIPTS\%KEYWORD%_begin_bkup.sql

declare
dbname varchar2(30);
begin
select name
into dbname
from sys.v_$database;

-- *** this is the spool file name that I would like to
-- *** incorporate the KEYWORD environment variable into
-- *** I thought that I could reference the KEYWORD env
-- *** variable using %KEYWORD%

dbms_output.put_line('spool '||%KEYWORD%||dbname||'.log');

-- Loop through tablespaces
for c1 in (select tablespace_name ts
from sys.dba_tablespaces)
loop
dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
end loop;
dbms_output.put_line('spool off');
end;
/
spool off

=========================================================
=========================================================

When the script is run, the following files is created:

D:\TESTSCRIPTS\VALID_HOTBKUP_19990131_begin_bkup.sql
======================

however the contents of this script, generated within the
pl/sql block, the %KEYWORD% is not interpreted as the value
of the KEYWORD environment variable, I was hoping to see
the following contents:

- this line

dbms_output.put_line('spool '||%KEYWORD%||dbname||'.log');

- would be resolved to

spool VALID_HOTBKUP_19990131TESTDB.log
======================

but this did now work.

Any help on this matter would be welcome.

Thanks in advance.



and Tom said...

It is quite impossible.


The environment you set up is perhaps visible to your SQLPLUS running on YOUR client however the plsql block is being executed by a dedicated server/shared server running in a totally different process space, typically on a different machine and DEFINITELY with a totally different environment.

that and SQLPlus has no mechanism for reading the environment anyway.


You would pass these IN as parameters, not as environment variables. For example:

set ORACLE_SID=TESTDB
set ORACLE_HOME=C:\ORACLE\ORA81
set KEYWORD=VALID_HOTBKUP_19990131
set SYS_PASS=TEST/TESTPW

start %ORACLE_HOME%\bin\sqlplus %SYS_PASS%
@C:\TESTSCRIPTS\script2.sql %ORACLE_SID% %ORACLE_HOME% %KEYWORD%



and then script2.sql would have:

----------------------------------
define ORACLE_SID=&1
define ORACLE_HOME=&2
define KEYWORD=&3


...
dbms_output.put_line('spool &&KEYWORD..log');
.......

----------------------------------

in it...


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Is it possible to use Java

Dilip, January 28, 2002 - 11:48 am UTC

DB: 8.1.7.0.0 on UNIX

Would it be possible to use a Java stored procedure (including pl/sql wrapper) to get the value of an environment variable from within a PL/SQL block ?

I'm thinking along the lines of the Java code that Tom gave for running o/s commands from PL/SQL.

Tom Kyte
January 28, 2002 - 12:26 pm UTC

If you can write the Java to get it -- yes. However, the same exact caveat spelled out in my question:

..
The environment you set up is perhaps visible to your SQLPLUS running on YOUR
client however the plsql block is being executed by a dedicated server/shared
server running in a totally different process space, typically on a different
machine and DEFINITELY with a totally different environment.
....

would apply. The java is running on the SERVER - not on the client. Hence, it would have access to the SERVERS environment -- it would be a totally different environment.

User defined "serveroutput" like variable

Maverick, November 15, 2006 - 10:05 am UTC

Tom, I am trying to define a variable [like serveroutput] so that if it's set on, then my application is going to log errors in table , else it's not.
eg: "set Logoutput on"
If this is set ,then i can my procedures are going to log in Error Log table otherwise, they won't.

Can I create something like that? I am using Oracle 10g.



Tom Kyte
November 16, 2006 - 3:02 am UTC

create or replace package my_globals
as
g_log_stuff boolean default false;
end;
/


then your applications would:


begin my_globals.g_log_stuff := true; end;


and your log code would:

if ( my_globals.g_log_stuff )
then
log it
end if;


Serveroutput

Maverick, November 16, 2006 - 4:40 pm UTC

Thanks Tom. That was very helpful. But I am interested in knowing logic behind serveroutput,define etc..Variables.

Can you throw some light on that?

Tom Kyte
November 16, 2006 - 4:50 pm UTC

define is a sqlplus thing, sqlplus is a scripting/command line environment, it responds to "define"

nothing else does, define is just something sqlplus does, sort of like:

export X=something
echo $

in shell,

define X=something
select '&X' from dual;


in sqlplus.


set serverout on is shorthand for:

exec dbms_output.enable


it just sets a global variable in a package.

Shorthand??

Maverick, November 16, 2006 - 6:12 pm UTC

"
set serverout on is shorthand for:

exec dbms_output.enable
"

Tom, So, How can one create shorthands like this?
I am using package you mentioned above [and added couple of procedures ->Set and Reset] and I can call
exec mypkg.set , but How to create shorthand for that?

Just curious ..

Tom Kyte
November 17, 2006 - 3:14 am UTC

you would write your own sqlplus.

"set" is a sqlplus command, that is all.

Grabbing DOS Variable in SQLPlus

A reader, March 11, 2020 - 8:43 pm UTC

SQL> host echo define os_string=%OS% > dumb.sql
SQL> @dump
SQL> define os_string
DEFINE OS_STRING = "Windows_NT" (CHAR)
SQL>
Chris Saxon
March 12, 2020 - 10:39 am UTC

Nice! Thanks for sharing.

How to pass OS variables in .SQL file?

Maulik, March 10, 2021 - 9:03 am UTC

Hi can set below parameters in .sql file?

export NLS_LANG=AMERICAN_AMERICA.UTF8
export ORA_NCHAR_LITERAL_REPLACE=true
export LC_CTYPE="en_US.utf8"
Chris Saxon
March 10, 2021 - 9:11 am UTC

Those are environment variables, you don't pass them to your SQL file; you set them in the script that calls this file.

If you need to reference these values in the script for some reason, you need to pass them as parameters as shown in the original answer.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.