PURPOSE
-------
We often get question about how to pass a UNIX environment variable to PL/SQL.
Basically, how to set a variable value in a UNIX, and somehow run PL/SQL code which
recognizes the environemnt variable and its value.
SCOPE & APPLICATION
-------------------
These documented is directed to anyone who want to implement a fast
workaround to passing environment variables to PLSQL.
How to pass an UNIX environemnt variable value to a PLSQL block?
-----------------------------------------------------------------
You want to set a variable value in a UNIX shell script, invoke SQL*Plus
from within the same script, and run the PL/SQL procedure, function or anonymous block, passing
in the value for that UNIX environemnt variable.
The only way to communicate to the outside from PLSQL is using JAVA STORED PROCEDURES
(Java) or alternatively External Procedures(C). But these solution will not be effective
since in both cases, the only environement variables settings visible are those owned by
the server unix user The environement variable that are set are those under the Oracle
user when the server was started.
Consequently, Here is a simple UNIX workaround to executing PLSQL code and pass in an
environement variable from a UNIX script. The script passes the environment
variable to the sql script by simply doing a search and replace before the script is executed.
(1) SET YOUR ENVIRONMENT VARIABLE
/u02/home/usupport> setenv MYENVVAR "'somevalue'"
/u02/home/usupport> echo $SHELL
/usr/bin/csh
(2) CREATE A SQL SCRIPT THAT CALLS SQLPLUS AND THE PLSQL PROCEDURE
sqlplus scott/tiger <<EOF
execute myproc(1,$MYENVVAR);
EOF
(3) login to sqlplus and create a procedure that inserts the data passed in as
a parameter from the UNIX shell to this script. The parameter passed will be
inserted in a table called testit. My procedure is called myproc as specified
in the UNIX script.
SQL>create table testit(id NUMBER, val VARCHAR2(20));
Table created
SQL>
1 create or replace procedure myproc (id NUMBER, val VARCHAR2)
2 is
3 BEGIN
4 insert into testit
5 values(id, val);
6 commit;
7* END;
8 /
Procedure created.
SQL> select * from testit;
no rows selected
(4) Run the script
/u02/home/usupport/dggriffi> ./testit
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Aug 28 09:11:28 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
SQL>
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 -
Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
(5) Lets see if the value is in the table .....
/u02/home/usupport/dggriffi> sqlplus scott/tiger
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Aug 28 09:13:29 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
SQL> select * from testit;
ID VAL
---------- --------------------
1 somevalue
note:
/作为结束符
insert后要用commit;