Usual Things You Need To Find In Oracle

There are always silly little things that you always need to do but always can’t remember the syntax to do it. Well in Oracle some of these things are finding the full column and table name when you only know part of the column name; and the other is getting the full value (meaning) of a list of values from the cg_ref_codes table.

So lets look at the query to get the table name and column name:

select
  table_name,
  column_name
from
  all_tab_columns
where
  column_name like '%COL_NAME%'

This will give you a list of table names and column names which will hopefully help you find what you where looking for.

Oracle has this wonderful technology called Oracle Forms that basically helps you develop applications that makes very good use of all of the Oracle’s database features.

One of the tables that is created with Oracle Forms is the cg_ref_codes table. This table holds the column name, low value, meaning, and lots of other info that you probably don’t need to know. For example, if you have a column that holds the type of customers with values: ‘VIP’, ‘Common’, and ‘Special’; you could specify them in the Oracle Forms as single characters: ‘V’, ‘C’, and ‘S’.

If you’re running a large system you will most likely forget the meanings of ‘V’, ‘C’, and ‘S’. However, you can simply use the following query to get the full values you need:

select
  rv_low_value,
  rv_meaning,
  rv_domain
from
  cg_ref_codes
where
  rv_domain = 'CUST_TYPE'

The rv_domain is the column name that holds the values for your customer types. The rv_low_value will be ‘V’, ‘C’, and ‘S’; and the rv_meaning will be ‘VIP’, ‘Common’, and ‘Special’.

Simple yet easy to forget!

A Simple Way To Optimized Your Query

If you’re running a large query against your Oracle database and don’t want to kill it, then writing optimized queries is a must. One of the most simple and basic, yet powerful ways of doing this is by using an indexed column in your where clause.

The first thing to do is find all the indexes for a table and then use the most appropriate one.

Here is the code:

SELECT INDEX_NAME, COLUMN_NAME FROM ALL_IND_COLUMNS WHERE TABLE_NAME =
'CUSTOMER';

The code if you’re going via a link:

SELECT INDEX_NAME, COLUMN_NAME FROM ALL_IND_COLUMNS@DB_LINK WHERE
TABLE_NAME = 'CUSTOMER';

Although this is something so simple, many people forget about indexes and how useful they are. All that’s left to do now is incorporating the index(s) you found using the query above into your main query. That will make a huge difference when you’re selecting millions of rows and joining millions of rows.

Dumping Large Volumes Of Data In Oracle

I recently had to do a dump of our system to csv files for analysis by auditors. Now exporting 23 million rows is not the easiest thing to do. Luckily I picked up a few tricks from our datawarehousing guru.

The first thing you need to do is move the data from the production system to the datawarehouse. For example if you need to export data from a table called supplier_claims this is roughly what you would do:

First logon to the warehouse and run the following command:

create table supplier_claims as
select * from sharedb.supplier_claims@dblink where 1 = 2;
alter table supplier_claims compress;

This ensures that the table is created with the exact columns and types as the live table. The where clause ensure no rows are dumped right yet. The compress statement will ensure that the data is compressed for this table. This not only saves database storage but it is also more efficient since the server has less I/O to do. Since I/O is slower this makes sense.

Now you can dump the rows you need:

insert into <code>supplier_claims </code>/* APPEND */
select * from sharedb.supplier_claims@dblink where billing_date &gt;= '01-JAN-09'

Now you have the data you need on the warehouse which means the production system will not have to suffer and you can work with the data anytime of the day.

A very useful and valueable tool that has saved me hours is the sqluldr2 tool. It is so simple and easy to use. You can export the data you need into a csv file specifying the delimiter you want and if you need headings included.

Clean A Dirty Text File Into CSV Using Python

So since I’ve been working in the BI team at MMSA in Durbs there wasn’t many requests for me to export data to csv. Now as simple as this sounds it is very difficult doing this on millions of rows. Since I’m still a nOOb at Oracle and datawarehousing, it proved to be a challenge completing this task.

One of the barriers is that there is an access limit on the production system that basically won’t let you put too much pressure on it (an I/O usage restriction). So what I have to do is dump the data at night or during certain times. Using the SQL Plus tool I spooled the data to a text file. The problem is that I didn’t set the page size and a whole host of other commands that would write the data in a much easier to work with format.

Unfortunately I was in such a rush and ended up with a very dirty text file. Since I know Python I thought maybe I can use my skills to clean the file. I spent about 4-5 hours on this little baby:

def make_csv_row(temp):
 count = 1
 the_row = ''

 for t in temp:
  t = t.replace('\t',' ')
  t = '"|"'.join(t.split())

  t = '"' + t + '"'
  if count != 4:
   t = t + "|"

  count += 1
  the_row += t
 return the_row

print 'Opening files'

OPEN_FILE = 'AUG08.csv'
OUT_FILE = 'AUG08_step_1.csv'

f = open(OPEN_FILE, 'rb') # file to read
fout = open(OUT_FILE, 'w') # file to write

print 'Process file'

counter = 0
for line in f.readlines():
 if not(line.find('---------') != -1 or line.find('FIELD1') != -1 or line.find('FIELD2') != -1 or line.find('FIELD3') != -1 or line.find('FIELD4) != -1):
  if line != ('\r\n'):
   fout.write(line)

print 'Completed'
# close all files
f.close()
fout.close()

print 'process step 2'
f = open(OUT_FILE, 'rb') # file to read
fout = open(OPEN_FILE, 'w') # file to write

count = 1
temp = []
fout.write('"FIELD1"|"FIELD2"|"FIELD3"'  + '\r\n')

for line in f.readlines():
 temp.append(line.replace('\r\n', '')) 

 if count == 4:
  t = make_csv_row(temp)
  fout.write(str(t) + '\r\n')
  count = 0
  temp = []
 count = count + 1

f.close()
fout.close()

Please don’t even ask what this does since it is too much of an effort to explain and still confuses me. Unfortunately this script almost worked but there was one little bug that prevented me from using it. So close yet so far.

There are probably a lot of refinements that could be done with this code but remember that I hadn’t touched Python in almost 6 months.

Limiting a Result Set in Oracle

The limit clause in SQL is pretty easy to use and very handy. This clause limits the result of your query and is used a lot for pagination and simple testing or debugging.
Here is a simple SQL statment that uses the limit clause:

select * from table_name limit 5;

The above will return 5 rows from the table.

Now this works find on most databases except for oracle. In oracle the limit functionality is as follows:

select * from table_name where rownum <= 5;

It’s kind of strange that Oracle does not implement the limit clause like most other databases but hopefully now you know how to do it using the where clause.