处变不惊,处忧不虑,不争一年的兴衰,但看十年的功过。笑中千关过,愁内百事哀,挣胜是祸,繁华似梦,平淡是福。

  IT博客 :: 首页 :: 联系 :: 聚合  :: 管理
  65 Posts :: 1 Stories :: 24 Comments :: 0 Trackbacks
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;
只有注册用户登录后才能发表评论。