[SQL] Oracle IF SELECT statement

While working with PL/SQL, you may encounter the need to use something like:

IF EXISTS(SELECT ...) THEN


or

IF (SELECT ...) IS NULL THEN

but Oracle doesn't allow this, instead, you'll have to alter your function/procedure:

DECLARE
    myFlag INTEGER;
BEGIN
    SELECT COUNT(*) INTO myFlag
    FROM table t
    WHERE [conditions]
  AND ROWNUM = 1;
  IF myFlag = 1 THEN
    --something was returned
  ELSE
    --else no data was found
  END IF;
END;

Using both COUNT and ROWNUM=1 ensures us that, no matter whether something was returned by the SELECT or not, myFlag will in any case be either 1 or 0, which reflects the outcome you'd have had if you could have used the two statements at the beginning of this post.

[SQL] Oracle insert multiple rows with single statement

In Oracle, to insert multiple rows from a single INSERT statement you can:

INSERT ALL
INTO table(column_list) VALUES (values)
INTO table1(column_list1) VALUES (values1)
...
SELECT *
FROM dual;

Should you need to, you may actually write your own SELECT statement. You can insert data into multiple tables at once too.

[SQL] Oracle run procedure

So, you've created your procedure to spare yourself some time and need to execute it, but how?

EXEC your_package.your_procedure; --no input parameters

If the procedure needs parameters you can add them inside the call.

[SQL] Oracle align sequence values after database import

In Oracle, after you import a database which had sequences in it, you may incur in violations of some primary key constraints when attempting to insert new data using said sequences.

This happens because the sequence values are not aligned with the DB tables so the next value the sequence will offer may be already in use. To check this you can:

SELECT MAX(key) FROM table;

To get the last sequence value used for that table key, then:

SELECT sequence_name.NextVal FROM DUAL;

To see where is the sequence at now. If that value is lower than the one you got from the last query, you'll have to correct the sequence. You can do so in many ways:

1- Drop and recreate the sequence with a new (correct) START WITH value:

DROP SEQUENCE sequence_name;

CREATE SEQUENCE sequence_name
START WITH new_correct_value--eg key+1
MAXVALUE how_high_can_it_go
MINVALUE how_low_can_it_be
[other additional parameters based on your needs];

2a- Query the sequence until you reach the desired value reusing multiple times the query we ran to check where it was.

2b- Alter the sequence increment step (either up or down) then query it for a new value, forcing it to reach your desired value in a single shot.

ALTER SEQUENCE sequence_name
INCREMENT BY x
MINVALUE 0;

Where x is your desired increment (must be negative to go back).

Note: by running our check query, you will effectively lose the value returned. If you need a strict control over which key values are generated, you may try the solution at 2b to rewind the sequence and recover the used value.

[SQL] Oracle ORA-21000 "error number argument to raise_application_error of X is out of range"

Oracle allows us to raise user defined exceptions from PL/SQL code with RAISE_APPLICATION_ERROR.

The most common usage is:

RAISE_APPLICATION_ERROR(code, message)

Where code is an integer and message a string. Now, when the exception occurs, the user will see ORA-code: message.

Sometimes you may get the ORA-21000: error number argument to raise_application_error of X is out of range error. This happens because, for user defined errors, the error code (first argument) MUST be between -20000 and -20999 included.

Run XP mode applications from Windows 7

To run XP mode applications directly from Windows 7 start menu, you'll need:

• Integration features enabled on the XP mode virtual machine
• Auto-publish enabled on the XP mode VM
• The application must not be listed under the HLKM\Software\Microsoft\Windows NT\CurrentVersion\Virtual Machine\VPCVAppExcludeList entry inside the XP mode VM registry

Then, after you install an application on the XP mode VM (may require restart) you should see it listed under 7's start menu under Windows Virtual PC\Windows XP Mode Applications.

If the application takes forever to load, you may try starting the XP mode VM, then hibernating it. Now start the application from 7 and when asked to restart the XP mode VM choose continue.

Further info on:

technet.microsoft.com
blogs.technet.com

Enable Tomcat remote access

To reach an application published inside Apache Tomcat using the host's IP (localhost does not work for remote access), you need to modify the server.xml file which is located under Tomcat's conf/ directory.

Look for this piece:

 <Valve className="org.apache.catalina.valves.AccessLogValve"  
 directory="logs" prefix="localhost_access_log." suffix=".txt"  
 pattern="common" resolveHosts="false"/>  

and set resolveHosts parameter to true. You may also need to add a line to the client machines' hosts file so that the IP can be resolved.

[SQL] Specify ORDER BY values in query for user defined sorting

In SQL, you can use the ORDER BY clause to sort the data in ascending or descending order; you would usually:

SELECTcolumn1, column2
FROM table t
ORDER BY t.column1 ASC; --or DESC

But that's not all; if you need, you can specify a sorting rule directly inside the ORDER BY clause, in case you want a non- alphabetical or numerical sorting:

SELECT column1, column2
FROM table t
ORDER BY (
    CASE WHEN t.column1='value1' THEN 1
    ...
    END
);

Install new language pack on Windows 7 Professional

Windows 7 Professional edition does not allow you to install a new language pack directly from the UI, you can however add it manually with a few commands.

When you've downloaded the language pack you need, either from MSDN (available for 32-bit OSs too, requires subscription) or other sources, save the lp.cab file somewhere eg: C:\lp.cab then issue the following commands in the cmd.exe command prompt (run as administrator):

 DISM /Online /Add-Package /PackagePath:C:\lp.cab  
 bcdedit /set {current} locale xx-XX  
 bcdboot %WinDir% /l xx-XX  

Replacing xx-XX with your language code (eg: en-US for english or it-IT for italian). Now, run regedit.exe and browse to the following entry:

HKEY_LOCAL_MACHINE-SYSTEM-CurrentControlSet-Control-MUI-UILanguages

to delete the previous language entry. Eg: if you had it-IT already installed and added en-US, then delete the it-IT entry. Finally, reboot and you're set.

Note: you may want to backup the entry before deleting it just to be safe; right click it and select export... to save it.

[SQL] Oracle call web service and read response

In Oracle, you can invoke a web service directly from a PL/SQL function/procedure and parse the response using the UTL_HTTP module.

It should be included with every standard installation, and it's owned by SYS; you may need to run:

GRANT EXECUTE ON UTL_HTTP TO [user]

 as SYS (usually user: SYS password: manager) to allow other users to execute that package's procedures.

[SQL] Oracle get data from XML object node

In Oracle, if you need to extract the node value from an XML object you may use the EXTRACTVALUE function specifying an XPath predicate.

Suppose your XML object has the following schema:

<?xml version="1.0" encoding="utf-8"?> <root> <node>value</node> </root>


and is of type XMLType, then you would:

SELECT EXTRACTVALUE(
    your_object,
    '//root/node/text()'
) node_value
FROM dual;

[SQL] Oracle pass namespace parameter to EXTRACT and EXTRACTVALUE XML functions

When working with Oracle's EXTRACT and EXTRACTVALUE XML functions, you'll have the option to add a namespace (namespace_string) parameter, but unfortunately the official documentation is very thin on that part.

Said argument must be a string in the form of:

'xmlns:alias="URI"' or 'xmlns="URI"'

if you need to list multiple namespaces, simply separate them with a space character:

'xmlns:alias="URI" xmlns:alias1="URI1"' 

or 

'xmlns:alias="URI" xmlns="URI1"'

Delete SVN stored password from Eclipse

Assuming that you're using Eclipse on Windows with the Subclipse plug-in, to delete the SVN stored passwords you will have to either:

• delete it from %appdata%\subversion\auth if using JavaHL
• delete the .keyring file from eclipse\configuration\org.eclipse.core.runtime if using SVNKit. Then restart eclipse which will regenerate it empty

[SQL] Oracle insert & (AND) character

To insert the & character in a string field in Oracle, you may need to split the string in two parts then concatenate them together putting the special character in the middle using the CHR function:

'string_part1'||chr(38)||'string_part2'

[SQL] Reuse query with different filters in same SELECT

Say you need to create one of those queries that produce a report or some statistics about the data stored in your DB tables. You will most likely need to reuse the same query but with different filters every time and gather the results in a single SELECT.

You will have a base query which gathers all the data you're interested into (the "total" one) and need to filter it multiple times (even on the same column) with different values.

[SQL] WITH clause query alias

In SQL you can easily define an alias for a table or column and just as easily for a query or subquery. This can be useful when joining two datasets (SQL ANSI-92 standard):

SELECT *
FROM (SELECT column1, column2
    FROM table1
    [WHERE]) query1
JOIN (SELECT column1, column3
    FROM table2
    [WHERE]) query2
ON query1.column1=query2.column1

[SQL] Oracle function return table dataset multiple rows

In Oracle, procedures may return multiple values as OUT parameters while functions should always return a single value. Functions CAN however return multiple values, but they must be enclosed in a TABLE object which is the "single value" to be returned.

[SQL] SQL Server change collation during query

When working with multiple Microsoft SQL Server databases, you may encounter an error which states something like "Cannot resolve collation conflict for equal to operation".

This happens when the two databases have different collations and you try to compare strings between them.

Now, you can change the server collation, database collation or column collation but it won't immediately solve the problem. If you just need to query data combining both databases, you can always use the COLLATE keyword:

SELECT * from DB1.DBO1.TABLE t1, DB2.DBO2.TABLE2 t2
WHERE t1.field=t2.field COLLATE [collation_name]; 

[SQL] SQL Server String concatenation and functions

In Oracle, you can concatenate two strings with ||

eg: SELECT 'string1'||'string2' FROM DUAL;

In Microsoft SQL Server you should: use (string1+string2) DON'T forget the parentheses

eg: SELECT ('string1'+'string2');

On MSDN you can also find a complete list of all SQL Server String functions.

[SQL] SQL Server SELECT FROM DUAL

In Oracle, you can easily test functions or simple statements with:

SELECT [things to test] FROM DUAL WHERE [conditions];

eg: SELECT LENGTH('string') FROM DUAL;

But Microsoft SQL Server doesn't have the FROM DUAL syntax; instead you should:

SELECT [thing to test] WHERE [conditions];

eg: SELECT LEN('string');

[SQL] Difference in months/years between two dates

In Oracle SQL, you can easily calculate the difference in months between two dates by using the MONTHS_BETWEEN function:

SELECT MONTHS_BETWEEN (date1, date2)

Note that if the difference is X months and Y days, you'll get a float number. You can truncate it with FLOOR or CEIL or ROUND(float_number, 0) depending on your needs.

Should you be looking for the difference in years, you can use the EXTRACT function:

SELECT EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2)

This will obviously always return an integer.

If you're using Microsoft SQL Server, you may find DATEDIFF useful instead.

[Java] Sort Map by Keys

If you have a Map (usually a HashMap since a TreeMap is already sorted) and need to sort it by keys, you will find that there's no method you can call directly on it.

What you can do however, is to create a SortedSet (we'll use a TreeSet) of the Map's keys and then use it alongside the Map.

Example:

myMap is a HashMap<K,V>

//Create sorted set from myMap - this already sorts the keys!
SortedSet mySortedMap = new TreeSet(myMap.keySet());
//Loop on it
for(Iterator iter = mySortedMap.iterator(); iter.hasNext();){
     //Obviously, replace K and V with your data types
     K key = (K) iter.next();
     V value = (V) myMap.get(key);
     /* do what you need */
}

If you are luckier than me and use Java 5+ you can type all collections directly in the declaration with the <K,V>syntax and also use the for-each loop to cycle through the sorted set
for(K key : mySortedMap){
     V value = (V) myMap.get(key);
     /* do what you need */ }

Disable Windows disk cleanup warning / Disabilitare il messaggio di pulizia disco

ENGLISH:

When one of your disks gets low on space, Windows warns you and suggests you to run the Disk cleanup utility.
If you want to disable the warning, open the Registry Editor (Start->Run, type regedit) then navigate to

HKEY_CURRENT_USER
-Software
--Microsoft
---Windows
----CurrentVersion
-----Policies
------Explorer 

If you are missing the Explorer folder, simply create it by right clicking on Policies and selecting New->Key

Now check for the NoLowDiskSpaceChecks entry. If it’s not already there, create it via Edit->New->DWORD then double click it and set its value to 1.

[Java] JSP Jasper/Tomcat code too large for try statement error

While working on a rather large JSP with Java and Tomcat, you may have Jasper sooner or later raise the "code too large for try statement" error.

This happens if your JSP contains a lot of code, especially Java code (incapsulated in the <% %> tags) or very very long comments.
In fact, before being presented as an HTML page, the JSP is compiled and in the process, it is enclosed in a try-catch statement which, like any method, has a maximum bytecode size of 64KB (65535 bytes).

The only way to avoid this issue is to split the JSP into multiple pages and then include them using the:

<jsp:include page="file.jsp"/>

directive which compiles the page BEFORE including it instead of:

<% @include file="file.jsp" %>

which embeds the page "as is"

[Java] ConcurrentModificationException

While iterating over any Collection such as Lists, HashSets, etc.. in Java, you may find the need to add or remove items and in doing so you may encounter the ConcurrentModificationException.

You can solve this by cycling over the collection using an Iterator:

Iterator iterator = [collection].iterator();
while (iterator.hasNext()) {
    [type] element = iterator.next();
    if([conditions]){

       iterator.[method];

    }
}

and then calling the iterator.add() or iterator.remove() method.

Note that in later Java versions you may need to type the Iterator as:

Iterator<[type]> iterator = [collection].iterator();

Test the number of different lines between two files with diff and wc

With diff and wc,  you can check the number of differing lines between two files as:

diff file1 file2 | wc -l

[SQL] Left and Right Join inside WHERE condition

In Oracle SQL you can perform a LEFT(or RIGHT) JOIN inside the FROM statement (SQL ANSI-89 standard) with:

SELECT *

FROM table1 RIGHT JOIN table2 ON table1.id1=table2.id2

or inside the WHERE condition as:

SELECT *
FROM table1, table2
WHERE table1.id(+)=table2.id

The LEFT JOIN is achieved with = table2.id(+) instead

Internet Explorer CheckBox onChange event

When working with Internet Explorer, you may notice that the onChange event, when tied to a CheckBox, won't work as you'd expect it to. You may indeed notice that the field linked to the event must lose focus before firing the event.

You can correct this by using the onClick event.

[SQL] String to Date

In SQL, you can create a Date value from a String representation with:

TO_DATE('string_date', 'pattern');

Where string_date is the String representation of the date you are working on, like '31/12/1900' and pattern defines how your date is represented, as 'dd/mm/yyyy' in our example.

You can also test two Date values using  the < = > ! operators.

[Java] Calendar Date to Timestamp

In Java you can convert a Date to a Timestamp simply by:

Date date = Calendar.getInstance().getTime();

This will return the current date, then:

Timestamp timestamp = new Timestamp(date.getTime());

where date.getTime() will return a long.

[Java] Format date with locale

In Java, you may need to format a date using a particular pattern or locale. You can easily do it with SimpleDateFormat:

SimpleDateFormat sdf = new SimpleDateFormat(pattern, locale);

Date date = Calendar.getInstance().getTime();

sdf.format(date); 

This code formats the current date using the given pattern and locale.

[SQL] Select column, max(count(column))

In SQL, to know which row id has the MAX(COUNT(*)) in a table, you would like to:

SELECT id, MAX(COUNT(*))

FROM table

WHERE [conditions]

But this will not work on its own, instead you should:

Excel spreadsheet functions - Funzioni foglio di calcolo

When working with Microsoft Excel, you will find the need to use spreadsheet functions sooner or later. Those functions can be put directly into a cell and do not require any knowledge of Basic.

However, their names are translated into the application language so non English copies will have localized function names; this means you will need to translate them into your locale before being able to use them otherwise you will get the error #NAME?

Remember that this operation is not necessary in order to view Excel files as upon opening they will automatically be converted to work with the application locale.

Here is a descriptive list of Excel 2003 functions.

Here you can translate function names between different languages.

Note: the same applies to OpenOffice.org / LibreOffice Calc

[SQL] Oracle LIMIT clause

To limit the number of rows returned by a query, in standard SQL you would:

SELECT *

FROM table

WHERE [conditions]

LIMIT number

In Oracle you just add ROWNUM to the WHERE clause like:

SELECT *

FROM table

WHERE [conditions] AND ROWNUM=1

To have the equivalent of SQL "LIMIT 1"

[PL/SQL] Test a string for a numeric value

In PL/SQL, you can easily check whether a string contains numeric values with:

LENGTH(TRIM(TRANSLATE(string, '0123456789', ' ')))

This will return null if the string contains only numeric characters otherwise it will return the number of non-numeric characters in it.

[How to] Run multiple Firefox instances

If you'd like to run multiple Firefox instances on Windows without having to fiddle with the profile settings and maybe run different versions too, you can always install both Firefox and Firefox portable (no need to have the same version).

Simply browse to the Firefox portable installation directory and edit the FirefoxPortable.ini file changing AllowMultipleInstances=false to AllowMultipleInstances=true

That's it, you can now run the two browsers side by side.

Grog application development

So, you decided to give Grog, the PaaS based on OSGi for Public Administrations and private networks a try and downloaded, installed and configured it. Now you'd like to test it with some applications of yours, but how?

In this post  I will describe how to develop and package an OSGi application so that it is Grog-ready.

Grog setup and configuration

In this tutorial we are going to install and configure the prototype for Grog, a PaaS based on OSGi for Public Administrations and private networks.

Grog, a PaaS based on OSGi for Public Administrations and private networks

As a result of my 9-months thesis work, I now present Grog, a PaaS based on OSGi for Public Administrations and private networks.

[Java] Find free port

Getting the number of an unused port on the system via Java 7 is as easy as:


import java.net.ServerSocket;


public static int findFreePort(){
int port = 0;
try(ServerSocket server = new ServerSocket(0)){
port = server.getLocalPort();
}catch(Exception e){
System.err.println("unable to find a free port");
return -1;
}
return port;
}


We do not need to call a server.close() since Java 7 does that automatically for us as we used the new try-catch block with resources declaration

[Java] Access MySQL with Hibernate

In this post we will give a fair example on how to access a MySQL database via Hibernate in Java.
For this example we will be using MySQL server 5.5, Hibernate 3.3 and the Eclipse IDE.

ECF remote OSGi DS Declarative Services example

In this post we will give a fair example of the workings of the OSGi DS Declarative Services remoted with ECF.

For this example we will be using the Eclipse IDE for Java with the ECF Remote Services Target Components  plug-in, the Equinox framework and ZooKeeper as a service discovery provider.

r-OSGi remote OSGi example

In this post we will give a fair example of the workings of the OSGi services remoted through r-OSGi.

In order for the example to work, we must have an OSGi framework with the r-OSGi extension. Also, make sure the 9278 port on your system is free as r-OSGi uses it for its purposes.

DOSGi DS Distributed OSGi Declarative Services example

In this post we will give a fair example of the workings of the OSGi DS Declarative Services remoted with DOSGi.

We can use either one of the following frameworks:

• Apache Camel
• Apache Aries
• Apache CXF

and the Eclipse IDE (for Java). The code can be launched either inside Eclipse or from a standalone OSGi framework.

OSGi Blueprint Services example

In this post we will give a fair example of the workings of the OSGi Blueprint Services.

We can use either one of the following frameworks:

• Eclipse Gemini


• Apache Camel


• Apache Aries

and the Eclipse IDE (for Java). The code can be launched either inside Eclipse or from a standalone OSGi framework.

OSGi DS Declarative Services example

In this post we will give a fair example of the workings of the OSGi DS services.

I suggest you read a good OSGi tutorial by Lars Vogel before moving further if you are not much confident with the OSGi specification.

We are working with the Equinox OSGi framework, which is part of the Eclipse IDE (for Java). The code can be launched either inside Eclipse or from a standalone OSGi framework; for the latter option, grab the following jar packages from under the plugins/ folder of our Eclipse installation:

org.eclipse.osgi
org.eclipse.equinox.ds
org.eclipse.equinox.util
org.eclipse.osgi.services

and copy them in a folder of your choice.

Why beer is good for the brain

Well ya see, Norm, it's like this. A herd of buffalo can only move as fast as the slowest buffalo. And when the herd is hunted, it is the slowest and weakest ones at the back that are killed first.
This natural selection is good for the herd as a whole, because the general speed and health of the whole group keeps improving by the regular killing of the weakest members.

In much the same way, the human brain can only operate as fast as the slowest brain cells. Excessive intake of alcohol, as we know, kills brain cells. But naturally, it attacks the slowest and weakest brain cells first. In this way, regular consumption of beer eliminates the weaker brain cells, making the brain a faster and more efficient machine.
That's why you always feel smarter after a few beers.

Cheers, 1982