2

In SQL*Plus, I can set the prompt to include the instance name:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

I can also query for the current schema:

select sys_context( 'userenv', 'current_schema' ) from dual;

Is there a way to get the current schema into the prompt?

Lumi
  • 14,775
  • 8
  • 59
  • 92

1 Answers1

3

This works:

undefine schema_display
col schema_display new_value schema_display

set termout off
select lower(sys_context('userenv', 'current_schema')) schema_display from dual
/
set termout on

set sqlprompt "&&schema_display.':'_USER'@'_CONNECT_IDENTIFIER > "

So:

USER@SID > alter session set current_schema="TEST";

Session altered.

USER@SID > -- run the above
TEST:USER@SID >

However, the prompt stays set to that value until another SET SQLPROMPT is performed. You need to put the code to switch schemas and create the schema_display variable in a script (like "switch_schema.sql"):

alter session set current_schema="&new_schema"; 

undefine schema_display 
col schema_display new_value schema_display 

set termout off 
select lower(sys_context('userenv', 'current_schema')) schema_display from dual; 
set termout on 

set sqlprompt "&&schema_display.':'_USER'@'_CONNECT_IDENTIFIER > "

The &new_schema variable causes SQL*PLUS to prompt you for the schema you want to switch to.

USER@SID > @switch_schema 
Enter value for new_schema: TEST 
old   1: alter session set current_schema="&new_schema" 
new   1: alter session set current_schema="TEST" 

Session altered. 

test:USER@SID >
Scott A
  • 7,745
  • 3
  • 33
  • 46
  • Thanks, Scott! Works as advertised. The only thing I changed: I omitted the double quotes around the variable in `alter session set current_schema="&new_schema"` because that makes the comparison case-sensitive. Thanks again! – Lumi Nov 13 '11 at 19:58