Creating a Queue in Oracle

For this particular example, let us assume that the information being between application takes on the form of two fields, row_id and row_status. Given that, we will create this following object type, which will be used by the queue shortly.

create or replace type obj_stuff as object (
	row_id	number,
	row_status	varchar2(10)
);

With the object type created, we will first create a queue table based on this object type, followed by a queue.

declare
begin
	dbms_aqadm.create_queue_table (
		queue_table	=>	'myschema.stuff_queue_table',
		queue_payload_type	=>	'myschema.obj_stuff'
	);
	dbms_aqadm.create_queue (
		queue_name	=>	'myschema.stuff_queue',
		queue_table	=>	'myschema.stuff_queue_table'
	);
end;

With the script above, we now have the queue table and the queue set up. To start the queue, use the .start_queue script, as below.

declare
begin
	dbms_aqadm.start_queue (
		queue_name	=>	'myschema.stuff_queue',
		enqueue	=>	true
	);
end;

Using dummy table to select pseudocolumns

In Oracle, the dummy table is “dual”. It is present in all Oracle database installations, thus no special setup is needed to use it. Below are some samples on how to use this table.

-- Getting the current system date:

PROMPT> select sysdate from dual;

SYSDATE
---------
06-AUG-10

-- Getting the current logged-in user:

PROMPT> select user from dual;

USER
------------------------------
YTCDBA

-- Display the result of some string manipulation

PROMPT> select substr('hello world',1,7) from dual;

SUBSTR(
-------
hello w

The DB2 equivalent of Oracle’s “dual” is “sysibm.sysdummy1”. Below is a sample of the usage.

-- Getting the current system date:

PROMPT> select current date from sysibm.sysdummy1;

00001
----------
2010-08-06

-- Getting the current logged-in user:

PROMPT> select current user from sysibm.sysdummy1;

00001
-----
USER1

-- Display the result of some string manipulation

PROMPT> select substr('hello world',1,7) from sysibm.sysdummy1;

00001
-------
hello w

It is also worthy of note that MySQL and SQL Server can select pseudocolumn data without the use of a dummy table. For example, in SQL Server, you can get the current date by running “select getdate();” without a “from” clause in your SQL statement.

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;

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

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;

Changing Oracle 11g password sensitivity

When I was testing for the planned Oracle 11g upgrade, I was fairly excited about the possibility of being able to add an additional dimension to password complexity by introducing case sensitivity. Unfortunately, after some investigation, I found several legacy applications that were still in use in my environment that either had many hard-coded all upper-case passwords or have program functions that changed all casing to upper case. While introducing case sensitive passwords was definitely a goal for my environment, I also understood that the developers responsible for the applications noted above would not have enough time to make the changes to meet my database upgrade schedule. My solution was to temporarily disable the case sensitivity feature in Oracle 11g, thus I could still proceed with my upgrade, while providing the developers an extra month or two to make modifications to their code to comply with the new security model.

To disable password case sensitivity in Oracle 11g:

alter system set sec_case_sensitive_logon=false;

Again, you should carefully evaluate your situation before disabling this new feature found in Oracle 11g. Password sensitivity may be a great tool in safeguarding your data from the unauthorized.

Adding or subtracting months or years for Oracle dates

I ran into the need to do this because one of my users performed a big data import, and it was not until he finished that he realized somewhere along the way when he was preparing the data, instead of “2009”, some of the years came out to be “1909”. To fix this in the database, I made use of Oracle’s built-in numtoyminterval() function, which stands for “Number to Year/Month Interval”. The syntax is as follows:

numtoyminterval(n, interval_name)

“n” is the quantity, and “interval_name” is either “year” or “month”. The following example illustrates its basic usage.

select sysdate as now,
sysdate + numtoyminterval(1,'month') as plus_1_month,
sysdate + numtoyminterval(3,'month') as plus_3_months,
sysdate + numtoyminterval(12,'month') as plus_12_months,
sysdate + numtoyminterval(1,'year') as plus_1_year
from dual;

NOW       PLUS_1_MO PLUS_3_MO PLUS_12_M PLUS_1_YE
--------- --------- --------- --------- ---------
20-MAR-09 20-APR-09 20-JUN-09 20-MAR-10 20-MAR-10

Armed with this Oracle built-in function, I simply ran the following update statement to correct the bad data that my user had imported today.

update example_table
set date_goes_here = date_goes_here + numtoyminterval(1,'year')
where date_goes_here between '1-jan-1909' and '31-dec-1909'
and trunc(entry_date,'ddd') = trunc(sysdate,'ddd')
and entry_by = 'careless_user';

Rollback Oracle transactions in VBScript

The key to allow transactions lies in the command “.BeginTrans” that you should declare immediately after making the database connection, as seen in the example below.

option explicit
dim dbType, dbHost, dbName, dbUser, dbPass

dbHost = "hostname"   ' Hostname of the Oracle database server
dbName = "database"   ' Name of the database/SID
dbUser = "username"   ' Name of the user
dbPass = "password"   ' Password of the above-named user

dim conn
set conn = CreateObject("ADODB.connection")
conn.ConnectionTimeout = 30
conn.CommandTimeout = 30
conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")

conn.BeginTrans

conn.execute("update order set order_total=(select qty * unit_price from order_detail where order_id=123) where order_id=123")

dim checkOrderTotal
set checkOrderTotal = conn.execute("select order_total from order where order_id=123")

if (checkOrderTotal("order_total") < 0) then
	conn.RollbackTrans
	' TODO : Send an email to accounting department to check it out
else
	conn.CommitTrans
end if

conn.close
set conn = nothing

Notice the if-else clause near the bottom. If the order total somehow ended up a negative number, we rollback the transaction (and presumably would notify someone to check it out at this point), we roll back the transaction. Otherwise, as seen in the "else" clause, we proceed with committing the Oracle transaction.

It is important that you must issue either ".RollbackTrans" or ".CommitTrans" before the connection is terminated, otherwise you might get the VBScript ADODB connection error 800A0CAE, "Connection object cannot be explicitly closed while in transaction."