Java Based Stored Procedure In Oracle

Stored procedure is a pre-compiled collection of SQL and selective control language. Stored procedure is saved and dealt as a unit, then stored in database after being com piled. Users can runa stored procedure by its name plus so me arguments (if it has any). Stored procedure is a database object which saves plans in database server, so it runs faster. Any well designed database application should adopt stored procedure, for it makes the management of database, the display of database and users information easier. Gener ally speaking, therere such advantaged in stored procedur eas below: Standard module programming. Faster running speed. Low networks flux. A safety mechanism.All of the 4 type stored procedures in Oracle, namely, stored procedure, stored functions, database triggers and object type methods can be fulfilled in PL/SQL and Java. Programmers can only use PL/SQL to develop stored procedurebefore 0racles8i, after that, both PL/SQL and Java can be used. Stored procedure in Java is just like in PL/SQL, whichcan also run on Oracle server. The two can call each other; however, stored procedure in Java can make use of Java programming language to fulfill some tasks which is impossible in PL/SQL. Compared with PL/SQL, developing stored proce dures in Java has these advantages:1. Java programming language is good at arithmetic, it provides more calculation. If you are design a stored proce dure with complex calculation, JSP is your best choice.2. PL/SQL can only be used in Oracle, yet Java can beu sed in more database(such as Sybase, DB2, Informix), so stored procedure in Java is more compatible and transplantableWe can combine Java with existing PL/SQL program to de velop Oracle9i application, using Java programming language to visit SQL and PL/SQL. Stored procedure in Java and in SQL and PL/SQL run in the same address space, so they can in teractive seamlessly, which enables the calling of transact ion logic written in SQL or PL/SQL.This article from PL/SQL which often uses generally memory process simple analysis obtaining, proposed is chooses PL/SQL or the Java question. The author passes PL/SQL and the Java memory process compares simply, elaborated has used theJava memory process the merit and introduced Java in detail through the example memory process, And has given PL/through the chart of symbolsThe SQL memory process and do the Java memory process are transfer how mutually.Using Java Stored Procedure to communicate with SQL, XML, Java, J2EE and Web services, stored procedure caneffec tively separate durable logic in database from transaction logic in middle layer. The separation reduces complexity ofapplication and improves reusability, safety, performance and flexibility. But maindifficulty of the widely appliance of stored procedure is different languages which depend on each manufacturers database. Java based stored procedure can resolve the problem. Oracle has realized ANSI standar dwhich regulate the methods of calling static Javas proc edure from SQL. The realization is simply named Java storedprocedure.In this paper wealso analyze in detail the developing steps of stored procedure in Java, which includes coding in Java, loading source code and class to 0racle9i database , making calling description, calling procedures in Java and calling functions in Java.Wealso elaborate how to program functions and storedprocedures in Java, how to control and limit the access to Oracle database with stored procedure in Java. Users are only permitted to manage the database through stored procedures, which can only run under callers privilege and no access to the table.In all, the integration of Oracle and Java VM can create transplantable, powerful and database irrelevant data logic and durable logic. The separation of transaction logicsrun in middle layer and data logics run in database layer brings more extendibility, flexibility and maintainability of application.