Oracle

How compatible is Oracle Lite with the standard Oracle Server?

Although code compatibility is very high, the following features are not supported in Oracle Lite:

• PL/SQL (use Java Stored Procedures and triggers instead)
• Oracle Lite is intended as a small, single-user client database and do not support any server functionalities like Java CORBA ORB's, SQL*Net Listeners, etc.
• In Oracle Lite, all rows are stored as objects, and there is a relational layer on top of the object layer to allow relational views of the data. Oracle7/8 stores data in a relational format, then uses an object metalayer to provide object views and functionality.
• Only selected dictionary views are available, eg. ALL_TABLES, ALL_VIEWS, ALL_USERS, CAT, etc.
• An Oracle Lite database can only be queried from a remote client if it's data is replicated.

What is Personal Oracle Lite?

Personal Oracle Lite (POL) is a lightweight, single-user object/relational database that runs on desktops, laptops, down to the smallest hand help devices. It is designed for mobile and distributed applications.
Some of the main features are:

• POL runs in under 1 megabyte of memory, and can be installed in 3 megabytes of hard disk space;
• has full Java and SQL support (no PL/SQL);
• is ODBC and JDBC compliant;
• can handle up to 16 concurrent application connections;
• supports replication via file, HTTP, SQL*Net, and Oracle Mobile Agents;
• runs on WindowsNT, Windows95/98, WindowsCE, and PalmOS.

NOTE: One must download and install a Java VM from the JavaSoft web site before all Oracle Lite components can work properly.

How does one connect to a POL database?

To connect to a POL database, the username is typically SYSTEM (with POL v2.4 the default username was OOT_SCH). The password is ignored as the POL database is initially not password protected, but must be specified for SQL*Plus. The connect string should be ODBC:data_source_name. The "ODBC:" tells SQL*Plus that this is not a SQL*Net connection, and that it must use the Oracle Open Client Adapter (OCA) to access the database.
The following command line is used to access the default starter database via SQL*Plus (8i and below):

sqlplus system/manager@odbc:polite:polite
SQL> select * from all_tables;

SQL> exit;

For Oracle 9i, use the Mobile SQL (msql) utility:

C:>msql system/manager@jdbc:polite:polite
Oracle MSQL Version 1.0.0.0.2
(c) Copyright 2001 Oracle Corporation. All rights reserved
Connected to: Oracle9i Lite ORDBMS
Database Name: POLITE (Read Write)
Database Version: 4.0.3.0.0
Auto Commit: off
Driver Name: oracle.lite.poljdbc.POLJDBCDriver (OLite 4.0)
SQL>

How does one write Java Stored Procedures?

Step 1: Create the Java Class and Methods in a file
In this example we will create a Java class "EMPL" in a file called EMPL.java to implement the get_sal method:

import java.sql.*;

public class EMPL {

  public static int get_sal(Connection conn, int empno) throws Exception
  {
     Statement s = conn.createStatement();
     ResultSet r = s.executeQuery("SELECT sal, nvl(comm,0) FROM EMP WHERE empno="+empno);
     int     sal = 0;

     if (r.next()) {
        sal = r.getInt(1) + r.getInt(2);   /* sal = :sal + :comm */
     } else {
        throw new Exception("Employee "+empno+" is invalid.");
     }

     r.close();  s.close();
     System.out.println("Salary for employee "+empno+" is "+sal);
     return sal;
  }
}

Step 2: Attach the Java Class to the Table

ALTER TABLE EMP ATTACH SOURCE "EMPL" IN '.';

Step 3: Execute the Method
To execute the get_sal method from SQL*Plus:

SELECT EMP."get_sal"(7369) FROM DUAL;

To execute this method from ODBC:

SQLExecDirect(hstm, "SELECT EMP."get_sal"(7369) FROM DUAL");

How does one replicate data using Oracle Lite?

Oracle Lite supports read-only and updatable snapshot replication.

Any other Oracle Lite tips?

• Use the ODBC function to get the DB name: SELECT CAST({FN DATABASE()} AS CHAR(16)) FROM DUAL;
• To create a new database use the following command line: CREATEDB polite new_db_name
• Connect to your newly created DB with connect string: system/manager@odbc:polite:new_db_name