[HOWTO] 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
 
-- 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 BEGIN_INTERVAL_TIME FORMAT A50
COL BEGIN_TIME FORMAT A30
COL BUFINFO FORMAT A30
COL BUFSIZE FORMAT A30
COL CAPACITY FORMAT 999999999
COL CAPACITY_GB FORMAT 999999999.99
COL CHAR_LENGTH  FORMAT 9999999
COL CLIENT_ID FORMAT A30
COL CLIENT_INFO FORMAT A40
COL COLUMN_LENGTH  FORMAT 9999999
COL COLUMN_NAME FORMAT A30
COL COLUMN_POSITION  FORMAT 9999999
COL COMPONENT FORMAT A30
COL COMPRESS_FOR FORMAT A30
COL CONSECUTIVE_OCCURRENCES FORMAT 9999999999
COL CONSTRAINT_NAME FORMAT A50
COL CPU_RANK FORMAT 999 
COL CRITICAL_OPERATOR FORMAT A16
COL CRITICAL_VALUE FORMAT A8
COL DATA_TYPE FORMAT A50
COL DATATYPE_STRING FORMAT A20
COL DB_LINK FORMAT A50
COL DEFLT FORMAT A30
COL DEPENDENCY_TYPE FORMAT A20
COL DESCRIPTION FORMAT A120
COL DESTINATION FORMAT A30
COL DEST_NAME FORMAT A20
COL DIRECTORY_NAME FORMAT A40
COL DIRECTORY_PATH FORMAT A180
COL DIRNAME FORMAT A120
COL DISKGROUP FORMAT A20
COL DISK_NAME FORMAT A25
COL DISPLAY_CHILD FORMAT A58
COL DISPLAY_PARENT FORMAT A58
COL END_INTERVAL_TIME FORMAT A50
COL END_TIME FORMAT A30
COL EVENT FORMAT A20 WORD_WRAPPED 
COL FAILGROUP FORMAT A30
COL FILENAME FORMAT A200
COL FILE_NAME FORMAT A80
COL FREE_GB FORMAT 999999999.99
COL FREE_MB FORMAT 999999999
COL GRANTOR FORMAT A33
COL HOST FORMAT A30
COL HOSTNAME FORMAT A40 WORD_WRAPPED 
COL INDEX_NAME  FORMAT A30
COL INDEX_OWNER  FORMAT A30
COL INSTANCE_NAME FORMAT A15
COL INSTNACE FORMAT A35
COL JOB_ACTION FORMAT A30
COL JOB_CREATOR FORMAT A30
COL JOB_NAME FORMAT A40
COL JOB_STYLE FORMAT A30
COL JOB_SUBNAME FORMAT A30
COL JOB_TYPE FORMAT A30
COL KSPPDESC FORMAT A120
COL KSPPINM FORMAT A60
COL LIMIT FORMAT A30
COL LOCAL FORMAT A40
COL LOCK_ID1 FORMAT A10
COL LOCK_ID2 FORMAT A10
COL LOCK_TYPE FORMAT A25
COL MACHINE FORMAT A40
COL MAX_MB FORMAT 99999999999
COL MAXQUERYID FORMAT A20
COL METRICS_NAME FORMAT A30
COL MODE_HELD FORMAT A23
COL MODE_REQUESTED FORMAT A23
COL NAME FORMAT A50
COL NAME_SPACE FORMAT A15
COL OBJECT FORMAT A50 
COL OBJECT_TYPE FORMAT A25
COL OPERATION_PERIOD FORMAT A10
COL OSPID FORMAT A7
COL OSUSER FORMAT A33
COL OWNER FORMAT A30
COL OWNER FORMAT A33
COL P1TEXT FORMAT A20 WORD_WRAPPED
COL PARAMETER FORMAT A50
COL PATH FORMAT A100
COL PATH FORMAT A40
COL "PERCENT" FORMAT 999
COL PERCENT FORMAT 999.99
COL PERMISSION_NAME FORMAT A50
COL POSITION FORMAT 999
COL PROFILE FORMAT A30
COL PROGRAM FORMAT A50
COL PROGRAM FORMAT A70 WORD_WRAPPED
COL PROGRAM_NAME FORMAT A35
COL PROGRAM_OWNER FORMAT A20
COL QUERY HEADING 'SQL|QUERY' FORMAT A100 WORD_WRAPPED 
COL R_CONSTRAINT_NAME FORMAT A50
COL RDMAENABLE FORMAT A10
COL REFERENCED_NAME FORMAT A33
COL REFERENCED_OWNER FORMAT A30
COL REFERENCED_TYPE FORMAT A30
COL REPEAT_INTERVAL FORMAT A70
COL RESOURCE_NAME FORMAT A50 
COL ROLL_NAME FORMAT A30
COL R_OWNER FORMAT A30
COL SCHEDULE FORMAT A15
COL SCHEDULE_NAME FORMAT A30
COL SCHEDULE_OWNER FORMAT A20
COL SEGMENT_NAME FORMAT A35
COL SERIAL FORMAT 999999 
COL SESSION_ID FORMAT 999999
COL SESSION_ID FORMAT 999999
COL SID FORMAT 99999
COL SID_SERIAL FORMAT A20 
COL SQL_ID FORMAT A15
COL SQLID FORMAT A15 WORD_WRAPPED 
COL SQL_TEXT FORMAT A130 WORD WRAPPED
COL START_DATE FORMAT A50
COL STATUS FORMAT A15
COL SUBNAME FORMAT A40
COL SVRNAME FORMAT A40
COL SYS_PATH FORMAT A80
COL TABLE_NAME FORMAT A30
COL TABLE_OWNER  FORMAT A30
COL TABLESPACE FORMAT A40
COL TABLESPACE_NAME FORMAT A35
COL TOTAL_CPU_TIME FORMAT 999999999 
COL TOTAL_MB FORMAT 99999990.9999 
COL TOTAL_MB FORMAT 999999999
COL TYPE FORMAT A30
COL USERID FORMAT A40
COL USERNAME FORMAT A33
COL VALUE_STRING FORMAT A100
COL WAITCLASS HEADING 'WAITING|CLASS' FORMAT A20 
COL WAITINGSECS HEADING 'SECONDS|WAITING' FORMAT 99999 
COL WARNING_OPERATOR FORMAT A16
COL WARNING_VALUE FORMAT A30
 
 
 
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
 
-- Enable timing
--SET timing ON

Ciberterminal Wiki

Table of Contents