User Tools

Site Tools


affordable_sqlplus

Wrapper

Descripción

El readline wrapper implementa las funciones típicas de:

  • History
  • backward search
  • fancy editing

Es decir, convierte SQL+ de un infierno a algo amigable. También lo podemos aplicar a asmcmd, rman y dgmgrl!

Instalación

rpm

Desde la EPEL:

yum -y install rlwrap

Entorno

Para hacerlo “worldwide” avalaible:

cat > /etc/profile.d/sqlplus_wrapper.sh <<EOF
for i in sqlplus rman asmcmd dgmgrl ttisql ; do
        alias \${i}="rlwrap \${i}"
done
EOF
chmod +x /etc/profile.d/sqlplus_wrapper.sh

De esta forma cada vez que se inicia una sesión se aplica el alias.

Si se quiere usar el sqlplus sin eliminar el alias, basta con ejecutar:

'sqlplus' '/ as sysdba'

Desinstalar

Borrar el fichero:

rm -fv /etc/profile.d/sqlplus_wrapper.sh

Configuración de SQLPLUS

Para definir parámetros de sqlplus, es decir, ejecutar órdenes para configurarlo, editar el fichero “login.sql” o “glogin.sql”.

Ver documentación oficial

Ejemplo

vim /u01/app/oracle/product/11.2.0/db/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
 
 
 
-- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER
-- and _DATE variables.
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
 
-- To set the number of lines to display in a report page to 24.
SET PAGESIZE 90
 
-- To set the number of characters to display on each report line to 78.
SET LINESIZE 270
 
 
SET LONG 52000
SET LONGC 52000
 
 
-- Enable dbms_output messages
SET SERVEROUTPUT ON
 
-- Enable timing
SET timing ON
 
-- To set the number format used in a report to $99,999.
-- SET NUMFORMAT $99,999
 
-- set the default editor
def_editor=vim
 
 
-- COLUMN DEFINITION
COL OWNER FORMAT A33
COL USERNAME FORMAT A33
COL TABLE_NAME FORMAT A35
COL PARTITION_NAME FORMAT A35
COL INDEX_NAME FORMAT A35
COL OBJECT_NAME FORMAT A35
COL OBJECT_TYPE FORMAT A35
COL TXT FORMAT A1000
 
COL SEGMENT_NAME FORMAT a35
 
COL INDEX_OWNER FORMAT A33
COL TABLESPACE_NAME FORMAT A33
COL COLUMN_NAME FORMAT A33
 
COL DIRECTORY_NAME FORMAT A33
COL DIRECTORY_PATH FORMAT A90
 
 
SET feed off
-- DBMS_METADATA
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
 
SET feed ON

Links

affordable_sqlplus.txt · Last modified: 2016/11/15 13:53 by dodger