18. Februar 2011

ASP.NET: Consuming a PHP WSDL SOAP Webservice

After starting Visual Studio we create a new Visual Basic ASP.NET Web Application project:


After it opens up we right-click on the project name in the "Solution Explorer" and choose "Add Web Reference..."


Now we can enter the URL of the WSDL file 'http://customer-ws.tld/customer-ws_Customers.wsdl' and press 'Enter'. Visual Studio fetches all the information provided by the xml file and shows up all the data:


We enter a "Web reference name" (I choose 'WSDLWebService') and click on "Add Reference". Now we can see the entered Web Reference in the "Solution Explorer":


Now we right-click the Web Reference called "WSDLWebService" and choose "View in Object Window":


We can see here that Visual Studio has created all the objects and methods that the WSDL file announced.

So we go to the "Code Editor" of our Default.aspx by right-clicking the file in the "Solution Explorer" and enter the following lines:



Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim CallWebservice As New WSDLWebService.customer-ws_Customers

        Dim Cache As System.Net.CredentialCache = New System.Net.CredentialCache

        Dim vUserId As String
        vUserId = "1889941"

        Dim vCustomer() As WSDLWebService.Customer
        vCustomer = CallWebservice.getCustomers()

        Label1.Text = vCustomer.length

    End Sub

End Class


I have added a Label with the ID "Label1" with the "Designer" and the Toolbar to the Default-Page:



Now we can press debug and the Internet Explorer should pop up and display in the Label1 field the amount of Customers returned by the WebService.

More information about SOAP Webservices:
PHP : SOAP: Creating and Consuming a WSDL Web-Service

17. Februar 2011

PHP: SOAP: Handle requests with class functions

To use a PHP class to handle the requests fetched by the server script you can simply assign the class file to the server:

$vClassName = 'MyHandlerClassName';
$vServer = new SoapServer('customer-ws_Customers.wsdl');
$vServer->setClass($vClassName);
$vServer->handle();

More information about SOAP Webservices: 
PHP : SOAP: Creating and Consuming a WSDL Web-Service

PHP : SOAP: Creating and Consuming a WSDL Web-Service

WebServices are very usefull if you need an common interface between external applications and your software. Especially WSDL driven WebServices are widely supported by many programming languages. Some SDKs offer possibilities to automatically create classes, functions and objects that you need to interact with the WSDL WebService.

The WSDL File

The WSDL File is an xml file that contains all information about the interface.
So we will find here information about the functionality the interface offers and how to access it. It also delivers the definition of parameters and return values required/delivered by the interface.

<?xml version ='1.0' encoding ='iso-8859-1' ?>
<wsdl:definitions
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
xmlns:tm="http://microsoft.com/wsdl/mime/textMatching/"
xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/"
xmlns:tns="http://customer-ws.tld/customer-ws_Customers.wsdl"
xmlns:s="http://www.w3.org/2001/XMLSchema"
xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/"
xmlns:http="http://schemas.xmlsoap.org/wsdl/http/"
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
targetNamespace="http://customer-ws.tld/customer-ws_Customers.wsdl"
name="customer-WS">



The definitions are requiered by the clients who access the WSDL-File to get information about the structure of the WSDL file and how to parse it.
targetNamespace and xmlns:tns are set to the URL (in my case: http://customer-ws.tld/customer-ws_Customers.wsdl) of the WSDL file.

Complex Types

<wsdl:types>
<s:schema
targetNamespace="http://customer-ws.tld/customer-ws_Customers.wsdl"
xmlns:tns="http://customer-ws.tld/PAF/customer-ws_Customers.wsdl">
<s:complexType name="Customer">
<s:sequence>
<s:element minOccurs="1" maxOccurs="1" name="customerNr" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="salutation" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="title" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="firstname" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="lastname" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="phone" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="fax" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="email" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="postalZip" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="postalCityName" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="postalStreet" type="s:string"/>
<s:element minOccurs="1" maxOccurs="1" name="postalStreetNumber" type="s:string"/>
</s:sequence>
</s:complexType>


<s:complexType name="CustomerArray">
<s:complexContent>
<s:restriction base="soapenc:Array">
<s:attribute ref="soapenc:arrayType" wsdl:arrayType="tns:Customer[]"/>
</s:restriction>
</s:complexContent>
</s:complexType>
</s:schema>
</wsdl:types>



The WSDL delivers simple data types like int, string etc. but in most cases we need much more complex data structures that have to be returned by our service. We have to define complex data types in the WSDL xml file. We define an object called 'Customer' and a type 'CustomerArray' which is an array consists of 'Customer' objects.

The Customer objects has the following attributes:

  • customerNr
  • salutation
  • title
  • firstname
  • lastname
  • phone
  • fax
  • email
  • postalZip
  • postalCityName
  • postalStreet
  • postalStreetNumber

Methods

Now it's time to define the methods our WebService should offer. For now we are creating 3 different methods:

1. getCustomers()
It returns an array of Customer objects.

2. getCustomer(aCustomerId)
It returns the customer with the given 'aCustomerId' as an Customer object

3. sendCustomer(aCustomer)
It sends a Customer Object to the server and returns the new Customer object.

<wsdl:message name='getCustomersRequest'>

</wsdl:message>
<wsdl:message name='getCustomersResponse'>
  <wsdl:part name='Result' type='tns:CustomerArray'/>
</wsdl:message>
<wsdl:message name='getCustomerRequest'>
  <wsdl:part name='aCustomerId' type='s:string'/>
</wsdl:message>
<wsdl:message name='getCustomerResponse'>
  <wsdl:part name='Result' type='tns:Customer'/>
</wsdl:message>
<wsdl:message name='sendCustomerRequest'>
  <wsdl:part name='aCustomer' type='tns:Customer'/>
</wsdl:message>
<wsdl:message name='sendCustomerResponse'>
  <wsdl:part name='Result' type='tns:Customer'/>
</wsdl:message>

<wsdl:portType name='CustomerPortType'>
  <wsdl:operation name='getCustomers'>
    <wsdl:input message='tns:getCustomersRequest'/>
    <wsdl:output message='tns:getCustomersResponse'/>
  </wsdl:operation>
  <wsdl:operation name='getCustomer'>
    <wsdl:input message='tns:getCustomerRequest'/>
    <wsdl:output message='tns:getCustomerResponse'/>
  </wsdl:operation>
  <wsdl:operation name='sendCustomer'>
    <wsdl:input message='tns:sendCustomerRequest'/>
    <wsdl:output message='tns:sendCustomerResponse'/>
  </wsdl:operation>  
</wsdl:portType>

<wsdl:binding name='ServiceSoap' type='tns:CustomerPortType'>
  <soap:binding style='rpc' transport='http://schemas.xmlsoap.org/soap/http'/>
  <wsdl:operation name='getCustomers'>
    <soap:operation soapAction='urn:customer-ws.tld-scramble#getCustomers'/>
    <wsdl:input>
      <soap:body use='encoded' namespace='urn:customer-ws.tld-scramble'
        encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'/>
    </wsdl:input>
    <wsdl:output>
      <soap:body use='encoded' namespace='urn:customer-ws.tld-scramble'
        encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'/>
    </wsdl:output>
  </wsdl:operation>
  <wsdl:operation name='getCustomer'>
    <soap:operation soapAction='urn:customer-ws.tld-scramble#getCustomer'/>
    <wsdl:input>
      <soap:body use='encoded' namespace='urn:customer-ws.tld-scramble'
        encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'/>
    </wsdl:input>
    <wsdl:output>
      <soap:body use='encoded' namespace='urn:customer-ws.tld-scramble'
        encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'/>
    </wsdl:output>
  </wsdl:operation>
  <wsdl:operation name='sendCustomer'>
    <soap:operation soapAction='urn:customer-ws.tld-scramble#sendCustomer'/>
    <wsdl:input>
      <soap:body use='encoded' namespace='urn:customer-ws.tld-scramble'
        encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'/>
    </wsdl:input>
    <wsdl:output>
      <soap:body use='encoded' namespace='urn:customer-ws.tld-scramble'
        encodingStyle='http://schemas.xmlsoap.org/soap/encoding/'/>
    </wsdl:output>
  </wsdl:operation>    
</wsdl:binding>



 WSDL method definitions always consists of two parts:

  1. Request
  2. Response

The request defines the handle of the data send to the server and the response the resultset.

Service-Definition

The service definition describes how the WebService is accessed and where the server component is located.

<wsdl:service name='customer-WS'>
  <wsdl:port name="ServiceSoap" binding="tns:ServiceSoap">
    <soap:address location='http://customer-ws.tld/server.php'/>
  </wsdl:port>
</wsdl:service>
</wsdl:definitions>



The SOAP server

First of all we create a new PHP class that extends the SoapServer class delivered with PHP (if compiled in: for more details see here):
MySoapServer.class.php

<?php
 if(class_exists('SoapServer')){
class Customer_SoapServer extends SoapServer {

}
}
?>


Now we create the Customer class that we previously defined in the WSDL file:
Customer.class.php

<?php
class Customer {
public $customerNr;
public $salutation;
public $title;
public $firstname;
public $lastname;
public $phone;
public $fax;
public $email;
public $postalZip;
public $postalCityName;
public $postalStreet;
public $postalStreetNumber;
}
?>


Then we create the server script, that initializes the SOAP server and supplies the functions we have defined in the WSDL file:
server.php

<?php
include('Customer_SoapServer.class.php');
include('Customer.class.php');

public function getCustomers(){
$vCustomers = CustomerUtility::getAllCustomersArray();
$vReturnArray = array();

foreach($vCustomers as $vCustomerId => $vCustomer){
$vWSCustomer = getWSCustomer($vCustomer);

$vReturnArray[] = $vWSCustomer;
}

return $vReturnArray;
}

public function getWSCustomer($vCustomer){
$vWSCustomer = new Customer();
$vWSCustomer->customerNr = $vCustomer['id_user'];
$vWSCustomer->salutation = $vCustomer['salutation'];
$vWSCustomer->title = $vCustomer['title'];
$vWSCustomer->firstname = $vCustomer['firstname'];
$vWSCustomer->lastname = $vCustomer['lastname'];
$vWSCustomer->phone = $vCustomer['tel'];
$vWSCustomer->fax = $vCustomer['fax'];
$vWSCustomer->email = $vCustomer['email'];
$vWSCustomer->postalZip = $vCustomer['postal_zip'];
$vWSCustomer->postalCityName = $vCustomer['postal_city'];
$vWSCustomer->postalStreet = $vCustomer['postal_street'];
$vWSCustomer->postalStreetNumber = $vCustomer['postal_streetnumber'];
return $vWSCustomer;
}

public function getCustomer($aCustomerNr){
$vWSCustomer = getWSCustomer(CustomerUtility::getCustomerArrayByCustomerNr($aCustomerNr));

return $vWSCustomer;
}

public function sendCustomer($aWSCustomer){
$vCustomer = CustomerUtility::storeCustomer($aWSCustomer);

return getCustomer($aWSCustomer->customerNr);
}

// turn off the wsdl cache
ini_set("soap.wsdl_cache_enabled", "0");

$vServer = new Customer_SoapServer("customer-ws_Customers.wsdl");

$vServer->addFunction("getCustomers");
$vServer->addFunction("getCustomer");
$vServer->addFunction("sendCustomer");

$vServer->handle();
?>


I've left out the actions inside the CustomerUtility because anything can be done here: fetching/storing from/to database etc.

The SOAP client

Now we create the SOAP client script that fetches the data from the WSDL file and connects to the soap server and fetches the data.
client.php:

<?php
include('Customer.class.php');

// turn off the WSDL cache
ini_set('soap.wsdl_cache_enabled', '0');
$vLocation = 'http://customer-ws.tld/server.php';

try {
$vSoapClient = new SoapClient('http://customer-ws.tld/customer-ws_Customers.wsdl',
array(
"location" => $vLocation,
"trace"=>1, "exceptions"=>1
)
);

$vFunctions = $vSoapClient->__getFunctions();

echo "<pre>";
var_dump($vFunctions);
echo "</pre>";

$vCustomerObjectArray = $vSoapClient->__soapCall( 'getCustomers', array());

echo "<pre>";
var_dump($vCustomerObjectArray);
echo "</pre>";

$vSingleCustomerObject = $vCustomerObjectArray[0];

echo "<pre>";
var_dump($vSingleCustomerObject);
echo "</pre>";

$vSingleCustomerObject->lastname = "My-Changed-Lastname";

$vSendCustomerObject = $vSoapClient->__soapCall( 'sendCustomers', array($vSingleCustomerObject));

echo "<pre>";
var_dump($vSendCustomerObject);
echo "</pre>";
}catch (SoapFault $vException) {
echo "Error:<br />" . nl2br($vException->faultcode) . '<br /><br />Error Details:<br />'. nl2br($vException->faultstring) . '<br />';

echo("<br />REQUEST :<br />" . htmlspecialchars($vSoapClient->__getLastRequest()) . "<br />");
echo("<br />RESPONSE:<br />" .htmlspecialchars($vSoapClient->__getLastResponse()) . "<br />");
}
?>


After we connect to the server, we display all functions (__getFunctions) available by the SOAP webservice. Then we call the getCustomers function and the server responds with an array of customers and we take out the first customer, change its lastname to "My-Changed-Lastname" and send this object back to the server.

14. Februar 2011

PHP SOAP: error: Procedure 'abc' not present

This error happens if the WSDL doesn't return this method.

<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body>
<SOAP-ENV:Fault>
<faultcode>SOAP-ENV:Server</faultcode>
<faultstring>Procedure 'abc' not present</faultstring>
</SOAP-ENV:Fault>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>


Most of the time the problem is that the SOAP client/server components cache the wsdl file and changes aren't refreshed when calling it. So you should simply delete those cache files (exact position where the cache-files are stored can be obtained from phpinfo()):

rm /tmp/wsdl-*

Credits goes to: http://artur.ejsmont.org/blog/content/php-soap-error-procedure-xxx-not-present

More information about SOAP Webservices: 
PHP : SOAP: Creating and Consuming a WSDL Web-Service

11. Februar 2011

PHP SOAP: error: looks like we got no XML document

This error has driven me crazy for hours.

The solution is much easier than it sounds:

The SOAP Client in PHP expects plain XML without any control characters.
In my case I had 2 trailing newline character in one of the PHP files which were included during the initialization of the soap server.

I just removed all trailing characters after the '?>' in all PHP files and everything worked perfectly.

More information about SOAP Webservices: 
PHP : SOAP: Creating and Consuming a WSDL Web-Service

3. Februar 2011

Despicable Me - Mini-Movie 'Banana' Preview

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

Inception Explosion Dubbreak.de

Great video by SaladUK


Inception Explosion Dubbreak.de from BassDrum on Vimeo.

Music: Butch Clancy - Russian Lullaby

2. Februar 2011

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.

Who I am & Why this Blog

Hey Folks,

first Blog, first Time...

Who I am
My name is Arne Kröger. I am working for a german company called Gutzmann EDV as a Software Developer and Systemadministrator.

Why This Blog
After 5 Years of development I think it's time to give something back to all those bloggers and forum members who have helped me over the years with my nooby questions.

But seriously, in times of Twitter, Youtube, Facebook etc. this blog takes place in the social community of Web 2.0 and we all know how funny, interesting and informative the web is. So I will also use this blog to share those things with all of you...

If you have any suggestions about any topics you want to read about, just write a comment to this post.

Best regards,
Arne Kröger