Monday, November 30, 2020
dbametrix
More
    Home Sql Tutorial Use of SQLPlus

    Use of SQLPlus

    The blog post explains about sqlplus usage in Oracle. SQLplus is an inbuilt tool of Oracle then why we need any third-party tool.

    Use of Oracle SQLPlus

    Even though I use SQL Navigator at work, I also often use SQLPlus. Almost everyone who has access to an Oracle database has access to SQL*Plus. On the other hand, not everyone has SQL Navigator or similar third-party tools installed on his/her machine.

    SQLPlus can be started in GUI mode (Windows only) or in command-line mode. I like the command-line mode because it is more flexible and can be used on any platform.

    - Advertisement -
    dbametrix

    By default, when you start SQLPlus, the prompt changes to SQL>:

    C:\>sqlplus scott/tiger
    SQL>

    You can easily change the prompt if you like, just use set sqlprompt to whatever you like:

    SQL> set sqlprompt 'command >'
    command >
    - Advertisement -
    dbametrix

    But you want to set the sql prompt to be something like “current user @ database name”, so you may do something like this:

    command >column user_sid new_value sql_prompt
    command >select
    2 lower(user) || '@' ||
    3 substr(global_name, 1,
    4 decode(instr(global_name, '.'),
    5 0, length(global_name),
    6 instr(global_name, '.') - 1)
    7 ) user_sid
    8 from global_name
    9 /
    
    USER_SID
    ---------------------------------------------
    [email protected]
    command >set sqlprompt '&sql_prompt> '
    [email protected]>

    Cool. However, in Oracle 9.2 and above, you can get rid of the “global_name” table and use the all new _CONNECT_IDENTIFIER DEFINE Variable. This new variable contains the SID as supplied by the user. This allows the connection information to be accessed like any other DEFINE variable. Let’s modify the above to make use of this new feature:

    [email protected]> set sqlprompt 'command >'
    command >column user_sid new_value sql_prompt
    command >select lower(user) || '@' ||
    2 '&_CONNECT_IDENTIFIER' user_sid from dual;
    
    USER_SID
    ------------------------------------
    [email protected]
    command >set sqlprompt '&sql_prompt> '
    [email protected]>

    Much simpler.

    You may already know that if you create a file named login.sql and put it in the same folder where you launch SQLPlus, that file, login.sql, will always execute once at the time you execute the command “sqlplus”. Great! Let’s put our SQL prompt initialization in login.sql, we can also throw in some extra SETtings. Here is the content of my login.sql:

    set term off
    set serveroutput on size 1000000 format wrapped
    set long 5000
    set linesize 131
    set trimspool on
    set pagesize 9999
    define sql_prompt=idle
    column user_sid new_value sql_prompt
    select lower(user) || '@' ||
    '&_CONNECT_IDENTIFIER' user_sid from dual;
    set sqlprompt '&sql_prompt> '
    set timing on
    set term on

    Let’s try it:

    H:\SQLplus>sqlplus scott/tiger
    [email protected]> disconnect
    [email protected]>

    OOPS! When I disconnected the sql prompt remained the same. To solve this issue, we have to execute login.sql whenever we connect and disconnect. Easy enough. Let’s create a connect.sql and disconnect.sql and put them in the same place where login.sql is:

    Connect.sql

    set term off
    connect &1
    @login

    Disconnect.sql

    set term off
    disconnect
    @login

    The only extra thing you need to do now is to use @disconnect and @connect instead of disconnect and connect. Let’s test:

    H:\SQLplus>sqlplus /nolog
    idle> @connect scott/tiger
    [email protected]> @disconnect
    idle>

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    Consider Reading to these articles:

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Oracle Scheduler Jobs Example

    Blog post explains how to configure job scheduler in Oracle by example with how to manage, delete, drop, disable and enable jobs with how to check history of job if broken and enable.

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    Recent Comments