Oracle SQL FAQ - Part II

How to generate primary key values for a table?

Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:

SQL> UPDATE table_name SET seqno = ROWNUM;
or use a sequences generator:


SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;

Finally, create a unique index on this column.

How to retrieve only the Nth row from a table?

Solution 1 to select the Nth row from a table:

SELECT * FROM t1 a
WHERE n = (SELECT COUNT(rowid)
FROM t1 b
WHERE a.rowid >= b.rowid);

Solution 2:

SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
WHERE RN = 100;

Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.

Solution 3:

SELECT f1 FROM t1
WHERE rowid = (
SELECT rowid FROM t1
WHERE rownum <= 10
MINUS
SELECT rowid FROM t1
WHERE rownum < 10);
SELECT rownum,empno FROM scott.emp a
GROUP BY rownum,empno HAVING rownum = 4;

Alternatively...
SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
(SELECT rowid FROM emp WHERE rownum < 10);

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

How to drop a column from a table?

From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.

Other workarounds:

1. SQL> update t1 set column_to_drop = NULL;
SQL> rename t1 to t1_base;
SQL> create view t1 as select from t1_base;

2. SQL> create table t2 as select from t1;
SQL> drop table t1;
SQL> rename t2 to t1;


How to rename a column in a table?

From Oracle9i one can RENAME a column from a table. Look at this example:

ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;

Other workarounds:

1. -- Use a view with correct column names...
rename t1 to t1_base;
create view t1 as select * from t1_base;

2. -- Recreate the table with correct column names...
create table t2 as select * from t1;
drop table t1;
rename t2 to t1;

3. -- Add a column with a new name and drop an old column...
alter table t1 add ( newcolame datatype );
update t1 set newcolname=oldcolname;
alter table t1 drop column oldcolname;

How can I change my Oracle password?

Issue the following SQL command: ALTER USER IDENTIFIED BY
/
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name".