3 Replies Latest reply: Sep 5, 2012 8:08 AM by 959987 RSS

    Openscript response in SQL Call Procedure

    766878
      Hi,

      How do we get the response data from an SQL Call Procedure, in Openscript?

      The SQL Statement is "call pkg_aaa.aaa(?)".

      The Details view does not seem to give any results. The procedure is supposed to output a string.

      Regards,
      Axel

      Edited by: ao on 2011-jun-09 10:51
        • 1. Re: Openscript response in SQL Call Procedure
          766878
          Any suggestions on how to do this?

          We need to verify the reply from a stored procedure in Oracle-DB.
          • 2. Re: Openscript response in SQL Call Procedure
            766878
            Problem solved.

            I got it working by using the returned List<Object> in the code.
            • 3. Re: Openscript response in SQL Call Procedure
              959987
              Can you help, the solution please. Thanks.

              I have this script:
              -----------------
              utilities.getSQLService().callProcedure(null,"DESA3",
                                  "Begin\n BMEP_EXECUTA_VALIDACION(?,?,?);\n End;",
                                  utilities.parameters(SQLService.parameter("{{db.par1.10}}", SQLService.SQLParameterType.In),
                                            SQLService.parameter("{{db.par1.10}}", SQLService.SQLParameterType.In),
                                       SQLService.parameter("abc",SQLService.SQLParameterType.Out)))
              ----------------

              Error when executing:
              -----------------
              Results 05-09-2012 12:26:04 AM     2,007               Failed     Failed to execute SQL statement: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
              Caused by: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
                   
              Script BaseDeDatos1     2,007               Failed     Failed to execute SQL statement: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
              Caused by: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
                   
              Initialize BaseDeDatos1     0,507               Passed          
              DefineDatabase: DESA3     0,001               Passed          
              Parameterize SQL: Begin
              BMEP_EXECUTA_VALIDACION('20','20',?);
              End;     0,001               Passed          
              Run BaseDeDatos1 - Iteration 1     1,325               Failed     Failed to execute SQL statement: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
              Caused by: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
                   
              GetNextDatabankRecord: par1     0,002               Passed     Data Used:[20]      
              Connect to Database: 'DESA3'     0,787               Passed          
              Call Procedure DESA3: 'Begin
              BMEP_EXECUTA_VALIDACION(?,?,?);
              End;'     0,25               Failed     Failed to execute SQL statement: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
              Caused by: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
              Data Used:20      
              Finish BaseDeDatos1     0,053               Passed          
              End Script BaseDeDatos1     2,007               Failed     Failed to execute SQL statement: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
              Caused by: SQLException occured. ORA-06550: line 1, column 12:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare exit for goto if loop mod null pragma
              raise return select update while with <an identifier>
              <a double-quoted delimited-identifier> <a bind variable> <<
              close current delete fetch lock insert open rollback
              savepoint set sql execute commit forall merge pipe
              The symbol "" was ignored.
              ORA-06550: line 2, column 36:
              PLS-00103: Encountered the symbol "" when expecting one of the following:

              begin case declare end exception exit for goto if loop mod
              null pragma raise return select update while with
              <an identifier> <a double-quoted delimited-id
                   
              -----------------

              I try, but :(

              Edited by: user2810993 on 05-sep-2012 6:03

              Edited by: user2810993 on 05-sep-2012 6:07

              Edited by: user2810993 on 05-sep-2012 6:07