HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

"PL/SQL"
Volume: 4 Issue: 8, p. 68

	

Listing 1: 

  add(F_NAME IN VARCHAR2, 
      ....  -- Other things like l_name, address etc. 
      PHONE  IN VARCHAR2, 
      RESULT_VAL OUT NUMBER) IS 
      Temp_Result NUMBER; 
      Temp_Seq    NUMBER; 
  BEGIN 
      /* Temp_Result is a temporary variable that 
         indicates to the calling Java application 
         whether the user information was recorded 
         successfully or not. 1 indicates success */ 
      Temp_Result := 1; 

      /* Create a unique Id for this user */ 
      SELECT Person_Seq.NEXTVAL FROM Dual INTO Temp_Seq; 

      /* We insert name of the person along with the ID 
         generated above by calling the add() procedure 
         in the Person_Pkg package created above */ 
      Person_Pkg.add(F_NAME, L_NAME, ID); 

      /* Similarly the address is added. The ID this 
         time goes into the Person_Id field of the 
         Address table */ 
      Address_Pkg.add(LINE_1, ...... ID); 

      /* We need to add the phone and e-mail as 2 
         separate records */ 
      Telecom_Address_Pkg.add(PHONE, 'PHONE', ID); 
      Telecom_Address_Pkg.add(EMAIL, 'EMAIL', ID); 

      /* If an exception occurs, result is set to 
         0 so the Java application can treat it 
         accordingly. Note that you can add similar 
         exception handlers in the other packages 
         also to have better control over generation 
         of error messages.*/ 
      EXCEPTION 
          WHEN OTHERS THEN 
             Temp_Result := 0; 
  END add; 

Listing 2: 

/*The OAS comes with a number of packages that allow 
you to decode the FORM variables as well as generate HTML. 
The "rdbms" package allows you to establish a Session with 
the database for invoking PL/SQL procedures/functions. */ 

import oracle.rdbms.*; 
import oracle.plsql.*; 
import oracle.html.*; 
public class Register 
{ 

/*We’re declaring a variable ‘s’ of the type Session - 
here Session is a class that comes in the rdbms package. 
It represents the connection we’re establishing with the 
database. We’re declaring this as a global ‘static’ variable 
so that the same Session can be used by multiple requests, 
increasing program efficiency. */ 

     static Session s = null; 
     public static void main(String[] args) 
     { 
          Register r = new Register(); 
          r.start(); 
     } 
     public void start() 
     { 

/* Declare a variable of HTTP.class - a class that comes 
as part of the OAS and can be used within Java applications 
to get information about the current HTTP request such as FORM 
variables */ 

     HTTP http; 
     ... 
     String fName = http.getURLParameter("fName); 

/* Similarly, get all other parameters such as lName, etc. 
Check if values are valid by calling a method value sAreValid 
 (not shown). You could check things like whether the e-mail 
entered by the user has a missing '@' and other things like 
this. */ 

          if(valuesAreValid(fName, lName ........)) 
          { 
             try{ 
                writeToDB(fName, lName ......); 
             }catch(Exception e){} 
          } 
          else 
          { 
                 errorHandler(); 
          } 
     } 
     void writeToDB(String fName, ......) 
          throws ServerException 
     { 

// Before we create a Session, we need to inform it of the 
ORACLE_HOME. 

         Session.setProperty("ORACLE_HOME", OracleHome); 

/* Check if there is a session already established with the 
database. If so, use that Session else create a new Session */ 

         if(s == null) 
              s = new Session("userName", 
                         "password", "connectString"); 

/* As we can see here, if a session has already been established 
in this instance, the same session can be used by other HTTP 
 requests handled by this instance. Since a good deal of time 
is established in creating connections to the database, we 
have created an optimization that will ensure the session 
need not be reestablished for every new request. */ 

/* Reg_Api is the class generated by pl2java */ 

         Reg_Api ra; 

/* Create an instance of Reg_Api and pass the instance of 
the session created above so the class knows which database/schema 
it needs to work against */ 

         ra = new Reg_Api(s); 

/* Since there are no datatypes in Java that map directly to 
PL/SQL datatypes, Oracle has provided mapping classes in the 
plsql package. For a VARCHAR2, you need to convert your String 
to a PStringBuffer() before calling a PL/SQL procedure that 
requires a VARCHAR2 as input. Similarly for NUMBER, you must 
create a PDouble. Likewise there are equivalent datatypes for 
most PL/SQL datatypes. */ 

         PStringBuffer pFName = new PStringBuffer(fName); 

// Similarly PStringBuffers for all variables to be passed 
to add() in Reg_Api 

         ... 

/* The result, however, is a NUMBER type, so we create a PDouble */ 

         PDouble result = new PDouble(1); 

/* Now invoke the procedure as if you were calling a normal 
 PL/SQL procedure */ 

         ra.add(pFName, pLName, ....pPhone, ... result); 

/* result will now contain the value stored in this variable 
by the add() procedure. We need to examine this. Before that 
we convert it to an int by calling a method in PDouble */ 

         if(ra.intValue() == 0) 
         { 
              errorHandler(); 

              /* Rollback this transaction as it failed */ 
              s.rollback(); 
         } 
         else 
         { 
             /* Commit this transaction */ 
             s.commit(); 
             showThankYouPage(); 
         } 
     } 
} 



 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.