Changing the prompt dynamically in SQL*Plus

6

This is just a simple trick which is not well-known but makes life easier when using the good ol’ SQL*Plus commandprompt (and many people still do!).

If you set the SQLPROMPT environment variabele (either manually or in the login.sql)  the standard prompt is replaced by the name of the instance:

SQL> set sqlprompt "_CONNECT_IDENTIFIER>"
mydb>

I use this in combination with the login.sql. I’m not sure if this was a feature before Oracle 10g, but recently I noticed that the login.sql is executed after every login, and not only when you start a new prompt. And that means that everytime you change your connection, the sqlprompt also changes according to the value of _CONNECT_IDENTIFIER. This probably will prevent many mistakes because you’re typing update-statements in the "wrong" environment!

These substitution variables are available from SQL*Plus 9.2 onwards. Other values you can use for sqlprompt are _USER, _DATE, _O_VERSION and _O_RELEASE. They speak for themselves I guess!

mydb&gt; set sqlprompt &quot;_USER _CONNECT_IDENTIFIER&gt; &quot;<br />SCOTT&nbsp;mydb&gt; 

 

Share.

About Author

6 Comments

  1. OK, that went wrong (now hopefully in the write order):






    define uniqueness=”uniqueness”
    column uniqueness new_value uniqueness

    – ——————————————————————————-
    set termout off

    – Table dba_indexes has changed in Oracle 8
    – Next piece of code switches the column name of the “create index” cursor.

    select ‘decode(i.uniqueness,”BITMAP”,i.uniqueness,”NORMAL”)’ uniqueness
    from dual where &&_O_RELEASE like ’7%’
    union
    select ‘i.index_type’ uniqueness
    from dual where &&_O_RELEASE like ’8%’
    ;
    – ——————————————————————————-

    set termout on
    set define ~
    set serveroutput on size 1000000




    – etc, etc, etc –




    — Rebuild index

    CURSOR c_index( p_index IN CHAR
    , p_schema IN CHAR
    )
    IS
    SELECT i.owner idxowner
    , i.index_name
    , ~uniqueness indextype
    , decode(i.uniqueness, ‘UNIQUE’,’ UNIQUE ‘,’ ‘) uq
    , i.table_name
    , i.table_owner
    , i.tablespace_name
    , i.initial_extent
    , i.next_extent
    , i.min_extents
    , i.max_extents
    , i.pct_increase
    , i.freelists
    , i.pct_free
    , decode(c.constraint_type,’P’,’PRIMARY KEY’
    ,’U’,’UNIQUE’
    ,c.constraint_type) constype
    , c.constraint_name
    , c.status
    , c.owner consowner
    FROM sys.dba_indexes i
    , sys.dba_constraints c
    WHERE (c.table_name(+) = i.table_name
    AND c.constraint_name(+) = i.index_name
    AND c.owner(+) = i.table_owner)
    AND i.table_type = ‘TABLE’
    AND i.index_name = p_index
    AND i.owner = p_schema
    ORDER BY i.index_name
    ;






    – etc, etc, etc –


    BEGIN

    …etc…etc



    IF check3(i).s_segment_type=’INDEX’
    THEN

    FOR r_index IN c_index(check3(i).s_segment_name,check3(i).s_owner)
    LOOP
    EXIT WHEN c_index%NOTFOUND;
    create_check3(i).r_statement := ‘alter index ‘||r_index.idxowner||’.’||r_index.index_name
    ||chr(10)
    ||’REBUILD ONLINE’
    ||chr(10)
    ||’PCTFREE ‘||r_index.pct_free
    ||chr(10)
    ||’storage’
    ||chr(10)
    ||’(‘
    ||chr(10)
    ||’initial ‘||v_seg_initial||’K’
    ||chr(10)
    ||’next ‘||v_seg_nextext||’K’
    ||chr(10)
    ||’pctincrease ‘||r_index.pct_increase
    ||chr(10)
    ||’minextents ‘||r_index.min_extents
    ||chr(10)
    ||’maxextents ‘||v_max_extents
    ||chr(10)
    ||’freelists ‘||r_index.freelists
    ||chr(10)
    ||’)’
    ||chr(10)
    ||’tablespace ‘||r_index.tablespace_name
    ||’;’
    ;

    dbms_output.put_line(create_check3(i).r_statement);
    dbms_output.put_line(‘–’);
    END LOOP;

  2. The _O_RELEASE variable reminded me on a small trick I once used.

    A while ago (understatement), I had to write a script that could be executed on Oracle 7 and on Oracle 8. In Oracle 7 the dba_indexes table had normal and bitmapped indicators in the same column. In Oracle 8, they added a extra column regarding bitmap indexes. My dynamically created “CREATE INDEX” statements via a cursor where based on the info in DBA_INDEXES.

    The following gives you an idea how i solved it:

    define uniqueness=”uniqueness”
    column uniqueness new_value uniqueness

    – ——————————————————————————-
    set termout off

    – Table dba_indexes has changed in Oracle 8
    – Next piece of code switches the column name of the “create index” cursor.

    select ‘decode(i.uniqueness,”BITMAP”,i.uniqueness,”NORMAL”)’ uniqueness
    from dual where &&_O_RELEASE like ’7%’

    …etc…etc…etc

    IF check3(i).s_segment_type=’INDEX’
    THEN

    FOR r_index IN c_index(check3(i).s_segment_name,check3(i).s_owner)
    LOOP
    EXIT WHEN c_index%NOTFOUND;
    create_check3(i).r_statement := ‘alter index ‘||r_index.idxowner||’.’||r_index.index_name
    ||chr(10)
    ||’REBUILD ONLINE’
    ||chr(10)
    ||’PCTFREE ‘||r_index.pct_free
    ||chr(10)
    ||’storage’
    ||chr(10)
    ||’(‘
    ||chr(10)
    ||’initial ‘||v_seg_initial||’K’
    ||chr(10)
    ||’next ‘||v_seg_nextext||’K’
    ||chr(10)
    ||’pctincrease ‘||r_index.pct_increase
    ||chr(10)
    ||’minextents ‘||r_index.min_extents
    ||chr(10)
    ||’maxextents ‘||v_max_extents
    ||chr(10)
    ||’freelists ‘||r_index.freelists
    ||chr(10)
    ||’)’
    ||chr(10)
    ||’tablespace ‘||r_index.tablespace_name
    ||’;’
    ;

    dbms_output.put_line(create_check3(i).r_statement);
    dbms_output.put_line(‘–’);
    END LOOP;
    union
    select ‘i.index_type’ uniqueness
    from dual where &&_O_RELEASE like ’8%’
    ;
    – ——————————————————————————-

    set termout on
    set define ~

    ..
    ..etc,etc
    ..
    – Rebuild index

    CURSOR c_index( p_index IN CHAR
    , p_schema IN CHAR
    )
    IS
    SELECT i.owner idxowner
    , i.index_name
    , ~uniqueness indextype
    , decode(i.uniqueness, ‘UNIQUE’,’ UNIQUE ‘,’ ‘) uq
    , i.table_name
    , i.table_owner
    , i.tablespace_name
    , i.initial_extent
    , i.next_extent
    , i.min_extents
    , i.max_extents
    , i.pct_increase
    , i.freelists
    , i.pct_free
    , decode(c.constraint_type,’P’,’PRIMARY KEY’
    ,’U’,’UNIQUE’
    ,c.constraint_type) constype
    , c.constraint_name
    , c.status
    , c.owner consowner
    FROM sys.dba_indexes i
    , sys.dba_constraints c
    WHERE (c.table_name(+) = i.table_name
    AND c.constraint_name(+) = i.index_name
    AND c.owner(+) = i.table_owner)
    AND i.table_type = ‘TABLE’
    AND i.index_name = p_index
    AND i.owner = p_schema
    ORDER BY i.index_name
    ;

  3. @arnoud

    the login.sql and glogin.sql can be found normally in $ORACLE_HOME/sqlplus/admin. Useful parameters can be set in these files to, for example, format you explain plan output differently (use among others “show all” and “define” in sqlplus to see some parameters you could use). Be also aware that the files are useful in hacking the system (if someone can write into it, OS/SQL commands in it will be executed during startup of sqlplus)

    See also http://www.adp-gmbh.ch/ora/sqlplus/login.html.

  4. You can create the login.sql yourself and put it in the startup-directory of your SQL*Plus.
    Typically, the contents of login.sql can be something like this:

    set sqlprompt “_CONNECT_IDENTIFIER> ”
    exec dbms_output.enable( 50000 );
    set serveroutput on
    set feedback on
    set timing on

    col object_name format a20
    exec ams_common.INIT_AUDIT_INFO;