This discussion is archived
2 Replies Latest reply: Nov 6, 2013 7:22 PM by Hjava RSS

what the following code try to do?

Hjava Newbie
Currently Being Moderated

can any one explain the following code for me? I only know the part that I write down after the code.

 

 

sql = "{ ? = call tola_import.import_record(?) }"; 

// call a package , what is the two question mark?

 

  stage = "10"; 

what stage standard for, and not ideal why sometimes past parameter in and sometimes only parameter out !

  cstmt = conn.prepareCall(sql);

  stage = "20";

  cstmt.registerOutParameter(1, java.sql.Types.INTEGER);

  stage = "30";

  cstmt.setString(2, library.getString(req, "pUSERNAME")); // past parameter in

  stage = "40";

  cstmt.execute();

  stage = "50";

  int returnValue = cstmt.getInt(1);

  • 1. Re: what the following code try to do?
    rp0428 Guru
    Currently Being Moderated

    Hjava wrote:

     

    can any one explain the following code for me? I only know the part that I write down after the code.

     

     

    sql = "{ ? = call tola_import.import_record(?) }";

    // call a package , what is the two question mark?

     

      stage = "10";

    what stage standard for, and not ideal why sometimes past parameter in and sometimes only parameter out !

      cstmt = conn.prepareCall(sql);

      stage = "20";

      cstmt.registerOutParameter(1, java.sql.Types.INTEGER);

      stage = "30";

      cstmt.setString(2, library.getString(req, "pUSERNAME")); // past parameter in

      stage = "40";

      cstmt.execute();

      stage = "50";

      int returnValue = cstmt.getInt(1);

    See 'PL/SQL Stored Procedures' in the JDBC Dev guide

    http://docs.oracle.com/cd/B28359_01/java.111/b31224/getsta.htm#sthref81

    PL/SQL Stored Procedures

    Oracle JDBC drivers support the processing of PL/SQL stored procedures and anonymous blocks. They support PL/SQL block syntax and most of SQL92 escape syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

    // SQL92 syntax  CallableStatement cs1 = conn.prepareCall  ( "{call proc (?,?)}" ) ; // stored proc  CallableStatement cs2 = conn.prepareCall  ( "{? = call func (?,?)}" ) ; // stored func  

    The code is calling a database function named IMPORT_RECORD. That function takes one string parameter (the second question mark) and returns a number (the first question mark).

     

    The question mark (?) indicates a BIND variable, or placeholder, for an IN or OUT parameter.

     

    The 'stage' variable appears to be set before each statement so that if an exceptin occurs the exception handler can provide the value of 'stage' to indicate the LAST statement that executed successfully. So if, in the exception handler, 'stage' has a value of 30 it would mean that this statement:

      cstmt.setString(2, library.getString(req, "pUSERNAME")); // past parameter in

    caused the exception because if that statement had executed successfully the next line of code would have set 'stage' to 40 but that didn't happen.

  • 2. Re: what the following code try to do?
    Hjava Newbie
    Currently Being Moderated

    thanks  for the reply,

    so the purpose of stage is for debug,  catch exception ! also I realize inside the function , also has stage. and it also show on the exception

Legend

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