shell&procedure variable in/out

将shell变量传递到oracle procedure并将procedure结果输出到shell变量。
$ sqlplus scott/tiger

SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 7月 3 15:07:21 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

ERROR:
ORA-28000: ??????

请输入用户名:  system
请输入口令:

连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace procedure test_sh_in(var_in varchar2)
  2  as
  3  begin
  4  dbms_output.put_line(var_in);
  5  end;
  6  /

过程已创建。

SQL> exit;
从 Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -

Production
With the Partitioning, OLAP and Data Mining options 断开

~/new_folder
$ str="hello"
~/new_folder
$ result=`sqlplus -s system/oracle << eof
set serveroutput on
exec test_sh_in('$str');
exit;
eof`

~/new_folder
$ echo $result
hello PL/SQL 过程已成功完成。

~/new_folder
$ result=`sqlplus -s system/oracle << eof
set serveroutput on
set feedback off # remove the feedback "PL/SQL 过程已成功完成。"
> exec test_sh_in('$str');
> exit;
> eof`

~/new_folder
$ echo $result
hello

~/new_folder
$

###########################################################
#about out parameter to shell
SQL> ed
已写入 file afiedt.buf

  1  create or replace procedure test_out_var(var_in varchar2,var_out

out varcha
r2)
  2  as
  3  begin
  4  var_out:=var_in;
  5* end;
SQL> /
SQL> declare
  2  var_out varchar2(100);
  3  begin
  4  test_out_var('china',var_out);
  5  end;
  6  /
SQL> set serveroutput on
SQL> /
SQL> ed
已写入 file afiedt.buf

  1  declare
  2  var_out varchar2(100);
  3  begin
  4  test_out_var('china',var_out);
  5  dbms_output.put_line(var_out);
  6* end;
SQL> /
china

~/new_folder
$ str="china"

~/new_folder
$ result=`sqlplus -s << eof
set serveroutput on
set feedback off
declare
var_out varchar2(100);
begin
test_out_var('$str',var_out);
dbms_output.put_line(var_out);
end;
/
exit;
eof`

~/new_folder
$ echo $result
china