miércoles, 4 de febrero de 2009

Cómo pasar valores desde sqlplus hacia Unix Shell

Hay ocasiones que dentro de un Shell script queremos consultar la base de datos y con dicho resultado continuar la lógica de nuestro Shell. Como logramos transferir ese resultado?

Tenemos dos enfoques para capturar valores luego de terminada la ejecución de Sql*plus: por archivos y por variables.

La primera de ellas es básicamente escribir la salida de la consulta en un archivo y luego levantarlo desde Shell. Este caso es ideal cuando nuestra consulta retorna varios registros, ya que el archivo servirá de entrada a algún comando Unix para procesarlo línea a línea. No veremos un ejemplo ya que es muy sencillo, basta usar el comando SPOOL de Sqlplus para obtener la salida en un archivo de texto. Si bien no es del todo prolijo, funciona.

Y cuando digo prolijidad, me refiero a que en realidad generar un archivo temporal no es necesario, ya que podemos recoger el resultado de la consulta en una variable Shell directamente. Lo veremos con algunos ejemplos hechos para Solaris 10. Algunos parámetros pueden variar respecto a Linux, se recomienda acudir a la documentación de man.

Resultado único
Supongamos que quiero leer el contenido de la columna Dummy de la tabla Dual y continuar manipulando el valor en Shell.

Unix lo hace fácil: ejecutamos la consulta en una sesión sqlplus, y la salida es capturada en la variable 'resultado'. Para simplificar este ejemplo no he considerado si hubo algún error en la consulta, pero puede agregarse lógica que trate mensajes de error dentro de la variable asignada.

#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
select * from dual;
exit;
EOF`

echo "El resultado es: $resultado"

Varios resultados en una línea
Qué hacer si necesitamos retornar más de un valor? Continuamos retornando una única línea, pero separamos los valores dentro del sql con algún caracter que no ocurra dentro de cada resultado, por ejemplo punto y coma. En el ejemplo obtenemos el usuario conectado, la fecha actual y el valor de la columna de Dual, todo al mismo tiempo.
#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
select user||';'||sysdate||';'||dummy from dual;
exit;
EOF`

echo "Los valores son: $resultado"
El valor retornado es: SCOTT;05-FEB-09;X

Con el comando cut separamos los valores fácilmente:

echo $resultado | cut -d';' -f1
SCOTT
echo $resultado | cut -d';' -f2
05-FEB-09
echo $resultado | cut -d';' -f3
X

Resultados multilínea
Si queremos obtener varias líneas en lugar de una sola, también podemos hacerlo de esta forma. Como en el caso anterior, si tenemos múltiples valores por línea es aconsejable usar separadores, ya que los espacios no son buenos a la hora de identificar strings que contengan espacios. Además, evitamos los incómodos espacios entre líneas al optimizar el tamaño de cada cadena y no llegar al fin de cada línea.

#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
set linesize 131
set pagesize 9999

select empno||';'||ename||';'||job||';'||mgr||';'||deptno from emp;
exit;
EOF`

echo "El resultado es: $resultado"


La salida en este caso es:

7369;SMITH;CLERK;7902;20
7499;ALLEN;SALESMAN;7698;30
7521;WARD;SALESMAN;7698;30
7566;JONES;MANAGER;7839;20
7654;MARTIN;SALESMAN;7698;30
7698;BLAKE;MANAGER;7839;30
7782;CLARK;MANAGER;7839;10
7788;SCOTT;ANALYST;7566;20
7839;KING;PRESIDENT;;10
7844;TURNER;SALESMAN;7698;30
7876;ADAMS;CLERK;7788;20
7900;JAMES;CLERK;7698;30
7902;FORD;ANALYST;7566;20


Ahora, podemos usar cualquier comando para tratar las líneas. Uno de mis favoritos es awk, ya que nos provee de muchas funciones para tratamiento de cada tipo.
Para que awk consuma cada línea de la variable como si fuese un archivo, debemos incluir comillas dobles, de otro modo lo considerará como una única gran línea.
En el siguiente ejemplo, vemos como awk toma línea a línea e imprime un texto anexo.

echo "$resultado" | awk -F";" 'BEGIN {$cnt=1}
{print "Linea "$cnt, $0; $cnt=$cnt+1;}'


La salida generada por awk es:

Linea 1 7369;SMITH;CLERK;7902;20
Linea 2 7499;ALLEN;SALESMAN;7698;30
Linea 3 7521;WARD;SALESMAN;7698;30
Linea 4 7566;JONES;MANAGER;7839;20
Linea 5 7654;MARTIN;SALESMAN;7698;30
Linea 6 7698;BLAKE;MANAGER;7839;30
Linea 7 7782;CLARK;MANAGER;7839;10
Linea 8 7788;SCOTT;ANALYST;7566;20
Linea 9 7839;KING;PRESIDENT;;10
Linea 10 7844;TURNER;SALESMAN;7698;30
Linea 11 7876;ADAMS;CLERK;7788;20
Linea 12 7900;JAMES;CLERK;7698;30
Linea 13 7902;FORD;ANALYST;7566;20
Linea 14 7934;MILLER;CLERK;7782;10

13 comentarios:

Anónimo dijo...

esto no funciona!!!

lfer dijo...

Si explicas que es lo que no te funciona, tal vez alguien pueda ayudarte!

Saludos

Anónimo dijo...

claro y sencillo GRACIAS!!!!!!

Lino dijo...

Justo lo que necesitaba. Muchas Gracias ;)

A.X.Y SOLUCIONES dijo...

Compadre usted sabe como transpar datos de unix a sqlplus especificamente ... gracias

Diego Ramirez dijo...

funciona, perfectamente, me gustaria saber si pueden ayudarme, yo necesito hacer esto pero con un procedure que retorna en variables out y me esta dando problemas, mil gracias de ante mano

lfer dijo...

Coloca mas detalles como el codigo del procedure, errores y que es lo que quieres hacer.
Saludos

Diego Ramirez dijo...

gracias por la respuesta, lo que necesito hacer es retornar desde oracle una lista de rutas de archivos .sh que están guardadas en una tabla y con esta lista formar un archivo .sh que ejecute a los que me retorna la lista; entonces con un SP que me retorne esa lista junto a otros datos podria constrirlo desde shell script. mil gracias!!

lfer dijo...

Diego, en ese caso no habria mucha diferencia, lo que tu SP tiene que hacer es escribir las lineas por la salida estandar, usando el paquete dbms_output.

Sigue este ejemplo:

STORED PROCEDURE:

CREATE OR REPLACE PROCEDURE show_emp IS
CURSOR emps IS
select * from emp;
BEGIN
FOR i IN emps LOOP
dbms_output.put_line(i.empno || ';' || i.ename || ';' || i.job);
END LOOP;
END;
/


SHELL:
vi run_emp.sh
#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
set linesize 131
set pagesize 9999

exec show_emp;
exit;
EOF`

echo "El resultado es: $resultado"


PRUEBA:
oracle@spodemo-01 # ./run_emp.sh
El resultado es: 7369;SMITH;CLERK
7499;ALLEN;SALESMAN
7521;WARD;SALESMAN
7566;JONES;MANAGER
7654;MARTIN;SALESMAN
7698;BLAKE;MANAGER
7782;CLARK;MANAGER
7788;SCOTT;ANALYST
7839;KING;PRESIDENT
7844;TURNER;SALESMAN
7876;ADAMS;CLERK
7900;JAMES;CLERK
7902;FORD;ANALYST
7934;MILLER;CLERK

Espero que te sirva.
Saludos

Diego Ramirez dijo...

Wow que buena idea, yo al final logre hacerlo con variables de salida pero este ejemplo esta excelente, muchas gracias.... es muy parecido a lo que hice que bueno saber que hay gente dispuesta ayudar mil gracias!!

Anónimo dijo...

Diego, como lo resolviste con variables de salida

ergio dijo...

Loco, te pasaste, un saludo desde Argentina.

Diego Ramirez dijo...

Hola Disculpa la demora acabo de ver esto =S lo resolvi asi

Shell

set feedback off pages 0
set lin 5000
variable outputvar REFCURSOR;
begin
PKG_Conciliation2.SP_CONTILIATION_RECARGA ( :outputvar );
end;
/
print :outputvar
EXIT

y en el otro Shell
out1=`sqlplus -s $strconex @/export/home/weblog10/digitel/appsBotonPago/conciliacion/entregables/bancoprepago/mercantil/recibidos/concilia.sql`

y out1 es una variable del shell que tienen el contenido que me retorna el SP que se corre y con ella puedo operar en el shell script, saludos!!