This page is a collection of tips, techniques and ideas I've collected as I've learnt about Oracle. I'd been working with Microsoft SQL Server and MySQL for a number of years so I had to map that knowledge to Oracle's way of doing things. I hope these notes are useful to you.
Oracle offer free downloads of their database products for personal use, allowing you to become learn and become familiar with a high-end database. However, the supplied applications can be overwhelming for beginners. Free for personal use, TOra is a feature-packed 'Toolkit For Oracle' and a great way to get started.
When you start working with someone else's database, it can take a while to find your way around it. If you miss the database diagram feature of Microsoft SQL Server Enterprise Manager, and can get hold of a licence for Visio, you can 'reverse engineer' a database diagram. From the 'Database' menu, select 'Reverse Engineer', choose your drivers and connection strings, and let it go. It won't show datatypes, but it will show primary and foreign keys and compulsory fields. The support for different drivers seems a bit hit and miss. I've made more luck with the "ODBC generic driver" or "Generic OLD DB driver" than the Oracle server driver. You may need to scroll down to see the views.
Check this comparison of the differences between Oracle 9i SQL and Standard SQL. You'll also need to look at the differences between MS SQL Server's implementation of the SQL Standard and Oracle.
The wha? Just trust me. If you're used to visual environments like Enterprise Manager (Oracle or SQL Server) and now you're working with SQL*Plus, it can be difficult to get an idea of what's there. One quick way to find out is to query Oracle's system tables. If you want to see a list of all tables belonging to your user, try:
select table_name from user_tables
You can 'desc usertables' to see all the fields but 'table_name' is a good column to start with. You can also 'desc user_constraints', 'user_sequences' or user_triggers' and pick a few columns to view.
Oracle can create them for you, when you use the 'create table' wizard, or you can create them by hand. Oracle implements keys as constraints, which might be a new way of thinking about keys for non-Oracle developers. It's not that difficult to create them by hand, and you can give them nice, sensical names that will make your developers' and users' lives easier. If you're working with a new database, you can include them in your 'create table' scripts. 'Not null' constraints are named and created in the line where you create the field, primary, unique and check constraints go at the end.
CREATE TABLE PERSON
(PERSON_ID NUMBER,
GENDER VARCHAR2(1) CONSTRAINT NN_GENDER NOT NULL,
FIRST_NAME VARCHAR2(50) NULL,
LAST_NAME VARCHAR2(50) CONSTRAINT NN_LASTNAME NOT NULL,
CONSTRAINT PK_PERSON PRIMARY KEY (PERSON_ID),
CONSTRAINT UK_PERSON UNIQUE (LAST_NAME),
CONSTRAINT CK_GENDER CHECK (GENDER IN ('M', 'F', 'U'))
) ;
Check constraints: alter table YourTable add constraint ck_YourTable_YourColumn check (YourColumn in ('Y','N', 'Your Values'))
Not null: alter table YourTable modify YourColumn constraint NN_YourColumn not null; You might need to run an update on the table to make sure there aren't any existing null values first.
Adding a default value: alter table YourTable modify (YourColumn default 'N')
If you've already used the wizard and you have lots of constraints with memorable names like 'SYS_C004729' you can rename constraints.
alter table YourTable add constraint fk_YourTable_YourColumn foreign key (YourColumn) references YourOtherTable(YourColumn)
%, not *. And _ to match any single character.
IF you want to update all values of a column to, for example, make all values lowercase, or uppercase, or trim whitespace, try this:
update tablename set columnname = upper(columnname)
Oracle doesn't have a boolean data type. (Though PL/SQL does). Oracle developers seem to use char(1) or varchar2(1) with values of 'Y'/'N' instead.
MS SQL Server people might be used to the 'top' (e.g. 'select top 10 * from YourTableName order by ImaginaryColumnName') command, to select a limited set of records. MySQL people might be used to 'limit' (e.g. 'SELECT ImaginaryColumnName FROM YourTableName LIMIT 5'). In Oracle, you have to use 'rownum'. For example, 'select ImaginaryColumnName from YourTableName where rownum < 5'
Usually used for surrogate primary keys - that is, indexes that increase automatically to give each row a unique id number.While this is a point and drool operation in MS SQL, or a simple'auto_increment' in MySQL, it's not so simple in Oracle. (Quelle surprise!) First, you need to create a sequence, and then a trigger.
You can create a sequence for each unique id number column you need, or re-use the same one for different numbers. This is weird if you're not used to it, but it's actually quite sensible. Just remember that, as with all automatically generated id numbers, they shouldn't be used where the end user might rely on them because they can be affected by rollbacks and failed transactions. For example, if your company uses automatically generated invoice numbers, generate them programmatically rather than with SQL-level autonumbers or sequences.
The syntax to create a sequence is:
create sequence YourSequenceName [increment by n] [start with n] [nomaxvalue]
Sequences can be renamed. E.g. 'rename OldSeq to NewSeq'
You can then call the next number in the sequence when you do an insert.
Helpful links: Comparing Autonumbering Methods in Different Relational Database Management Systems
The first time I saw 'varchar2' as a data type, I wondered what happened to plain old varchar or varchar1. Rumour has it varchar2 was created because the definition of the ANSI SQL standard varchar looked like changing at one point, and Oracle wanted to keep the old standard datatype. In the end, the standard didn't change, but Oracle want you to use varchar2 anyway.
It's pretty simple using the 'insert into' syntax.
insert into table_a (column1, column2, column3, column5) select column1, column2, column3, column5 from table_b
Most sources will tell you it's because the script has to end with a new line character, but as I've discovered it can also be because you've set the linesize (lines) variable too big for your system. On my system, a safe value seems to be 2400 though you could probably get to 2499.
If you're not sure which issue is causing your truncation, the new line issue seems to truncate at the length of the last line, while the linesize variable set too large issue will truncate at a much higher number.
In Oracle 8, the Schema Manager has a beautiful 'create like' function. Right-click on the table you want to make a copy of, and select 'create like'. This will create a table with all the constraints of the original, ideal for testing imports or during application development.
If you want to quickly populate a test table but you don't need to worry about the constraints, use 'create table NAME as select * from '
You can rename columns. Nice one.
The syntax is:
alter table YourTableName rename column OldColumnName to NewColumnName
You can also modify columns on the fly. For example, to increase the length of a column, try:
alter table YourTableName modify ( YourColumnName VARCHAR2(150) )
If you want a rename a table, the syntax is:
alter TABLE OldTableName rename TO NewTableName
I've found these links helpful: