oracle向shell输入参数,Shell向SQL传递参数

PURPOSE

-------

We often get question about how to pass a UNIX environment variable to PLSQL. 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

(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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/58054/viewspace-628655/，如需转载，请注明出处，否则将追究法律责任。