Pictures on Google map

Few days ago I wrote about idea to integrate Google Maps and Picasa pictures in the way that you can see pictures on map. Today I've discovered that it is already done in very similar way. When you go to maps.google.com there is an new tab My Maps. Then you can check "Photos from Panoramio" and you can scroll and pane map to see pictures from different places. Really cool feature. I'm looking forward for same functionality with Google Picasa. BTW, Panoramio was (will be) bought by Google in May 2007. I wonder if they integrate it with Picasa ...
Have Fun with pictures




Cheers, Paweł

Oracle 11g Announced: Features Preview

Oracle 11g was today announced. As an engineer I'm interested in new features of 11g release. I was already writing about this here and here. But this were just my thoughts based on others relations from Oracle Open World 2006.
Now official list of Oracle 11g new features is published. Here is page that contains more technical information on Oracle 11g.
Based on new features list I choose some that are very useful in project that I'm work now on, and I see them as important:

1. Binary XML storage and XML path indexing for schema-less XML documents
   
2. Result caches: improves speed of repeated execution of queries and function calls that access read-only or read-mostly data.
   
3. Database Resident Connection Pooling: enables faster connections to the database for application, that do not provide connection pooling (ex. PHP).
   
4. Faster triggers, including more-efficient invocations of per-row triggers
5. Faster simple SQL operations
6. SecureFiles: ti's new solution for storing large objects (LOBs) and datatypes such as XML
7. Automatic compilation for PL/SQL and Java in the database: new “native” compilation of PL/SQL that do not need C compiler.
   

New features that look interesting, and I want to play with them:

1. Semantic Technologies: native support for Resource Description Framework (RDF) and Web Ontology Language (OWL) standards,
2. Oracle Spatial enhancements: Richer, more-interactive map application development
3. New partitioning capabilities:
   - Partitioning by parent/child references,
   - Partitioning by virtual columns,
   - More composite partitioning options, including range/range, list/range, list/hash, and list/list,
   - Interval partitioning, which allows you to automatically create new partitions based on intervals, such as every month or every day,
   
4. Oracle Flashback Data Archive: enables fast query access to old versions of the data.
5. Expanded support for standards, such as XML Query (XQuery) 1.0 and service-oriented architecture (SOA)

I'm waiting for Oracle 11g to be available for download...

Cheers, Paweł

Extracting Data to Excel using only SQL*Plus

Lately I had to quickly extract some data from Oracle DB to Excel. There are a lot of tools that can do that, ex. Toad or SQL Developer, but I had only access to SQL*Plus. Additionally I was on Windows machine and database was in UTF8. And extracted data was also using many different languages.
Here are steps I used to archive my goal:

1. start command line (CMD)
2. set NLS_LANG variable
   

   set NLS_LANG=.UTF8

3. start sqlplus and log to database
   

   sqlplus user/pwd@db

4. set long line size and avoid paging
   

   SQL> set linesize 4000
   SQL> set pagesize 0

5. set markup html in sqlplus
   

   SQL> set markup html on spool on

   
   It is important to use spool on clause, as it adds html header to spool file when spooling is started
6. start spooling
   

   SQL> spool a.html

7. run your query
   
8. turn off spooling
   

   SQL> spool off

9. open file in Excel

I've tested this procedure on Oracle 10g R2 (10.2.0.1 and 10.2.0.3) and Excel 2003

Cheers, Paweł

After Vacations: Tagging Photos with Geo Location

Welcome back after my vacations. This time nothing about Oracle, but about some idea that was in my mind for few years and now is life: Google Picasa has new functionality: putting pictures on maps. I'm using Picasa to share pictures with friends and family for more than year now. And often I was asked: where did you take this picture? Now I can tag pictures with Geo coordinates and then are shown on map. Take a look at my public galleries on picasa, especially latest one with some pictures from my vacations.
There are still some features that I would like to see;When playing with google maps I would like to have option to show pictures from specific place. Pictures from public galleries could be presented. There might be an copyright issue, but it could be solved easily: users will have option to allow (or not) to publish pictures this way.
And I must buy Camera with build in GPS receiver to simplify mapping pictures...


Cheers, Paweł

Dynamically set name of spool file in SQL*Plus

Small tip for today. Often there is a need to set name of spool file based on current time and might by also database you are connected to. It's often needed when script is run as scheduled task and there is a need to generate files with different names. So here is an solution for using date:

define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'

If there is a need to have also name of database included into filename then it can be read from global_name view:

define logname=date
column clogname new_value logname
select 'prefix_'||substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||
 to_char(sysdate, 'yyyymmdd') clogname
from (select global_name, instr(global_name, '.') dot from global_name);
spool '&logname'

Hope you find it useful.
I would also like to share an photo I made in Rome in February this year

To see more pictures you are welcome to follow Rome 1, Rome 2, Rome 3

Cheers, Paweł

Log Buffer #48

Welcome to 48 edition of Log Buffer. While I read Log Buffer regularly for many months already it's first time I do review of databases blogosphere. Thanks to Dave Edwards from Pythian for inviting me.

This edition will be mostly related to development tools, techniques and environments for database applications. Let start with Inside Oracle APEX where Patrick Wolf presents internals of APEX Repository. In turn Dimitri Gielis is discussing how to reuse Oracle APEX components effectively. If you still do not know what APEX is, you can begin with Oracle Application Express—What’s it All About? by Peter Lorenzen.

Jonathan Bruce at his Weblog is reviewing and recommending DataDirect Connect for ADO.NET Oracle provider for Visual Studio.
Anton Scheffer from AMIS is solving Sudoku with single SQL statement using MODEL Clause; really impressive method, I must find time to experiment with MODEL clause as it seems to be very powerful.

Linear Algebra is not commonly known feature of Oracle database. Marcos M. Campos on Oracle Data Mining and Analytics is presenting how it can be used for Principal Components Analysis and Visualization. Unfortunately complexity of examples rather will not help with wider usage of UTL_NLA package.

Jonathan Lewis on his Oracle Scratchpad presents differences in new (Oracle 10g) and old sorting algorithm. Difference is visible only for those who were using side effect of sorting by ROWID in previous oracle releases.

Jorrit Nijssen alias Jornica issued an warning on using after statement trigger and returning clause together as there is an side effect.

For many of Oracle DBAs it might be weird idea: Kevin Closson is proposing to install Oracle over NFS. He states that it's cheaper, simpler and will be even better with upcoming Oracle 11g. By the way Oracle 11g will be launched on 11th of July according to Eddie Awad. This announcement made big rumour in Oracle blogosphere and Lutz Hartmann is now documenting new feature of collecting statistics in 11g.

Laurent Schneider is discussing Best Practices for Aliasing columns in SQL queries. Dominic Delmolino on Oracle Musigns suggest another method: uniquely name columns. Also Steven Feuerstein on Toad World shows good practice: "Don't put COMMIT; in you code!".

Kevin Closson and Doug Burns discuses what is the role of Oracle, OTN, Technorati and readers for bloggers community.

Jeff of his Jeff' SQL Server Weblog that database design should not be made due to desired output. "We often see bad designs submitted at SQLTeam.com with the justification of that design being the client wanted it that way! ... you need to demonstrate to them that even when storing the data properly, we can *still* create a View which returns those exact results, making them happy".

Data Quality: Where to Validate? Beth on Confessions of a database geek is reviewing levels where validation should occur also giving some recommendations.

Roland Bouman shows What MySQL can do to enter the off-line Web. In my opinion the idea of using Web applications offline will be very hot in nearest future.
Peter Zaitsev is discussing an issue with degraded performance after upgrading MySQL 4 to MySQL 5. He also presents some workaround for that problem.
Paul McCullagh on PrimeBase XT shows how to manipulate BLOBs in MySQL using BLOB Streaming Engine.
Ronald Bradford on Technical Notes and Articles of Interest reviews Clustering solution for MySQL database.
Kevin Burton is predicting end of RAIDs . Instead he proposes to use many instances of MySQL with replication mechanism.
Xaprb announced release of MySQL Archiver 0.9.1 tool to archive content of critical OLTP tables.

JonC is answering most common Security Questions Applied to MySQL and pointing that biggest challenge is to deal with human errors. Alex Kornbrust is presenting THC Orakel new tool to Sniffer Oracle Passwords. He also points few errors in white-paper that is accompanying the tool.

Mark Rittman is on vacations leaving us with lot of BI Articles and Links to read.

Steve Jones on SQL Musings talks over High Availability of SQL Server. Euan Garden on his blog announces interesting Web Casts about new features of SQL Server 2008. While Jamie Thomson show how Group by Grouping Sets will work in Katmai: SQL Sever 2008.

Josh Berkus is reporting on JPUG 2007 PostgreSQL Conference and his keynote on "PostgreSQL Today and Tomorrow". "This was the most challenging conference for me so far since the entire conference was in Japanese, a language of which I have no comprehension."

At the and some news from my country. Jakub Pawlowski is summarizing XV PLOUG (Polish Oracle Users Group) Seminary - "Oracle Application Server 10g R3: applications, productivity, security, reliability."
That's all for today. Wait for next Log Buffer on June 15th presented by Coskan Gundogar.

Paweł

Rows to single string or aggregating strings

In oracle there is no build-in group function that will concatenate strings, just like max summarizes all numbers. But there is quite easy way to do that in pure SQL. Assume we want to have get as an query result list of tables and column. But we want all columns to be concatenated and comma separated. Result should look

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS	BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS	BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE	CCH_URL, CCH_TIME, CCH_VALUE

Lets start with hierarchical query to build "tree" for each table. The only one branch of this tree will start with first column and end with last one. SYS_CONNECT_BY_PATH gives as option to track that branch:

SQL> select table_name, 
  2        SYS_CONNECT_BY_PATH(column_name,  ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  ;

TABLE_NAME	COLUMN_LIST
BLOGS	, BGH_ID
BLOGS	, BGH_ID, BGH_NAME
BLOGS	, BGH_ID, BGH_NAME, BGH_FEED_URL
...	...

Then I just take max path and truncating leading comma gives me expected result:

SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from user_tab_columns
  4  start with column_id = 1
  5  connect by table_name = prior table_name
  6             and column_id = prior column_id +1
  7  group by table_name;

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_BLG_ID, BLC_IDENTIFIER, BLC_PUBLISHED_TEXT, BLC_PUBLISHED, BLC_UPDATED_TEXT, BLC_UPDATED, BLC_CONTENT, BLC_URL, BLC_AUTHOR, BLC_AUTHOR_URI
BLOG_POSTS	BLG_ID, BLG_IDENTIFIER, BLG_TITLE, BLG_PUBLISHED_TEXT, BLG_PUBLISHED, BLG_UPDATED_TEXT, BLG_UPDATED, BLG_CONTENT, BLG_URL, BLG_BGH_ID, BLG_ENTRY
BLOGS	BGH_ID, BGH_NAME, BGH_FEED_URL
FEED_CACHE	CCH_URL, CCH_TIME, CCH_VALUE

Function Max works becouse if one string (ex.: "abc") is an prefix of the second one (ex: "abc123") than this second one is threated as bigger. I must warn that it might be not true for all NLS settings.

But what if we want columns to be ordered alphabetically? Than we have to use Row_Number function to calculate column position in table and make same changes to SQL:

SQL> select table_name, 
  2        ltrim(max(SYS_CONNECT_BY_PATH(column_name,  ',')), ',') column_list
  3  from (select table_name,  column_name,  ROW_NUMBER() OVER (partition by table_name ORDER BY column_name) AS curr  from user_tab_columns)
  4  start with curr = 1
  5  connect by table_name = prior table_name
  6             and curr = prior curr +1
  7  group by table_name;

TABLE_NAME	COLUMN_LIST
BLOG_LABELS	BLL_BLG_ID, BLL_LABEL
BLOG_COMMENTS	BLC_AUTHOR, BLC_AUTHOR_URI, BLC_BLG_ID, BLC_CONTENT, BLC_IDENTIFIER, BLC_PUBLISHED, BLC_PUBLISHED_TEXT, BLC_UPDATED, BLC_UPDATED_TEXT, BLC_URL
BLOG_POSTS	BLG_BGH_ID, BLG_CONTENT, BLG_ENTRY, BLG_ID, BLG_IDENTIFIER, BLG_PUBLISHED, BLG_PUBLISHED_TEXT, BLG_TITLE, BLG_UPDATED, BLG_UPDATED_TEXT, BLG_URL
BLOGS	BGH_FEED_URL, BGH_ID, BGH_NAME
FEED_CACHE	CCH_TIME, CCH_URL, CCH_VALUE

Hope you will find it useful and fun.
All samples where run on Oracle 10gR2 XE 10.2.0.1.
Schema used in this sample is described in Blogger Backup tool entry.

Paweł