I would like to change the sqlplus command-prompt to something more useful than<\/p>\n
\r\nSQL>\r\n<\/pre>\nYou can modify the sqlprompt variable as follows,<\/p>\n
\r\nSQL>\r\nSQL> set sqlprompt \"_USER'@'_CONNECT_IDENTIFIER> \"\r\nFOO_OWNER@FOO> \r\n<\/pre>\nYou can also use variables such as _PRIVILEGE and _DATE, although all I really want is the user and schema. Unfortunately, with the above technique, if you ever connect to a database using a full connection string (rather than a tnsnames.ora entry), this prompt will be a bit unwieldy. e.g.,<\/p>\n
\r\n$ sqlplus foo_owner\/foo_pass@'(DESCRIPTION=(ADDRESS_LIST=(load_balance=on)(failover=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.11)(PORT=1528)))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=FOO)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY=15))))'\r\n\r\nSQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 11 12:47:19 2012\r\n\r\nCopyright (c) 1982, 2007, Oracle. All Rights Reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production\r\nWith the Partitioning option\r\n\r\nSQL> \r\nSQL> set sqlprompt \"_USER'@'_CONNECT_IDENTIFIER> \" \r\nFOO_OWNER@(DESCRIPTION=(ADDRESS_LIST=(load_balance=on)(failover=on)(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.11)(PORT=1528)))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=FOO)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY=15))))> \r\n<\/pre>\nI love that I can connect without a tnsnames.ora entry, but I would like a simplistic prompt and I want it loaded every time I start sqlplus.<\/p>\n
Fortunately, sqlplus will look in the current directory and in the $SQLPATH environment variable for a login.sql, and execute it automatically. For example, if you include the following in your login.sql<\/p>\n
\r\nSET TERMOUT OFF \r\nDEFINE sqlprompt=none\r\nCOLUMN sqlprompt NEW_VALUE sqlprompt\r\nSELECT LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_NAME') as sqlprompt FROM DUAL;\r\nSET SQLPROMPT '&sqlprompt> '\r\nUNDEFINE sqlprompt\r\nSET TERMOUT ON\r\n<\/pre>\nThen whenever you run sqlplus the prompt will be set accordingly, e.g.,<\/p>\n
\r\n$ sqlplus foo_owner\/foo_pass@FOO\r\n\r\nSQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 11 12:47:19 2012\r\n\r\nCopyright (c) 1982, 2007, Oracle. All Rights Reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production\r\nWith the Partitioning option\r\n\r\nfoo_owner@FOO> show user\r\nUSER is \"FOO_OWNER\"\r\nfoo_owner@FOO> \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"I would like to change the sqlplus command-prompt to something more useful than SQL> You can modify the sqlprompt variable as follows, SQL> SQL> set sqlprompt “_USER’@’_CONNECT_IDENTIFIER> ” FOO_OWNER@FOO> You can also use variables such as _PRIVILEGE and _DATE, although all I really want is the user and schema. Unfortunately, with the above technique, if […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18,14],"tags":[],"_links":{"self":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/560"}],"collection":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/comments?post=560"}],"version-history":[{"count":2,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/560\/revisions"}],"predecessor-version":[{"id":703,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/posts\/560\/revisions\/703"}],"wp:attachment":[{"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/media?parent=560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/categories?post=560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tech.avant.net\/q\/wp-json\/wp\/v2\/tags?post=560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}