Have you ever tried to order by a VARCHAR2 column that has numeric information in it? Your query is sorted ALPHABETICALLY instead of numerically. That is, your order is 1,10,2,20,200,3,4… instead of 1,2,3,4,10,20,200. If you try to use TO_NUMBER in
How to recreate an Oracle table index
This script will spool out the index create statement with the true size of the index. variable block_size number begin select to_number(value) into :block_size from v$parameter where name = ‘db_block_size’; end; / drop table t_size; create table t_size (owner varchar2(30)
How to find trailing spaces in Oracle scripts
The attached code will list the owner, table, columns containing trailing spaces. This is good for data cleanup (used a lot in data warehousing). You can switch it easily to see the ones with leading spaces. set linesize 300 drop
How to find data that was entered yesterday
Date arithmetic can be convoluted in Oracle. A customer wanted to know how to find all the records entered into a table since midnight. In order to do this, you can convert the column containing the timestamp to character with
How to drop all tables that are empty
Here’s an easy way to drop all the tables that have no data in them. Save this to a file called drop_tab.sql and run by using: @drop_tab After this has run, a file named dr_tab.sql will have been created. You
How to drop a column from a table using Oracle 8i
The DROP COLUMN statement is a new feature in Oracle 8i that allows a column to be dropped from a table. Columns can either be dropped immediately, or flagged as UNUSED and physically dropped later. Once flagged as UNUSED, a
How to create Varrays
Varrays is a type of collector that was introduced with Oracle8. It’s useful if you have a fixed number of repeating columns that you want to represent without using a child table. If the number of elements isn’t known or
How to compare two strings
This allows you to find the difference between two strings by using a single SQL statement The code is shown below : SELECT ltrim(rtrim(translate(string2,string1,rpad(‘ ‘,length(string1))))) from dual;
How to centre data in a column
The following code allows you to centralise data in a column using SQL, if you declared the column to be 20 characters wide. SELECT lpad(‘ ‘,((20-length(dname))/2),’ ‘))||dname dept_name from dept
How to select set of 6 random numbers every time
/* This SQL will a select a set of 6 random numbers every time it is run. The numbers will be between 1 and 49 */ select r from ( select r from ( select rownum r from al_objects where