Experiences #6 - Loading a database into itself

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.

Count The K’s

oraclenerd | Jul 8, 2008 20:00 -0600
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. ;)

XML Goodness in SQL - Part 1

Eddie Awad | Jul 8, 2008 13:20 -0600

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

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




*/* use a SUM function but save the detail from each row */*

M.Moore | Jul 8, 2008 11:40 -0600

/* use a SUM function but save the detail from each row */

/* this technique could be used instead of procedural logic which

   manually sums the desired column and then tests if the grouping

   column value is changing. There is no need for

   'save previous values'

   or 'if first_time' or 'if last_time' code logic.

  

CREATE OR REPLACE TYPE detail_rec AS OBJECT (

   source_table_name   VARCHAR2 (30),

   source_table_key    NUMBER (10)

);

CREATE TYPE detail_table AS

       TABLE OF detail_rec;

*/

DECLARE

   CURSOR tst

   IS

      SELECT   purch_order,

               SUM (dollars),

               CAST

                  (COLLECT (

                     detail_rec (tname,vendor_key

                           )

                   ) AS detail_table)

          FROM (SELECT 1 purch_order,

                       3 dollars,

                       435235 vendor_key,

                       'tab11' tname

                  FROM DUAL

                UNION ALL

                SELECT 1 purch_order,

                       8 dollars,

                       123452 vendor_key,

                       'tab2' tname

                  FROM DUAL

                UNION ALL

                SELECT 2 purch_order,

                       4 dollars,

                       383738 vendor_key,

                       'tab3' tname

                  FROM DUAL

                UNION ALL

                SELECT 2 purch_order,

                       7 dollars,

                       433738 vendor_key,

                       'tab2' tname

                  FROM DUAL

                UNION ALL

                SELECT 2 purch_order,

                       5 dollars,

                       387118 vendor_key,

                       'tab8' tname

                  FROM DUAL)

      GROUP BY purch_order;

   v_purch_order         NUMBER;

   v_dollars             NUMBER;

   a_detail_table          detail_table;

   a_detail_record         detail_rec;

   list_of_keys          VARCHAR2 (4000);

   list_of_table_names   VARCHAR2 (4000);

BEGIN

   OPEN tst;

   LOOP

      list_of_keys := NULL;

      list_of_table_names := NULL;

      FETCH tst

       INTO v_purch_order,

            v_dollars,

            a_detail_table;

      IF tst%NOTFOUND

      THEN

         EXIT;

      END IF;

      -- loop throught the collection and build a string so that

      -- we can display it and prove that it works

      FOR cur1 IN (SELECT source_table_key,source_table_name

                     FROM TABLE (a_detail_table))

      LOOP

         list_of_keys := list_of_keys || ',' || cur1.source_table_key;

         list_of_table_names :=

                 list_of_table_names || ',' || cur1.source_table_name;

                

      -- based on the value of the summed column, you can do

      -- something with each detail. For Example:

      --   if v_dollars > 12 then

      --     UPDATE VENDOR SET paid_status = 'P'

      --          where vendor_key = cur1.source_table_key;

      --   end if;

      END LOOP;

      DBMS_OUTPUT.put_line (   'PO-> '

                            || TO_CHAR (v_purch_order)

                            || '  $ total-> '

                            || TO_CHAR (v_dollars)

                            || '   vendorkeys-> '

                            || SUBSTR (list_of_keys, 2)

                            || '   tablenames-> '

                            || SUBSTR (list_of_table_names, 2));

   END LOOP;

   CLOSE tst;

END;

/* the result of running this will give ...

PO-> 1  $ total-> 11   vendorkeys-> 435235,123452   tablenames-> tab11,tab2

PO-> 2  $ total-> 16   vendorkeys-> 383738,387118,433738   tablenames-> tab3,tab8,tab2

*/

XMLDB and Lucene Domain Index

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.

Interesting change in the XML export format of Oracle SQLDeveloper

Byte64 | Jul 7, 2008 05:50 -0600
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

Process

oraclenerd | Jul 6, 2008 22:10 -0600
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...

Tip #5 - Using UTL_HTTP and SSL

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.

Lexers and privileges errors


I got in a hurry while building an Oracle Text sample case. It triggered an error because of a missing privileges. When I google’d it, I found there wasn’t a clean answer. Here’s a quick answer and the two dependencies.

1. You must have choosen one of the correct lexer (they’re found in the Oracle Text manual). They are only 10 in the list. Western European languages are typically managed by the BASIC_LEXER or MULTI_LEXER. The former for a single language and the latter for mixed languages. When you create a CONTEXT index.

2. You must have the CTXAPP role granted to your user.

The choice of a wrong lexer is illustrated in the Oracle forum. This illustrates the error stack raised when you don’t have the right role privileges.

Create a CONTEXT index without the CTXAPP role:

CREATE INDEX regexp_index ON sample_regexp(story_thread)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS(’LEXER english_lexer STOPLIST ctxsys.default_stoplist’);

It creates a corrupt index but raises the following error stack:

CREATE INDEX regexp_index ON sample_regexp(story_thread)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: english_lexer
ORA-06512: at “CTXSYS.DRUE”, line 160
ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 365

The word “preference” indicates the missing privilege. Grant the user the CTXAPP, drop the index, and recreate it. Basically, the CTXAPP runs an external procedure that reads the Inxight lexers. They’re found in directories within the $ORACLE_HOME/ctx/data directory.

Reading a CTX Index STOPLIST


If you want to know what’s in the default or custom STOPLIST, you can use this set of tools to find, format, and output the words in a STOPLIST. This example requires you to unlock the CTXSYS account. Then, you must grant the SELECT privileges on the CTX_STOPWORDS table to your user.
 
You create a package that contains an associative array that is indexed by a single alphabetic character:

CREATE OR REPLACE PACKAGE tools IS
  TYPE list IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(1);
END tools;
/

You create a function to organize the list of excluded words alphabetically in an associative array:

CREATE OR REPLACE FUNCTION get_stoplist (stoplist VARCHAR2) RETURN TOOLS.LIST IS
  — Define and declare collection.
  TYPE alpha_key IS TABLE OF CHARACTER;
  code ALPHA_KEY := alpha_key(’A',’B',’C',’D',’E',’F',’G',’H',’I',’J’
                             ,’K',’L',’M',’N',’O',’P',’Q',’R',’S',’T’
                             ,’U',’V',’W',’X',’Y',’Z');
  — Define two collections.
  stopword TOOLS.LIST;
  — Define cursor.
  CURSOR c (stoplist VARCHAR2) IS
    SELECT spw_word
    FROM ctxsys.ctx_stopwords
    WHERE spw_stoplist = UPPER(stoplist)
    ORDER BY spw_word;
BEGIN
  — Initialize reserved and key word collections.
  FOR i IN 1..code.LAST LOOP
    FOR j IN c(stoplist) LOOP
      IF code(i) = UPPER(SUBSTR(j.spw_word,1,1)) THEN
        IF stopword.EXISTS(code(i)) THEN
          stopword(code(i)) := stopword(code(i)) || ‘, ‘ || j.spw_word;
        ELSE
          stopword(code(i)) := j.spw_word;
        END IF;
      END IF;
    END LOOP;
  END LOOP;
  RETURN stopword;
END get_stoplist;
/

You create a function to format the output at the SQL*Plus prompt:

CREATE OR REPLACE FUNCTION format_list (list_in TOOLS.LIST) RETURN BOOLEAN IS
  — Declare control variables.
  current VARCHAR2(1);
  element VARCHAR2(2000);
  status BOOLEAN := TRUE;
BEGIN
  — Read through an alphabetically indexed collection.
  IF list_in.COUNT > 0 THEN
    current := list_in.FIRST;
    element := list_in(current);
    WHILE current IS NOT NULL LOOP
      dbms_output.put_line(current||’ ‘||element);
      current := list_in.NEXT(current);
      — Prevent reading beyond the list with a NULL index value.
      IF current IS NOT NULL THEN
        element := list_in(current);
      END IF;
    END LOOP;
  END IF;
  RETURN status;
END format_list;
/

You call the formatted output using an anonymous block:

BEGIN
  IF format_list(get_stoplist(’default_stoplist’)) THEN
    NULL;
  END IF;
END;
/

You’ll see the following output:

A a, all, almost, also, although, an, and, any, are, as, at
B be, because, been, both, but, by
C can, could
D d, did, do, does
E either
F for, from
H had, has, have, having, he, her, here, hers, him, his, how, however
I i, if, in, into, is, it, its
J just
L ll
M Mr, Mrs, Ms, me, might, my
N no, non, nor, not
O of, on, one, only, onto, or, our, ours
S s, shall, she, should, since, so, some, still, such
T t, than, that, the, their, them, then, there, therefore, these, they, this, those, though, through, thus, to, too
U until
V ve, very
W was, we, were, what, when, where, whether, which, while, who, whose, why, will, with, would
Y yet, you, your, yours

 

There are permutations that would let you see XHTML formatted output. They’re left to your creativity.

Log Buffer: #104: a Carnival of the Vanities for DBAs

APC | Jul 4, 2008 10:50 -0600
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.

serendipity

Catherine | Jul 3, 2008 15:40 -0600
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.

Forms : How to dynamically set the MAX LENGTH in a Text item

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..

[...]

ORA-22289: cannot perform LOADFROMFILE operation on an unopened file or LOB

Byte64 | Jul 3, 2008 06:40 -0600
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

Isn’t it cool…..

venzi | Jul 3, 2008 05:40 -0600

… when your team mate means that you and the other one should be in a different team because he is doing all the work and the other one and myself are just DBAs? And the best of all that: The truth it totally the opposite. He is playing around on one feature since 6 weeks with little progress and I do all the other stuff that comes. Isn’t it also funny that everyone who needs something from the team speaks to me and not to him. Strange when he is doing all the work. Hm maybe I should just do the DBAs stuff, would be funny when really all work goes to him.

I’m really pissed on at the moment. I mean why the hell am I doing what I’m doing? If I’m just a DBA at his point of view I should just be a DBA. Only bad thing then is that I know that nothing will happen anymore in the team and that is something I don’t want. I like the team and the goals of it. But fact is: The motivation for this week is over, and I guess also for the next one.

Funny License Plate

oraclenerd | Jul 2, 2008 19:40 -0600

H20UUP2
First to figure out wins absolutely nothing but praise!

Editorial Control Over Comments

oraclenerd | Jul 2, 2008 19:40 -0600
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.

Compound Triggers

oraclenerd | Jul 2, 2008 19:40 -0600
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!

New Oracle Designer ?

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

Berkeley DB Java Edition and JavaFX

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 &lt;> 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 &lt;> 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