Oracle SQL*Plus : How to include current schema in prompt?

Question

在 SQL*Plus 中，我可以设置提示以包含实例名称:

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

我还可以查询当前模式:

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

有没有办法让当前模式进入提示？

Answer 1

这有效:

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 > "

所以:

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

Session altered.

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

但是，提示将保持设置为该值，直到执行另一个 SET SQLPROMPT。您需要将代码用于切换模式并在脚本中创建 schema_display 变量(如“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 > " 

&new_schema 变量使 SQL*PLUS 提示您输入要切换到的模式。

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 >