betweenGo

(Photo: Love’s Old Sweet Song by linda yvonne)

The syntax for doing this is similar to doing an update in one table based on values in another table yet simpler.

INSERT INTO suppliers (name)
SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id);

If you want to add constant values into the insert you can do something like this.

INSERT INTO suppliers (name, city)
SELECT customers.name, 'Toronto'
  FROM customers
  WHERE customers.id = suppliers.id);

For further reading please see SQL INSERT INTO.

(Photo: Rain on a window in Sunnyvale by basictheory)

There are various ways to alter a table and I usually forget what they are so I am writing this post to remind me.

Columns

ALTER TABLE foo DROP COLUMN nickname;
ALTER TABLE foo RENAME COLUMN name TO nickname;
ALTER TABLE foo ADD name VARCHAR2(254) DEFAULT 'Frank' NOT NULL;
ALTER TABLE foo ADD age INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE foo MODIFY name VARCHAR2(500);

Constraints

ALTER TABLE foo DROP CONSTRAINT foo_a_f;
ALTER TABLE foo ADD CONSTRAINT foo_b_f FOREIGN KEY (bar_id) REFERENCES bar (id);

If you forget the name of a constraint and you can try to find it using this handy piece of SQL.

SELECT constraint_name FROM user_constraints WHERE constraint_name LIKE 'foo_%_f%';

I was always doing a commit before exiting SQL*Plus when it occurred to me today that maybe I didn’t need to do that.  Doing a Google search quickly answered that for me.

If you issue a graceful exit (via the “exit” or “quit” command), sqlplus will always issue a commit. However, if you were to be ungracefully disconnected, for example by closing your terminal window, then PMON will issue a rollback like it does with any other disconnected session.

sqlplus commit-on-exit?

Therefore there is no need to do a commit before you exit.

You can also set autocommit on, it is off by default, but I would not recommend doing this.

During interactive usage with sqlplus, Oracle also supports an AUTOCOMMIT option. With this option set to ON each individual SQL statement is treated as a transaction an will be automatically commited right after it is executed. A user can change the AUTOCOMMIT option by typing

 SET AUTOCOMMIT ON

or

 SET AUTOCOMMIT OFF

whereas by typing

 SHOW ALL

a user can see the current setting for the option (including other ones).

Oracle SQL Transactions

When you do an import sometimes you will find you will need to also create a user for this new set of data. Today I find myself in that situation as I imported data from Bell Canada and set up a new user for that data.

1. Create a new user for the data.

   This example creates a user named "foo" with the password "foo".  Foo is a typical user that can create sessions, synonyms, procedures and tables.

   CREATE USER FOO IDENTIFIED BY FOO
     DEFAULT TABLESPACE users
     TEMPORARY TABLESPACE temp
     QUOTA UNLIMITED ON users;
   
   GRANT CREATE SESSION to FOO;
   GRANT CREATE SYNONYM TO FOO;
   GRANT CREATE PROCEDURE TO FOO;
   GRANT CREATE TABLE TO FOO;
   GRANT CREATE VIEW TO FOO;

   For more information please read Oracle’s CREATE USER documentation.

   If you want this user to be a DBA you can grant that to her too

   GRANT DBA TO FOO;

2. Import the dump using the new user.

   This example imports the dump from the file "dump.dmp". This dump was created using the user "foo" and is being imported to the user "foo". The dump will be logged in the file "dump.log".

   imp system/system@example file=dump.dmp log=dump.log fromuser=foo touser=foo

   For more information please read Oracle’s Import Export FAQ.

3. To redo recreate user and then import again.

   If you need to redo an import the easiest thing to do is to drop the user, recreate her and then do the import again. When you drop the user specify CASCADE to drop all the objects in the user’s schema.

   DROP USER FOO CASCADE;

   For more information please read Oracle’s DROP USER documentation.

1. Get the Ruby OCI8 driver.  Download the file that ends with “mswin32.rb” and install like this:

   E:\ruby>ruby ruby-oci8-1.0.3-mswin32.rb
   Copy OCI8.rb to e:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8
   Copy oci8.rb to e:/ruby/lib/ruby/site_ruby/1.8
   Copy oci8lib.so to e:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt
   OK?
   Enter Yes/No: Yes
   Copying OCI8.rb to e:/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8 ... done
   Copying oci8.rb to e:/ruby/lib/ruby/site_ruby/1.8 ... done
   Copying oci8lib.so to e:/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt ... done
   OK

   You can test the driver by running a query using Ruby.

   E:\>ruby -r oci8 -e "OCI8.new('foo','12345','sid').exec(
   'SELECT * from users') do |r| puts r.join(' | ') ; end"

2. Install the ActiveRecord Oracle adapter.gem

   E:\ruby>install activerecord-oracle-adapter --source http://gems.rubyonrails.org

3. Update config/database.yml to connect to Oracle

   development:
     adapter: oracle
     database: sid
     username: foo
     password: 12345
     timeout: 5000

4. Test by doing a rake db:migrate.
5. Test by running the Ruby on Rails server and making sure there are no errors upon startup.

This article is based on these articles.

• Oracle in Ruby on Rails
• Ruby on Rails with Oracle FAQ
• Connecting Ruby on Rails to Oracle on an Intel Mac in Leopard (Mac OSX 10.5)

To list all the users in a database log in as the sysadmin and query the dba_users table.

$ sqlplus sysman/123456@dev01

SQL> select username from dba_users where username like 'Frank%';

Recently our DBA recommended the following for creating tables.

1. All constraints (primary keys, unique keys, foreign keys, etc….) should be declared outside of the CREATE TABLE …. statements, and instead done as ALTER TABLE statements.
2. All tables must have table and column comments to provide information for the data dictionary/schema metadata.

Not Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL,
  PRIMARY KEY (id)
);

Best Practice:

CREATE TABLE items (
  id   VARCHAR2(40) NOT NULL,
  type NUMBER(5)    NOT NULL
);
ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY (id);
COMMENT ON TABLE items IS 'repository items';
COMMENT ON COLUMN items.id IS 'primary key (repository id)';
COMMENT ON COLUMN items.type IS 'item type';

I asked the DBA why this is considered best practice and this is what he said.

The DBA’s put indexes into a different tablespace than the table itself for storage, admin, and somewhat performance reasons (NetApp spreads out the I/O so does not quite apply to Upromise environment).

If the PK is part of the table create statement, they have to break out the statement in order to put the PK into a different tablespace or different storage parameters than the table. Having the statements separate from the start makes things smoother.

- Jeff Janousek, Upromise DBA

This morning I was unable to access my Oracle database which runs on my laptop. It’s the first time I have had this problem.

When I tried to login I saw this.

$ sqlplus foo/foo@dev01

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 3 06:38:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I tried to login without the TNS alias I saw this:

$ sqlplus foo/foo

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 3 06:38:20 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

On the internet I saw recommendations to set your ORACLE_SID and ORACLE_HOME environment variables. But on my system ORACLE_SID is not set and ORACLE_HOME is blank. Finally I just tried restarting the OracleServiceDEV01 service and that worked.

(Photo: Dolphin Crest by jurvetson)

I was wondering how to do this and thankfully someone wrote up a nice article on the SQL update statement.

Here is their example of how to do this.

UPDATE suppliers
SET name =
  ( SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id)
WHERE EXISTS
  ( SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id);

Thanks to this article, Oracle/PLSQL: Change a user’s password in Oracle, it was quite simple to change a user’s password.

SQL> alter user frank identified by password;