TechFAQ

What?s the command to see the current user name? Sql> show user;

What?s the command to change the SQL prompt name?

SQL> set sqlprompt |database-1 > |
database-1 >
database-1 >

How do you switch to DOS prompt from SQL prompt? SQL> host
How do I eliminate duplicate rows in an Oracle database?

SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);

or

SQL> delete duplicate_values_field_name dv from table_name ta where rowid < (select min(rowid) from table_name tb where ta.dv=tb.dv);

How do I display row number with records? Use the row-num pseudocolumn with query, like

SQL> select rownum, ename from emp;
How do you display the records within a given range?

select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum < =&rangeend
minus
select rowid from emp where rownum<&rangebegin);

The NVL function only allows the same data type. But here?s the task: if the commission field is null, then the text |Not Applicable? should be displayed, instead of blank space. How do you write the query?

SQL> select nvl(to_char(comm.),?Not Applicable?) from emp;

Explain explicit cursor attributes. There are four cursor attributes used in Oracle: cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
Explain implicit cursor attributes. Same as explicit cursor but prefixed by the word SQL: SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
How do you view version information in Oracle?

SQL> select banner from $version;