El usuario Scott no existe

SQL> conn scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied

Dependiendo de las opciones de instalación o de los schemas que vengan instalados en algunas versiones del RDBMS (como Express por ejemplo), el usuario más famoso de Oracle, Scott, puede no estar presente. Citando el caso de Oracle XE, el único schema de ejemplo que viene instalado por defecto es HR.

Pero no hay problema, lo único que tenemos que hacer para instalar el schema Scott con sus clásicas tablas EMP, DEPT, BONUS y SALGRADE, es correr (en SQL*Plus) el script utlsampl.sql que está en el directorio del servidor $ORACLE_HOME/RDBMS/ADMIN.

Como se requiere de ciertos permisos especiales que un usuario corriente no tiene, deberá hacerlo el DBA.

Variables en SQL*Plus (parte 2)

Hay tres tipos de variables en SQL*Plus y suelen causar confusión.
Estas son: variables de usuario, variables de sustitución y bind variables.
En la primera parte repasamos las variables de usuario, aquí van las variables de sustitución.

VARIABLES DE SUSTITUCION

Las variables de sustitución suelen confundirse con las variables de usuario, más que nada porque ambas aparecen precedidas con un símbolo ampersand (&). Sin embargo, hay diferencias en la definición, asignación y uso de las mismas. Como vimos en la parte 1, las variables de usuario se declaran explícitamente con el comando DEFINE, tienen asociado un tipo y se les pueden asignar valores en el script como lo hacemos en cualquier lenguaje de programación.
Las variables de sustitución en cambio cuando están indefinidas, solicitan el valor al usuario cada vez que aparecen, y el resultado es exactamente el mismo que si escribiéramos el texto directamente.

Por ejemplo: en la siguiente consulta en cada aparición de variable será solicitado el valor al usuario. Notar que aunque la variable COLUMNA aparece repetida, se trata como si fueran diferentes y el valor es solicitado dos veces.

SELECT &COLUMNA, SUM(MONTO)
FROM cuentas
GROUP BY &COLUMNA;

Enter value for columna: ID_REGION
old 1: SELECT &COLUMNA,
new 1: SELECT ID_REGION,
Enter value for columna: ID_REGION
old 1: GROUP BY &COLUMNA
new 1: GROUP BY ID_REGION

Mientras que el símbolo ampersand (&) declara una variable sustitución temporal (vale una única vez), dos ampersand consecutivos (&&) definen una variable permanente evitando que el usuario deba ingresar nuevamente su valor.

Scripts parametrizados

Las variables de sustitución se utilizan típicamente para pasar parámetros a un script a través del comando START. Dentro del script las variables deben enumerarse secuencialmente &1, &2, etc, y éstas serán asignadas en orden con los parámetros pasados en la llamada.

Ejemplo: Un script al que le pasamos dos parámetros, un nombre de columna y la tabla

programa.sql
SET VERIFY OFF
SELECT &&1, SUM(MONTO)
FROM &2
GROUP BY &1;

La llamada se realiza de la siguiente manera:

SQL> START programa.sql ID_REGION cuentas

• La variable 1 tiene dos ampersand por lo que queda definida a la primera vez que aparece.
• SET VERIFY OFF elimina el feedback de old y new values.
• Las variables de sustitución 1 y 2, al igual que las variables de usuario también se pueden borrar de memoria, por ejemplo incluyendo la línea UNDEFINE 1 en el script.

Ver también:
Variables en SQLPlus (parte 1) - Sobre variables de usuario
Variables en SQLPlus (parte 3) - Sobre variables bind

Variables en SQL*Plus (parte 1)

Hay tres tipos de variables en SQL*Plus y suelen causar confusión.
Estas son: variables de usuario, variables de sustitución y bind variables.

Aquí va la primera de ellas, más adelante revisaremos las otras.

VARIABLES DE USUARIO

Se definen de la siguiente forma:

SQL> DEFINE u_nombre = ANDREA

Para confirmar que haya quedado el valor guardado:

SQL> DEFINE u_nombre
DEFINE U_VAR1 = "ANDREA" (CHAR)

El comando DEFINE nos permite definir una cadena que se sustituye textualmente en cualquier lugar de nuestro script. Aunque quede definida como CHAR realmente no importa el tipo ya que la sustitución se realiza antes de ejecutar el código y además no incluye comillas. Por estas razones podemos jugar a 'construir' el script con nuestras variables de sustitución.
Si queremos usar la entrada como varchar le agregamos las comillas nosotros, si queremos usarla como número no las agregamos. Para recuperar el contenido de la variable usamos el símbolo ampersand.

Por ejemplo: SELECT * FROM empleado WHERE nombre = '&u_var1';

Ahora definimos una variable con un número...

SQL> define u_edad = 25
SQL> define u_edad
DEFINE U_EDAD = "25" (CHAR)

...y la usamos de la misma forma pero sin incluir las comillas:

SELECT * FROM empleado WHERE edad = &u_edad;

Las variables de usuario son útiles cuando se define su valor al comienzo de un script y luego se utiliza varias veces en el código.

No solamente podemos sustituir valores de columnas, sino también nombres de columnas o de tablas, incluso fragmentos de código, permitiéndonos hacer scripts más dinámicos.

Un uso interesante es 'aceptar' el nombre de una columna y luego usarla para parametrizar el ORDER BY de un reporte.

Ver también:
Variables en SQLPlus (parte 2) - Sobre variables de sustitución
Variables en SQLPlus (parte 3) - Sobre variables bind

Manipulando HTML en SQL*plus

"Necesito ejecutar unos scripts SQL*plus que insertan líneas de código HTML en una tabla. El problema que tengo es que cuando los valores contienen el símbolo ampersand, Oracle me pide que ingrese un valor. Como hago para evitarlo?"

Es común en HTML encontrar cosas del estilo:

<font><B>Acentuaci&oacute;n en la Gram&aacute;tica</B></font>

El símbolo ampersand en SQL*Plus suele utilizarse como prefijo standard para indicar un parámetro de sustitución en un script.

Por lo pronto, el único problema que presenta el código HTML en SQL*Plus, es para escribir el símbolo ampersand (suerte que las comillas en HTML son dobles y no simples).

Bueno, quien me planteó el problema no tenía intenciones de modificar los scripts originales, así que le di una solución simple: crear un script que invoque a los demás (con @), y que al comienzo del mismo contenga la línea:

SET DEFINE OFF

Este comando de SQL*Plus inhibe los parámetros de entrada en SQL*Plus, por lo cual va a ignorar todos los ampersand que encuentre.

Aunque la solución es simple y funciona para muchos casos, es también un poco drástica: inhibe los verdaderos parámetros que contengan nuestros scripts. Si nuestro script recibe un parámetro &valor y en la siguiente línea inserta un texto que contiene un &aacute, entonces no hay manera de que SQL*plus se de cuenta cuál es parámetro y cuál no. Conclusión: los scripts deben alterarse.

Para esto hay algunas alternativas. La primera, es cambiar el prefijo para definir parámetros, por algún símbolo que no sea caracter especial en HTML, por ejemplo, el símbolo de pesos.

SET DEFINE $

De esta manera los ampersand de HTML serán ignorados.

Una segunda alternativa, es utilizar el caracter de escape (por defecto '\') para 'escapar' los ampersand del código. Luego los ampersand no son incluídos al guardarse en la tabla. Por lo general tendremos muchos menos parámetros que símbolos ampersand, así que particularmente elijo la primer opción.

En conclusión, cambiar el prefijo de parámetros con SET DEFINE $ es la opción más flexible. Usar SET DEFINE OFF al comienzo del script nos inhibe los parámetros, pero si no los necesitamos, se transforma en la opción más sencilla de implementar.

Generar spools de más de 2 Gigas en Unix

Hay varias formas de generar spools con sql*plus desde Unix. La forma tradicional (o directa) es escribir un shell que realice lo siguiente:

-Invocar a sql*plus en modo embebido.
-Ejecutar spool y realizar el SELECT.
-Finalizar el spool con spool off.
-Salir de sql*plus.
-Zipear el archivo de salida (recomendado si se va a enviar a otro destino).

El problema que presenta este enfoque, es que en algunos sistemas operativos, puede existir el límite de 2 Gigas de tamaño para archivos, por lo cual nuestro script va a alimentar el spool hasta llegar a ese tamaño. En el mejor de los casos obtendremos un error inesperado, en otros quizás ni nos enteremos.

Una forma alternativa (y para mi gusto más elegante), es utilizar el comando mknod de Unix. Este comando, entre otras funcionalidades, permite crear un pipe de tipo FIFO que funciona como un dispositivo que utiliza el buffer del sistema operativo y permite conectarlo directamente con un programa de compresion como compress o gzip, evitando que se utilice el file system para almacenar el spool sin comprimir. También es posible conectarlo al programa split para que particione el archivo zip, si este continúa superando el límite máximo.

Básicamente las tareas que hay que escribir en nuestro shell son:
-Borrar pipes que hayan quedado anteriormente (puede fallar si ya existe)
-Crear el pipe
-Asignarle permisos al pipe (con chmod)
-Invocar en modo background al programa destino (el que va a recibir el spool). Este podría ser gzip, split, o ambos.
-Invocar a sqlplus para que realice el spool
-Borrar el pipe creado

Resulta poco intuitivo llamar primero al programa destino y luego en definitiva a quien genera el spool, pero veamos un ejemplo de código para visualizarlo más claramente.

Este script genera un spool y lo comprime en un zip:

1   #/bin/ksh
2   rm -f ${ARCHIVO}.pipe 2>/dev/null
3   mknod ${ARCHIVO}.pipe p
4   gzip -f < ${ARCHIVO}.pipe > ${ARCHIVO}.txt.Z 2>>${LOG} &
5   sqlplus -s usuario/password << EOF > /dev/null 2>> ${LOG}
6   spool ${ARCHIVO}.pipe
7   select * FROM user_objects;
8   spool off
9   EOF
10  rm -f ${ARCHIVO}.pipe 2>/dev/null

En la línea 2 eliminamos pipes que hayan podido quedar abiertos por alguna razón. De quedar abierto obtendríamos un error al crearlo de nuevo.

En la línea 3 creamos el pipe. El primer parámetro es el nombre y el segundo el tipo (p=FIFO)

En la línea 4 invocamos en background a gzip (notar el & al final de la linea). El pipe (el cual todavia no ha recibido datos), va a ser la entrada fuente para gzip. Luego gzip generará el archivo .txt.Z.

Las líneas 5 a 8 llaman a sqlplus y comienzan a enviar datos al pipe, luego gzip que esta corriendo en background zipea el contenido, sin necesidad de tener un archivo en file system.

La línea 10 elimina el pipe.

Si el archivo final .zip alcanza los 2 Gigas, vamos a tener el mismo problema, gzip no va a poder crearlo. Es necesario adicionarle el comando split y conectarlo con gzip para que parta el archivo en secciones de un tamaño manejable.
Para eso, hay que insertar entre la línea 3 y la 4 del script anterior, la siguiente línea:

3.5 split -b 1024m < ${ARCHIVO}.txt.Z &

Conviene leer primero la línea 4 y luego la 3.5, observar que ambos programas corren en background.

La línea 4 es la misma, genera el archivo .txt.Z.

La línea 3.5 recibe el archivo .txt.Z y lo divide en archivos de 1024 megas, el tamaño es en bytes (especificado por el parámetro b).

Sea cual sea el programa que genera el archivo, siempre podemos manipular con pipes los datos sin llegar a utilizar un archivo del file system para pasos intermedios.

Próximos pasos:
MAN del comando mknod
MAN del comando split