Como dar formato a las consultas Oracle en SQLPlus

La diferencia entre ver una consulta así….

SQL> SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';
 
PROFILE
--------------------------------------------------------------------------------
RESOURCE_NAME                    RESOURCE
-------------------------------- --------
LIMIT
--------------------------------------------------------------------------------
COM
---
DEFAULT
FAILED_LOGIN_ATTEMPTS            PASSWORD
10
NO
 
 
PROFILE
--------------------------------------------------------------------------------
RESOURCE_NAME                    RESOURCE
-------------------------------- --------
LIMIT
--------------------------------------------------------------------------------
COM
---
DEFAULT
PASSWORD_LIFE_TIME               PASSWORD
180
NO
 
 
PROFILE
--------------------------------------------------------------------------------
RESOURCE_NAME                    RESOURCE
-------------------------------- --------
LIMIT
--------------------------------------------------------------------------------
COM
---
DEFAULT
PASSWORD_REUSE_TIME              PASSWORD
UNLIMITED
NO
 
 
PROFILE
--------------------------------------------------------------------------------
RESOURCE_NAME                    RESOURCE
-------------------------------- --------
LIMIT
--------------------------------------------------------------------------------
COM
---
DEFAULT
PASSWORD_REUSE_MAX               PASSWORD
UNLIMITED
NO
 
 
PROFILE
--------------------------------------------------------------------------------
RESOURCE_NAME                    RESOURCE
-------------------------------- --------
LIMIT
--------------------------------------------------------------------------------
COM
---
DEFAULT
PASSWORD_VERIFY_FUNCTION         PASSWORD
NULL
NO
 
 
PROFILE
--------------------------------------------------------------------------------
RESOURCE_NAME                    RESOURCE
-------------------------------- --------
LIMIT
--------------------------------------------------------------------------------
COM
---
DEFAULT
PASSWORD_LOCK_TIME               PASSWORD
1
NO
 
 
PROFILE
--------------------------------------------------------------------------------
RESOURCE_NAME                    RESOURCE
-------------------------------- --------
LIMIT
--------------------------------------------------------------------------------
COM
---
DEFAULT
PASSWORD_GRACE_TIME              PASSWORD
UNLIMITED
NO
 
 
7 rows selected.

Y verla de esta forma

SQL>  SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';
 
PROFILE    RESOURCE_NAME                  RESOURCE LIMIT           COM
---------- ------------------------------ -------- --------------- ---
DEFAULT    FAILED_LOGIN_ATTEMPTS          PASSWORD 10              NO
DEFAULT    PASSWORD_LIFE_TIME             PASSWORD 180             NO
DEFAULT    PASSWORD_REUSE_TIME            PASSWORD UNLIMITED       NO
DEFAULT    PASSWORD_REUSE_MAX             PASSWORD UNLIMITED       NO
DEFAULT    PASSWORD_VERIFY_FUNCTION       PASSWORD NULL            NO
DEFAULT    PASSWORD_LOCK_TIME             PASSWORD 1               NO
DEFAULT    PASSWORD_GRACE_TIME            PASSWORD UNLIMITED       NO
SQL>

Radica en el formato que se le de al sqlplus.

SQL> COLUMN RESOURCE_NAME FORMAT A30;
SQL> COLUMN  COM FORMAT A10;
SQL> COLUMN PROFILE  FORMAT A10;
SQL> COLUMN  RESOURCE FORMAT A40;
SQL> COLUMN LIMIT FORMAT A15;

Observese que se usa el parametro COLUMN seguido del nombre de columna que queremos formatear luego la directiva FORMAT y el ancho que vamos a dar a la columna.

Para el caso anterior se tiene por ejemplo que la columna PROFILE tendrá un formato ALFANUMERICO(A) y 10 Caracteres de ancho.

Views All Time
Views All Time
170
Views Today
Views Today
1

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *