shell怎么获取调用的oracle存储过程的输出参数

wsryyffs66

#!/bin/sh
sqlplus /nolog<< EOF
connect aa/aa@test

DECLARE
  IS_CITY_CODE VARCHAR2(200);
  OUT_AREA_CODE VARCHAR2(200);

BEGIN
  IS_CITY_CODE := '$1';
  OUT_AREA_CODE := NULL;

  TESTINDB.T_TEST_P ( IS_CITY_CODE, OUT_AREA_CODE );

  DBMS_OUTPUT.Put_Line('OUT_AREA_CODE = ' || OUT_AREA_CODE);

  DBMS_OUTPUT.Put_Line('');

  COMMIT;
END;
/
exit
EOF
echo "hello"
echo $OUT_AREA_CODE

CREATE OR REPLACE PROCEDURE t_test_p
(
    is_city_code     IN VARCHAR2,
    out_area_code    out varchar2
)

AS

vs_area_code  varchar2(10);
   
BEGIN

select area_code
into vs_area_code
from t_city_area_t
where city_code=is_city_code;

out_area_code:=vs_area_code;

-- DBMS_OUTPUT.Put_Line('OUT_AREA_CODE = ' || OUT_AREA_CODE);

insert into t_test_exec_t
values(vs_area_code);
commit;


END ;
/




echo $OUT_AREA_CODE 结果为空

waker

http://baike.baidu.com/view/1492468.htm