Make SQL prompt show schema and database |
 |
Using the whoami() function below, you can see what schema and database you are connected to.
The function itself may be used as in the setprompt.sql script to modify your prompt
Note that you should create the function as SYS with a publicly executable synonym.
Instead of having "SQL>" for a prompt you can have "DBNAME: SCOTT> " as a prompt
|
-- Author: Ahbaid Gaffoor - OCPdba.Net
-- File: whoami.sql
--
-- Purpose: Creates a public function whoami that allows a user to see the database and user he/she is connected to.
-- the database and user he/she is connected to.
--
PROMPT Enter SYS Password and TNS Connect String....
CONNECT SYS AS SYSDBA
--
-- Create the WHOAMI() function
--
CREATE OR REPLACE FUNCTION WHOAMI RETURN VARCHAR2 AS
DBN VARCHAR2(100);
BEGIN
SELECT NAME||': '||USER INTO DBN FROM V$DATABASE;
RETURN DBN;
END;
/
--
-- Give Permissions to Public
--
grant execute on whoami to public;
create public synonym whoami for whoami;
|  |
The following script setprompt.sql may be used to have your SQL prompt show the current connection.
|
-- Author: Ahbaid Gaffoor - OCPdba.Net
-- File: setprompt.sql
--
-- Purpose: Call this script from the SQL prompt to have it reflect your current schema and database connection
--
-- Usage: @setprompt
set termout off
column iam new_value myprompt noprint;
select whoami||'> ' as iam from dual;
set sqlprompt "&&myprompt"
set termout on
| |