存储过程执行模式名、权限

O

1、SP

CREATE OR REPLACE PROCEDURE DB."AAAPROCEDURE"

AS 
BEGIN 
   dbms_output.put_line(1);

END;

2、调用SP(有输出变量 )

VAR="`sqlplus -S  $USERNAME/$PASSWORD@$SERVER > ${SPLOGFILE} <<EOF
DECLARE 
FLAG INT;
begin
sapirdb.SAP_CO_ETL_00(2017080801,2017080801,FLAG);
end;
/
EOF`"

RetV=`tac ${SPLOGFILE}|sed -n '2p'|sed 's/^[[:space:]]*//;s/[[:space:]]*$//'`
echo RetV=$RetV
if [ "$RetV" != "PL/SQL procedure successfully completed." ]
then
  echo "SP return error"
  exit 1
fi

S

1、SP

create procedure ETL_USER.aaaprocedure
      @STR_DTE    INT,
      @END_DTE      INT
as
BEGIN 
  DECLARE  @PROCESS_ID   INT
END;

2、调用SP：

VAR="`isql -U$USERNAME -P$PASSWORD -S$SERVER -o $SPLOGFILE <<EOF

use $DB
go
declare @iReV int
declare @cReV char(1)
exec @iReV=$SPNAME $STR_DTE,$END_DTE
select @cReV=convert(char(1),@iReV)
print @cReV
go
quit
EOF`"

RetV=`cat ${SPLOGFILE}|sed -n '$p'|sed 's/^[[:space:]]*//;s/[[:space:]]*$//'`
echo RetV=$RetV
if [ "$RetV" != "0" ]
then
  echo "SP return error"
  exit 1
fi






drop procedure ETL_USER.aaaprocedure