Oracledbacr, por simple pasión ...-Copyleft Miembro Comunidad Tecnológica de Oracle Latinoamérica

Creación de ambiente de Pruebas y Producción Ejecución de scripts-shell desde Procedimiento o Paquete de base de datos
Oracle 9i R2 9.2.0.7
Linux Red Hat Enterprise Server 4 y 5


Objetivo:
Ejecutar desde un procedimiento o paquete de base de datos Oracle, scripts de S.O.

Pre-requisitos de implementación:
Los paquetes de Java de la base de datos, deben estar en estado “VÁLIDO” y para esta prueba e implementación se tiene la base de datos parchada con el patch set 6. ( 9.2.0.7 ). En esencia debería funcionar con un patch set superior

SQL> column comp_name format a60
SQL> select comp_name, status, version from dba_registry;
COMP_NAME STATUS VERSION
------------------------------------------------------
Oracle9i Catalog Views VALID 9.2.0.7.0
Oracle9i Packages and Types VALID 9.2.0.7.0
Oracle Workspace Manager VALID 9.2.0.1.0
JServer JAVA Virtual Machine VALID 9.2.0.7.0
Oracle XDK for Java VALID 9.2.0.9.0
Oracle9i Java Packages VALID 9.2.0.7.0
Oracle interMedia VALID 9.2.0.7.0
Spatial VALID 9.2.0.7.0
Oracle Text VALID 9.2.0.7.0
Oracle XML Database VALID 9.2.0.7.0

Debemos contar con acceso al sistema operativo con el usuario root ó el dueño de los scripts.
Cuenta con privilegios de DBA en la base de datos y de ser necesario, cuenta y password del esquema en donde se implementará el llamado del paquete.
Es requisito un directorio en la base de datos apuntando al directorio físico en el servidor. Este debe ser creado por el usuario “SYS” de la BD. El directorio físico debe existir antes de crear el directorio.

DROP DIRECTORY DIRECTORIO;
CREATE OR REPLACE DIRECTORY
DIRECTORIO AS
'/opt/oracle/files';
GRANT READ, WRITE ON DIRECTORY SYS.DIRECTORIO TO esquema WITH GRANT OPTION;
GRANT READ, WRITE ON DIRECTORY SYS.DIRECTORIO TO PUBLIC;

Creación ambiente de prueba:

Vamos a crear un ambiente rápido de pruebas, para verificar el funcionamiento de los paquetes. Con la cuenta DBA, procedemos a otorgar los permisos de ejecución a los paquetes de la base de datos sobre los directorios de trabajo del sistema operativo, así como definimos el usuario de base de datos a utilizar dichos permisos.
Con el siguiente bloque, vamos a definir los permisos de ejecución, lectura y escritura sobre todos los archivos que se encuentren en el directorio /opt/oracle/scripts , /opt/oracle/files, /opt/oracle/files/historico, /opt/oracle/files/bitacora, al esquema.

begin
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/scripts/*',
'execute');
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'/opt/oracle/scripts/*',
'readFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/scripts/*',
'read,write');
-- commit;
end;
/
begin
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/files/historico/*',
'execute');
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'/opt/oracle/files/historico/*',
'readFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/files/historico/*',
'read,write');
-- commit;
end;
/
begin
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/files/bitacora/*',
'execute');
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.lang.RuntimePermission',
'/opt/oracle/files/bitacora/*',
'readFileDescriptor' );
dbms_java.grant_permission
('esquema',
'java.io.FilePermission',
'/opt/oracle/files/bitacora/*',
'read,write');
-- commit;
end;
/

Una vez creados los permisos, procedemos a crear el paquete que utilizaremos para el llamado de los scripts desde la base de datos.

El script fue tomado del blog de:
http://asktom.oracle.com/pls/asktomç
http://www.erpschools.com/Shell_Script_from_database_procedure_PL_SQL.asp

create or replace and compile java source named "Util"
as
import java.io.*;
import java.lang.*;
public class Util extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

Luego vamos a crear la función con la cuál haremos el llamado de los scripts:

create or replace function RUN_CMD(p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String) return integer';
/

Vamos a crear el procedimiento que ejecutará la llamada a la función anterior.

create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/

Vamos a crear un pequeño script, para realizar la prueba de ejecución:

[oracle@test scripts]$ more escribe.sh
#!/bin/sh
echo "Hola como estas, esto se escribe desde la base de datos" > /opt/oracle/scripts/texto.txt
[oracle@test scripts]$
[oracle@test scripts]$ ls -la escri* pasar*
-rwxr-xr-x 1 oracle oinstall 105 Feb 4 08:25 escribe.sh
-rwx------ 1 oracle oinstall 865 Feb 4 08:27 pasar_archivo.sh
[oracle@test scripts]$

Este script, deberá crear un archivo en la ruta especificada con el parámetro del comando “echo” incluido en el mismo.
Luego simplemente llamamos a al procedimiento desde la base de datos, logeados con el usuario del esquema al cuál le dimos permisos.

SQL> execute rc('/opt/oracle/scripts/escribe.sh');
PL/SQL procedure successfully completed.
[oracle@test scripts]$ more texto.txt
Hola como estas, esto se escribe desde la base de datos
[oracle@test scripts]$

Ahora vamos a realizar las pruebas con un script más complejo, que se encargue de llamar a un procedimiento de la base de datos, se conecte a un servidor ftp y traslade el contenido del procedimiento ejecutado, dejando un historial de los archivos creados y escribiendo a una bitácora.

[oracle@test scripts]$ more pasar_archivo.sh
#!/bin/sh
# Proceso para envio de archivos

# Hecho 03 de Febrero de 2009
# Ronald Vargas
# Archivo de autologin /opt/oracle/.netrc
# Generacion de datos llamando a procedimiento almacenado de la BD

echo 'Hora de inicio '>>/opt/oracle/files/bitacora/bitacora_proceso.txt
date >>/opt/oracle/files/bitacora/bitacora_proceso.txt
#-------------------------------------
sqlplus usuario/$PASS <>/dev/null
echo "Proceso de Traslado de archivos concluido"
echo 'Hora de Finalizacion '>>/opt/oracle/files/bitacora/bitacora_proceso.txt
date >>/opt/oracle/files/bitacora/bitacora_proceso.txt
[oracle@test scripts]$

El archivo .netrc, contiene el usuario utilizado para conectarse haciendo autologin en el servidor FTP.

[oracle@test ~]$ more .netrc
machine nombre_servidor login $USER password $PASS
[oracle@test ~]$

Nota:
Las variables $USER y $PASS, deben ser sustituidas por los valores verdaderos a utilizar.

En el procedimiento debe incluirse la llamada al script de traslado de archivos, de la siguiente manera:

# ----Llamado al procedimiento de traslado de archivos al servidor
execute rc('/opt/oracle/scripts/pasar_archivo.sh');

Otras Aplicaciones:

Podemos utilizar el mismo procedimiento para dar permisos de ejecución a comandos del sistema operativo, como en el ejemplo a continuación:

SQL> begin
2 dbms_java.grant_permission
3 ('esquema',
4 'java.io.FilePermission',
5 '/usr/bin/who',
6 'execute');
7 end;
8 /

PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec dbms_java.set_output(1000000);
SQL> execute rc('/usr/bin/who');

root :0 Sep 18 15:39
root pts/1 Sep 18 15:41 (:0.0)
root pts/2 Feb 3 17:59 (t1_rvargas.red.co.cr)
PL/SQL procedure successfully completed.