2 Replies Latest reply: Oct 15, 2012 10:06 AM by 886772 RSS

    OLT openScript CallProcedure - accessing the OUT variables in List<Object>

    swa_jacfin
      I am testing a stored procedure with the generated openscript code auto generated out of the Tree View as follows:


           public void run() throws Exception {
           getDatabank("invoice_hist").getNextDatabankRecord();
           utilities.getSQLService().callProcedure("db", "DECLARE \r\n C_RESULT SYS_REFCURSOR;\r\n RESULT_CODE NUMBER;\r\n RESULT_MESSAGE VARCHAR2(32767);\r\nBEGIN \r\n{ CALL GETINVOICEHISTORY ( ?, ?, ?, C_RESULT, RESULT_CODE, *?* );}\r\nEND; ",
                          utilities.parameters(SQLService.parameter("{{db.invoice_hist.BAN}}",SQLService.SQLParameterType.In),
      SQLService.parameter(toDate("{{db.invoice_hist.Start}}"),SQLService.SQLParameterType.In),
      SQLService.parameter(toDate("{{db.invoice_hist.End}}"),SQLService.SQLParameterType.In),
      SQLService.parameter("RESULT_MESSAGE", SQLService.SQLParameterType.Out))
                );
           }

      The last 3 parameters are OUT parameters (sys_refcursor, number, string respectively). I am trying to access the last one, RESULT_MESSAGE and print it out to the console. In the stored procedure call, I have replaced this with a ? and then set up an OUT parameter with value "RESULT_MESSAGE". The documentation gives no hint on what to see the value to for a Out parameter

      The documentation is vague on all of this and the examples don't cover it. I see people on Google referring to a List<Object> but I cannot find any examples on how to access this. I am not a strong java coder

      Other than that, it all works quite well.

      Stuart
        • 1. Re: OLT openScript CallProcedure - accessing the OUT variables in List<Object>
          swa_jacfin
          OK. I worked it out for the simple string and integer being returned. Anybody have a clue on how to access the sys_refcursor being returned? How do I define it in the parameter list and then access it?

          public void run() throws Exception {
               getDatabank("invoice_hist").getNextDatabankRecord();
               List<Object> MyList = utilities.getSQLService().callProcedure("db", "DECLARE C_RESULT SYS_REFCURSOR;\r\n RESULT_CODE NUMBER;\r\n RESULT_MESSAGE VARCHAR2(32767);\r\n BEGIN \r\n{ CALL SELFCARE.GETINVOICEHISTORY ( ?, ?, ?, C_RESULT, ?, ? );}\r\nEND; ",
                              utilities.parameters(
                                   SQLService.parameter("{{db.invoice_hist.BAN}}",SQLService.SQLParameterType.In),
                                        SQLService.parameter(toDate("{{db.invoice_hist.Start}}"),SQLService.SQLParameterType.In),
                                        SQLService.parameter(toDate("{{db.invoice_hist.End}}"),SQLService.SQLParameterType.In), ,
                                        SQLService.parameter("RESULT_CODE", SQLService.SQLParameterType.Out),
                                        SQLService.parameter("RESULT_MESSAGE", SQLService.SQLParameterType.Out)
                              )
                    );
               info((String)MyList.get(0));
               info((String)MyList.get(1));
               }
          • 2. Re: OLT openScript CallProcedure - accessing the OUT variables in List<Object>
            886772
            I've also failed use OpenScript functionality to call a PL/SQL function returning a CURSOR variable. An alternative route is to use JDBC for calling a stored procedure. Here's an example

            String query = "{ call ? := PKI_FRAMEWORK.TestProc(aParam1 => ?, aParam2 => ?) }";

            +// Before the call however you would need to perform certaing steps:+
            +// set a connection to the database (using JDBC)+
            CallableStatement stmt = getConnection().prepareCall(query);

            +// set an OUT parameter of a corresponding type+
            stmt.registerOutParameter(1, OracleTypes.CURSOR);

            +// set IN parameters of corresponding types+
            stmt.setInt(2, aParam1);
            stmt.setString(3, aParam2);

            // execute the statement and get the resultant CURSOR
            stmt.execute();
            ResultSet rs = (ResultSet) stmt.getObject(1);