Did You Know? -- November 2000

By Hervé Deschamps, Oracle Corporation.

Wireless Technology

Download The Wapalizer from Gelon.net.
By using this WAP browser you will be able to see what kind of services are offered today, and you can quickly find out if the service offered, is something for you.
The WAP browser is still not bug free, and it may not ever be. The script fetches pages  from WAP sites, and converts them to HTML (WEB pages) "on-the-fly". This means that  you will be able to view most WAP pages, but some pages, especially those with a lot of  input forms are very difficult to convert to HTML.

You can also point the browser at any of the WAP addresses found on http://www.gelon.net/links/.

Working with CLOB

There is a lot of CLOB stuff going on in Portal30, behind the scenes. This is because all free text is stored in table wwv_text in a CLOB column. So how do you search the table for specific rows?

A straight 'like' command does not work:

SQL> select text from wwv_text where text like '%marathon%';
select text from wwv_text where text like '%marathon%'
ERROR at line 1:
ORA-00932: inconsistent datatypes

One trick is to query the table by using the DBMS_LOB.INSTR(<column name>,   '<string to be searched>') procedure.  If the specified string is in the CLOB column, the procedure will return the position of the first appearance of the string.  So if the output value for the procedure is >0, the string exist in the CLOB column.

SQL> select text from wwv_text where dbms_lob.instr(text, 'marathon')>0;

Created item runner.gif in fitness/
Created item marathon_pla


Quick example:

      dbms_job.submit(job=>v_jobno, -- out parameter
                      next_date=>sysdate+1/24/60*1, -- every 1 minute
                      interval=>'sysdate+1/24/60*1');  -- watch the varchar2 parameter datatype!

The the next_date parameter of  dbms_job.submit can be set to a date in the past. Dbms_job processed jobs from the earliest next_date to the latest one. So setting a next_date in the past is a good trick to move jobs ahead of others when the traffic is heavy.

Watch it when you try to use a function for the interval parameter for dbms_job.submit. Although in theory it should work, it can result in erratic job execution behavior.

On some platforms and versions of Oracle you need a commit after a dbms_job.submit. I don't know the list but I have experienced this on NT and Oracle 8i Release 2.

Oracle export and import utilities preserve job numbers. So when you import jobs in a job queue that already has its own jobs running you may run into job number conflicts.

Sometimes you may not need the complications dbms_jobs -- a simple dbms_lock.sleep(<number of seconds>) might be adequate.

Autonomous Transactions

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log
events, increment retry counters, and so on, even if the main transaction rolls back.

More important, autonomous transactions help you build modular, reusable software components. For example, stored procedures can start and finish autonomous
transactions on their own. A calling application need not know about a procedure's autonomous operations, and the procedure need not know about the application's transaction context. That makes autonomous transactions less error-prone than regular transactions and easier to use.

Furthermore, autonomous transactions have all the functionality of regular transactions. They allow parallel queries, distributed processing, and all the transaction
control statements including SET TRANSACTION.

Here is an example that I programmed for a general purpose util package:

   procedure log ( p_process   in varchar2             default 'Not Supplied',
                   p_ts        in date                 default sysdate,
                   p_msg       in varchar2             default 'Not Supplied',
                   p_is_error  in varchar2             default 'N',
                   p_is_trace  in varchar2             default 'N' ) is

      -- do this in independent transaction, so that the log entry be committed to the DB even
      -- if the caller of this procedure generates an unhandled exception.
      pragma autonomous_transaction;
      insert into log (process,   ts,   msg,   is_error,   is_trace   ) values
                      (p_process, p_ts, p_msg, p_is_error, p_is_trace);
   end; -- procedure log

In the example below, we mark a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as

create trigger parts_trigger
before insert on parts for each row
   pragma autonomous_transaction;
   insert into parts_log values(:new.pnum, :new.pname);
   commit;  -- allowed only in autonomous triggers

SQL*PLus (yes, good old one!)

Ever wondered how to create a procedure that included the character '&' without getting SQL*plus to think you meant to defined a substitution variable? There are a lot of ampersands in web related code because it is a typical URL character. Here is an example of such code:

   x := utl_http.request_pieces(url=> 'http://finance.yahoo.com/q?s=ORCL&d=e',
                                max_pieces=> c_num_pieces,
                                proxy=> 'www-proxy.us.oracle.com');

But when SQL*Plus encounters it it goes:

Enter value for d:
old  30:    x := utl_http.request_pieces(url=> 'http://finance.yahoo.com/q?s=ORCL&d=e',
new  30:    x := utl_http.request_pieces(url=> 'http://finance.yahoo.com/q?s=ORCL=e',

And my URL parameter d is gone!

The trick:

SQL> set define off

PS1: If you still want SQL*plus to prompt you for substitution variables but want it to use some other character than ampersand, you can also use the command set define to defined a replacement for '&'.

PS2: To turn off the old-new thing, type 'set verify off'.

Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others.  He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at herve.deschamps@.oracle.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.