Order or precedence in Oracle CASE

Obvious enough, but nice to confirm

SELECT 
  CASE 
    WHEN SUBSTR('asd', 1, 1) = 'a' THEN 'first match'
    WHEN SUBSTR('asd', 1, 2) = 'as' THEN 'second match'
  END AS TestCase
FROM
  DUAL

 

TESTCASE
————
first match

Advertisements
Posted in code, development, oracle, Uncategorized | Tagged | Leave a comment

Notepad++ with Explorer plugin – the perfect dev tool for SQL projects

I have been working a lot with database scripts lately and have been missing a good way to manage ‘projects’ in the form of a structured collection of .sql files. A fullblown IDE is overkill for this purpose and using plain Notepad++ dos not give easy access to dependent files.

The Explorer plugin provides a neat sidebar to Npp and can be enabled by going to Plugins – Plugin Manager and look for ‘Explorer’. In the options menu for this plugin you can turn off “Folders with braces” if prefer a regular and clean view of sub-folders.

npp_explorer

Posted in development, look and feel | Tagged , , , | 1 Comment

Clover

<3

Link | Posted on by | Leave a comment

Invoke Oracle Stored Procedure from PowerShell using OleDB

$con = New-Object System.Data.OleDb.OleDbConnection
$con.connectionString = "Provider=OraOLEDB.Oracle;Data Source=<MYTNS>;User Id=<USER>;Password=<PASS>;"
$cmd = $con.createcommand()
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd.commandtext = "test_pkg.do_something()"
$con.Open()
$cmd.ExecuteNonQuery()
$con.Close()
Posted in code, development, oracle, windows powershell | Tagged , , , | 4 Comments

PowerShell ExecutionPolicy

The execution policies you can use are:

  • Restricted – Scripts won’t run.
  • RemoteSigned – Scripts created locally will run, but those downloaded from the Internet will not (unless they are digitally signed by a trusted publisher).
  • AllSigned – Scripts will run only if they have been signed by a trusted publisher.
  • Unrestricted – Scripts will run regardless of where they have come from and whether they are signed.

But how does Windows actually know that a file was downloaded from the Internet? And if the script need to be transferred to a target environment that way, is there any way to remove this meta-info from the file afterwards?

I found my answers in the following posts:

http://blogs.msdn.com/b/powershell/archive/2007/03/07/how-does-the-remotesigned-execution-policy-work.aspx

http://www.hanselman.com/blog/RemovingSecurityFromDownloadedPowerShellScriptsWithAlternativeDataStreams.aspx

Windows is actually appending a ZoneTransfer property to the file when your browser writes the file during downloading! And frankly it is easy to unlock the file if you have sufficient permissions, just clicking ‘Unlock’ in the file properties.

 

Posted in development, windows powershell | Tagged , | Leave a comment

Get a smooth new ringtone on Windows Phone 8

  1. Find a nice tune on https://soundcloud.com/
  2. Download it using http://soundcloud-download.com/
  3. Extract a ~20 seconds part of the tune and add a ~1 second fade in and fade out using Audacity
  4. Save as .mp3 and drag-drop into the Ringtons on your WP8 phone

It appears automaticlly in the list of ringtones under settings.
Examples: skogsdans_ringtone.mp3, floyta_ringtone.mp3

Posted in life, music, software, WP8 | Leave a comment

Polstat: web scraping with Scrapy

The www is a huge repository of information, but it is not an easy task to get the exact information you need, when you need it and in the form you need it. Although information sharing through feeds, online APIs and even semantic annotations are becoming increasingly popular, sharing features tend to be restrictive and not a priority for web owners. In my opinion, economical restrictions or webmaster laziness should not blockade for those who want access and use freely available information in new ways. The number of websites that do not implement any data particular sharing capabilities (other than its html structure, and possibly a news feed) are by far dominating the web.

Extracting data directly from the source code of an external webpage tend to be error-prone. As I see it, there are two main reasons for this. The first is related to the code that actually parses the target html and the fact that it may face suddenly changes in the layout and content, totally out of the developers control. Secondly comes the challenge of scaling. Parsing an array of URLs, possibly on different hosts, in one process execution is a risky approach without proper quality mechanisms. Moreover, there is soon a need for more sophisticated crawling functionality such as following links or customized behavior based on given patterns.

A solution to most of the above challenges are addressed in modern web crawling frameworks. One of the more interesting solutions in terms of simplicity, flexibility and community is the python-based Scrapy web crawling framework.

To check it out, I wrote a bot for collecting wine prices from vinmonopolet.no* every month. This turned out to work quite well, and the result is available at polstat.inevitable.no. Visualization of historical price development is interesting for two main reasons: (1) advertisements for for alcoholic beverages are illegal in this country, and (2) as Vinmonopolet buys large quantities of products from their vendors, supply and demand in the market could cause prices to fluctuate. The following links have more information about this:

http://www.dagbladet.no/2010/01/06/tema/drikke/mat/klikk/9797457/
http://www.klikk.no/mat/drikke/article476105.ece

I hope to return with more posts related to the implementation as well as legal aspects of polstat.

*) Vinmonopolet has the exclusive rights to sell wine, spirits and strong beer in Norway.

Posted in Uncategorized | 1 Comment