OraNA :: Oracle News Aggregator

The Ouroboros, is an ancient symbol depicting a serpent or dragon swallowing its own tail and forming a circle (reference: http://en.wikipedia.org/wiki/Ouroboros).
So being a bit mischievous, I asked the question "can you store a database within itself". I bet any DBA has thought about this at sometime when looking at lobs.

With securefiles and high compression, you could store in theory any database within a lob.
You can certainly store a virtualisation within a lob, but what about the exact same database? Would you be able to point to it, and store it in itself?
The exercise to do this is just one of those fun scenario's to play with.

The program I used for loading is one I have been using to test load virtualisations into the database. I have been saying for over a year now, that as the number of virtualisations grow, they will need to be managed.
I have to deal with over 30 at the moment, and then back them up and work out which ones go where and how they are to be used. Some are small and as little as 2Gb, and some are over 100Gb in size.

Having a database manage them seems sensible, even though they are quite large in size. Disk prices in the last 12 months have dropped to the point where 1Tb costs under $300.
One can't run the virtualisation from within the database, maybe that will happen later, but one can certainly back them up and store copies in the database.

So what happens when you try to load a database into a lob in the same database? As my database is split over 2 drives, I broke it up into two steps. The first step loaded the core database files, like system and sysaux.
They loaded in ok.

Source code found at : http://www.xor.com.au/presentations/prog.sql
(note: the routines os_command.directory_listing and gl.img_size, are my own routines used for getting directory listings and formatting output, so you need to replace them with your own equivalents).

Here is what happened:

WEBSYS vnlz> @w:\vmware_in_database\prog Start at:09-JUL-08 10.10.32.937000000 AM +10:00 . Deleted at:+000000000 00:00:00.031000000 . Loading:O1_MF_SYSAUX_46Y67DGN_.DBF[834224128]F . . Size:814.7 Mb . . Inserted at:+000000000 00:00:29.391000000 . Loading:O1_MF_SYSTEM_46Y66Z0H_.DBF[1073758208]F . . Size:1,048.6 Mb . . Inserted at:+000000000 00:00:56.234000000 . Loading:O1_MF_TEMP_46Y67SFO_.TMP[1073758208]F . . Size:1,048.6 Mb . . Inserted at:+000000000 00:00:17.219000000 . Loading:O1_MF_UNDOTBS1_46Y67GWS_.DBF[1073758208]F . . Size:1,048.6 Mb . . Inserted at:+000000000 00:00:42.313000000 . Loading:O1_MF_USERS_46Y684X9_.DBF[5259264]F . . Size:5.1 Mb . . Inserted at:+000000000 00:00:00.156000000 Finish:+000000000 00:02:25.344000000

The second part involved loading in 131Gb of allocated datafiles into itself. These datafile were pretty much empty.

Off course I was expecting it to fail, as even if it compressed it, there would be some point of infinite regression where it would have to fail.
This is the table definition:

create table vmware ( id number(16), core_file ordsys.ordsource ) tablespace piction_med_1 LOB (core_file.localdata) STORE AS SECUREFILE l_core_file (TABLESPACE PICTION_IMG_2 disable storage in row RETENTION AUTO COMPRESS HIGH KEEP_DUPLICATES STORAGE (MAXEXTENTS UNLIMITED PCTINCREASE 0) NOCACHE LOGGING);

Nothing fancy, just a PK and the tablespace it loads into.
This is the tablespace creation commands:

CREATE TABLESPACE PICTION_IMG_2 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1G segment space management auto datafile 'r:\oradata\VNLZ\piction_img_2a.dbf' size 60G; alter tablespace PICTION_IMG_2 add datafile 'r:\oradata\VNLZ\piction_img_2b.dbf' size 60G;

I had to create 2 datafiles because there is a max file size.

This was done on Windows XP, 64bit running 11.1.0.6. I just happen to have more free storage on this platform than on Linux. And the database resides on off the shelf SATA drives.
There is 8Gb of memory on the box.

Curious yet as to what happened?

WEBSYS vnlz> @w:\vmware_in_database\prog Start at:09-JUL-08 10.16.53.578000000 AM +10:00 . Loading:PICTION_ARC_1.DBF[104873984]F . . Size:102.4 Mb . . Inserted at:+000000000 00:00:02.860000000 . Loading:PICTION_IMG_1.DBF[-2147467264]F . . Size:10.49 Gb . . Inserted at:+000000000 00:04:47.047000000 . Loading:PICTION_IMG_2A.DBF[16384]F . . Size:62.91 Gb . . Inserted at:+000000000 00:39:24.703000000 . Loading:PICTION_IMG_2B.DBF[16384]F . . Size:62.91 Gb . . Inserted at:+000000000 00:50:12.687000000 . Loading:PICTION_IND_1.DBF[419446784]F . . Size:409.6 Mb . . Inserted at:+000000000 00:00:13.328000000 . Loading:PICTION_LRG_1.DBF[524304384]F . . Size:512.0 Mb . . Inserted at:+000000000 00:00:14.360000000 . Loading:PICTION_MED_1.DBF[524304384]F . . Size:512.0 Mb . . Inserted at:+000000000 00:00:15.078000000 . Loading:PICTION_SML_1.DBF[419446784]F . . Size:409.6 Mb . . Inserted at:+000000000 00:00:11.094000000 . Loading:PICTION_TSP_1.DBF[104873984]F . . Size:102.4 Mb . . Inserted at:+000000000 00:00:02.765000000 Finish:+000000000 01:35:23.937000000

Yep. It loaded it ok.

(note: for datafiles >2Gb, there seems to be a java bug where it incorrectly reports the file size. It didn't impact the load, just the reporting of the size).

WEBSYS vnlz> select gl.img_size(bytes,'A') from user_segments where segment_name = 'L_CORE_FILE';

GL.IMG_SIZE(BYTES,'A')
--------------------------------------------------------------------------------------------------
35.65 Gb

and it compressed the 130+Gb down to 35.65Gb.

Very surprised by that result. My next assumption is that what is in these lobs is corrupted as I didn't enable database backup online before doing the load. So the next question is, if I extract it from the database will the database work (assume I did enable backup online)?

I'll leave that for another time. I have other work to do.....

If someone can see fault in what I did, please let me know. Am happy to retry the test under different conditions.

I have this really annoying co-worker who happens to be the DBA. Everytime he walks by my desk he pounces on my keyboard. I've learned to Windows Key + L to lock the computer when I hear him approaching, but occasionally I forget.

As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better...

Walking away he asks, "How many K's are in there?"

I ignored him, but then wondered myself...what's the best solution to this problem?

So, here it goes:

DECLARE
  l_count_k NUMBER := 0;
  l_string VARCHAR2(300);
  l_string_length INTEGER;
BEGIN
  l_string := 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER';

  l_string_length := LENGTH( l_string );

  FOR i IN 1..l_string_length LOOP
    IF SUBSTR( l_string, i, 1 ) IN ( 'K', 'k' ) THEN
      l_count_k := l_count_k + 1;
    END IF;
  END LOOP;
  
  dbms_output.put_line( 'Kk Count:  ' || l_count_k );
END;
/

Easy enough, 45.

Then I started thinkinhg...can I do this in pure SQL? Of course!

SELECT 
  SUM( CASE
         WHEN SUBSTR( UPPER( mystring ), rownum, 1 ) = 'K' THEN
           1
       END ) k
FROM
  dual,
  (
    SELECT 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
    LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
    EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
    TKELTKER' mystring
    FROM dual
  ) c
CONNECT BY LEVEL <= LENGTH( mystring )

COUNT_OF_K
----------
        45

1 row selected.

I'm sure many of you can do better than that. So let's see 'em. Prodlife, this isn't a complicators test either. ;)

Here are some cool and useful things you can do with XML using pure SQL in an Oracle database.

HTTPURIType

The HTTPURIType provides support for the HTTP protocol. It uses the UTL_HTTP package underneath to access the HTTP URLs. With HTTPURIType, you can create objects that represent links to remote Web pages (or files) and retrieve those Web pages by calling HTTPURIType member methods.

The getXML() member function returns the XMLType located at the address specified by the URL. An error is thrown if the address does not point to a valid XML document.

SQL>  SELECT HTTPURITYPE('http://feeds.feedburner.com/orana').getxml() orana_feed
  2     FROM dual;

ORANA_FEED
--------------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet href="http://feeds.feed
...

The getClob() member function returns the CLOB located by the HTTP URL address.

SQL>  SELECT HTTPURITYPE('http://google.com/').getClob() goog_html
  2     FROM dual;

GOOG_HTML
--------------------------------------------------------------------------------

<html><head><meta http-equiv="content-type" content="text/html; charset=ISO-8859
...

DBURIType

The DBURIType provides support for DBUri-refs. A DBUri-ref is an intra-database URL that can be used to reference any row or row-column data in the database. With DBURIType, you can create objects that represent links to database data, and retrieve such data as XML by calling DBURIType member methods.

SQL> CREATE TABLE t (a NUMBER, b VARCHAR2 (10));

Table created.

SQL> INSERT INTO  t (a,b) VALUES (1, 'Eddie');

1 row created.

SQL> INSERT INTO  t (a,b) VALUES (2, 'John');

1 row created.

SQL> INSERT INTO  t (a,b) VALUES (3, 'Pat');

1 row created.

SQL> commit;

Commit complete.

The member function getXML() returns the XMLType located at the address specified by the URL.

SQL> SELECT DBURIType('/SCOTT/T').getxml()
  2  from dual;

DBURITYPE('/SCOTT/T').GETXML()
---------------------------------------------------------------------------

<?xml version="1.0"?>
<T>
 <ROW>
  <A>1</A>
  <B>Eddie</B>
 </ROW>
 <ROW>
 <ROW>
  <A>2</A>
  <B>John</B>
 </ROW>
 ...

The above was tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.

In part 2 we’ll explore more XML goodness…

Sources and resources

• Accessing Data Through URIs
• Database URI TYPEs
• Oracle XML DB Developer’s Guide
• What bloggers are saying about HTTPURIType
• What bloggers are saying about DBURIType

---
Related Articles at Eddie Awad's Blog:

• Anonymous annoyance now a crime
• iPhone and iPod Touch Version of OraNA.info
• Oracle PL/SQL package initialization
• Are you experiencing the blogger burnout?
• 6 Useful Links from Around the Web (2007-08-31)

I am integrating DBPrism CMS with Lucene Domain Index project by replacing free text searching functionality implemented with Oracle Text.
DBPrism CMS uses XMLDB as storage system for content author documents.
CMS documents are in Apache document-v20 format, and are stored using this annotated XSD schema.
Mainly we choose XMLDB to get these benefits:

• Documents are stored in an object relational table to get fast access in a relational way for some meta-data values.
• Content authors information is stored as CLOB because only is searched using free text way.
• XMLDB protocols handlers, content authors can use WebDAV or FTP to access to your documents instead of the CMS Front End for complex file system like operations such as copy, move, rename, backup operations.
• We can use SQL duality to traverse the XMLDB folder structure using resource_view.
• Get all the benefits of relational storage for storing non structured content, backup, concurrency access, etc.

As mentioned above a content authors information is stored in a CLOB columns and indexes with some free text searching functionality.
Now is indexed with Oracle Text with this script:

begin
ctx_ddl.create_preference('english_lexer','basic_lexer');
ctx_ddl.set_attribute('english_lexer','index_text','yes');
ctx_ddl.create_preference('spanish_lexer','basic_lexer');
ctx_ddl.set_attribute('spanish_lexer','index_text','yes');
ctx_ddl.create_preference('cms_docs_lexer','multi_lexer');
ctx_ddl.add_sub_lexer('cms_docs_lexer','default','english_lexer');
ctx_ddl.add_sub_lexer('cms_docs_lexer','spanish','spanish_lexer','es');
end;
/

create index cms_docs_xml_idx
on cms_docs c (value(c)) indextype is ctxsys.context
parameters('section group ctxsys.auto_section_group filter ctxsys.null_filter lexer cms_docs_lexer language column "XMLDATA"."lang"');

We index with Oracle Text using a top level attribute lang as language classifier to provide then the availability to choose between searching in Spanish or English document separately.
Now we will index this free text content using Lucene Domain Index with this definition:

create index cms_docs_xml_lidx
on cms_docs c (value(c)) indextype is lucene.LuceneIndex
parameters('LogLevel:ALL;IncludeMasterColumn:false;\
ExtraTabs:resource_view r;\
ExtraCols:extract(object_value,''//text()'') "text",\
substr(r.any_path,2,1) "path",\
extract(object_value,''/document/@lang'') "lang",\
extract(object_value,''//@href'') "links",\
extractValue(r.res,''/Resource/Owner'') "Owner",\
extractValue(r.res,''/Resource/ModificationDate'') "modTime";\
WhereCondition:extractValue(r.res,''/Resource/XMLRef'')=make_ref(cms_docs,object_id);\
FormatCols:path(UN_TOKENIZED),lang(UN_TOKENIZED)');

I will explain in detail each preferences when describing search use cases.

• Use case 1 - search for some free text under public or private folders for an specific language
  This use case basically use text field of Lucene Domain Index. Lucene Domain Index have a functionality called User Data Store which provides the availability of indexing virtually anything that you can extract in a simple or a joined query. At above example we will index information of the base table and information of resource_view (meta-data) joined by his document id (ExtraTabs and WhereCondition parameters). Note that we do not index a master column of the object relational table (his complete row) because we will choose using XMLDB extract function which information to index.
  For this use case we need text, path and Owner Fields. Note that for path information we need to know only if a document is at /home or /public path, so only store one char (h or p). Following Oracle Text and Lucene query syntax example:
  

  select /*+ domain_index_sort */ score(1),extractValue(c.object_value,'/document/header/title')
  from cms_docs c,resource_view r  where under_path(r.res,'/public',2)=1 and
  extractValue(r.res,'/Resource/XMLRef')=ref(c) and
  extractValue(r.res,'/Resource/Owner')='DBPRISM_ADMIN' and
  contains(c.object_value,
  'Here a CTXCAT XML syntax for querying CMS in english',1)>0
  order by score(1) DESC;
  

  Lucene Domain Index syntax:
  

  select /*+ domain_index_sort */
  lscore(1),r.any_path,extractValue(c.object_value,'/document/header/title')
  from cms_docs c,resource_view r
  where extractValue(r.res,'/Resource/XMLRef')=make_ref(cms_docs,object_id) and
  lcontains(c.object_value,
  'text:CMS AND -lang:es AND path:p AND Owner:DBPRISM_ADMIN',1)>0
  order by lscore(1) DESC;

  Executions plans for both queries
  Note that a highlighted text includes a double filter by execution as:
  "P"."SYS_NC00019$"=SYS_XMLCONV('DBPRISM_ADMIN',7,2,2,1,4,23) AND "XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/public',2)=1
  
  Highlighted filter on Lucene Domain Index syntax only includes a joined syntax with resource_view
  "XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"P"."XMLEXTRA","P"."XMLDATA"),'/',9999)=1
  Also note that we use DOMAIN_INDEX_SORT optimizer hint to prevent sorting of the result due Oracle Text and Lucene returns rows order by score descending as natural order.
  

  Note: In Lucene query syntax we use a negation of Spanish match field (-lang:es) to look for English documents, this negation includes document with lang="en" or without it which assume English.

• Use case 2 - paginate over a result set efficiently
  Obviously above queries need to be paginated in a real world application, to paginate above result using Oracle Text syntax we need to use Top-N syntax as follow:
  

  select * from (select rownum as ntop_pos, q.* from (
  select /*+ domain_index_sort */ score(1),extract(c.object_value,'/document/header/*')
  from cms_docs c,resource_view r  where under_path(r.res,'/public',2)=1 and
  extractValue(r.res,'/Resource/XMLRef')=ref(c) and
  extractValue(r.res,'/Resource/Owner')='DBPRISM_ADMIN' and
  contains(c.object_value,
  'Here a CTXCAT XML syntax for querying CMS in english',1)>0
  order by score(1) DESC
  ) q) where ntop_pos>=1 and ntop_pos<=10;
  

  But using Lucene Domain Index its not necessary, look:
  

  select /*+ domain_index_sort */
  lscore(1),r.any_path,extractValue(c.object_value,'/document/header/title')
  from cms_docs c,resource_view r
  where extractValue(r.res,'/Resource/XMLRef')=make_ref(cms_docs,object_id) and
  lcontains(c.object_value,
  'rownum:[1 TO 10] AND text:CMS AND -lang:es AND path:p AND Owner:DBPRISM_ADMIN',1)>0
  order by lscore(1) DESC;

  Using Top-N syntax the cost of the query is 35 but with Lucene Domain syntax still in 30. See executions plans for both queries:
  
  
  
  
• Use case 3 - filter a result set based a modification time of documents
  Usually users want to filter and order documents based on his modification time, that is the reason to include resource_view information inside Lucene Domain Index, any information pushed inside Lucene Index will be faster if then is used to filter and sort the result without the intervention of Oracle where clause, on Oracle 11g Text you can do a similar task, but is designed for relational columns, not for and XML content joined with resource view, see Composite Domain Index (CDI) in Oracle Text.
  Well here a filter and order by constraints for above queries:
  

  select * from (select rownum as ntop_pos, q.* from (
  select /*+ domain_index_sort */
  score(1),r.any_path,extractValue(c.object_value,'/document/header/title'),extractValue(r.res,'/Resource/ModificationDate')
  from cms_docs c,resource_view r
  where under_path(r.res,'/home',2)=1 and
  extractValue(r.res,'/Resource/XMLRef')=ref(c) and
  extractValue(r.res,'/Resource/Owner')='CMS_PUBLIC' and
  extractValue(r.res,'/Resource/ModificationDate') between
  TO_TIMESTAMP('01.01.2006:00:00:00','DD.MM.YYYY:HH24:MI:SS') and
  TO_TIMESTAMP('01.01.2007:00:00:00','DD.MM.YYYY:HH24:MI:SS') and
  contains(c.object_value,
  'Here a CTXCAT XML syntax for querying CMS',1)>0
  order by extractValue(r.res,'/Resource/ModificationDate') ASC
  ) q) where ntop_pos>=1 and ntop_pos<=10;

  Using Lucene Domain Index lcontains syntax:
  

  select /*+ DOMAIN_INDEX_SORT */
  extractValue(r.res,'/Resource/ModificationDate'),
  extractValue(value(c),'/document/header/title')
  from cms_docs c,resource_view r
  where extractValue(r.res,'/Resource/XMLRef')=make_ref(cms_docs,object_id) and
  lcontains(value(c),'rownum:[1 TO 10] AND text:CMS AND \
  -lang:es AND path:h AND Owner:DBPRISM_ADMIN AND \
  modTime:[20060101 TO 20070101]','modTime:ASC',1)>0;

  Note that like pagination use case, here filter by and order by information is injected at lcontains() operator reducing a number of rows returned by the Domain Index and avoiding the filter view. Oracle Text syntax have a cost 35 and Lucene syntax down to 29 because no lscore() is computed. Following screenshots which depicts the execution plans:
  
  This was the reason to store extractValue(r.res,''/Resource/ModificationDate'') "modTime during Lucene Domain Index build/update process.
  
• Use case 4 - search for documents which have possible broken links to a moved/deleted page
  DBPrism CMS have a functionality which advices in seconds which pages could have broken links when content authors moves, rename or delete CMS pages, not crawlers or spiders are required, only search his domain index. For example using Oracle Text syntax, if you rename a page named j1-Pages.html:
  

  select path(2) path
  from cms_docs c,resource_view r
  where contains(c.object_value,'j1 Pages within a@href',1)>1 and
  extractValue(r.res,'/Resource/XMLRef')=ref(c) and
  under_path(r.res,'/home/DBPRISM_ADMIN/cms/en',2)=1;
  

  
  and using Lucene Domain Index syntax:
  

  select /*+ DOMAIN_INDEX_SORT */ r.any_path path
  from cms_docs c,resource_view r
  where extractValue(r.res,'/Resource/XMLRef')=ref(c) and
  lcontains(value(c),'path:h AND -lang:es AND \
  Owner:DBPRISM_ADMIN AND \
  links:(+j1 +Pages)',1)>0;

  Both queries return a page /home/DBPRISM_ADMIN/cms/en/j2-CMS/Users/frontend/index.xml which have a link to j1-Pages.html, the magic here is to use within operator with Oracle Text or look for the field links which was added during index build/update time with all the links extracted from the document under a@href tag. For above queries Oracle Text cost down to 34 because no score and order by is required and Lucene Domain Index still in 29.
  

Have fun with Lucene and XMLDB.

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

In the last days I've been playing with XML files using SQL Developer and other tools and i noticed the following interesting change in the behavior of SQL Developer when it comes to exporting the data from a table in XML format:

until version 1.5.0 build 5338, the elements in the XML

I read The Daily WTF, well, daily. On Thursday last week, there was a good one on process. Essentially, the entire process had to be followed when an error occurred at boot. F1 would have solved the problem immediately...

My first job I never really got to put anything into production, so I wasn't real familiar with it. My second job, I was the lone ranger, so I did everything myself (though I did not do development in production). My last job however, was full of "The Process."

Rightfully so, especially in a large environment (i.e. more than 1 developer), though I think it was a bit overdone. And up until one of my failed deployments, the deployment itself was done through the Change Request (CR). What I mean by that, is that the code was attached to the CR itself. Since I attached a newer version, which had not been QA'd, well, you get the picture. We finally moved to a system whereby the DBAs actually deployed from our source control system...thankfully.

Now I'm in an environment that's a mix between the last job and the second to last. Everything is QA'd, but there isn't this whole process surrounding deployments...yet. Fortunately we're small enough to deal with it.

What's the point? I'm not sure.

Perhaps it's that I've learned more what not to do from The Daily WTF...

To access an SSL website using the utl_http package and PL/SQL is easy once you come to grips with the concept of a Wallet. The wallet stores all the encryption keys that the database needs to access the SSL site. But before looking at wallet config, lets review the PL/SQL code:

1. Declare some variables:
req utl_http.req;
resp utl_http.resp;
rw varchar2(32767);

2. Define the location of the wallet:
UTL_HTTP.SET_WALLET ( 'file:' || 'p:\wallet', 'mypassword' );

Where 'file:' is they keyword to state that the wallet is a file. 'p:\wallet' is the windows directory of the wallet. For unix, this might be '/u01/wallet'. 'mypassword' is the password required to open the wallet.

The disadvantage is that the password to the wallet is stored in clear text. Its recommended then that you create a wallet that only contains public keys from SSL sites, information which is publically available and will not pose a security risk. You can store multiple wallets in different locations if you are paranoid about security.

The disadvantage of wallets is that you need to know in advance each SSL site you will be accessing and extract its public key certificate. This isn't as flexible as a web browser, which does this for you.

3. Now access the SSL site. There are lots of ways of doing this, the following is just one:

req := utl_http.begin_request( 'https://www.amazon.com');
utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
resp := utl_http.get_response(req);

4. And then you can process the data that comes back using a simple loop:

loop
begin
rw := null;
utl_http.read_line(resp, rw, TRUE);
-- process rw
exception when others then exit;
end;
end loop;
utl_http.end_response(resp);

Wallet Configuration

5. To configure the wallet go into IE and retrieve the certificate of the SSL site (haven't been able to do this in firefox yet, which shows you that IE still has some uses).

You can either click on the certificate icon if it appears, or go to file-properties and retrieve it as follows.













Click on certificates to save it:


















Choose the copy to file option:


















And then choose the Base-64 encoded option to save it. Save it to a local directory.

Keep in mind the SSL certificates can be hierarchical, in that the certification of a site might be dependent on its parent (authorising) site also approving it.

So it might require extracting a couple of certificates to correctly authorise a site.

Once saved, create an Oracle wallet. There are similar options to the ones shown below in Linux and even if you only have apache installed. The java program is called: owm.cl

Go into the wallet and create a new wallet and specify its location as p:\wallet (or whatever directory is suitable for your database). Remember that the database has to be able to access the wallet, not your computer. So its best located on the same server as the database.


Then import the certificate you have just created.


















You can cut and paste the certificate in, but I found it useful to keep the certificates in the same directory as the wallet, so I have a physical trail of all the certificates.


Don't forget to save the wallet. You will find that the program creates an ewallet.p12 file in the directory. Leave it as is, this is an important file.




The certificate is now installed. Your PL/SQL program can now access the SSL site.

Today, 4th July, is Independence Day. I know this because Tech Republic has sent me an e-mail of special Independence Day offers. Only not that special, as the list seems to be the same list of offers they mailed for Father's Day. At least that made sense: after all, nothing says "You're the best dad in the world" quite like a gift of the Administrator's Guide to TCP/IP But what sort of patriot celebrates Independence Day by settling down with IT Professional's Guide to Policies and Procedures, Third Ed instead of fireworks, corn dogs and "light tasting" beer? Probably the sort of patriot who reads Log Buffer, so I'd better get on with it.

Staying with the Independence Day theme Curt Monash picks up on a humourous press release from data warehouse appliance vendor Dataupia. It's in the form of a Declaration of Data Independence and is probably funnier if you're American.

In the UK there have been rumours that the government is planning a giant database to track all our telephone and internet activity. On the BCS blog David Evans skips the ethical dimensions and looks at some of the practical considerations. However, the most pertinent point is made by Matthew in the comments: "How many days after the launch of the Big Brother Database ... do you think it will be before someone loses a disk or backup tape full of its contents?"

I'm just an Oracle person, which according to Max Kanat-Alexander means I suffer from Oracle-itis. Apparently symptoms include not being able to recognise the difference between NULL and an empty string, and thinking that one thousand items is a sensible limit for an IN clause. Kevin Closson posted a suitably withering response in his series on things which doth crabby make.

Anyway, doing the Log Buffer has given me - with the assistance of David Edwards and Google - with some exposure to other databases and other ways of doing things. For instance, Leo Hsu and Regina Obe wrote about inheriting tables in PostgreSQL. This is quite a neat idea.

"lets say you developed a timesheet app for an organization and each department insisted on having their own version of the app and each along with the basic fields needed to track some additional ones of their own. Then higher forces came in and said I need to know what everyone is doing, but I don't need to see all that other crap they keep track of.. Two options come to mind - create a bunch of views that union stuff together or institute a round-up-the-children-and-adopt-them program."

In Oracle the only option would be the view (possibly of the materialized kind). Off the top of my head I can't recall a case where I could have used this but it's definitely the sort of capability it's nice to have in your back pocket.

Another intriguing idea which has no parallel in Oracle is the MySQL Sandbox. This is a framework for testing features of different versions of MySQL without jeopardising our primary environment. Its developer, Giuseppe Maxia, The Data Charmer announces that MySQL Sandbox 2.0 has been released.

Regardless of which database you use performance is always an issue. Hubert Lubacewski has a offers a technique for identifying who is is trashing the performance of your PostgreSQL database. Arjen Lentz posts a MySQL script for finding useless indexes. The problems are the same, but the metrics are very different from the ones I'm used to in Oracle: "The query returns all indexes in a db where the cardinality is lower than 30% of the rows, thus making it unlikely that the server will ever use that index." Peter Zaitsev on the MySQL Performance Blog discusses the importance of identifying where the bottlenecks are. There's no point in a web developer tweaking CSS or JavaScript if the real problem lies in the database access layer: "get real numbers for your application before you decide." Ken Downs , the Database Programmer, has some general SQL advice on designing your web application's data model. Mr Oracle Index himself, Richard Foote, gives us his 3 Steps To Performance Tuning.

Transaction management is one of those things which varies considerably from product to product. Many Oracle practioners still think MySQL doesn't have transaction management. This is a canard Pythian's own Keith Murphy lays to rest by writing on transactions in InnoDB. In a related post covering transaction basics says he may write further pieces on "the major storage engines and their transactional characteristics". I presume he means the different MySQL storage engines but I think there's scope for a series which covers all the different database products.

For instance, nested transactions in SQL Server strikes me as asking for trouble. Which is why Kalen Delaney rails against the loss of the Sysprocesses.open_tran column in the SQL Server 2005 metadata.

"Sysprocesses contains a columns called open_tran which reflects the transaction nesting of each session. If a session issues four BEGIN TRAN commands, with no COMMITs or ROLLBACKs, their session will have an open_tran value in sysprocesses of 4. Any open_tran value greater than 0 might mean that a transaction is holding locks and blocking other processes, or it might be keeping the transaction log from being cleared. If you ever notice open_tran values in higher than 2 or 3, it's a pretty good indication that a developer doesn't know much about SQL Server transaction management."

Back to Pythian where Sheeri Cabra reviews MONyog, a GUI monitoring tool for MySQL. Overall she is favourably impressed: "MONyog is the best out-of-the-box GUI monitoring tool for MySQL that I have seen.” Although she does have reservations about its logging. Personally I think the name is a mistake: it sounds too much like something out of H P Lovecraft.

Some Oracle stuff now. Chen Shapira, the not-so-simple DBA puts her Statistics degree to good use by building a custom aggregation function that will return a random salary using Oracle's Data Cartridge extensibility features.

"The main challenge was to make the aggregation truly random....Suppose I have three rows. The way aggregation works, I first take two rows and flip a coin to pick one. Now I have a current value - and I have to take the third row and decide if I want to keep the current value or the new one. I can’t flip the coin again - because if the third row has 50% chance to be selected, this means the first and second rows only have 25% chance each. Not fair. So I need to give the third row 1/3 chance, and the current value 2/3."

On Oracle Base, Tim Hall demontrates the long-overdue support for case-sensitive passwords which Oracle have introduced in 11g.

Oracle has acquired the IKAN tool CWD4ALL and they're going to use it to give SQL Developer a decent modelling support capability. I would have though there would be more excitement about this in the blogosphere (certainly the ODTUG Designer listerserver has been cock-a-hoop) but only Dietmar Aust seems to have picked it up. Perhaps hardly anybody cares about modelling, in which case TOAD's marketshare is safe.

Last week I was judging abstracts for the UKOUG 2008 Conference, and there were three submissions for sessions on best practices in programming with ApEx. Alex Gorbachev (Pythian, again) shows why these talks are necessary with an example of poor SQL taken from the official Oracle documentation.

Lot's of people are asking questions. SQLDenis asks rhetorically Sybase IQ Is A Columnar Database, Why Should I Care?

"What does this mean? This mean that the data is stored in columns and not in rows. Inserts are slower that a traditional row based database but selects are many times faster (up to 50 times). The good thing about this technology is that the SQL looks the same, the only difference is that the data is stored in a different way."

Robert Hodges at The Scale-Out Blog wants to know, what's your favorite database replication feature? Call me shallow, but it's not a topic to which I'm given much thought. I can tell you my five all-time top favourite cover versions instead.

Meanwhile Jon Emmons poses the question Ever wonder what your DBAs really do?. It turns out there's more to the job than drinking coffee, swearing at developers and losing the backup tapes. Who knew?

Of course, DBAs have plenty of reasons to swear at developers. In Extreme Makeover - Database Edition CrazyDBA shows us his scars from a SQL Server version upgrade:

"Saturday morning, migrating from "old prod" to "new prod". We finish up during the afternoon. On Sunday evening (yes, more than 24 hours later), we are notified that the system is not performing properly. We double check things on our end and everything seems to be working, well, except that the duration for some queries have gone from three seconds on "old prod" to twelve minutes on "new prod". Ouch. Our team investigates a bit further and escalates the issue to the (sleeping) onsite team, who pick up their research on Monday morning.

What do we do first on Monday morning? Well, we go to the new test system and run the query. It takes eight minutes. Turns out development is slow as well. Surely someone noticed this during testing, right?"

Er, wrong.

Rick Heiges asks It's Q3 - where is SQL Server 2008? To make him happy (and Mr CrazyDBA even happier), according to Jason Massie there's a rumour that SQL Server 2008 is due to ship next week (or this week if you're reading after the weekend).

From the new releases to some ancient history. Willie Favero comments on an article about DB2's 25th birthday from Information Week. It's interesting to see what counted as a new feature in those days: "You could dynamically add tables or change tables without taking the system down. It doesn't take much imagination now to see this was a huge leap forward," recalled Don Haderle, chief architect of DB2.

Back to the future. Over at the IT Toolbox Lewis Cunningham has his head in the clouds. Or rather Cloud. This is a neat summation of all the main players in Cloud databases. Cloud computing is a rather attractive idea, but I think there is some way to go before it is a practical solution for business. Web access is still far from pervasive or guaranteed, and as the Register pointed out this week, there are still some kinks in the business model to iron out. Lewis describes Amazon as the 800lb gorilla in the cloud space (stratosphere?) and the Register also has a good piece explaining Amazon's interest in the technology.

In his Data Migration blog Johny Morris (no, not that one) invites us to consider the benefits of meetings, in this case Data Quality Rules meetings: "Use them not just instrumentally to solve the issues in front of you but also to build the team that jointly will have uncover all the knowledge hidden in the organisation." Exactly the sort of benefit which will be hard to realise when we are all working in the Cloud and never visit the office.

Finally, nothing to do with databases but I'm sure relevant to us all (at least those who are still office bound), Suzanne Thornberry at Tech Republic writes about the health risks IT professionals run. These include such things as eye strain, bobblehead syndrome and seated immobility thromboembolism (SIT), which is like DVT only worse. So stop reading this and go do something more healthy instead!

Postscript

The Log Buffer is a community activity facilitated by Pythian. Find out more.

I just met with Amanda Laucher at the Blue Moose Cafe in Morgantown, WV.

I don't live here; she doesn't live here; we weren't at a conference; we didn't expect to meet. We were both in town on family visits, and both seeking out an internet fix. (I'm visiting my father-in-law at Ruby Hospital - but writing Python there, hah.)

She's a serious geek-community addict like me - she's organizing the DevLink Bus. She gave me great advice for PyOhio and tipped me off to other upcoming events, and I gave her some ideas from PyCon. It was great!

That's just really cool. Someday, when wireless access is ubiquitous, geekish nomads will no longer concentrate themselves in a handful of WiFi hotspots, and unforeseeable meetings like these will become more rare. That will be the downside of the future.

It is not the first time I see, on forums, questions about how to restrict, at runtime, the number of characters a user can enter in a text item.
The solution consists in understanding that every standard Forms widget has an associated PJC that can be handled by the Set_Custom_Property() built-in..

[...]

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may see this error when invoking procedure DBMS_LOB.LOADCLOBFROMFILE as follows:
create or replace
FUNCTION LoadTempClobFromFile (
p_Dir IN VARCHAR2,
p_FileName IN VARCHAR2,
p_csid IN INTEGER)
RETURN CLOB IS

l_srcFile BFILE := BFILENAME(p_Dir, p_FileName);
l_tmpClob

H20UUP2

First to figure out wins absolutely nothing but praise!

I recently commented on a blog that's fed through OraNA. Actually, I left one, received a comment back by the author, and then commented again. The author has editorial review before posting comments publicly.

I have no problem with that, I practice it. Fortunately, I haven't had the tough choice of whether to publish or not. It can't be easy.

That said, my second comment, in response to his response to my initial comment (still with me? ;) was never published. This may have been a simple oversight (it was yesterday), but I have no idea (yet anyway).

I blog because I crave the challenge. I want people to tell me I'm completely full of sh*t. Seriously. As long as it doesn't get personal, I'm all for discussion.

Seriously, I don't like triggers. But if you have to maintain them, you might as well make the best of it.

While trying to figure out my problem the other day, I ran across Compound Triggers. I hadn't read about it in the 11g New Features guide, but since I don't use triggers, I wasn't sure if it was new or not. Apprently it is...

In essence, you can combine multiple triggers into one. I won't go into the gory details (because I don't know the gory details), but I will provide the example from the docs for your perusal.

CREATE TRIGGER compound_trigger
  FOR UPDATE OF sal ON emp
    COMPOUND TRIGGER

  -- Declaration Section
  -- Variables declared here have firing-statement duration.
  threshold CONSTANT SIMPLE_INTEGER := 200;

  BEFORE STATEMENT IS
  BEGIN
    ...
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    ...
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    ...
  END AFTER EACH ROW;
END compound_trigger;
/

At the very minimum (if you have to use them), you might as well combine them into one and save a bit on maintenance/debugging!

I can't believe it...

Oracle SQL-Developer is announcing modeling-support in an upcoming release.

Here are the top features:

Database Data Modeling to support:

    * Designing logical Entity Relation Diagrams
    * Building physical schema designs
    * Generating and executing DDL scripts
    * Reverse and forward engineering of existing relational data structures
    * Data domain administration
    * Naming standardization
    * Model formatting (font, colors)
    * Importing data models from CA Erwin and Oracle Designer
    * Compare and merge facilities
    * Multiple database support
          o Oracle Database
          o DB2 (Mainframe & UDB)
          o Microsoft SQL Server
    * Logical and physical multi-dimensional modeling
    * Object relational Data Types
    * Spatial Modeling
    * Multi-level logical and physical design environments
    * Model validation rules
    * Offline (file based) and Repository based modeling

what a wonderful news. The old designer is not the best choice today, since it got the codefreeze years ago.
Gerd

Sun recently announced JavaFX, "a new family of Java technology-based products that will help content providers create and deploy rich Internet applications (RIA)".  They also announced "a new scripting language, JavaFX Script
[which] gives Java developers the power to quickly create content-rich
applications for the widest variety of clients, including mobile
devices, set-top boxes, desktops, even Blu-ray discs."

We've always wanted to port JE to Java ME, but for various reasons, never gotten around to it.  JavaFX Script opens the possibility of using JE in an embedded environment and so I thought it would be interesting to verify that it all works ok.  It makes perfect sense to have a lightweight, persistence, ACID storage engine in a JavaFX environment, and since Java FX Script is one of the new class of scripting languages which sit on top of a JVM (e.g. JRuby, Groovy, etc.), it would have surprised me if JE didn't play well with Java FX Script.  Indeed, it was a pretty painless task to get a simple example running.

Here's the sample code:

import java.io.File;
import java.lang.System;
import com.sleepycat.je.Cursor;
import com.sleepycat.je.Database;
import com.sleepycat.je.DatabaseConfig;
import com.sleepycat.je.DatabaseEntry;
import com.sleepycat.je.Environment;
import com.sleepycat.je.EnvironmentConfig;
import com.sleepycat.je.JEVersion;
import com.sleepycat.je.OperationStatus;
import com.sleepycat.je.Transaction;

doSomeJEOps();

operation doSomeJEOps() {
    var theDb : Database = initJE();
    var txn : Transaction = null;

    initJE();

    txn = theDb.getEnvironment().beginTransaction(null, null);
    for (i in [0..9]) {
	put(theDb, txn, "foo{i}", "bar{i}");
    }
    txn.commit();

    System.out.println("Retrieval by key: foo0 => {get(theDb, null, 'foo0')}");
    var key : DatabaseEntry = new DatabaseEntry();
    var val : DatabaseEntry = new DatabaseEntry();
    var cursor : Cursor = theDb.openCursor(null, null);
    System.out.println("Retrieval by cursor");
    while (cursor.getNext(key, val, null) == OperationStatus.SUCCESS) {
	System.out.println
	    ("{new String(key.getData())} => {new String(val.getData())}");
    }

    closeJE();
}

operation initJE() {
    var envConfig = null;
    var env = null;
    var dbConfig = null;
    var db = null;
    envConfig = new EnvironmentConfig();
    envConfig.setAllowCreate(true);
    envConfig.setTransactional(true);
    env = new Environment(new File("c:/temp/blort"), envConfig);
    dbConfig = new DatabaseConfig();
    dbConfig.setAllowCreate(true);
    dbConfig.setTransactional(true);
    db = env.openDatabase(null, "foo", dbConfig);
    return db;
}

operation closeJE() {
    theDb.close();
    theDb.getEnvironment().close();
}

operation put(theDb:Database, txn:Transaction, theKey:String, theVal:String) {
    var keyDE : DatabaseEntry = new DatabaseEntry(theKey.getBytes());
    var valDE : DatabaseEntry = new DatabaseEntry(theVal.getBytes());
    var status = theDb.put(txn, keyDE, valDE);
    if (status <> OperationStatus.SUCCESS) {
	throw status;
    }
}

operation get(theDb:Database, txn:Transaction, theKey:String) {
    var keyDE : DatabaseEntry = new DatabaseEntry(theKey.getBytes());
    var valDE : DatabaseEntry = new DatabaseEntry();
    var status = theDb.get(txn, keyDE, valDE, null);
    if (status <> OperationStatus.SUCCESS) {
	throw status;
    }
    return new String(valDE.getData());
}

and the output:

compile thread: Thread[AWT-EventQueue-0,6,main]
compile 0.11
Retrieval by key: foo0 => bar0
Retrieval by cursor
foo0 => bar0
foo1 => bar1
foo2 => bar2
foo3 => bar3
foo4 => bar4
foo5 => bar5
foo6 => bar6
foo7 => bar7
foo8 => bar8
foo9 => bar9
init: 1.332