[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'