Archives

Tagged ‘Oracle‘

New Oracle Data Miner tool is now Available

Today the new Oracle Data Miner tool has been made available as part of the SQL Developer 3.0 (Early Adoptor Release 4).
The new ODM tool has been significantly redeveloped, with a new workflow interface and new graphical outputs. These include graphical representations of the decision trees and clustering.
To download the tool and to read the release documentation go to

http://tinyurl.com/62u3m4y

http://tinyurl.com/6heugsh

If you download and use the new tool, let me know what you think of it.

Losing timestamp in ODI when using ojdbc14.jar

The ojdbc14.jar version of the JDBC driver is losing timestamp information when returning DATE datatypes from Oracle. As per Oracle JDBC driver FAQ this is a known issue. As a workaround it is suggested to set the oracle.jdbc.V8Compatible JDBC parameter to true. You can set this parameter in ODI when defining a Topology:

odi_jdbc

However, a better solution for this is to use a 1.5 JDK in combination with the 11.1 ojdbc5.jar driver. This is supported for both Oracle 10G and 11G. Refer to note 807235.1 on Metalink for  information which JDBC and JVM/JDK versions work together.

In a first step stop all ODI agents and download the 1.5 JDK from the SUN website. Next install and point the ODI_JAVA_HOME environment variable to point to the JDK, e.g. E:\Program Files\sun\jdk\jdk.

Next go to the ODI drivers folder and rename the ojdbc14.jar to ojdbc14.jar.bak. We now need to download the 11.1 driver from the Oracle website. Once downloaded put the ojdbc5.jar into the ODI drivers folder.

We are now ready to test this. Log into the ODI Designer module. Expand the Projects folder and right click Variables and select Insert Variable. Give the variable a name and change to the Refreshing tab. From the Schema dropdown select an Oracle Schema. In the Select Query textbox type the following:

SELECT sysdate FROM dual

Click the Refresh button and change to the History tab. Next click the Refresh icon and you should get timestamp information.

odi_jdbc_timestamp

One more thing: You will need to re-install your agents as windows services.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Oracle on Windows in the EC2 cloud. Persist computer name across instance shutdown.

For various reasons it is important that your Windows computer name persists across instance shutdown on EC2 (listener, tnsnames.ora, loopback adapter etc.).

To achieve this you need to perform the following steps:

1. Connect to your instance via remote desktop

2. Change the computer name via control panel

3. Open windows explorer and browse to the EC2 config tool. By default this is located at C:\Program Files\Amazon\Ec2ConfigSetup. Double click Ec2ConfigServiceSettings.exe

4. On the General tab deselect Set Computer Name

ec2_1

5. Go to the Bundle tab. Deselect Sysprep

ec2_2

6. Log on to the AWS Management Console

7. Right click your instance and select Bundle Instance. This will take a while to complete.

8. Go to Bundle Tasks and select Register as an AMI.

The next time you launch your instance from your AMI you will see that the computer name you entered in step two has persisted across instance termination.

Note:

Before bundling your instance stop the Oracle Windows service. I typically set the Oracle service to start up manually. As the bundle task reboots the machine before taking an image it guarantees that the Oracle service is not started.

Consuming a REST web service with Oracle

SOAP web services dominate in a SOA environment. However, there is a new kid on the block: REST. This is a more lightweight approach to exchange data over HTTP, which is becoming more and more popular with web mashups and the web 2.0 in general. Amazon, Twitter, Yahoo all expose their APIs via REST.

REST is an architecture style for designing networked applications. The idea is that, rather than using complex mechanisms such as CORBA, RPC or SOAP to connect between machines, simple HTTP is used to make calls between machines.

In this post I’ll show you how you can consume a REST web service via pure SQL in Oracle.

I have picked the Google Translate API as a show case. The API exposes a RESTful infrastructure via HTTP GET. You may want to use this API in a document warehousing situation where you need to translate unstructured documents on the fly.

The API exposes two methods. The Detect Language method allows us to identify a language by submitting a sentence or phrase. The Translate Language method allows us to translate one ore more pieces of texts into one or more languages. Details on parameters etc. can be found in the documentation. The Google Translate web service then returns a JSON object with the language detected or the translated text, depending on the method chosen.

JSON is a better fit [than XML] for Web services that power Web mashups and AJAX widgets due to the fact that it is essentially serialized Javascript objects which makes it fit better client side scripting which is primarily done in Javascript.

In Oracle we can use the HTTPURITYPE function to perform an HTTP GET to consume a RESTful web service. See one of my previous posts for some more details on HTTPURITYPE.

Ok, so let’s finally get started. In a first step we populate a table with the text we want to translate and its source language. For this I have picked an excerpt from the poem Rhyme of the Ancient Mariner by Samuel T. Coleridge. This is a great poem and if you get a chance read it. There is also an Iron Maiden song about it, if you want an executive summary…

CREATE TABLE go_trans_text (text_doc CLOB, language VARCHAR2(2))

INSERT INTO go_trans_text VALUES ('The rhyme of the ancient mariner. Day after day, day after day, we stuck, nor breath nor motion. As idle as a painted ship upon a painted ocean. Water, water, every where, and all the boards did shrink. Water, water, every where,nor any drop to drink.','en')

COMMIT;

Next we just run the following query against the web service and parse the JSON object. In Oracle no native support for JSON is provided. So we’ll just parse this with string functions and regular expressions. I have also come across some third party JSON PL/SQL package but have not tried this out yet. If you do it would be nice to leave me some feedback on it.

SELECT 
   SUBSTR(httpget,INSTR(httpget,'langpair',1,level)+14,2),
   SUBSTR(JSONtranslate,INSTR(JSONTRANSLATE,'"translatedText":"',1,level)+18,INSTR(JSONTRANSLATE,'"},"responseDetails"',1,level)-INSTR(JSONTRANSLATE,'"translatedText":"',1,level)-18),
   text_doc
FROM (
   SELECT 
      HTTPURITYPE(httpget).getCLOB() as JSONtranslate,
      httpget,
      text_doc
   FROM (   
      SELECT
         text_doc,
         'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&'||'q='||utl_url.escape(text_doc)||'&'||'langpair=' || language || '%7Cde&'||'langpair=' || language || '%7Cfr&'||'langpair=' || language || '%7Cit' AS httpget
      FROM
         go_trans_text   
   )  
)   
CONNECT BY 
   level <= (LENGTH(httpget) - LENGTH(REPLACE(httpget, 'q='))) / LENGTH('q=') *
   (LENGTH(httpget) - LENGTH(REPLACE(httpget, 'langpair='))) / LENGTH('langpair=') 

We will now go through the above SQL statement step by step.

In the innermost SQL of the above query we construct our query string and bind the document text and source language values from our table into this query string.

SQL>       SELECT
  2           text_doc,
  3           'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&'||'q='||utl_url.escape(text_doc)||'&'||'langpair=' || language || '%7Cde&'||'langpair=' || language || '%7Cfr&'||'langpair=' || language || '%7Cit' AS httpget
  4        FROM
  5           go_trans_text  ;

Note the use of the utl_erl.escape function. We use this function to URL encode our Coleridge poem.

The result is the following query string:


http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&q=The%20rhyme%20of%20the%20ancient%20mariner.%20Day%20after%20day,%20day%20after%20day,%20we%20stuck,%20nor%20breath%20nor%20motion.%20As%20idle%20as%20a%20painted%20ship%20upon%20a%20painted%20ocean.%20Water,%20water,%20every%20where,%20and%20all%20the%20boards%20did%20shrink.%20Water,%20water,%20every%20where,nor%20any%20drop%20to%20drink.&langpair=en%7Cde&langpair=en%7Cfr&langpair=en%7Cit

In the next innermost SQL we perform an HTTP Get to the Google Translate REST API.

SQL>    SELECT
  2        HTTPURITYPE(httpget).getCLOB() as JSONtranslate,
  3        httpget,
  4        text_doc
  5     FROM (
  6        SELECT
  7           text_doc,
  8           'http://ajax.googleapis.com/ajax/services/language/translate?v=1.0&'||'q='||utl_url.escape(text_doc)||'&'||'langpair=' || language || '%7Cde&'||'langpair=' || language || '%7Cfr&'||'langpair=' || language || '%7Cit' AS httpget
  9        FROM
 10           go_trans_text
 11     )  ;

Google returns the translated text in a JSON object:

{"responseData": [{"responseData":{"translatedText":"Der Reim der alten Seefahrer. Tag für Tag, Tag für Tag, wir stecken noch Atem noch Bewegung. Im Leerlauf wie ein gemaltes Schiff auf einem gemalten Ozean. Wasser, Wasser, überall, und alle Platten haben schrumpfen. Wasser, Wasser, überall, noch ein Tropfen zu trinken."},"responseDetails":null,"responseStatus":200},{"responseData":{"translatedText":"La comptine de l\u0026#39;ancien marin. Jour après jour, jour après jour, nous avons bloqué, ni souffle, ni mouvement. Comme au ralenti comme une peinture sur un bateau peint océan. L\u0026#39;eau, l\u0026#39;eau, où tous, et tous les conseils scolaires ne diminuera. L\u0026#39;eau, l\u0026#39;eau, partout, ni de goutte à boire."},"responseDetails":null,"responseStatus":200},{"responseData":{"translatedText":"La rima del vecchio marinaio. Giorno dopo giorno, giorno dopo giorno, abbiamo bloccato, né respiro né movimento. Come minimo dipinta come una nave dipinta su un oceano. Acqua, acqua, ogni dove, e tutti i pannelli non si restringono. Acqua, acqua, ogni dove, né alcuna goccia da bere."},"responseDetails":null,"responseStatus":200}], "responseDetails": null, "responseStatus": 200}

In a last step we now need to multiply our JSON object by the number of target languages and then parse out the translated text. This will give us the translation:

TARGET_LANG	TRANSLATED_TEXT	                                                                                                                                                                                                                                                                              ORIGINAL_TEXT
de	         Der Reim der alten Seefahrer. Tag für Tag, Tag für Tag, wir stecken noch Atem noch Bewegung. Im Leerlauf wie ein gemaltes Schiff auf einem gemalten Ozean. Wasser, Wasser, überall, und alle Platten haben schrumpfen. Wasser, Wasser, überall, noch ein Tropfen zu trinken.	                  The rhyme of the ancient mariner. Day after day, day after day, we stuck, nor breath nor motion. As idle as a painted ship upon a painted ocean. Water, water, every where, and all the boards did shrink. Water, water, every where,nor any drop to drink.
fr	         La comptine de l'ancien marin. Jour après jour, jour après jour, nous avons bloqué, ni souffle, ni mouvement. Comme au ralenti comme une peinture sur un bateau peint océan. L'eau, l'eau, où tous, et tous les conseils scolaires ne diminuera. L'eau, l'eau, partout, ni de goutte à boire.	The rhyme of the ancient mariner. Day after day, day after day, we stuck, nor breath nor motion. As idle as a painted ship upon a painted ocean. Water, water, every where, and all the boards did shrink. Water, water, every where,nor any drop to drink.
it	         La rima del vecchio marinaio. Giorno dopo giorno, giorno dopo giorno, abbiamo bloccato, né respiro né movimento. Come minimo dipinta come una nave dipinta su un oceano. Acqua, acqua, ogni dove, e tutti i pannelli non si restringono. Acqua, acqua, ogni dove, né alcuna goccia da bere.	   The rhyme of the ancient mariner. Day after day, day after day, we stuck, nor breath nor motion. As idle as a painted ship upon a painted ocean. Water, water, every where, and all the boards did shrink. Water, water, every where,nor any drop to drink.

Note: As per Google terms and conditions you are only allowed to store this information for caching purposes, but are not allowed to persist, e.g. in a database.

Date dimension script with Oracle SQL

The script below will create a date dimension in just one SQL statement. We don’t use performance-killer nonsense such as cursors, functions etc.

We just pick a start date and the number of days we want to create and with a bit of magic of the Connect By clause and the NUMTODSINTERVAL function we do the whole thing in 14 lines of code.

SQL> CREATE TABLE d_date AS
  2  SELECT
  3     n AS Date_ID,
  4     TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
  5     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD') AS Days,
  6     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
  7     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM') AS Month_Num,
  8     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
  9     TO_CHAR(TO_DATE('31/12/2007','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY') AS Year
 10  FROM (
 11  select level n
 12  from dual
 13  connect by level <= 2000
 14  );

Table created.

Pretty much self-explanatory. The above will create a date dimension starting at 01/01/2008 and ending at 01/01/2008+2000 days = 22/06/2013.

If you want to read up on NUMTODSINTERVAL have a look at the documentation. Intervals are also very well explained in Tom Kyte's Expert Oracle Database Architecture. It is worth buying just for this chapter.

Asynch CDC: Delete old archived redo logs

The following steps need to be taken to be able to delete archived redo logs that are no longer required by the CDC capture process:

1. Determine the new first_scn for your capture process

SELECT
cap.CAPTURE_NAME,
cap.FIRST_SCN,
cap.APPLIED_SCN,
cap.REQUIRED_CHECKPOINT_SCN
FROM DBA_CAPTURE cap, CHANGE_SETS cset
WHERE cap.CAPTURE_NAME = cset.CAPTURE_NAME

As per Oracle documentation: This query “determines a new first_scn value that is greater than the original first_scn value and less than or equal to the applied_scn and required_checkpoint_scn”. So the new first_scn is the lower value of applied_scn and required_checkpoint_scn.

2. Raise the first_scn for the change source:

exec dbms_cdc_publish.ALTER_AUTOLOG_CHANGE_SOURCE (change_source_name => <name of change source>,first_scn => 11395421887)

3. Identify which redo log files can be deleted:

SELECT *
FROM DBA_LOGMNR_PURGED_LOG

4. Write a script to delete or archive obsolete archived redo logs based on the results of the query in step 3.