This discussion is archived
3 Replies Latest reply: Sep 5, 2012 6:08 AM by 959987 RSS

Openscript response in SQL Call Procedure

766878 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Problem solved.

    I got it working by using the returned List<Object> in the code.
  • 3. Re: Openscript response in SQL Call Procedure
    959987 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points