Thomas Kyte

Greenhorn
+ Follow
since Jul 15, 2001
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Thomas Kyte

In the database, you can always see your changes. The isolation levels are there to isolate you from OTHER sessions, not yourself.
So, you can see your uncommitted changes regardless of the isolation level.
If you are using Oracle and need to see the values in the database without seeing your changes, you can use an autonomous transaction to open a ref cursor (result set from a stored procedure). These result set will be opened as if it were another session all together and will not see your changes.
Well, I would tend to disagree with the Object database comment as a "pointer" as you call it is really a step backwards somewhat in data integrity (we had that with network databases way back when). You lose the referential integrity rule the user wants. You can "point" to a user or a group but there is nothing stopping the system from deleting that user or group (leaving you with a dangling pointer that points to nothing). We can do that with REFs and UDTs as you point out but you have that nasty dangling ref issue.
In a pure relational world (as would be true in an OO world), the data model would involve some super type (suppose we call it "ENTITY"). The USER table would be a child table of ENTITY as would the GROUP table. Now the MESSAGE_TABLE has a foreign key to ENTITY (as the OO model would have a pointer to an ENTITY instance but the pointer might be the USER or GROUP as ENTITY is probably an abstract class)..
So, one method to solve this is to have tables such as:
create table entity( id int primary key, data varchar2(10) );
create table users
( id primary key references entity, other_data varchar2(10) );
create table groups
( id primary key references entity, other_data varchar2(10) );
create table messages
( msg_id int primary key, who_from references entity );
Sure, you can get some information from:

the wrox website
but here is an annotated table of contents:

Chapter 1: Developing Successful Oracle Applications
Here I cover topics such as why it is fairly important to understand how your database works, how transactions work, how things in general in your database of choice works (and every database works differently). This is done by relating observations of what happened when the database and its implementation was ignored..

Chapter 2: Architecture
Here I go over the architecture of the Oracle database from the organization of the SGA, how memory is used by the various Oracle processes to the Oracle processes themselves (what they are, what they do and how they interact)
Chapter 3: Locking and Concurrency
This section covers the ins and outs of locking in Oracle, the various types of locks/latches, how to use them (and not use them) and how concurrency control in Oracle is implemented.
Chapter 4: Transactions
Covers the transaction control statements, what they do and how to use them. Cover also how integrity constraints work with transactions, "bad" transaction habits I've observed, distributed transactions and lastly how redo and rollback work (what the server does to support transactions) in Oracle
Chapter 5: Redo and Rollback
Covers most things you would want to know about redo and rollback in Oracle..
Chapter 6: Database Tables
Many people don't know there is more then one type of table -- we have heap tables, index organized tables, clustered objects, hash clustered objects, nested tables, temporary tables and object tables -- this chapter explores the implementation and uses of each

Chapter 7: Indexes
Like tables -- there is more then one index structure. Here we cover b*trees (and the various types of b*trees out there), bitmaps, function based, and domain indexes. This section also has a "FAQ" on indexes covering what I've seen to be the most frequently asked questions on indexes.
Chapter 8: Import and Export
Chapter 9: Data Loading
These two chapters cover the ins and outs of using the IMP/EXP and SQLLDR tools with examples and suggested uses for the tools, lots of examples.
Chapter 10: Tuning Strategies and Tools
Covers an approach to tuning (it starts from day 1 of design). How to use the supplied tools everyone has access to such as sql_trace, timed_statistics, tkprof, statspack, dbms_profiler, and so on.
Chapter 11: Optimizer Plan Stability
Discusses the implementation of an advanced query hinting facility in Oracle8i..
Chapter 12: Analytic Functions
Starting with 8i, release 2 -- Oracle added dozens of analytic functions. These functions are extremely powerful in day to day usage (you need not be a statistician to make use of these functions every day). This chapter goes into how they work, what they do and how to use them.
(most of the chapters that follow have this format now:
o intro to topic
o how topic works
o why you might want to use topic
o examples of topic
o caveats to be aware of with regards to topic
o summary)
Chapter 13: Materialized Views
A materialized view is a way of answering complex questions ahead of time and can be used to take a query that would take hours to execute and get the answer in milli-seconds. This chapter explores materialized views, how they work... (that list from above)
Chapter 14: Partitioning
Same for partitioning (that list)
Chapter 15: Autonomous Transactions
Autonomous transactions are a new 8i feature that are sometimes misunderstood (or missed all together). This chapter goes into detail as to what they are and why they are (the list again)
Chapter 16: Dynamic SQL
Covers dynamic sql in PLSQL
Chapter 17: interMedia
Using interMedia text to query, summarize, and theme textual data..
Chapter 18: C-Based External Procedures
How to write an external procedure in C ( a stored procedure written in C)
Chapter 19: Java Stored Procedures
What they are, why they are, how to do them, (that list again)
Chapter 20: Using Object Relational Features
covers the object relational features of Oracle8i, what they mean to you and how to use them.
Chapter 21: Fine Grained Access Control
How the virtual private databases (VPD) function is implemented in Oracle, how/when to use it.
Chapter 22: n-Tier Authentication
Covers the ability for a middle tier application to "proxy" log in on behalf of some user.
Chapter 23: Invoker and Definer Rights
What the differences between invokers and definers rights are and how they affect the execution of stored procedures written in Java, C or PLSQL.

Appendices
Appendix A: Necessary Supplied Packages
Covers all of the goodies Oracle supplies out of the box like dbms_job, dbms_application_info and so on...

------------------
Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
see
http://osi.oracle.com/~tkyte/ResultSets/index.html
there I have complete examples showing how to interact with ref cursors using jdbc, proc, oci, perl, vb, etc....
Can you describe what you mean by 5 levels deep with regards to a nested table type?
Until Oracle9i, collections only go one level -- I need to know whether you are talking about a multi-level collection in 9i or if you have some specific construct you are using in Oracle8i.
An example of what you've done would be useful.
There are a couple of ways to do this. If the file resides on the database server itself -- perhaps the easiest method is to just use DBMS_LOB.LOADFROMFILE and let the server do all of the work:
create table demo
( id int primary key,
theBlob blob
)
/
create or replace directory my_files as '/export/home/tkyte/public_html';
create or replace procedure load_a_file( p_file in varchar2 )
as
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theBlob into l_blob;
l_bfile := bfilename( 'MY_FILES', p_file );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
/
would give you a procedure to call to load a file into the database. It also shows you how to create a BFILE as it created one temporarily to load the file. I could have just inserted l_bfile instead of writing l_blob if I wanted the files "external" from the database.

If the file is not on the database server itself, loadfromfile won't work, we can use a stream instead. Here is an example. We'll use a BLOB to serialize an object into (and out of) the database. The concept is identical for images, documents -- any binary data.
This example shows:
o creating an object
o serializing it into the database
o reading it back out
o printing it.
The second, more robust example with comments is a little more
flexible. It will serialize a java instance into the database
if passed no inputs. After it puts it in -- it'll print out the
"id" for that object instance. Later, you can run this same
java example and pass it that id -- it'll read out that object
instance and print it out, showing how to make this work across
invocations.
Things you will need to run this example in this case are:

create sequence java_obj_seq;
create table java_objects(
id number,
classname varchar2(2048),
bytes blob default empty_blob()
)
We use the sequence to create a "primary key" for out object
instances. We need this number later to retrieve a specific
instance. The table is what we use to store the object
instance. Here we store the classname of the original java instance in a varchar2 field and in the BLOB we'll store the
bytes of the serialized object.
Here is the first small example:

import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
public class serial2 {
static String genID =
"select java_obj_seq.nextval from dual";
static String writeSQL =
"begin insert into java_objects(id,classname,bytes) "+
" values (?,?,empty_blob()) " +
" return bytes into ?; end;";
static String readSQL =
"select bytes from java_objects where id = ?";
public static long write(Connection conn, Object o)
throws Exception
{
long id = nextval(conn);
String className = o.getClass().getName();
CallableStatement stmt = conn.prepareCall(writeSQL);
stmt.setLong(1, id);
stmt.setString(2, className);
stmt.registerOutParameter(3, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBlob(3);
OutputStream os = blob.getBinaryOutputStream();
ObjectOutputStream oop = new ObjectOutputStream(os);
oop.writeObject(o);
oop.flush();
oop.close();
os.close();
stmt.close();
System.out.println("Done serializing " + className);
return id;
}
public static Object read(Connection conn, long id)
throws Exception
{
PreparedStatement stmt = conn.prepareStatement(readSQL);
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
rs.next();
InputStream is = rs.getBlob(1).getBinaryStream();
ObjectInputStream oip = new ObjectInputStream(is);
Object o = oip.readObject();
String className = o.getClass().getName();
oip.close();
is.close();
stmt.close();
System.out.println("Done de-serializing " + className);
return o;
}
private static long nextval(Connection conn)
throws SQLException
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(genID);
rs.next();
long id = rs.getLong(1);
rs.close();
stmt.close();
return id;
}
public static void main(String[] argv)
throws Exception
{
String cs = "jdbc racle ci8:@ora8idev";
String user = "scott";
String pass = "tiger";
DriverManager.registerDriver(new OracleDriver());
Connection conn =
DriverManager.getConnection(cs, user, pass);
conn.setAutoCommit(false);
LinkedList l = new LinkedList();
l.add("This");
l.add("is");
l.add("a");
l.add("test");
l.add(new Long(123123123));
l.add(new java.util.Date());
long id = write(conn, l);
conn.commit();
System.out.println("ID= " + id);
System.out.println("Object= " + read(conn, id));
conn.close();
}
}
Now, if we run the code:
$ /usr/java1.2/bin/java serial2
Done serializing java.util.LinkedList
ID= 1
Done de-serializing java.util.LinkedList
Object= [This, is, a, test, 123123123, Sun Jan 21 09:45:21 EST
2001]

So, according to that, the object has been serialized and stored
in the database. In fact, we can log in using sqlplus and see
it if we like:

scott@DEV816> create or replace function clean( p_raw in blob )
return varchar2
2 as
3 l_tmp long default utl_raw.cast_to_varchar2(
dbms_lob.substr(p_raw,2000,1));
4 l_char char(1);
5 l_return long;
6 begin
7 for i in 1 .. length(l_tmp)
8 loop
9 l_char := substr( l_tmp, i, 1 );
10 if ( ascii(l_char) between 32 and 127 )
11 then
12 l_return := l_return | | l_char;
13 else
14 l_return := l_return | | '.';
15 end if;
16 end loop;
17 return l_return;
18 end;
19 /
Function created.
scott@DEV816> scott@DEV816> select id, classname,
2 dbms_lob.getlength(bytes) len,
3 clean(bytes) clean
4 from java_objects
5 /
ID CLASSNAME LEN CLEAN
---------- -------------------- ---- --------------------
1 java.util.LinkedList 191 ....sr..java.util.Li
nkedList.)S]J`."...x
pw.....t..Thist..ist
..at..testsr..java.l
ang.Long;.....#....J
..valuexr..java.lang
.Number...........xp
.....V..sr..java.uti
l.Datehj..KYt....xpw
.....1...xx
So, we see now how to serialize using a BLOB as the input stream
and deserialize using the BLOB again. We can also review the
contents (sort of) in SQLPlus.
UTL_RAW might not be installed on your system -- it comes with
Oracle7.1.6 and up. To install if you do not have it:
o cd $ORACLE_HOME/rdbms/admin
o connect as SYS or INTERNAL only using SVRMGRL
o @utlraw
o @prvtrawb.plb

Now for the longer, more robust example with error handling and
comments. You have all you need above -- the following is just
a little more sophisticated. After you compile this:
$ /usr/java1.2/bin/javac serial.java
we can run:
$ /usr/java1.2/bin/java serial 1
Done de-serializing java.util.LinkedList
Object= [This, is, a, test, 123123123, Sun Jan 21 09:45:21 EST
2001]
(after running the first example serial2.java that is). All
this did was read out our existing java instance and "rebuild
it" for us and then print it out. You can run serial without
any arguments to create new instances of the linked list and
read them back out by ID later. Here is the code:
import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
public class serial {
static String genID =
"select java_obj_seq.nextval from dual";
static String writeSQL = "begin insert into java_objects "+
" (id,classname,bytes) values (?,?,empty_blob()) "+
" return bytes into ?; end;";
static String readSQL =
"select bytes from java_objects where id = ?";
// We attempt to serialize the object to the database table.
// We use a
// sequence number to identify the object and return that
// value to the
// caller. We also save the class name in case someone wants
// to search
// for objects based on that name. If we get any exception,
// we rethrow
// it to the caller but we always try to clean up after
// ourself.
public static long write(Connection conn, Object o)
throws Exception
{
ObjectOutputStream oop = null;
CallableStatement stmt = null;
OutputStream os = null;
long id = nextval(conn);
String className = o.getClass().getName();
try {
stmt = conn.prepareCall(writeSQL);
stmt.setLong(1, id);
stmt.setString(2, className);
stmt.registerOutParameter(3, java.sql.Types.BLOB);
stmt.executeUpdate();
BLOB blob = (BLOB) stmt.getBlob(3);
os = blob.getBinaryOutputStream();
oop = new ObjectOutputStream(os);
oop.writeObject(o);
oop.flush();
} catch (Exception e) {
throw e;
} finally {
if (oop != null) { oop.close(); }
if (os != null) { os.close(); }
if (stmt != null) { stmt.close(); }
System.out.println("Done serializing " + className);
}
return id;
}
// We attempt to de-serialize the object from the database
// table using
// the given identifier. If we get any exception, we rethrow
// it to the
// caller but we always try to clean up after ourself.
public static Object read(Connection conn, long id)
throws Exception
{
ObjectInputStream oip = null;
PreparedStatement stmt = null;
InputStream is = null;
Object o = null;
String className = null;
try {
stmt = conn.prepareStatement(readSQL);
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
// Even though we expect only one row back, the caller
// could have
// passed an invalid identifier so we wrap this in a
// loop to
// make sure we don't get null pointer exceptions. In
// the case
// where there are 0 rows, we would return a null
// value. Where
// there are > 1 rows, we would return only the first
// one.
while (rs.next()) {
is = rs.getBlob(1).getBinaryStream();
oip = new ObjectInputStream(is);
o = oip.readObject();
className = o.getClass().getName();
break;
}
} catch (Exception e) {
throw e;
} finally {
if (oip != null) { oip.close(); }
if (is != null) { is.close(); }
if (stmt != null) { stmt.close(); }
System.out.println("Done de-serializing " + className);
}
return o;
}
// Get the next sequence value. Ideally, this would be better
// if
// we just created a prepared statement and repeatedly used
// that.
// Obviously, we must assume the same connection is always
// used.
private static long nextval(Connection conn)
throws SQLException {
long id = -1;
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(genID);
while (rs.next()) {
id = rs.getLong(1);
}
} catch (SQLException e) {
throw e;
} finally {
if (rs != null) { rs.close(); }
if (stmt != null) { stmt.close(); }
}
return id;
}
// Here we just simply connect to the database and either
// serialize or de-serialize an object. If we get an
// exception then we will rollback whatever we have done.
// Commit changes as necessary and close the connection.
public static void main(String[] argv) throws Exception {
long id;
Connection conn = null;
String cs = "jdbc racle ci8:@ora816dev";
String user = "scott";
String pass = "tiger";
try {
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(cs, user, pass);
conn.setAutoCommit(false);
if (argv.length == 0) {
id = write(conn, new java.util.Date());
conn.commit();
System.out.println("ID= " + id);
LinkedList l = new LinkedList();
l.add("This");
l.add("is");
l.add("a");
l.add("test");
id = write(conn, l);
conn.commit();
System.out.println("ID= " + id);
} else {
id = Long.parseLong(argv[0]);
Object o = read(conn, id);
System.out.println("Object= " + o);
}
} catch (Exception e) {
e.printStackTrace();
if (conn != null) { conn.rollback(); }
} finally {
if (conn != null) { conn.close(); }
}
}
}