set up sqlplus environment

set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 120
set pagesize 9999
column plan_plus_exp format a80
set termout off
define gname=idle
column global_name new_value gname
select lower(user)||'@'||substr(global_name,1,
decode(dot,0,length(global_name),dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name);
set sqlprompt '&gname> '
set termout on

if you have database with the same sid and global_name is not setup, you can include hostname in sqlplus prompt.


define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
--column global_name new_value gname
set termout off
define gname=idle
define hostname=idle
column global_name new_value gname
column host_name new_value hostname
select lower(user) || '@' || substr(global_name, 1,
  decode(dot,0, length(global_name), dot - 1) ) global_name
  from (select global_name, instr(global_name, '.') dot from global_name );
select  substr(host_name, 1,
  decode(dot,0, length(host_name), dot - 1) ) host_name
  from (select host_name, instr(host_name, '.') dot from v$instance );
set sqlprompt '&gname..&hostname> '
set termout on

above lines need be put into login.sql file
and the path to the login.sql file need be defined as SQLPATH environment

invlalid number error

When column type is VARCHAR2()

sometimes you can compare the column with a number in one table and sometimes not in another table.

It depends on the data in the column. If all the data stored in the column has only digit characters, you will be able to compare otherwise you will fail.