[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
);