Oracle 11G XE Installation error: Database Configuration failed

While running the oracle configuration after installation:

/etc/init.d/oracle-xe configure

 the following error specified:

Database Configuration failed. Look into /u01/app/oracle/product/11.2.0/xe/config/log for details

After looking inside the log file /u01/app/oracle/product/11.2.0/xe/config/log/CloneRmanRestore.log you can see an error which looks like:

 ORA-00119: invalid specification for system parameter LOCAL_LISTENER ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=oralinux)(PORT=1521))'

The hostname "oralinux" cannot be found because it is not registered inside the dns-server. You can simply add an /etc/hosts file:

127.0.0.1 oralinux

After saving the file, rerun "/etc/init.d/oracle-xe configure" and everything should be fine.

DBVisualizer: ERROR: column c.reltriggers does not exist

DBVisualizer is one of my favourite database development tools because it's plattform and database independent and is really intuitive.

Since I updated my PostgreSQL Database Instance to V9 I get an error in DBVisualizer due to missing relations in the database.

It is possible to edit the statements DBVisualizer executes for displaying tables and data. Go to the Application folder and Right-Click on the DBVisualizer.app and Choose "Show package contents":

Then step down to the following directory

Now open the postgresql8.xml file and search and replace:

Search: (c.reltriggers > 0)

Replace: (/*c.reltriggers*/ 0 > 0)

Save and close the file. After a restart of the DBVisualizer everything should work fine.

DHL Tracking with PHP v0.1

The german post (packet service: DHL) provides a XML webservice which gives information about the delivery state. It just returns the current state and not the whole history, so I've written a short PHP script which is pulling the state from the webservice and enters the data to a MySQL database.
It compares the current state and the last inserted state and sends an email when the status is saved.
It allows me to stay informed.

Online-Shop like Amazon refreshes their state not live so newer information could be available before Amazon ist displaying it.

From time to time I will extend the script with Google Maps support (the webservice cotains localization data) and hopefully some other delivery services like UPS etc.

Tell me if you have any suggestions for future versions.

Create the tables in a MySQL Database:


CREATE TABLE `delivery_type` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `identifier` varchar(255) DEFAULT NULL,
  `function_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `delivery_type` (`id`,`identifier`,`function_name`)
VALUES
(1, 'dhl', 'dhl_tracking');


CREATE TABLE `packet_tracking` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_delivery_type` bigint(20) NOT NULL,
  `identifier` varchar(255) NOT NULL,
  `id_status` bigint(20) DEFAULT NULL,
  `creation_date` bigint(20) DEFAULT NULL,
  `last_action` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tracking_event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_packet` bigint(20) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `recipient` varchar(255) DEFAULT NULL,
  `event_timestamp` bigint(20) DEFAULT NULL,
  `status_text` varchar(255) DEFAULT NULL,
  `next_status_text` varchar(255) DEFAULT NULL,
  `standard_event_code` varchar(2) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


And the PHP scripts:

connect.php

$vHost = 'hostname';
$vPort = 'port';
$vUser = 'username';
$vPassword = 'password';
$vDatabase = 'database-name';

$vConn = mysql_pconnect($vHost . ':' . $vPort,
        $vUser,
        $vPassword);
mysql_select_db($vDatabase, $vConn);


tracking.php
<?php

include('connect.php');

function dhl_tracking(
   $aTrackingNumber, 
   $aPacketId = 1, 
   $aLastAction = null){
   
   
 global $vConn;
 
 $vRootMail = "mail@mail.de";
 
 $vData  = '<?xml version="1.0" encoding="ISO-8859-1" ?>';
 $vData .= '<data appname="nol-public" password="anfang" 
   request="get-status-for-public-user" 
   language-code="de">';
 $vData .= '  <data 
   piece-code="'.$aTrackingNumber.'"></data>';
 $vData .= '</data>';

 // URL bauen und File hohlen
 $vXml = simplexml_load_file(sprintf(
  'http://nolp.dhl.de/nextt-online-public/direct/nexttjlibpublicservlet?xml=%s', 
  $vData));
 
 // FALSE, if Syntax or HTTP Error
 if ($vXml === false) return false;

 $vReturn = array();
 
 // coverts xml to array
 foreach ($vXml->data->data->attributes() 
    as $vKey => $vValue) {
  $vReturn[$vKey] = (string) $vValue;
 }

 $vActionTime = strptime(
  $vReturn['last-event-timestamp'], 
  '%d.%m.%y %H:%M');

 $vActionTime = mktime(
  $vActionTime['tm_hour'], 
  $vActionTime['tm_min'], 
  0, 
  $vActionTime['tm_mon']+1, 
  $vActionTime['tm_mday'], 
  1900+$vActionTime['tm_year']);

 if($aLastAction === null || $vActionTime 
   > $aLastAction){
  
  mysql_query("INSERT INTO tracking_event values (
   null, 
   $aPacketId, 
   '" . mysql_real_escape_string(
     $vReturn['event-location']) . "', 
   '" . mysql_real_escape_string(
     $vReturn['event-country']) . "', 
   '" . mysql_real_escape_string(
     $vReturn['recipient-name']) . "', 
   " . $vActionTime . ", 
   '" . mysql_real_escape_string(
     $vReturn['status']) . "', 
   '" . mysql_real_escape_string(
     $vReturn['status-next']) . "', 
   '" . mysql_real_escape_string(
     $vReturn['standard-event-code']) . "', 
   '" . mysql_real_escape_string(
     $vReturn['product-name']) . "')", 
   $vConn);

   $vStatus = (int)(
    $vReturn['delivery-event-flag'] == '1'
    );
   mysql_query("UPDATE packet_tracking set 
    last_action = $vActionTime, 
    id_status = $vStatus 
    where id = $aPacketId", $vConn);
   
   $vMessage = var_export($vReturn,true);
   mail($vRootMail, 
    "TRACKING-TOOL: New Event Found", 
    $vMessage);
 }
 
 return $vReturn;
}

function processAllOpenTrackings(){
 global $vConn;
 
 $vResult = mysql_query("SELECT pt.*, dt.function_name 
  FROM packet_tracking pt, delivery_type dt 
  where pt.id_delivery_type = dt.id and pt.id_status = 0", 
  $vConn);

 while ($vRow = mysql_fetch_array($vResult, MYSQL_ASSOC)) {
  call_user_func(
   $vRow['function_name'], 
   $vRow['identifier'], 
   $vRow['id'], 
   $vRow['last_action']);
 }
}

processAllOpenTrackings();
?>

Just create a new set in the 'packet_tracking' table and call the script. I have entered a Cron Entry and call the script via wget. Files are attached...

Tracking v0.1

Using Oracle Text Search

Oracle offers with "Oracle Text" a strong and highly adjustable Text Search to their database customers. We have used Oracle for years and 2 years ago we needed a Text-Search which has the ability to create a better sort order for search results.

Requirement: It should be possible to specify different emphases in the search query.

1. Object definition
A 'Product' in our case has the following attributes:

1. Title
2. Alternate title
3. Feature
1. Feature-Title
2. Feature-Content
4. Tags
5. External links
6. Link description

2. Table defintion
We created a help table in the database. All tags assigned to the product are written space-delimited into the PRODUCT_TAG column, the same for

• PRODUCT_ALTERNATE_TITLE 
• PRODUCT_FEATURE
• FEATURE_CONTENT
• EXTERNAL_LINK
• LINK_DESCRIPTION

The CREATE TABLE statement looks like:


CREATE TABLE PRODUCT (
    ID NUMBER(38,0) NOT NULL,
    PRODUCT_TITLE VARCHAR2(256),
    PRODUCT_ALTERNATE_TITLE clob,
    PRODUCT_DESCRIPTION VARCHAR2(4000),
    PRODUCT_TAG clob,
    PRODUCT_FEATURE clob,
    FEATURE_CONTENT clob,
    EXTERNAL_LINK clob,
    LINK_DESCRIPTION clob
);


3. Index creation
First of all we need to grant the ctxapp to our user:

grant ctxapp to MY_USER;

Then we need to create a 'Lexer' to use the extended possibilities Oracle Text delivers:

"base-letter conversion, composite word indexing, case-sensitive indexing and alternate spelling for whitespace-delimited languages that have extended character sets."

begin
    ctx_ddl.create_preference(
        'german_lexer','basic_lexer'
    );
    ctx_ddl.set_attribute(
        'german_lexer','composite','german'
    );
    ctx_ddl.set_attribute (
        'german_lexer', 'SKIPJOINS', '-'
    );
    ctx_ddl.create_preference (
        'german_wordlist', 'BASIC_WORDLIST'
    );
    ctx_ddl.set_attribute (
        'german_wordlist', 'STEMMER', 'GERMAN'
    );
end;
/


We have to create a preference for the index so that we can create an index over more than one column (MULTI_COLUMN_DATASTORE).


begin
    ctx_ddl.create_preference(
        preference_name => 'PRODUCT_DATA_STORE',
        object_name => 'MULTI_COLUMN_DATASTORE'
    );
    ctx_ddl.set_attribute(
        preference_name => 'PRODUCT_DATA_STORE',
        attribute_name => 'COLUMNS',
        attribute_value => '
            PRODUCT_TITLE,
            PRODUCT_DESCRIPTION,
            PRODUCT_ALTERNATE_TITLE,
            PRODUCT_TAG,
            PRODUCT_FEATURE,
            FEATURE_CONTENT,
            EXTERNAL_LINK,LINK_DESCRIPTION'
    );
end;
/

The creation of the index looks like:


CREATE INDEX PRODUCT_FT_IDX ON PRODUCT(PRODUCT_TITLE)
    INDEXTYPE IS CTXSYS.CONTEXT
    PARAMETERS('
        datastore PRODUCT_DATA_STORE
        section group CTXSYS.AUTO_SECTION_GROUP
        LEXER german_lexer
        WORDLIST german_wordlist
        STOPLIST CTXSYS.EMPTY_STOPLIST
        SYNC (ON COMMIT)');


The 'index column' you have to specify is the first column from our MULTI_COLUMN_DATASTORE preference (here: PRODUCT_TITLE). The 'german_lexer' we created before handles all the special character stuff for us.

The 'german_wordlist' enables some extra functionality provided by oracle text:

"Use the wordlist preference to enable the query options such as stemming, fuzzy matching for your language. You can also use the wordlist preference to enable substring and prefix indexing, which improves performance for wildcard queries with CONTAINS and CATSEARCH."

The stoplist parameter specifies which words ahouldn't be indexed by Oracle; We don't want it so we specify a empty stoplist (CTXSYS.EMPTY_STOPLIST).

4. Search Query
Now the the index is ready and waits for better queries to use it. Our query looks like this:


SELECT
    score(1) as SCORE_VALUE,
    ID_PRODUCT ,
    PRODUCT_TITLE,
    PRODUCT_DESCRIPTION
FROM PRODUCT
WHERE CONTAINS(PRODUCT_TITLE,'
    (((autos) within (PRODUCT_TITLE))*10
    ACCUM (($autos) within (PRODUCT_TITLE))*9
    ACCUM ((%autos%) within (PRODUCT_TITLE))*2)
    ACCUM (((autos) within (PRODUCT_DESCRIPTION))*9
    ACCUM (($autos) within (PRODUCT_DESCRIPTION))*8
    ACCUM ((%autos%) within (PRODUCT_DESCRIPTION))*2)
    ACCUM (((autos) within (PRODUCT_ALTERNATE_TITLE))*8
    ACCUM (($autos) within (PRODUCT_ALTERNATE_TITLE))*7
    ACCUM ((%autos%) within (PRODUCT_ALTERNATE_TITLE))*2)
    ACCUM (((autos) within (PRODUCT_TAG))*7
    ACCUM (($autos) within (PRODUCT_TAG))*6
    ACCUM ((%autos%) within (PRODUCT_TAG))*2)
    ACCUM (((autos) within (PRODUCT_FEATURE))*6
    ACCUM (($autos) within (PRODUCT_FEATURE))*5
    ACCUM ((%autos%) within (PRODUCT_FEATURE))*1)
    ACCUM (((autos) within (FEATURE_CONTENT))*5
    ACCUM (($autos) within (FEATURE_CONTENT))*4
    ACCUM ((%autos%) within (FEATURE_CONTENT))*1)
    ACCUM (((autos) within (EXTERNAL_LINK))*4
    ACCUM (($autos) within (EXTERNAL_LINK))*3
    ACCUM ((%autos%) within (EXTERNAL_LINK))*1)
    ACCUM (((autos) within (LINK_DESCRIPTION))*3
    ACCUM (($autos) within (LINK_DESCRIPTION))*2.5
    ACCUM ((%autos%) within (LINK_DESCRIPTION))',1)>0


Now it is possible to adjust the criterias:

1. Is the searchstring found as the 'complete phrase': (autos) within (COLUMN)
2. Is the baseword of the searchstring found as the 'complete phrase': ($auto) within (COLUMN); [This means: Searching for 'cars' should also find all occurrences of 'car']
3. Is the searchstring anywhere inside the text: (%autos%) within (COLUMN)

For all of this possiblities for every column we now can adjust the index score value: ((autos) within (PRODUCT_TITLE))*10. Oracle Text uses this value to calculate the score value (there are more criteria like: amount of occurrences).

Now we can sort the resultset after score(1) or SCORE_VALUE to get a better sort order for the search.