Category: Oracle

How to SSH into your VirtualBox VM from another machine, OS X edtion

By far the easiest way to get Oracle up and running on OS X is to install VirtualBox and run a developer VM. The VirtualBox and VMs are free downloads from Oracle. You don't have to install the database and the developer VM comes with labs to get you started.

So what could make it even better? Well, the VM needs memory and CPU, and my laptop is old and feeble (the problem with "laptop RAC" alas). What if I could install the VM on a desktop and just ssh into it from my older, low-powered low-memory laptop?

Read more

A little fun with global temporary tables

Here’s a little bit of fun I thought up as part of a SQL game I was working on at Oracle. It’s a simple use of a trigger and a global temporary table to create a “combination lock” that any logged-in session can try to “open”.

To use it:

SQL> INSERT INTO combinationlock (combination) VALUES ('Test');  
 
1 ROW created.
 
SQL> SELECT * FROM combinationlock;
 
COMBI LOCK_STATE
----- --------------------------------------------------
Test  Closed
 
SQL> UPDATE combinationlock SET combination='99999';
 
1 ROW updated.
 
SQL> SELECT * FROM combinationlock;
 
COMBI LOCK_STATE
----- --------------------------------------------------
99999 Closed
 
SQL> SELECT * FROM combinationlock;
 
COMBI LOCK_STATE
----- --------------------------------------------------
12345 OPEN Sesame

To create:

DROP TABLE combinationlock;
CREATE global TEMPORARY TABLE combinationlock (combination varchar2(5), lock_state varchar2(50))
ON commit preserve ROWS;
 
DROP TRIGGER combinationlock_trig ;
CREATE TRIGGER combinationlock_trig 
BEFORE INSERT OR UPDATE ON combinationlock
FOR each ROW
BEGIN
  IF :NEW.combination = '12345' 
    THEN :NEW.lock_state := 'Open Sesame'; 
  ELSE :NEW.lock_state := 'Closed';
  END IF;
exception
  WHEN others THEN
    :NEW.lock_state := 'Closed';
    raise;
END;
/

The nice thing about this is that, since it’s using a global temporary table, any user can try their guesses at the combination and it affects only their session.