Share

Database, SQL and PL/SQL

Java, the ESCAPE Sequence, and Synonym Alternatives

 

Our Oracle expert expounds on serializing Java objects, using ESCAPE with LIKE, communicating return code from SQL*Plus, and using a synonym to view.

By Tom Kyte Oracle Employee ACE

May/June 2001

 

How do I serialize Java objects into the Oracle database and vice versa? For those of you who are not Java programmers, let's start with a bit of explanation. In Java, if an object is "serializable," you can save it to disk or send it to another program over the network. A serializable object is one that can be saved as a serial stream of bytes and reloaded later. To serialize an object into the database means to save the object instance into the database for later use. The program "variable" (object instance) will be saved.

I asked our Java guru Mark Piermarini (mark.piermarini@oracle.com) to come up with an example to answer this question. His Java code example, shown below, includes the following steps:

  • Creating an object

  • Serializing it into the database

  • Reading it back out

  • Printing it

These are the database objects you'll need to run this example:

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 our object instances. We'll 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 as shown in "Serializing Java Objects Code Example." When we run the code, we get the following:

$ /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]

According to the results of running the code, the object has been serialized and stored in the database. In fact, we can log in using SQL*Plus 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  /

You can see how to serialize using a BLOB as the input stream and deserialize using the BLOB again. You can also review the contents to some extent. You'll need to use the UTL_RAW package, because SQL functions don't work on the RAW data type, so you must turn "raw" data into varchar2 so SQL*Plus will print it. UTL_RAW might not be installed on your system—it comes with Oracle7 Release 7.1.6 and up. To install UTL_RAW:

  1. Go to the directory $ORACLE_HOME/rdbms/admin

  2. Connect only as SYS or INTERNAL, using SVRMGRL

  3. Type @utlraw and press Enter

  4. Type @prvtrawb.plb and press Enter


Using ESCAPE with LIKE

I would like to retrieve TABLE_NAMES from ALL_TABLES where the table name is like 'ADD_'. I tried using the following, but my query is returning ADDRESS and ADD_CODES:

select distinct table_name from all_tables
where table_name like 'ADD_%'


I also tried using the ESCAPE sequence: 

select distinct table_name from all_tables
where table_name like 'ADD\_%'

Your question comes up frequently. How do you use LIKE to find data that contains an underscore or percent sign? The answer is to escape the underscore and/or percent sign in the LIKE template. You have to designate what character you would like to use via the ESCAPE keyword. A slash is commonly used, but any character would actually work:

select distinct table_name
  from all_tables
 where table_name like 'ADD\_%' ESCAPE '\'
/

Return Code from SQL*Plus

I have written a UNIX shell script to automate an execution of stored procedures and functions one after another. These pro-cedures/functions return a success/failure code. If one of the procedures fails due to some error, then I want to stop execution of other procedures in the queue and return a failure code in shell script, or otherwise keep continuing and return success code at the end. I don't know how to communicate this return code from Oracle to the UNIX shell script, because this return code becomes unavailable as the control comes back from Oracle to UNIX. This script will be executed by Autosys scheduler, which schedules other jobs as well based on this result.

 

I assume you are using SQL*Plus. If so, the WHENEVER SQLERROR directive can help. We can return a status from 0...255 to the UNIX shell. This is usually in the $status or $rc environment variable immediately after execution of a command. Here is a CSH script that shows how this might work:

#!/bin/csh
sqlplus -s scott/tiger >>EOF
variable rc number
whenever sqlerror exit sql.sqlcode
begin
    :rc := 55;  -- this is really your
function call, not 55
    if ( :rc <> 0 )
    then
        raise_application_error( (-20000-
224) - :rc, 'Bummer' );
   end if;
end;
/
EOF
set x=$status
echo $x

When I run this code, I see:

$ test.csh
begin
*
ERROR at line 1:
ORA-20279: Bummer
ORA-06512: at line 5
55

The 55 is what we're looking for. The key is to use:

raise_application_error( (-20000-224) -
:rc, 'Bummer' );

to raise the error. We can raise errors in a given range, but the shell will only keep an unsigned byte in the status return value (values 0...255). It takes our exit file and just looks at that last byte. By using -20000-224 and subtracting from your return code, we end up exiting with the value of your return code (given that :RC is in the range...255).


Using—Or Not Using—a Synonym

I created a view called V_EMP in one user object called CDSMANAGER, and I want to view the content of the view V_EMP from a different user called CDSBATCH, which is in the same database. I don't want to use the username in the SELECT statement, as follows: 

select * from cdsmanager.v_emp;

I also don't want to use a synonym. Is there any way to obtain the content without mentioning the username, that is, without using the CDSMANAGER.VIEW name?

Here are your options (I've included even the ones you don't want to use):

Use a private synonym. This will make it so that only CDSBATCH does not have to qualify the tablename. As CDSBATCH, issue this command:

SQL> create synonym v_emp for
cdsmanager.v_emp;


Use a public synonym. This allows all users to use V_EMP if they have been granted access to the base table. By this I mean that all users will see the synonym V_EMP, but they might not be able to see the base table CDSMANAGER.V_EMP. As a user with the CREATE PUBLIC SYNONYM privilege, execute this command:

SQL> create public synonym v_emp for cds-
manager.v_emp;

I myself prefer to use a private synonym over a public synonym if at all possible, so as to not "pollute" the database namespace with lots of synonyms. If I am going to host many applications in my database, I try to be sensitive to the fact that many people will be creating things in there. There can be only one "V_EMP" public synonym. If I use it, then no one else can. The only fair thing to do is not to use the public synonym. Use ALTER SESSION. Use the ALTER SESSION SET command ( ALTER SESSION SET CURRENT_SCHEMA =cdsmanager;) in CDSBATCH's session. Then instead of adding CDSBATCH as the default schema name when resolving objects, we'll use CDSMANAGER as the default name. For example:

ops$tkyte@DEV816> desc emp
ERROR:
ORA-04043: object emp does not exist
ops$tkyte@DEV816> alter session set
current_schema=scott;
Session altered.
ops$tkyte@DEV816> desc emp
Name                  Null?                  Type
-----------      ----------  --------------------
EMPNO              NOT NULL             NUMBER(4)
ENAME                                VARCHAR2(10)
JOB                                   VARCHAR2(9)
MGR                                     NUMBER(4)
HIREDATE                                     DATE
SAL                                   NUMBER(7,2)
COMM                                  NUMBER(7,2)
DEPTNO                                  NUMBER(2)

This command finds SCOTT.EMP for me the second time without my having to specify SCOTT. This does not affect your privileges at all—you still need to be granted access to the object. This ALTER SESSION simply changes the way we attempt to resolve names. This command was first documented in Oracle8i but exists in prior releases as well. It is used extensively by IMP during the import of objects. Use a view. As CDSBATCH, issue this command:

SQL>  create view v_emp as select * from
cdsmanager.v_emp;

This method has the advantage over the synonyms in that, as CDSBATCH, you have a copy of the metadata about V_EMP in your schema. A query on USER_TAB_COLUMNS and such, when you're logged in as CDSBATCH, will return rows for V_EMP. A synonym would not do that.

 
Serializing Java Object Code Example 
serializing java objects code 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:oracle:oci8:@ora8
idev";
      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();
   }
}
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
Next Steps

Oracle Managing Technologist Tom Kyte answers your most difficult Oracle technology questions in Oracle Publishing Online's forum Ask Tom, at asktom.oracle.com. Highlights from that forum appear in this column.

For more information on using Java with Oracle, visit theJava Technologies area of the Oracle Technology Network.

For an introduction to synonyms, consult the Oracle8i Concepts manual available on the Oracle Technology Network.
 

Tom Kyte

In his 22 years at Oracle, Tom Kyte started the Ask Tom question and answer site and was the site's sole proprietor for almost 15 years. In his spare time, he also wrote Expert Oracle Database Architecture (Apress, 2005, 2010, 2014) and Effective Oracle by Design (Oracle Press, 2003), among other books. (The Ask Tom site continues today as Ask TOM: Ask The Oracle Masters.)

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.