Glogin.sql for pluggable databases or dfferent database on Oracle

user_3970561 0
Viewed 118

This is my glogin.sql

set feedback on;
set linesize 9000
set  colsep |
column titolo format a30
column  anno format a20
column  genere format a20
column  formato format a20
column  lingua format a20
--alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
set serveroutput on;
set autocommit off;
set pagesize 100;
set long 20000;
set timing on;
alter session set cursor_sharing = 'EXACT'

select           'You are:          '||upper(user) "Welcome to LINUX-ORACLE system" from dual
union all select 'Logged into:      '||upper(sys_context('userenv','db_name')) from dual
union all select 'Database Version: '||banner from v$version where rownum <= 2;

I have two pluggable db's called "video" and "libri" Is working fine for "video" database, but for different database the column "titolo" which is present in the video db and "libri" db is too large. My question is simple: is possible to set different format for the same column but in different db? For example a30 for video db and a10 for libri db.

sqlplus oracle
1 Answers

First issue 

SQL> column col_width new_value col_width;

command of SQL*Plus, and then use the following query containing DB names as conditional, and specify your related column name in the table by column <col_name> format "a&col_width"

SQL> select case when regexp_like(sys_context('userenv','db_name'),'LibRi','i') then 
                      10 
                 else 30 
                 end as col_width 
       from dual; --> assuming you have only two different DB "else" is used directly.

 COL_WIDTH
----------
         2

SQL> column titolo format "a&col_width"
SQL> select titolo from mybooks where id=41471;

TITOLO
----------
MEMORIEDIU  
NALADRA
user_58413060
Related