Using JDBC to Execute Stored Procedures

      在使用PeopleCode调用存储过程之前，我们首先需要确认SYSADM有这个存储过程的执行权限，并且这个存储过程的同义词存在，如果不存在建议对这个存储过程创建一个PUBLIC的公共同义词，以方便调用，如下为PKG的代码示例

CREATE OR REPLACE PACKAGE PT_PRC_TEST AS

  PROCEDURE PRC1(
    IN1    IN     VARCHAR2,
    INOUT1 IN OUT VARCHAR2);
END PT_PRC_TEST;
/

CREATE OR REPLACE PACKAGE BODY PT_PRC_TEST AS
  PROCEDURE PRC1(
    IN1    IN     VARCHAR2,
    INOUT1 IN OUT VARCHAR2) IS
  BEGIN
    INOUT1 := 'Hello World';
  END P;
END PT_PRC_TEST;
/

CREATE PUBLIC SYNONYM PT_PRC_TEST FOR XXXXX.PT_PRC_TEST;
GRANT EXECUTE ON PT_PRC_TEST TO SYSADM;

如下代码展示了使用IScript 来调用数据库存储过程

Function IScript_OraclePrc()
   Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");;
   Local JavaObject &info = CreateJavaObject("java.util.Properties");
   &info.put("UserName", "dbuser");
   &info.put("password", "secret");
   Local JavaObject &conn = &driver.connect("jdbc:oracle:thin:@server:1521:SID", &info);
   Local JavaObject &stmt = &conn.prepareCall("{call PT_PRC_TEST.PRC1 (?,?)}");
   Local JavaObject &types = GetJavaClass("java.sql.Types");
   &stmt.setString(1, "aa");
   &stmt.setString(2, "bb");
   &stmt.registerOutParameter(2, &types.VARCHAR);
   &stmt.execute();
   %Response.SetContentType("text/plain");
   %Response.WriteLine("PT_PRC_TEST.PRC1 output: " | &stmt.getString(2));
   &conn.close();
End-Function;