在shell中执行pl/sql语句的方法？

lyrican

在shell里执行sql可以这样做：

sqlplus test/test@testDB<<!
select * from tab;
exit
!

但执行pl/sql的像这样不报错，但实现不了插入语句：

sqlplus test/test@testDB<<!
declare
    v_Department char(3):='CHN'; --Variable to hold the 3 character
                          --department code
    v_Course     number:=5;  --Variable to hold the course number
begin
    --Insert the course identifited by v_Department and v_Course
    --into the classes table in the database
    insert into classes (DEPARTMENT,COURSE)
        values(v_Department,v_Course);
end;
exit
!

却执行不了插入这条语句！

lyrican

怎么没人给个回复呢？：（＊）

heijude

把！
替换成
EOF
试试

lyrican

一样，

2-1.sh

sqlplus test/test@pltest<<EOF
declare
    v_Department char(3):='KOR'; --Variable to hold the 3 character
                          --department code
    v_Course     number:=105;  --Variable to hold the course number
begin
    --Insert the course identifited by v_Department and v_Course
    --into the classes table in the database
    insert into classes (DEPARTMENT,COURSE)
        values(v_Department,v_Course);
end;
exit
EOF




[oracle@bjcnc /]$ ./mnt/d/plsqlStudy/code/2-1.sh

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Aug 5 20:13:44 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>   2    3    4    5    6    7    8    9   10   11   12  Disconnected
from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

insert还是没有插进值

dradhzn

for your reference  

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
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

:em11:

winterroot

commit;

ZealeS

过程语句要用 / 作为结束符。
insert操作要加commit。。