Manually incrementing an Oracle sequence by a defined amount

Let’s say you have a sequence object created called myschema.seq_mysequence which has the next value of 3000 and it normally increments 1 at a time, and let’s say that you need to reserve all numbers between 3000 and 4000 for a special purpose, thus you need to manually advance the sequence from 3000 to 4000. This is how you can increment the sequence 1000:

-- First, alter the object so the next increment will jump 1000 instead of just 1.
alter sequence myschema.seq_mysequence increment by 1000;

-- Run a select to actually increment it by 1000
select myschema.seq_mysequence.nextval from dual;

-- Alter the object back to incrementing only 1 at a time
alter sequence myschema.seq_mysequence increment by 1;

Javascript delay function

The sample Javascript delay() function below performs a delay for X milliseconds. During the time specified, users can proceed to perform actions on the particular web page.

Quick reminder: 1000 milliseconds = 1 second, 60000 milliseconds = 1 minute

function delay(milliseconds) {
	var cmd = 'alert("Hello World!  This should have appeared after ' + milliseconds + ' milliseconds.");';
	setTimeout(cmd, milliseconds);
}

Below is a live example, followed by the HTML/Javascript code used.



<script type="text/javascript">
function delay(milliseconds) {
	var cmd = 'alert("Hello World!  This should have appeared after ' + milliseconds + ' milliseconds.");';
	setTimeout(cmd, milliseconds);
}
</script>
<input type="button" onclick="javascript: delay(3000);" value="Do a 3-second delay">

If you are looking for a way to “freeze” the browser during the wait time, try the Javascript pause() method.

How to use the ampersand (&) symbol in SQL statements as a literal value

Because the ampersand symbol, “&”, denotes the start of a variable, you might be caught off guard when you are trying to use it as a literal value. For example:

SQL> select 'I like fish & chips' as x from dual;

Enter value for chips: old   1: select 'I like fish & chips' as x from dual
new   1: select 'I like fish ' as x from dual

X
------------
I like fish

1 row selected.

Of course, this is not what what you intended; you meant for the literal string to appear as “I like fish & chips”. There are two ways for you to get around this problem. The first method is to make sure the ampersand symbol is at the end of a string, thus requiring you to break up your sentence in one of the two following ways.

SQL> select 'I like fish &' || ' chips' as x from dual;

X
-------------------
I like fish & chips

1 row selected.

SQL> select concat('I like fish &',' chips') as x from dual;

X
-------------------
I like fish & chips

1 row selected.

The second method is to escape the ampersand symbol in the following manner.

SQL> set escape on;
SQL> select 'I like fish & chips' as x from dual;

X
-------------------
I like fish & chips

1 row selected.

Using Oracle RMAN to duplicate a database

Today, I was working with a code base still in development, but really wanted to use it against the latest production data. To achieve this, I used Oracle 11g’s Recovery Manager, “RMAN”, to duplicate the latest content of my production database onto a development Oracle server that I have set up to work with my development code. Let us assume that my production database name was “prodDb” residing on server “prodServer”, the database I was creating is “devDb” residing on “devServer”.

First, I needed to create some directories on devServer.

mkdir -p /u01/app/oracle/admin/devDb/adump
mkdir -p /u01/app/oracle/flash_recovery_area/devDb
mkdir -p /u01/oradata/devDb

Then, on devServer, I used the scp command to copy the prodServer password file to devServer.

scp prodServer:/u01/app/oracle/product/ver_no/db_1/dbs/orapwproddb $ORACLE_HOME/dbs/.

Next, I created a dummy init file so we can mount this empty devDb database.

echo 'DB_NAME=devDb' > $ORACLE_HOME/dbs/initdevDb.ora
echo 'control_files=(/u01/oradata/devDb/control01.ctl,/u01/oradata/devDb/control02.ctl,/u01/oradata/devDb/control03.ctl)' >> $ORACLE_HOME/dbs/initdevDb.ora

I mounted devDb, then restarted the listener.

(From terminal prompt, log in to SQL*Plus as sysdba)
sqlplus /nolog
conn / as sysdba

(SQL*Plus command to mount database)
startup nomount pfile=$ORACLE_HOME/dbs/initdevDb.ora;
exit;

(Back in terminal prompt, restart the listener)
lsnrctl stop
lsnrctl start

My work on the devServer was then done. I moved on to the prodServer machine; from the terminal prompt of the prodServer, I started RMAN.

(From terminal prompt, run "rman")
rman

(In RMAN prompt, connect to prodDb as target, and to devDb as the auxiliary)
connect target sys@prodDb
connect auxiliary sys@devDb

(In RMAN, run the following script; you may wish to modify to suit your needs, of course)
run {
   duplicate target database to devDb from active database
   spfile
           parameter_value_convert 'prodDb','devDb'
           set db_unique_name='devDb'
           set service_names='devDb.mydomain.com'
           set log_file_name_convert='prodDb','devDb'
           set log_archive_config=''
           set fal_client=''
           set fal_server=''
           set log_archive_dest_1=''
           set memory_max_target='1200m'
           set memory_target='1200m'
           set sga_max_size='800m'
           set sga_target='800m'
           set db_recovery_file_dest_size = '20G'
           db_file_name_convert 'prodDb','devDb'
        ;
     }

Inserting multiple records at once into a database table

For demonstration purposes, we are going to assume that we have a table named “employees” with two fields, name and department.

Oracle

insert all
  into employees (name, department) values('Aaron','Accounting')
  into employees (name, department) values('Ibis','IT')
  into employees (name, department) values('Mary','Marketing')
  into employees (name, department) values('Paul','Useless')
select '' from dual;

MySQL, PostgreSQL, and SQL Server

insert into employees (name, department) values
  ('Aaron','Accounting'),
  ('Ibis','IT'),
  ('Mary','Marketing'),
  ('Paul','Useless');

Shorten long URLs with Perl, cURL, and the short.to API

#!/usr/bin/perl

### This is the long URL we wish to ultimately shorten
$url = "http://ww2db.com/battle_spec.php?battle_id=10";



sub body_callback {
        my ($chunk,$context)=@_;
        push @{$context}, $chunk;
        return length($chunk);
}

use Curl::easy;

my $curl= Curl::easy::init();
Curl::easy::setopt ($curl, CURLOPT_SSL_VERIFYHOST, 0);
Curl::easy::setopt ($curl, CURLOPT_SSL_VERIFYPEER, 0);
Curl::easy::setopt ($curl, CURLOPT_URL, "http://short.to/s.txt?url=$url");
Curl::easy::setopt ($curl, CURLOPT_WRITEFUNCTION, &body_callback);
my @body;
Curl::easy::setopt ($curl, CURLOPT_FILE, @body);
Curl::easy::setopt ($curl, CURLOPT_ERRORBUFFER, "errorBuffer");

if (Curl::easy::perform ($curl) != 0) {
	print "Fail: $errorBuffern";
}
else {
	### Now we have the URL stored within @body for our use.
	print "Success: @bodyn";
};

Curl::easy::cleanup($curl);

Update Twitter status programmatically with PHP and cURL

function twitterUpdate($u, $p, $upd) {
	// $u is your username (string)
	// $p is your password (string)
	// $upd is your status update (string)

	$url = 'http://twitter.com/statuses/update.xml';

	$curl_handle = curl_init();
	curl_setopt($curl_handle, CURLOPT_URL, $url);
	curl_setopt($curl_handle, CURLOPT_CONNECTTIMEOUT, 2);
	curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($curl_handle, CURLOPT_POST, 1);
	curl_setopt($curl_handle, CURLOPT_POSTFIELDS, "status=".$upd);
	curl_setopt($curl_handle, CURLOPT_USERPWD, $u.":".$p);
	$buffer = curl_exec($curl_handle);
	curl_close($curl_handle);

	if (empty($buffer)) {
		return 'fail';
	}
	else {
		return 'success';
	}
}

Example usage is below.

$u='username';
$p='password';
$upd='Hello world!';

$twitterUpdateResult = twitterUpdate($u, $p, $upd);

echo $twitterUpdateResult;

Recover from a bad update statement with Oracle flashback

This morning, a coworker came to my office with an awkward smile on his face. “Oops”, he started, “I ran a bad query and I updated a bunch of records incorrectly.” He thought he had messed up big time, but actually, I was able to recover data for him quite easily, utilizing our Oracle 11g database’s flashback feature (note: this method applies to Oracle 10g as well).

I asked him two questions: What was the update SQL statement that he ran, and when did he run it? For our example, let’s say the SQL statement was as follows, and he ran it at 10am.

-- Bad SQL statement executed by user
update myTable
set field1 = field2, field2 = null
where field1 is null;

As you can see, since he is moving field2 into field1, and previously other records had already gone through this process, he could no longer tell which ones were modified by him at 10am and which ones were done previously. Since it only had been a few minutes since he committed the bad update statement, I knew I had plenty of time to recover the data. Actually, I did not need much time at all. This is what I did to identify the records he modified:

create table myTable_comparison
as
select before.pkey_field, before.field1, after.field1
from myTable after, 
myTable as of timestamp to_date('6/1/2009 09:59:00','mm/dd/yyyy hh24:mi:ss') before
where before.pkey_field=after.pkey_field
and before.field1 <> after.field1;

Note in the above example, I used the additional clause … as of timestamp [insert a date value here] to pull out a view of what the data looked like today at 9:59am, a moment before my coworker updated the data. This creates the table myTable_comparison that contains all rows which had a different before and after field1 value, using myTable’s primary key column as the unique identifier to help my coworker review what had been changed. He confirmed the records in this table are exactly what he wanted to revert, so I rewrote the select statement into an update statement to revert the data back to the previous state.

In this case, my coworker mistakenly updated values. What if he had mistakenly deleted records instead? To recover, we just need to find the records that was present previously, but no longer present anymore by using an outer join between the before and after tables.

select before.pkey_field, before.field1, after.field1
from myTable after, 
myTable as of timestamp to_date('6/1/2009 09:59:00','mm/dd/yyyy hh24:mi:ss') before
where before.pkey_field=after.pkey_field
and before.pkey_field=after.pkey_field(+) and after.pkey_field is null;

Java hashtable sample usage

A hashtable stores key-value pairs. In Java, it can be found in the package java.util.Hashtable. The examples below demonstrates some ways hashtables can be used in Java.

Instantiating a Hashtable

// Generic hashtable
Hashtable hashtable = new Hashtable();
// Hashtable with a specified initial size
Hashtable hashtable = new Hashtable(100);

Adding Data

hashtable.put("A", 1);
hashtable.put("B", 2);

In the example above, “A” and “B” are the keys, and 1 and 2 are the corresponding values.

Displaying Hashtable Info

// General info
System.out.println("Is Empty (returns true/false)?: " + hashtable.isEmpty());
System.out.println("Hashtable size (returns integer): " + hashtable.size());
// Query by key
System.out.print("Contains key \"A\" (returns true/false)?: " + hashtable.containsKey("A"));
// Query by value
System.out.print("Contains value 1 (returns true/false)?: " + hashtable.contains(1));

Getting Data

// Get by a particular key
System.out.print("Value for Key \"A\" is: " + hashtable.get("A"));
// Get all
for (String key : hashtable.values()) {
	// ...
}

Removing Data

hashtable.remove("A");