Archivo de la etiqueta: PL/SQL

UTL_FILE test

PL/SQL cuenta con un modo de acceso al sistema de ficheros que abstrae al programador del medio físico. A través de la declaración de directorios virtuales (select * from ALL_DIRECTORIES) hace que sea más sencillo trabajar con recursos del sistema de ficheros. Pero al mismo tiempo dificulta el análisis de cualquier problema con el acceso a estos recursos.

Un pequeño script como el siguiente, que verifique cada una de las operaciones disponibles en PL/SQL para el acceso a un medio físico y nos informe de los errores que se produzcan en cada una de ellas, puede sernos de gran ayuda:

set serveroutput on;
DECLARE

 -- Configure procedure
 v_dir varchar2(255) := 'LOG_FILE_DIR';

 -- Variables
 f_file utl_file.file_type;
 v_file varchar2(255);
 v_file_dest varchar2(255);
 v_file_copy varchar2(255);

 v_msg_line varchar2(32767);
 v_buffer varchar2(32767);

 v_write_error varchar2(2048);
 v_read_error varchar2(2048);
 v_copy_error varchar2(2048);
 v_remove_error varchar2(2048);
 v_rename_error varchar2(2048);

BEGIN

 -- Filename.
 v_file := 'utl_test.tmp';
 v_file_dest := 'utl_test_moved.tmp';
 v_file_copy := 'utl_test_copied.tmp';

 -- Write test.
 begin
 v_msg_line := 'Write test line.';
 f_file := utl_file.FOPEN( v_dir, v_file, 'A', 32767 );
 utl_file.PUT_LINE( f_file, v_msg_line, true );
 utl_file.FCLOSE( f_file );
 exception
 when others then
 v_write_error := sqlerrm;
 end;

 -- Read test.
 begin
 f_file := utl_file.FOPEN( v_dir, v_file, 'R', 32767 );
 utl_file.GET_LINE( f_file, v_buffer, 32767 );
 utl_file.FCLOSE( f_file );
 exception
 when others then
 v_read_error := sqlerrm;
 end;

 -- Copy test.
 begin
 utl_file.FCOPY( v_dir, v_file, v_dir, v_file_copy );
 exception
 when others then
 v_copy_error := sqlerrm;
 end;

 -- Remove test.
 begin
 utl_file.FREMOVE( v_dir, v_file );
 exception
 when others then
 v_remove_error := sqlerrm;
 end;

 -- Rename test.
 begin
 utl_file.FRENAME( v_dir, v_file_copy, v_dir, v_file_dest );
 exception
 when others then
 v_rename_error := sqlerrm;
 end;

 -- Test Result
 dbms_output.put_line('UTL_FILE Test results at '||to_char(sysdate, 'dd mon yyyy hh24:mi:ss'));
 dbms_output.put_line('');
 dbms_output.put_line('DB_NAME.......'||sys_context('userenv','db_name'));
 dbms_output.put_line('SERVER_HOST...'||sys_context('userenv','server_host'));
 dbms_output.put_line('');
 dbms_output.put_line('Write test....'||nvl(v_write_error, 'OK'));
 dbms_output.put_line('Read test.....'||nvl(v_read_error, 'OK'));
 dbms_output.put_line('Copy test.....'||nvl(v_copy_error, 'OK'));
 dbms_output.put_line('Remove test...'||nvl(v_remove_error, 'OK'));
 dbms_output.put_line('Rename test...'||nvl(v_rename_error, 'OK'));

 -- Clean test file.
 utl_file.FREMOVE( v_dir, v_file_dest );

EXCEPTION
 WHEN OTHERS THEN
 utl_file.FCLOSE( f_file );
 dbms_output.put_line('Error utl_file_test: '||sqlerrm);
END;

Si todo es correcto, deberíamos obtener un resultado como el siguiente:

UTL_FILE Test results at 25 jun 2014 22:37:23

DB_NAME.......ora_test
SERVER_HOST...vmtest

Write test....OK
Read test.....OK
Copy test.....OK
Remove test...OK
Rename test...OK
Anuncio publicitario

Búsquedas en columnas de tipo LONG

Cuando intentamos hacer una búsqueda sobre una columna de tipo LONG del mismo modo que operamos con columnas VARCHAR nos encontramos con el problema de que no se comportan del mismo modo. Y el problema es más común de lo que a priori se puede esperar dado que varias vistas del diccionario de datos utilizan este tipo de datos para almacenar información bastante interesante.

En el caso que nos ocupa, estábamos intentando localizar el uso de una columna concreta en cualquier vista accesible por el usuario. Cuando intentamos consultar la vista ALL_VIEWS para localizar en ALL_VIEWS.TEXT las menciones a la columna que buscábamos nos encontramos con el siguiente error:

select *
from all_views
where upper(text) like '%TABLE_NAME%';
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

La función UPPER() es una más de las muchas que no se pueden aplicar sobre tipos de datos LONG.

Después de darle varias vueltas al problema, e intentar distintas alternativas, logramos escribir un pequeño bloque anónimo que nos permitía realizar la búsqueda:

-- Search in LONG datatype columns
set serveroutput on;
begin
    for i in (select * from all_views) loop
        if instr(upper(i.text), 'TABLE_NAME') > 0 then
            dbms_output.put_line('View: '||i.view_name);
        end if;
    end loop;
end;

Y por fin obtuvimos el resultado esperado:

View: ALL_ALL_TABLES
View: ALL_TAB_COLUMNS
View: ALL_TAB_COL_STATISTICS
...
...
...

Recompile all invalid objects

En el trabajo con PL/SQL es habitual encontrarse con distintos objetos de base de datos que han quedado en estado INVALID. Dependiendo de la cantidad de objetos y la complejidad del código PL/SQL utilizado la tarea de recompilar estos objetos puede volverse bastante pesada.

Oracle ofrece distintos medios para recompilar objetos:

  • Identifying Invalid Objects
  • The Manual Approach
  • Custom Script
  • DBMS_UTILITY.compile_schema
  • UTL_RECOMP
  • utlrp.sql and utlprp.sql

En el siguiente artículo detallan el uso y limitaciones de cada uno de ellos:

http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php

Por mi parte, me decanto por el uso del paquete UTL_RECOMP. A continuación incluyo un script que nos permite recompilar todos los objetos de la base de datos marcados como no VALID:

set serveroutput on;

declare
  v_num_invalid_objects number;
begin

  dbms_output.put_line('Process start at '||to_char(sysdate, 'dd-Mon-yyyy hh24:mi:ss'));

  select count(*) into v_num_invalid_objects from dba_objects where STATUS not like 'VALID';

  if v_num_invalid_objects = 0 then
    dbms_output.put_line('All objets are valid.');
  else

    dbms_output.put_line('There are '||trim(to_char(v_num_invalid_objects))||' invalid objects.');

    for c_schemas in (select OWNER
              from dba_objects
              where STATUS not like 'VALID'
              group by OWNER)
    loop
      begin
        UTL_RECOMP.RECOMP_SERIAL(c_schemas.OWNER);
        dbms_output.put_line('Schema compiled: '||c_schemas.OWNER);
      exception
        when others then
          dbms_output.put_line('Error when recompile objets for schema '||c_schemas.OWNER||': '||sqlerrm);
      end;
    end loop;

    select count(*) into v_num_invalid_objects from dba_objects where STATUS not like 'VALID';

    if v_num_invalid_objects = 0 then
      dbms_output.put_line('All invalid objects recompiled.');
    else

      dbms_output.put_line('**** After recompilation, there are '||trim(to_char(v_num_invalid_objects))||' invalid objects !!!!!');

      for c_invobj in (select OWNER, OBJECT_NAME, OBJECT_TYPE, OBJECT_ID
                          from dba_objects
                          where STATUS not like 'VALID')
      loop
        dbms_output.put_line('* Invalid '||lpad(c_invobj.OBJECT_TYPE, 12, ' ')||': '||c_invobj.OWNER||'.'||c_invobj.OBJECT_NAME||' - id '||c_invobj.OBJECT_ID);
      end loop;

    end if;

  end if;

  dbms_output.put_line('Process finished at '||to_char(sysdate, 'dd-Mon-yyyy hh24:mi:ss'));

exception
  when others then
    dbms_output.put_line('General error: '||sqlerrm);
end;

Este proceso identifica todos los objetos en estado distintos de VALID que se encuentran en la base de datos y los recompila mediante el paquete UTL_RECOMP. Este paquete permite realizar recompilaciones de distintas formas. En el script que se incluye se utiliza la más sencilla de ellas. Pero retocando este script se puede incluir la posibilidad de recompilar objetos en paralelo o lanzar la recompilación a través de jobs.

Para más información sobre el paquete UTL_RECOMP (Oracle 10R2):

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_recomp.htm

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope

El error PLS-00653 está reportado por el soporte de Oracle en la nota Doc ID 1371524.1 y afecta a las bases de datos 10.2.0.1 (10R2) y posteriores.

Según esta nota, el error está identificado y verificado en el Bug no publicado 5668788. Y como solución proponen no sobrecargar funciones PIPELINED.

Lo primero que tenemos que hacer, por lo tanto, es identificar las funciones que están generando este error de compilación. Y para lograrlo disponemos de una vista del diccionario de datos que nos ofrece toda la información necesaria: DBA_PROCEDURES (y sus alternativas ALL_PROCEDURES y USER_PROCEDURES)

La primera consulta sobre la vista DBA_PROCEDURES muestra todas las funciones PIPELINED que existen en nuestra base de datos y que se encuentran sobrecargadas. Las funciones que generan el problema se encontrarán por tanto en el resultado de esta consulta:

-- PIPELINED functions overloaded
select *
from DBA_PROCEDURES
where pipelined = 'YES'
    and nvl(overload, -1) > 0;

Afinando un poco más, podemos identificar exactamente qué funciones están sobrecargadas por otra función no PIPELINED y proceder a renombrarlas:

-- PIPELINED overloaded with other not PIPELINED functions
select *
from DBA_PROCEDURES pipe
where pipelined = 'YES'
    and nvl(overload, -1) > 0
    and exists
    (
    select object_id
    from DBA_PROCEDURES no_pipe
    where no_pipe.pipelined = 'NO'
        and no_pipe.owner = pipe.owner
        and no_pipe.object_name = pipe.object_name
        and no_pipe.procedure_name = pipe.procedure_name
    );

Ya solo nos queda decidir a qué funciones vamos a cambiar el nombre, y de este modo evitar la sobrecarga. La decisión dependerá del impacto que suponga en cada caso el renombrado y la cantidad de código que deberemos adaptar.