EBS OAF开发中调用已有的function和Procedure时对Boolean的处理

EBS OAF开发中调用已有的function和Procedure时对Boolean的处理

（版权声明，本人原创或者翻译的文章如需转载，如转载用于个人学习，请注明出处；否则请与本人联系，违者必究）

在OAF开发过程中，可能会经常遇到调用已有的Function和Procedure，但由于JDBC对PL/SQL中的Boolean类型不支持，我们不得不要做一些特别处理以便可以重用已有的Function和Procedure.

CREATE OR REPLACE PACKAGE OAF_TEST AS

procedure proWithOut1(isTest OUT boolean);
function  funWithOut1
return boolean;

END;

CREATE OR REPLACE PACKAGE BODY OAF_TEST AS
  procedure proWithOut1(isTest OUT boolean) IS
  BEGIN
      isTest := FALSE;
  END;

 function  funWithOut1  RETURN BOOLEAN IS 
  BEGIN 
   RETURN TRUE;
  END;

END;

1.如果是Function或者Procedure的传入/传出（IN/OUT）参数是布尔类型的话，我们只能写一个包装的Function或者Procedure(Wrapper).

2.如果是Function的返回值是布尔类型，那相对好处理一些.

  a.可以用Wrapper来包装一个新的function，这个和1的处理是一样子的

  b.可以在调用Function或者Procedure的时候，写一个复杂点的PL/SQL块来处理布尔类型

  c.可以调用数据库内置的Function sys.diutil.bool_to_int(),也可以写一个自己的Function来做类似的处理

修改后的Package如下

PROMPT CREATE OR REPLACE PACKAGE oaf_test
CREATE OR REPLACE PACKAGE oaf_test AS

procedure proWithOut1(isTest OUT boolean);
procedure proWithOut1Wrapper(isTest OUT Number);
function  funWithOut1
return boolean;

END;
/

PROMPT CREATE OR REPLACE PACKAGE BODY oaf_test
CREATE OR REPLACE PACKAGE BODY oaf_test AS
  procedure proWithOut1(isTest OUT boolean) IS
  BEGIN
      isTest := FALSE;
  END;

  procedure proWithOut1Wrapper(isTest OUT Number) IS
  x_temp BOOLEAN;
  BEGIN
      proWithOut1(x_temp);
      IF x_temp THEN
         isTest :=1;
      ELSE
         isTest :=0;
      END IF;
  END;


 function  funWithOut1  RETURN BOOLEAN IS
  BEGIN
   RETURN TRUE;
  END;

END;
/

相应的JAVA代码

      public static void testBooleanOfPLSQLInOAF(OADBTransaction tx) 
      {
        OracleCallableStatement stmt = null;
        Number result = null;
        String wrapper       = "begin OAF_TEST.proWithOut1Wrapper(:1); end;";
        String complexPLSQL  = "declare x_ret Boolean; x_temp number :=0; " +
                               "begin x_ret := OAF_TEST.funWithOut1; " +
                               "if x_ret then x_temp:=1; end if; :1:=x_temp; end;";
        String sysCall       = "begin :1:=sys.diutil.bool_to_int(OAF_TEST.funWithOut1); end;";
        try 
        {
          //To solve this with wrapper
          stmt = (OracleCallableStatement) tx.createCallableStatement(wrapper, 1);
          //set In parameters
          
          //register out parameters
          stmt.registerOutParameter(1, OracleTypes.NUMBER);
          stmt.execute();
          result = getNumber(stmt,1);
          System.out.println("wrapper result:"+result);
          stmt.close();
          
          //To solve this with complexPLSQL call
          stmt = (OracleCallableStatement) tx.createCallableStatement(complexPLSQL, 1);
          //set In parameters
          
          //register out parameters
          stmt.registerOutParameter(1, OracleTypes.NUMBER);
          stmt.execute();
          result = getNumber(stmt,1);
          System.out.println("complexPLSQL result:"+result);
          stmt.close();  

          //To solve this with ORACLE function
          stmt = (OracleCallableStatement) tx.createCallableStatement(sysCall, 1);
          //set In parameters
          
          //register out parameters
          stmt.registerOutParameter(1, OracleTypes.NUMBER);
          stmt.execute();
          result = getNumber(stmt,1);
          System.out.println("sysCall result:"+result);
          stmt.close(); 
        }
        catch (Exception e)
        {
           e.printStackTrace();
        }
        finally
        {
           try
           {
             if (stmt != null)
             {
               stmt.close();
             }
           }
           catch (SQLException sqlE)
           {
             if (tx.isLoggingEnabled(OAFwkConstants.EXCEPTION))
               tx.writeDiagnostics(null, "closeStmt: Exception : " + 
                                         sqlE.getMessage(), OAFwkConstants.EXCEPTION);
           }
        }
      } 

输出结果