3 Replies Latest reply on Aug 23, 2013 9:56 PM by gdieckmann

    SQLPlus 12c | Set SQLPROMPT


      Is there a way to make the SQLPROMPT re-execute when we alter a session into a PDB?


      For example, I've edited my glogin.sql script to show the con_name and db_name. However, if I access my CDB, and from the CDB execute "alter session set container=<NAME_OF_PDB>", then the glogin.sql script doesn't get re-executed, and the prompt still shows the name of my CDB.


      SQL in glogin.sql: select upper(sys_context ('userenv', 'db_name') || '@' || sys_context('userenv', 'con_name')) global_name from dual;


      If the glogin.sql can't do this, is there another way that the SQLPROMPT can be updated after an 'alter session'?

        • 1. Re: SQLPlus 12c | Set SQLPROMPT



          You can get the value when you connect with PDB not with the alter session.

          connect with you pdb and then check that value.

          conn sys/pass@<pdbname> as sysdba



          • 2. Re: SQLPlus 12c | Set SQLPROMPT

            Hi DK,


            The SQL above works regardless of whether I sign directly into a CDB, directly into a PDB or even after an alter session. I just liked being able to use the SQLPROMPT to show which database/instance I was working in, without having query or use a SHOW command.


            Is the idea that if I wanted to work in the CDB, I'd connect to the CDB. And if I wanted to work in a PDB, I'd connect directly to the PDB? At my institution, I'd would be both the CDB and PDB admin, so could imagine myself switching between the instances fairly often.



            • 3. Re: SQLPlus 12c | Set SQLPROMPT

              I think I found somewhat of a solution (maybe it was too obvious).


              In the $ORACLE_HOME/sqlplus/admin directory, I created a new script (enter_plug.sql) that has the following:

              set termout off

              alter session set container=&1;


              set termout on


              I can enter into the CDB with SQLPlus, and see the name of the CDB and that I'm in the root:

              [oracle@localhost admin]$ sqlplus / as sysdba

              TEST12C@CDB$ROOT> show con_name







              And if I want to switch into a PDB, I just call the script from above:

              TEST12C@CDB$ROOT> @enter_plug PLUG3

              TEST12C@PLUG3> show con_name







              The other alternative is to just call the SQL script after the alter session:

              TEST12C@CDB$ROOT> show con_name






              TEST12C@CDB$ROOT> alter session set container=PLUG3;



              Session altered.



              TEST12C@CDB$ROOT> @glogin.sql