Tips and Tricks - Oracle

Item How To Do It
Determine the user id that was used to login to the current SQL*Plus session SELECT USERNAME FROM USER_USERS;
Sequentially number the rows of a query Use the ROWNUM field
Determine compilation errors for a stored subprogram Include the SHOW ERRORS command in the SQL file after the /
Grant execute permissions to a user role GRANT EXECUTE ON stored-program-name TO user-role-name
Re-login to SQL*Plus session to a different server or as a different user CONNECT username[/password]]@server  - OR -
CONNECT username@server[[/password]]
Determine the server that the current SQL*Plus session is connected to SELECT * FROM GLOBAL_NAME;
Redirect output to a file SPOOL file
Prompt the user for the value of a bind variable ACCEPT variable-name
Set SQL*Plus startup preferences Add commands to C:\ORACLE\ORAxx\SQLPLUS\ADMIN\GLOGIN.SQL
Execute an outer joinThe (+) sign is placed in the WHERE clause after the column name that designates the deficient data. The (+) never goes with a primary key, but frequently goes with a foreign key.

Example
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column