Oracle Database Programming in Linux

1. Introduction

Since PL/SQL cannot be used to develop applications for common users, you must use other languages or development tools. In Linux, what language or development tool should I use to develop Oracle database applications? This article introduces two solutions: Pro * C and JDBC. Another SQLJ method is omitted due to space limitations.

2. Environment Settings

2.1 software environment

The experiment environment in this article is in the Red Hat Linux 8.0 operating system. Because Oracle 9i requires at least MB of memory, we chose Oracle 8.1.7.0.1 as the database environment. Note: The typical installation of Oracle 8i does not include Pro * C. You need to select Pro * C in the development tool under the customized installation method. We chose to install JDK in J2SE 1.4.1 For Linux. In fact, JDK 1.1.6 and later versions can all be used.

2.2 Oracle user's environment variable settings

For convenience, all operations in this article are performed by logging on to Oracle users. The. bash_profile file of an Oracle user must contain the following lines:

# JAVA environment settings Export CLASSPATH =.:/usr/local/java/lib:/usr/local/jre/lib Export JAVA_HOME =/usr/local/java # Oracle database environment settings Export ORACLE_BASE =/home/oracle Export ORACLE_HOME = $ ORACLE_BASE Export ORACLE_SID = MyDB Export ORACLE_TERM = vt100 Export ORACLE_OWNER = oracle Export TNS_ADMIN = $ ORACLE_HOME/network/admin # Set the compiling and running environment for JDBC and SQLJ Export CLASSPATH =: $ CLASSPATH: $ ORACLE_HOME/jdbc/lib/classes111.zip Export CLASSPATH =: $ CLASSPATH: $ ORACLE_HOME/jdbc/lib/nls_charset11.zip Export CLASSPATH =: $ CLASSPATH: $ ORACLE_HOME/sqlj/lib/runtime12.zip Export CLASSPATH =: $ CLASSPATH: $ ORACLE_HOME/sqlj/lib/translator.zip # Set the search path Export PATH = $ PATH:/usr/local/java/bin:/usr/local/jre/bin:/sbin Export PATH = $ PATH:/usr/bin:/usr/sbin:/usr/local/bin: $ ORACLE_HOME/bin

2.3 create lab users and data tables

Create a new Oracle user test and create a student table that records student scores under the new user. This table is very simple and has only three fields: id, name, and score.

Code $ sqlplus system/managerSQL> create user test identified by test default tablspace users; SQL> grant connect to test; SQL> grant resource to test; SQL> connect test/test; SQL> create table student (id number, name varchar2 (20), score number); SQL> insert into test values (1, 'zhang ', 95 );

Add some experiment data to the student table.

3. Pro * C Programming

Oracle's Pro * C pre-compiler is a programming tool that allows you to embed SQL statements in the C language source program. During the pre-compilation process, the Pro * C pre-compiler converts embedded SQL statements into calls to the standard Oracle Runtime Library to generate pre-compiled source code, then you can run the program after compiling and connecting it. Oracle Pro * c pre-compiler enables us to use powerful and flexible SQL in applications. Through this interface, we can use our c language program to directly access the Oracle database.

Using Pro * c Programming involves three steps:

1) First, create a C source program file with the suffix ". pc" embedded SQL statement;

$ Vi stu. pc

2) pre-compile the source program stu. pc to generate a simple C source program stu. c.

$ Proc PARSE = NONE stu. pc

3) Compile the C program, add the dynamic link library of the Oracle client, and generate the executable file stu.

$ Gcc-o stu. c $ ORACLE_HOME/lib/libclntsh. so

The following is the source program of stu. pc. The function of this program is to query the name and score of student table students based on the ID number entered by the user.

# Include Exec SQL INCLUDE SQLCA; Void main () { /* Declare the host variable */ Exec SQL BEGIN DECLARE SECTION; VARCHAR usr [20], pass [20], serv [20]; Char name [8]; Int id, score; Exec SQL END DECLARE SECTION; /* Set the username, password, and database service name for connecting to the database */ Strcpy (usr. arr, "test "); Usr. len = (unsigned short) strlen (char *) usr. arr ); Strcpy (pass. arr, "test "); Pass. len = (unsigned short) strlen (char *) pass. arr ); Strcpy (serv. arr, "MyDB "); Serv. len = (unsigned short) strlen (char *) serv. arr ); /* Connect to the database as a test user */ Exec SQL CONNECT: usr IDENTIFIED BY: pass USING: serv; Printf ("Connect! \ N "); /* Enter the ID number to be queried */ Printf ("Enter the student code :"); Scanf ("% d", & id ); /* Execute dynamic SQL query statements */ Exec SQL SELECT id, name, score into: id, : Name,: score from student where id =: id; Printf ("Name = % s Score = % d \ n", name, score ); /* Submit the transaction and disconnect the database */ Exec SQL COMMIT WORK RELEASE; Printf ("Disconnect! \ N "); }

In Pro * C, the method of traversing each record in the data table is implemented by declaring the cursor. The following routine stu2.pc is an example of using the cursor, the function is to display each record in the student table.

# Include Exec SQL INCLUDE SQLCA; Void main () { /* Declare the host variable */ Exec SQL BEGIN DECLARE SECTION; VARCHAR usr [20], pass [20], serv [20]; Char name [8]; Int id, score; Exec SQL END DECLARE SECTION; /* Set the username, password, and database service name for connecting to the database */ Strcpy (usr. arr, "test "); Usr. len = (unsigned short) strlen (char *) usr. arr ); Strcpy (pass. arr, "test "); Pass. len = (unsigned short) strlen (char *) pass. arr ); Strcpy (serv. arr, "MyDB "); Serv. len = (unsigned short) strlen (char *) serv. arr ); /* Connect to the database as a test user */ Exec SQL CONNECT: usr IDENTIFIED BY: pass USING: serv; Printf ("Connect! \ N "); /* Declare the cursor */ Exec SQL DECLARE stu_cursor CURSOR SELECT id, name, score from student; Printf ("declare cursor OK! \ N "); /* Open the cursor */ Exec SQL OPEN stu_cursor; Printf ("open cursor OK! \ N "); /* Handle errors */ Exec SQL WHENEVER NOT FOUND DO break; /* Cyclically access cursor data */ While (1) { Exec SQL FETCH stu_cursor INTO: id,: name,: score; Printf ("Id = % d Name = % s Score = % d \ n", id, name, score ); } /* Close the cursor */ Exec SQL CLOSE stu_cursor; /* Submit the transaction and disconnect the database */ Exec SQL COMMIT WORK RELEASE; Printf ("Disconnect! \ N "); }

4. JDBC Programming

In Linux, JDBC is used to access the Oracle database. The following technical points are provided:

(1) register the JDBC driver of Oracle. There are two methods: one is to call the class. forName method:

Class. forName ("oracle. jdbc. driver. OracleDriver ");

The other method is to call the registerDriver method of the DriverManager class:

DriverManager. registerDriver (new oracle. jdbc. driver. OracleDriver ());

(2) obtain the connection to the Oracle database. There are also two methods. First, call the OCI driver. The OCI driver uses the Java localized interface JNI) to communicate with the database through the Oracle client software.

Connection conn=DriverManager.getConnection ("jdbc:oracle:oci8:@","test","test");

Another way is to call the Thin driver. The Thin driver is a pure Java driver that communicates directly with the database.

conn=DriverManager.getConnection ("jdbc:oracle:thin:@10.1.14.34:1521:MyDB","test","test");

To achieve the highest performance, Oracle recommends using the OCI driver in client software development.

(3) create a Statament and result set object, execute an SQL statement, and return the result to the result set.

Statement pstmt=conn.createStatement(); ResultSet rset= pstmt.executeQuery(query)

(4) processing result set and displaying data.

The following is the Java source code Stu that traverses the student data table. java, the source program contains two ways to register the Oracle driver, and the OCI and Thin methods to connect to the database because it is impossible to use both methods in the program, so another method is commented out ).

/* Import Java class */import java. SQL. *;/* main class */public class Stu {public static void main (String argv []) throws SQLException {/* declares and initializes the variable */String query = new String ("SELECT id, name, score FROM student"); String name; int id, score; connection conn = null; try {/* First method to register the Oracle driver of JDBC * // Class. forName ("oracle. jdbc. driver. oracleDriver ");/* method for registering the Oracle driver of JDBC */DriverManager. registerDriver (new oracle. jdbc. dr Iver. oracleDriver ();} catch (Exception e) {System. out. println ("cocould not load drive:" + e); System. exit (-1);}/* use the Thin driver to obtain the Oracle connection * // conn = DriverManager. getConnection ("jdbc: oracle: thin: @ 10.1.14.34: 1521: MyDB", "test", "test"); // System. out. println ("Connected with thin client! ");/* Use the OCI driver to obtain the Oracle connection */conn = DriverManager. getConnection ("jdbc: oracle: oci8: @", "test", "test"); System. out. println ("Connected with OCI8! \ N ");/* use try... catch captures and processes exceptions */try {Statement pstmt = conn. createStatement ();/* Execute SQL statement */ResultSet rset = pstmt.exe cuteQuery (query);/* process JDBC result set data cyclically */while (rset. next () {id = rset. getInt (1); name = rset. getString (2); score = rset. getInt (3); System. out. println ("ID =" + id); System. out. println ("NAME =" + name); System. out. println ("SCORE =" + score); System. out. println ("---------------");}/* close JD BC result set */rset. close ();/* close the dynamic SQL statement */pstmt. close ();} catch (SQLException e) {System. out. println ("SQL exceptions:" + e. getMessage ();} conn. close () ;}} compile and execute the above source code to display all records in the student table. $ Javac Stu. java $ java Stu

5. Conclusion

From the perspective of Oracle's product strategies and development trends, Java will become the most powerful tool for Oracle databases. Oracle introduces JVM in Oracle 8i and has developed extremely mature in Oracle 9i. This allows us not only to access the Oracle database using JDBC and SQLJ, but also to execute stored procedures and functions written in Java in Oracle PL/SQL, the combination of the two is bidirectional and seamless. Java makes Oracle a platform-independent database, as Oracle's Development Goal says: no operating system, only Oracle and Internet.