lunes, 30 de abril de 2018

Backing Up in NOARCHIVELOG Mode: Example

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP FORCE DBA;
SQL> SHUTDOWN IMMEDIATE;

RMAN> STARTUP MOUNT;
RMAN> BACKUP COPIES 2 INCREMENTAL LEVEL 0 MAXSETSIZE 10M DATABASE; RMAN> ALTER DATABASE OPEN;
 

martes, 20 de marzo de 2018

Relacion de Confianza Solaris 11

Relación de confianza SSH

Este articulo se refiere a la creación de un certificado SSH para poder conectarnos a maquina unix sin necesidad de clave

1. Conectarse al solaris con el usuario oracle
2. Crear una llave de tipo DSA con una longitud de 1024 bits para el protocolo SSHv2 con una frase de paso (passphrase) nula (ingresar ENTER cuando se solicite la frase de paso):
Ejecutar el siguiente comando y seguir los pasos :
ssh-keygen -t dsa -b 1024
Generating public/private dsa key pair.
Enter file in which to save the key (/export/home/usuario/.ssh/id_dsa): [ENTER]
Created directory '/export/home/usuario/.ssh'.
Enter passphrase (empty for no passphrase): [ENTER]
Enter same passphrase again: [ENTER]
Your identification has been saved in /export/home/usuario/.ssh/id_dsa.
Your public key has been saved in /export/home/usuario/.ssh/id_dsa.pub.
The key fingerprint is:
18:c8:a8:80:32:bf:12:d3:4b:1d:f6:ae:35:d7:e4:e0 usuario@servidor
Este comando genera una llave privada contenida en el archivo $HOME/.ssh/id_dsa y una llave pública contenida en el archivo $HOME/.ssh/id_dsa.pub.
3. Transferir de forma segura la llave pública creada en el servidor y colocarla en el directorio .ssh recién creado:
cd .ssh
scp id_dsa.pub usuario@cliente:.ssh/id_dsa.pub
id_dsa.pub           100% |*************************|   600       00:00
4. Acceder al cliente con el usuario por ssh y crear el archivo authorized_keys. Este archivo debe contener la llave id_dsa.pub (y el resto de las llaves, en caso de que se requiera configurar más de una):
ssh usuario@cliente
cd .ssh
cat id_dsa.pub >> authorized_keys
chmod 600 authorized_keys
rm id_dsa.pub
5. Probar el acceso al servidor desde el cliente:
ssh usuario@servidor
The authenticity of host 'solaris' can't be established.
RSA key fingerprint in md5 is: 77:83:db:48:12:ea:15:70:04:ec:8c:22:36:de:b4:15
Are you sure you want to continue connecting(yes/no)? yes

martes, 6 de febrero de 2018

AWK

básico de AWK

Qué es awk

AWK es una herramienta de procesamiento de patrones en líneas de texto. Su utilización estándar es la de filtrar ficheros o salida de comandos de UNIX, tratando las líneas para, por ejemplo, mostrar una determinada información sobre las mismas.
Por ejemplo:
  Mostrar sólo los nombres y los tamaños de los ficheros:
  # ls -l | awk '{ print $8 ":" $5 }'

  Mostrar sólo los nombres y tamaños de ficheros .txt:
  # ls -l | awk '$8 ~ /\.txt/ { print $8 ":" $5 }'

  Imprimir las líneas que tengan más de 4 campos/columnas:
  # awk 'NF > 4 {print}' fichero

Formato de uso

El uso básico de AWK es:
 awk [condicion] { comandos }
Donde:
  • [condicion] representa una condición de matcheo de líneas o parámetros.
  • comandos : una serie de comandos a ejecutar:
    • $0 → Mostrar la línea completa
    • $1-$N → Mostrar los campos (columnas) de la línea especificados.
    • FS → Field Separator (Espacio o TAB por defecto)
    • NF → Número de campos (fields) en la línea actual
    • NR → Número de líneas (records) en el stream/fichero a procesar.
    • OFS → Output Field Separator (" ").
    • ORS → Output Record Separator ("\n").
    • RS → Input Record Separator ("\n").
    • BEGIN → Define sentencias a ejecutar antes de empezar el procesado.
    • END → Define sentencias a ejecutar tras acabar el procesado.
    • length → Longitud de la línea en proceso.
    • FILENAME → Nombre del fichero en procesamiento.
    • ARGC → Número de parámetros de entrada al programa.
    • ARGV → Valor de los parámetros pasados al programa.
Las funciones utilizables en las condiciones y comandos son, entre otras:
  • close(fichero_a_reiniciar_desde_cero)
  • cos(x), sin(x)
  • index()
  • int(num)
  • lenght(string)
  • substr(str,pos,len)
  • tolower()
  • toupper()
  • system(orden_del_sistema_a_ejecutar)
  • printf()
Los operadores soportados por awk son los siguientes:
  • *, /, %, +, -, =, ++, --, +=, -=, *=, /=, %=.
El control de flujo soportado por AWK incluye:
  • if ( expr ) statement
  • if ( expr ) statement else statement
  • while ( expr ) statement
  • do statement while ( expr )
  • for ( opt_expr ; opt_expr ; opt_expr ) statement
  • for ( var in array ) statement
  • continue, break
  • (condicion)? a : b → if(condicion) a else b;
Las operaciones de búsqueda son las siguientes:
  • /cadena/ → Búsqueda de cadena.
  • /^cadena/ → Búsqueda de cadena al principio de la línea.
  • /cadena$/ → Búsqueda de cadena al final de la línea.
  • $N ~ /cadena/ → Búsqueda de cadena en el campo N.
  • $N !~ /cadena/ → Búsqueda de cadena NO en el campo N.
  • /(cadena1)|(cadena2)/ → Búsqueda de cadena1 OR cadena2.
  • /cadena1/,/cadena2>/ → Todas las líneas entre cadena1 y cadena2.

Ejemplos de selección de columnas

 Mostrar una determinada columna de información:
 # ls -l | awk '{ print $5 }'
 176869
 12
 4096
 4096

 Mostrar varias columnas, así como texto adicional (entre comillas):
 # ls -l | awk '{ print $8 ":" $5 }'
 AEMSolaris7.pdf:176869
 fich1:12
 Desktop:4096
 docs:4096

 Imprimir campos en otro orden:
 # awk '{ print $2, $1 }' fichero

 Imprimir último campo de cada línea:
 # awk '{ print $NF }' fichero

 Imprimir los campos en orden inverso:
 # awk '{ for (i = NF; i > 0; --i) print $i }' fichero

 Imprimir última línea:
 # awk '{line = $0} END {print line}'

 Imprimir primeras N líneas:
 # awk 'NR < 100 {print}' 

 Mostrar las líneas que contienen valores numéricos mayor o menor que uno dado:
 # ls -l | awk '$5 > 1000000 { print $8 ":" $5 }'

 # ls -l | awk '$5 > 1000000 || $5 < 100 { print $8 ":" $5 }'
 fich1:12
 z88dk-src-1.7.tgz:2558227
 (También hay operaciones &&)

 Mostrar la línea con el valor numérico más grande en un campo determinado:
 # awk '$1 > max { max=$1; linea=$0 }; END { print max, linea }' fichero

 Mostrar aquellos ficheros cuya longitud es mayor que 10 caracteres:
 # ls -l | awk 'length($8) > 10 { print NR " " $8 }'
 2 AEMSolaris7.pdf
 10 function_keys.txt
 14 notas_solaris.txt
 19 z88dk-src-1.7.tgz
 (Campo $8 -nombrefichero- > 10 caracteres, y numero de record)

 Mostrar líneas con más o menos de N caracteres:
 # awk 'length > 75' fichero
 # awk 'length < 75' fichero

 Mostrar campos/líneas que cumplan determinadas condiciones entre campos:
 # awk '$2 > $3 {print $3}' fichero
 # awk '$1 > $2 {print length($1)}' fichero

 Mostrar y el número de campos de un fichero además de la línea:
 # awk '{ print NF ":" $0 } ' fichero

 Mostrar y numerar sólo las líneas no vacías (no en blanco):
 # awk 'NF { $0=++a " :" $0 }; { print }'
 
 Imprimir las líneas que tengan más de N campos:
 # awk 'NF > 5 { print $0 }' fichero

 Mostrar el número de línea de cada fichero de una lista iniciando 
 desde cero la cuenta de líneas al empezar cada fichero:
 # awk '{print FNR "\t:" $0}' *.txt

 Mostrar el número de línea de cada fichero de una lista sin resetear
 la cuenta de líneas al empezar cada nuevo fichero:
 # awk '{print FNR "\t:" $0}' *.txt

Matcheo/Sustitución de cadenas

 Mostrar las líneas que contengan una determinada cadena
 # awk '/cadena/ { print }' fichero

 Mostrar las líneas que NO contengan una determinada cadena
 # awk '! /cadena/ { print }' fichero

 # ls -l | awk '/4096/ { print $8 ":" $5 }'
   Desktop:4096
   docs:4096

 Mostrar todas las líneas que aparezcan entre 2 cadenas:
 # awk '/cadena_inicio/, /cadena_fin/' fichero

 Comparaciones exactas de cadenas:
 # awk '$1 == "fred" { print $3 }' fichero

 Líneas que contengan cualquier numero en punto flotante:
 # awk '/[0-9]+\.[0-9]*/ { print }' fichero

 Busqueda de cadenas en un campo determinado
 # awk '$5 ~ /root/ { print $3 }' fichero

 # ls -l | awk '$8 ~ /txt/ { print $8 ":" $5 }'
 apuntes.txt:9342
 function_keys.txt:2252
 notas_solaris.txt:57081
 (Nota, con ~ hemos buscado sólo /txt/ en el campo $8)

 Sustituir cadena1 por cadena2:
 # awk '{ gsub(/cadena1/,"cadena2"); print }' fichero

 Sustituir 1 por 2 sólo en las líneas que contengan "cadena3".
 # awk '/cadena3/ { gsub(/1/, "2") }; { print }' fichero

 Sustituir una cadena en las líneas que no contengan "cadena3".
 # awk '!/cadena3/ { gsub(/1/, "2") }; { print }' fichero

 Sustituir una cadena u otra:
 # awk '{ gsub(/cadena1|cadena2/, "nueva"); print}' fichero

Filtrado / Eliminación de información

 Filtrar las líneas en blanco (numero campos ==0):
 # cat file.txt | awk {' if (NF != 0) print $0 '}

 Contar número de líneas donde la columna 3 sea mayor que la 1:
 # awk '$3 > $1 {print i + "1"; i++}' fichero

 Eliminar campo 2 de cada línea:
 # awk '{$2 = ""; print}' fichero

 Eliminar líneas duplicadas aunque sean no consecutivas:lines.
 # awk '!temp_array[$0]++' fichero

 Eliminar líneas que tengan una columna duplicada (usando un separador de columna).
 Usamos _ como temp_array. Usamos -F para indicar el separador de columnas.
 En este ejemplo miramos que la columna 3 sea diferente. Es el equivalente de un
 comando "uniq" comparando sólo una columna determinada.
 # awk -F',' '!_[$3]++' fichero

 (Se basa en utilizar un array de tipo hash/diccionario temporal donde
 las líneas que aún no han aparecido valen 0, y las que han aparecido !=0)

 Saltar 1000 Líneas de un fichero:
 # awk '{if ( NR > 1000 ) { print $0 }}' fichero

 Saltar líneas < 10 y > 20:
 # awk '(NR >= 0) && (NR <= 20) { print $0 }'

Cálculos de tamaños / longitudes

 Imprimir el número de líneas que contienen una cadena:
 # awk '/cadena/ {nlines = nlines + 1} END {print nlines}' fichero
  
 Sumar todos los tamaños de ficheros de un ls -l, o con una condición:
 # ls -l | awk '{ sum += $5 } END { print sum }'
 3660106
 # ls -l | awk ' $3 == "sromero" { sum += $5 } END { print sum }'
 3660106

 Contar número de líneas del fichero:
 # awk 'END {print NR}' infile

 Contar el número de líneas vacías de un fichero:
 # awk 'BEGIN { x=0 } /^$/ {x=x+1} END { print "Lineas vacias:", x }' fichero
 
 NOTA: Expandido para leerlo mejor:
    BEGIN { x=0 } 
    /^$/  { x=x+1 } 
    END   { print "Lineas vacias:", x } 

 Contar el número de palabras / campos en un fichero:
 # awk '{ total = total + NF }; END { print total+0 }' fichero

 Contar el número total de líneas que contienen una cadena:
 # awk '/cadena/ { total++ }; END { print total+0 }' fichero

 Imprimir con formato:
 yes | head -10 | awk '{printf("%2.0f", NR)}'

 Calcular la suma de todos los campos de cada línea
 # awk '{suma=0; for (i=1; i<=NF; i++) suma=suma+$i; print suma}' fichero

 Calcular la suma y la media de todas las primeras columnas del fichero:
 # awk '{ suma += $1 } END { print "suma:", s, " media:", s/NR }

Cambio de IFS (Field Separator)

 Sacar campos 1 y 3 de /etc/passwd: 
 # awk -F":" '{ print $1 "\t" $3 }' /etc/passwd

 Sacar datos de un fichero CSV separado por comas:
 # awk -F"," '$1=="Nombre" { print $2 }' fichero.csv

Otros

 Tabular líneas con 3 espacios:
 # awk '{ sub(/^/, "   "); print }'

 Obtener 1 números aleatorio:
 # awk '{print rand()}'

 Generar 40 número aleatorio (0-100) sustituyendo la línea por el número:
 # yes | head -40 | awk '{ print int(101*rand())}'

 Generar 40 números aleatorios sólo con awk:
 # awk 'BEGIN { for (i = 1; i <= 40; i++) print int(101 * rand()) }'

 Reemplazar cada campo por su valor absoluto:
 # awk '{ for (i=1; i<=NF; i=i+1) if ($i<0 0="" 1="" a="" awk="" d="" de="" definici="" else="" en="" espositivo="" f="sprintf(" fichero="" ficheros="" field="" for="" funciones="" function="" gazzette="" i="-$i" if="" individuales="" linux="" lo="" mbox="" n="" nawk="" okopnik="" partir="" print="" propias="" return="" rom="" s="" un="" utilizaci="" y="">>f}' mail_file
<0 0="" 1="" a="" awk="" d="" de="" definici="" else="" en="" espositivo="" f="sprintf(" fichero="" ficheros="" field="" for="" funciones="" function="" gazzette="" i="-$i" if="" individuales="" linux="" lo="" mbox="" n="" nawk="" okopnik="" partir="" print="" propias="" return="" rom="" s="" un="" utilizaci="" y=""> 
<0 0="" 1="" a="" awk="" d="" de="" definici="" else="" en="" espositivo="" f="sprintf(" fichero="" ficheros="" field="" for="" funciones="" function="" gazzette="" i="-$i" if="" individuales="" linux="" lo="" mbox="" n="" nawk="" okopnik="" partir="" print="" propias="" return="" rom="" s="" un="" utilizaci="" y=""> 
<0 0="" 1="" a="" awk="" d="" de="" definici="" else="" en="" espositivo="" f="sprintf(" fichero="" ficheros="" field="" for="" funciones="" function="" gazzette="" i="-$i" if="" individuales="" linux="" lo="" mbox="" n="" nawk="" okopnik="" partir="" print="" propias="" return="" rom="" s="" un="" utilizaci="" y=""> 
<0 0="" 1="" a="" awk="" d="" de="" definici="" else="" en="" espositivo="" f="sprintf(" fichero="" ficheros="" field="" for="" funciones="" function="" gazzette="" i="-$i" if="" individuales="" linux="" lo="" mbox="" n="" nawk="" okopnik="" partir="" print="" propias="" return="" rom="" s="" un="" utilizaci="" y=""> 
<0 0="" 1="" a="" awk="" d="" de="" definici="" else="" en="" espositivo="" f="sprintf(" fichero="" ficheros="" field="" for="" funciones="" function="" gazzette="" i="-$i" if="" individuales="" linux="" lo="" mbox="" n="" nawk="" okopnik="" partir="" print="" propias="" return="" rom="" s="" un="" utilizaci="" y="">df -h | awk '{print $6}' > prueba.txt
sed 's/Mounted//' prueba.txt > archivo.txt
for i in `cat archivo.txt`
do 
#echo $i
FS_UTIL=`df -h $i |grep /|awk '{print $5}'|cut -f1 -d"%"`
#echo $FS_UTIL
if test $FS_UTIL -ge 80
then
echo "File System" $i " > 80%"
fi
done
df -h | awk '{print $6}' > prueba.txt
sed 's/Mounted//' prueba.txt > archivo.txt
for i in `cat archivo.txt`
do
#echo $i
FS_UTIL=`df -h $i |grep /|awk '{print $5}'|cut -f1 -d"%"`
#echo $FS_UTIL
if test $FS_UTIL -ge 85
then
echo "File System" $i " > 85%"  >> fileystem.txt
fi
done
rm -rf prueba.txt
rm -rf archivo.txt
echo "Los filesystem que se encuentran en el archivo adjunto tienen un consumo superior al 85% de uso" | mailx -s "Monitoreo filesystem servidor" -a fileystem.txt Nelson.martinez@acme.com
<0 0="" 1="" a="" awk="" d="" de="" definici="" else="" en="" espositivo="" f="sprintf(" fichero="" ficheros="" field="" for="" funciones="" function="" gazzette="" i="-$i" if="" individuales="" linux="" lo="" mbox="" n="" nawk="" okopnik="" partir="" print="" propias="" return="" rom="" s="" un="" utilizaci="" y="">

miércoles, 17 de enero de 2018

Oracle Limits

Logical Database Limits

ItemType of LimitLimit Value
IndexesMaximum per tableUnlimited
IndexesTotal size of indexed column75% of the database block size minus some overhead
ColumnsPer table1000 columns maximum
ColumnsPer index (or clustered index)32 columns maximum
ColumnsPer bitmapped index30 columns maximum
ConstraintsMaximum per columnUnlimited
SubqueriesMaximum levels of subqueries in a SQL statementUnlimited in the FROM clause of the top-level query
255 subqueries in the WHERE clause
PartitionsMaximum length of linear partitioning key4 KB - overhead
PartitionsMaximum number of columns in partition key16 columns
PartitionsMaximum number of partitions allowed per table or index1024K - 1
SubpartitionsMaximum number of subpartitions in a composite partitioned table1024K - 1
RowsMaximum number per tableUnlimited
System Change Numbers (SCNs)Maximum281,474,976,710,656, which is 281 trillion SCNs
Stored PackagesMaximum sizeApproximately 6,000,000 lines of code.
Trigger Cascade LimitMaximum valueOperating system-dependent, typically 32
Users and RolesMaximum2,147,483,638
TablesMaximum per clustered table32 tables
TablesMaximum per databaseUnlimited
A.2 Physical Database Limits
ItemType of LimitLimit Value
Database Block SizeMinimum2048 bytes; must be a multiple of operating system physical block size
Database Block SizeMaximumOperating system dependent; never more than 32 KB
Database BlocksMinimum in initial extent of a segment2 blocks
Database BlocksMaximum per datafilePlatform dependent; typically 222 - 1 blocks
ControlfilesNumber of control files1 minimum; 2 or more (on separate devices) strongly recommended
ControlfilesSize of a control fileMaximum of 201031680 logical blocks
Database filesMaximum per tablespaceOperating system dependent; usually 1022
Database filesMaximum per database65533
May be less on some operating systems
Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extentsMaximum per dictionary managed tablespace4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extentsMaximum per locally managed (uniform) tablespace2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file sizeMaximumOperating system dependent. Limited by maximum operating system file size. See the Bigfile Tablespaces and Smallfile (traditional) Tablespaces rows for more information about the maximum database file size in these types of tablespaces.
MAXEXTENTSDefault valueDerived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTSMaximumUnlimited
Redo Log FilesMaximum number of logfilesLimited by value of MAXLOGFILES parameter in the CREATE DATABASE statement
Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log FilesMaximum number of logfiles per groupUnlimited
Redo Log File SizeMinimum size4 MB
Redo Log File SizeMaximum SizeOperating system limit; typically 2 GB
TablespacesMaximum number per database64 K
Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile TablespacesNumber of blocksA bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) TablespacesNumber of blocksA smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables fileMaximum sizeDependent on the operating system.
An external table can be composed of multiple files.
 

lunes, 23 de octubre de 2017

Oracle fecha

root@USBLDMD1012:~# date
Thursday, January 26, 2017 03:54:10 PM COT
root@USBLDMD1012:~# date -u
Thursday, January 26, 2017 08:54:14 PM GMT
srvctl setenv database -d ORCL -t "TZ=America/Bogota"
srvctl setenv database -d ACME -t "TZ=America/Bogota"
srvctl setenv listener -l listener -t "TZ=America/Bogota"

export TZ=America/Bogota

select * from nls_database_parameters

martes, 3 de octubre de 2017

UTL_CALL_STACK

UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release 1 (12.1)

Oracle database 12c introduced the UTL_CALL_STACK package to allow programmatic access to the call stack and error stack, giving much greater flexibility for debugging and error handling of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The existing functionality in the DBMS_UTILITY package is still available and has not been deprecated.

Call Stack

The call stack allows you to identify exactly where you are in the currently running code, which includes information about nesting of subprogram calls. In previous releases this information was displayed using the DBMS_UTILITY.FORMAT_CALL_STACK function, as shown below.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_call_stack AS
BEGIN
  DBMS_OUTPUT.put_line('***** Call Stack Start *****');
  DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
  DBMS_OUTPUT.put_line('***** Call Stack End *****');
END;
/

-- Test package to show a nested call.
CREATE OR REPLACE PACKAGE test_pkg AS
  PROCEDURE proc_1;
  PROCEDURE proc_2;
  PROCEDURE proc_3;
END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS

  PROCEDURE proc_1 AS
  BEGIN
    proc_2;
  END;

  PROCEDURE proc_2 AS
  BEGIN
    proc_3;
  END;

  PROCEDURE proc_3 AS
  BEGIN
    display_call_stack;
  END;

END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Call Stack Start *****
----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
0xb6d4ac18         4  procedure TEST.DISPLAY_CALL_STACK
0xb6d14298
15  package body TEST.TEST_PKG
0xb6d14298        10  package body
TEST.TEST_PKG
0xb6d14298         5  package body TEST.TEST_PKG
0xb99fe7c8
1  anonymous block

***** Call Stack End *****

PL/SQL procedure successfully completed.

SQL>
As you can see, the output from the DBMS_UTILITY.FORMAT_CALL_STACK function is rather ugly and we have no control over it, other than to manually parse it.
The UTL_CALL_STACK package contains APIs to display the contents of the call stack in a more readable form.
  • DYNAMIC_DEPTH : The number of subprograms on the call stack, starting at the current position in the call stack, to the initial call.
  • LEXICAL_DEPTH : Lexical depth of the subprogram within the current call.
  • UNIT_LINE : Line number in the subprogram of the current call.
  • SUBPROGRAM : Subprogram name associated with the current call.
  • CONCATENATE_SUBPROGRAM : Returns the UNIT.SUBPROGRAM.LOCAL_SUBPROGRAM form of the subprogram name.
  • OWNER : The owner of the subprogram associated with the current call.
  • CURRENT_EDITION : The edition of the subprogram associated with the current call.
The following example recreates the DISPLAY_CALL_STACK procedure to use the UTL_CALL_STACK package, then re-runs the test.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_call_stack AS
  l_depth PLS_INTEGER;
BEGIN
  l_depth := UTL_CALL_STACK.dynamic_depth;

  DBMS_OUTPUT.put_line('***** Call Stack Start *****');

  DBMS_OUTPUT.put_line('Depth     Lexical   Line      Owner     Edition   Name');
  DBMS_OUTPUT.put_line('.         Depth     Number');
  DBMS_OUTPUT.put_line('--------- --------- --------- --------- --------- --------------------');

  FOR i IN 1 .. l_depth LOOP
    DBMS_OUTPUT.put_line(
      RPAD(i, 10) ||
      RPAD(UTL_CALL_STACK.lexical_depth(i), 10) ||
      RPAD(TO_CHAR(UTL_CALL_STACK.unit_line(i),'99'), 10) ||
      RPAD(NVL(UTL_CALL_STACK.owner(i),' '), 10) ||
      RPAD(NVL(UTL_CALL_STACK.current_edition(i),' '), 10) ||
      UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(i))
    );
  END LOOP; 

  DBMS_OUTPUT.put_line('***** Call Stack End *****');
END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Call Stack Start *****
Depth     Lexical   Line      Owner     Edition   Name
.         Depth     Number
--------- --------- --------- --------- --------- --------------------
1         0          13       TEST                DISPLAY_CALL_STACK
2         1          15       TEST                TEST_PKG.PROC_3
3         1          10       TEST                TEST_PKG.PROC_2
4         1           5       TEST                TEST_PKG.PROC_1
5         0           1                           __anonymous_block
***** Call Stack End *****

PL/SQL procedure successfully completed.

SQL>
Starting with the call to DISPLAY_CALL_STACK, we can work back through all the nested calls to the original anonymous block. The output includes the procedure names in the package as well as the associated line numbers of the calls. If we wanted to, we could have displayed the output in reverse order, starting at the top-level call.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_call_stack AS
  l_depth PLS_INTEGER;
BEGIN
  l_depth := UTL_CALL_STACK.dynamic_depth;

  DBMS_OUTPUT.put_line('***** Call Stack Start *****');

  DBMS_OUTPUT.put_line('Depth     Lexical   Line      Owner     Edition   Name');
  DBMS_OUTPUT.put_line('.         Depth     Number');
  DBMS_OUTPUT.put_line('--------- --------- --------- --------- --------- --------------------');

  FOR i IN REVERSE 1 .. l_depth LOOP
    DBMS_OUTPUT.put_line(
      RPAD(i, 10) ||
      RPAD(UTL_CALL_STACK.lexical_depth(i), 10) ||
      RPAD(TO_CHAR(UTL_CALL_STACK.unit_line(i),'99'), 10) ||
      RPAD(NVL(UTL_CALL_STACK.owner(i),' '), 10) ||
      RPAD(NVL(UTL_CALL_STACK.current_edition(i),' '), 10) ||
      UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(i))
    );
  END LOOP; 

  DBMS_OUTPUT.put_line('***** Call Stack End *****');
END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Call Stack Start *****
Depth     Lexical   Line      Owner     Edition   Name
.         Depth     Number
--------- --------- --------- --------- --------- --------------------
5         0           1                           __anonymous_block
4         1           5       TEST                TEST_PKG.PROC_1
3         1          10       TEST                TEST_PKG.PROC_2
2         1          15       TEST                TEST_PKG.PROC_3
1         0          13       TEST                DISPLAY_CALL_STACK
***** Call Stack End *****

PL/SQL procedure successfully completed.

SQL>
You now have programmatic control to interrogate and display the call stack if you need to.

Error Stack

Exceptions are often handled by exception handlers and re-raised. In some cases, exceptions in nested calls result in different errors being produced by the error handler of the calling routine. The error stack allows you to display chains of errors, making it easier to determine the real cause of the issue. In previous releases this information was displayed using the DBMS_UTILITY.FORMAT_ERROR_STACK function, as shown below.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_error_stack AS
BEGIN
  DBMS_OUTPUT.put_line('***** Error Stack Start *****');
  DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
  DBMS_OUTPUT.put_line('***** Error Stack End *****');
END;
/

-- Test package to show a nested call.
CREATE OR REPLACE PACKAGE test_pkg AS
  PROCEDURE proc_1;
  PROCEDURE proc_2;
  PROCEDURE proc_3;
END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS

  PROCEDURE proc_1 AS
  BEGIN
    proc_2;
  EXCEPTION
    WHEN OTHERS THEN
      display_error_stack;
  END;

  PROCEDURE proc_2 AS
  BEGIN
    proc_3;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE DUP_VAL_ON_INDEX;
  END;

  PROCEDURE proc_3 AS
  BEGIN
    RAISE NO_DATA_FOUND;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE TOO_MANY_ROWS;
  END;

END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Error Stack Start *****
ORA-00001: unique constraint (.) violated
ORA-06512: at "TEST.TEST_PKG", line
16
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512:
at "TEST.TEST_PKG", line 24
ORA-01403: no data found

***** Error Stack End *****

PL/SQL procedure successfully completed.

SQL>
The output from DBMS_UTILITY.FORMAT_ERROR_STACK function is fine, but there are occasional formatting errors and we can't order the output to suit our taste.
The UTL_CALL_STACK package contains APIs to display the contents of the error stack.
  • ERROR_DEPTH : The number of errors on the error stack.
  • ERROR_MSG : The error message associated with the current line in the error stack.
  • ERROR_NUMBER : The error number associated with the current line in the error stack.
The following example recreates the DISPLAY_ERROR_STACK procedure to use the UTL_CALL_STACK package, then re-runs the test.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_error_stack AS
  l_depth PLS_INTEGER;
BEGIN
  l_depth := UTL_CALL_STACK.error_depth;

  DBMS_OUTPUT.put_line('***** Error Stack Start *****');

  DBMS_OUTPUT.put_line('Depth     Error     Error');
  DBMS_OUTPUT.put_line('.         Code      Message');
  DBMS_OUTPUT.put_line('--------- --------- --------------------');

  FOR i IN 1 .. l_depth LOOP
    DBMS_OUTPUT.put_line(
      RPAD(i, 10) ||
      RPAD('ORA-' || LPAD(UTL_CALL_STACK.error_number(i), 5, '0'), 10) ||
      UTL_CALL_STACK.error_msg(i)
    );
  END LOOP; 

  DBMS_OUTPUT.put_line('***** Error Stack End *****');
END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Error Stack Start *****
Depth     Error     Error
.         Code      Message
--------- --------- --------------------
1         ORA-00001 unique constraint (.) violated

2         ORA-06512 at "TEST.TEST_PKG", line 16

3         ORA-01422 exact fetch returns more than requested number of rows

4         ORA-06512 at "TEST.TEST_PKG", line 24

5         ORA-01403 no data found

***** Error Stack End *****

PL/SQL procedure successfully completed.

SQL>
In the previous example, the display order matches the DBMS_UTILITY.FORMAT_ERROR_STACK output, which reports last to first in the chain. We could easily reverse it to display first to last.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_error_stack AS
  l_depth PLS_INTEGER;
BEGIN
  l_depth := UTL_CALL_STACK.error_depth;

  DBMS_OUTPUT.put_line('***** Error Stack Start *****');

  DBMS_OUTPUT.put_line('Depth     Error     Error');
  DBMS_OUTPUT.put_line('.         Code      Message');
  DBMS_OUTPUT.put_line('--------- --------- --------------------');

  FOR i IN REVERSE 1 .. l_depth LOOP
    DBMS_OUTPUT.put_line(
      RPAD(i, 10) ||
      RPAD('ORA-' || LPAD(UTL_CALL_STACK.error_number(i), 5, '0'), 10) ||
      UTL_CALL_STACK.error_msg(i)
    );
  END LOOP; 

  DBMS_OUTPUT.put_line('***** Error Stack End *****');
END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Error Stack Start *****
Depth     Error     Error
.         Code      Message
--------- --------- --------------------
5         ORA-01403 no data found

4         ORA-06512 at "TEST.TEST_PKG", line 24

3         ORA-01422 exact fetch returns more than requested number of rows

2         ORA-06512 at "TEST.TEST_PKG", line 16

1         ORA-00001 unique constraint (.) violated

***** Error Stack End *****

PL/SQL procedure successfully completed.

SQL>

Backtrace

Backtrace shows a walk through the call stack from the line where the exception was raised, to the last call before the exception was trapped. This shows the propagation of the exception, which allows you to identify the actual line that caused the problem. In previous releases this information was displayed using the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, as shown below.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_backtrace AS
BEGIN
  DBMS_OUTPUT.put_line('***** Backtrace Start *****');
  DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_backtrace);
  DBMS_OUTPUT.put_line('***** Backtrace End *****');
END;
/

-- Test package to show a nested call.
CREATE OR REPLACE PACKAGE test_pkg AS
  PROCEDURE proc_1;
  PROCEDURE proc_2;
  PROCEDURE proc_3;
END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS

  PROCEDURE proc_1 AS
  BEGIN
    proc_2;
  EXCEPTION
    WHEN OTHERS THEN
      display_backtrace;
  END;

  PROCEDURE proc_2 AS
  BEGIN
    proc_3;
  END;

  PROCEDURE proc_3 AS
  BEGIN
    RAISE NO_DATA_FOUND;
  END;

END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Backtrace Start *****
ORA-06512: at "TEST.TEST_PKG", line 18
ORA-06512: at "TEST.TEST_PKG", line
13
ORA-06512: at "TEST.TEST_PKG", line 5

***** Backtrace End *****

PL/SQL procedure successfully completed.

SQL>
With the exception of some minor formatting issues, this output is fine and will probably be OK for most situations.
The UTL_CALL_STACK package contains APIs to display the backtrace.
  • BACKTRACE_DEPTH : The number of backtrace messages on the error stack.
  • BACKTRACE_LINE : Line number in the subprogram of the current call.
  • BACKTRACE_UNIT : Subprogram name associated with the current call.
The following example recreates the DISPLAY_BACKTRACE procedure to use the UTL_CALL_STACK package, then re-runs the test.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_backtrace AS
  l_depth PLS_INTEGER;
BEGIN
  l_depth := UTL_CALL_STACK.backtrace_depth;

  DBMS_OUTPUT.put_line('***** Backtrace Start *****');

  DBMS_OUTPUT.put_line('Depth     BTrace     BTrace');
  DBMS_OUTPUT.put_line('.         Line       Unit');
  DBMS_OUTPUT.put_line('--------- --------- --------------------');

  FOR i IN 1 .. l_depth LOOP
    DBMS_OUTPUT.put_line(
      RPAD(i, 10) ||
      RPAD(TO_CHAR(UTL_CALL_STACK.backtrace_line(i),'99'), 10) ||
      UTL_CALL_STACK.backtrace_unit(i)
    );
  END LOOP; 

  DBMS_OUTPUT.put_line('***** Backtrace End *****');
END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Backtrace Start *****
Depth     BTrace     BTrace
.         Line       Unit
--------- --------- --------------------
1           5       TEST.TEST_PKG
2          13       TEST.TEST_PKG
3          18       TEST.TEST_PKG
***** Backtrace End *****

PL/SQL procedure successfully completed.

SQL>
There is very little you can do with the backtrace, other than reordering it. The "ORA-06512" error is not included, but this is implied because it is a backtrace message. The following example shows the backtrace in reverse order.
-- Procedure to display the call stack.
CREATE OR REPLACE PROCEDURE display_backtrace AS
  l_depth PLS_INTEGER;
BEGIN
  l_depth := UTL_CALL_STACK.backtrace_depth;

  DBMS_OUTPUT.put_line('***** Backtrace Start *****');

  DBMS_OUTPUT.put_line('Depth     BTrace     BTrace');
  DBMS_OUTPUT.put_line('.         Line       Unit');
  DBMS_OUTPUT.put_line('--------- --------- --------------------');

  FOR i IN REVERSE 1 .. l_depth LOOP
    DBMS_OUTPUT.put_line(
      RPAD(i, 10) ||
      RPAD(TO_CHAR(UTL_CALL_STACK.backtrace_line(i),'99'), 10) ||
      UTL_CALL_STACK.backtrace_unit(i)
    );
  END LOOP; 

  DBMS_OUTPUT.put_line('***** Backtrace End *****');
END;
/


-- Run the test.
SET SERVEROUTPUT ON
EXEC test_pkg.proc_1;
***** Backtrace Start *****
Depth     BTrace     BTrace
.         Line       Unit
--------- --------- --------------------
3          18       TEST.TEST_PKG
2          13       TEST.TEST_PKG
1           5       TEST.TEST_PKG
***** Backtrace End *****

PL/SQL procedure successfully completed.

SQL>

DBMS_UTILITY.EXPAND_SQL_TEXT

DBMS_UTILITY.EXPAND_SQL_TEXT : Expand SQL References to Views in Oracle Database 12c Release 1 (12.1)

Views can be a useful way to hide complexity from developers, but that can in turn cause problems. It's easy to write apparently simple statements, that result in extremely complex SQL being sent to the server. The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands references to views, turning them into subqueries in the original statement. A simple example of this is shown below.
Create a view containing a join.
CONN scott/tiger@pdb1

CREATE OR REPLACE VIEW emp_v AS
SELECT e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.hiredate,
       e.sal,
       e.comm,
       e.deptno,
       d.dname
FROM   emp e
       JOIN dept d ON e.deptno = d.deptno;
The view has hidden the complexity of the join, allowing us to use an extremely simple query.
SELECT * FROM emp_v;
We can see the real SQL statement processed by the server by expanding the statement.
SET SERVEROUTPUT ON 
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => 'SELECT * FROM emp_v',
    output_sql_text => l_clob
  );

  DBMS_OUTPUT.put_line(l_clob);
END;
/

SELECT "A1"."EMPNO" "EMPNO",
       "A1"."ENAME" "ENAME",
       "A1"."JOB" "JOB","A1"."MGR" "MGR",
       "A1"."HIREDATE" "HIREDATE",
       "A1"."SAL" "SAL",
       "A1"."COMM" "COMM",
       "A1"."DEPTNO" "DEPTNO",
       "A1"."DNAME" "DNAME"
FROM   (SELECT "A2"."EMPNO_0" "EMPNO",
               "A2"."ENAME_1" "ENAME",
               "A2"."JOB_2" "JOB",
               "A2"."MGR_3" "MGR",
               "A2"."HIREDATE_4" "HIREDATE",
               "A2"."SAL_5" "SAL",
               "A2"."COMM_6" COMM",
               "A2"."QCSJ_C000000000400000_7" "DEPTNO",
               "A2"."DNAME_9" "DNAME"
         FROM  (SELECT "A4"."EMPNO" "EMPNO_0",
                       "A4"."ENAME" "ENAME_1",
                       "A4"."JOB" "JOB_2",
                       "A4"."MGR" "MGR_3",
                       "A4"."HIREDATE" "HIREDATE_4",
                       "A4"."SAL" "SAL_5",
                       "A4"."COMM" "COMM_6",
                       "A4"."DEPTNO" "QCSJ_C000000000400000_7",
                       "A3"."DEPTNO" "QCSJ_C000000000400001",
                       "A3"."DNAME" "DNAME_9"
                FROM   SCOTT."EMP" "A4",
                       SCOTT."DEPT" "A3"
                WHERE  "A4"."DEPTNO"="A3"."DEPTNO") "A2"
       ) "A1"

PL/SQL procedure successfully completed.

SQL>

sábado, 30 de septiembre de 2017

AUTHID CURRENT (USER - DEFINER)


AUTHID CURRENT_USER
Specify CURRENT_USER to indicate that the package executes with the privileges of CURRENT_USER. This clause creates an invoker's rights package.
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the package resides.

AUTHID DEFINER

Specify DEFINER to indicate that the package executes with the privileges of the owner of the schema in which the package resides and that external names resolve in the schema where the package resides. This is the default and creates a definer's rights package.


WARNING: Writing PL/SQL code with the default authid definer, can facilitate SQL injection attacks, because an intruder would get privileges that they would not get if they used authid current_user.


PL/SQL Function Result Cache

On the PL/SQL Function Result Cache

By Steven Feuerstein Oracle Employee ACE 

Best practices—and preparation—for PL/SQL in Oracle Database 11g
I have been reading that Oracle is launching the 11th release of its database. Very exciting! But here's the problem: I don't think I'll be able to use it for another two years. So why should I even care about the new PL/SQL features of this future (for me) release?
Ah, yes, the real world. Oracle comes out with a new database release, and by the time it does so, its developers are already focused on the next new release. People like me start writing about, demonstrating, and even training on the newer release. And then there's almost everybody else: still on older releases, hoping and praying that someday maybe their management will see fit to catch up.
I feel your pain.
Having said that, I do think it makes an awful lot of sense to learn now about what Oracle Database 11g will have to offer you and your company in the future. The reason is very simple: once you see what is going to be available in Oracle Database 11g, you will probably change the way you write your code now !
I would say that the single most important new PL/SQL feature in Oracle Database 11g is thePL/SQL function result cache . Quite a mouthful, but then it is quite a feature.
I offer in this answer a quick overview of this feature, and I conclude by discussing how knowing about this feature should affect the way you write PL/SQL programs for earlier Oracle Database releases.
Suppose I am on a team that is building a human resources application. The employees table is one of the key structures, holding all the data for all the employees. Hundreds of users execute numerous programs in the application that read from this table—and read from it very often. Yet the table changes relatively infrequently, perhaps once or twice an hour. As a result, the application code repeatedly retrieves from the block buffer cache what is mostly static data, enduring the overhead of checking to see if the particular query has already been parsed, finding the data in the buffer, and returning it.
The team needs to improve the performance of querying data from the employees table. Currently, we use the following function to return a row from the employees table: 
FUNCTION one_employee (employee_id_in 
IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
IS
    l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
      INTO l_employee
      FROM employees
    WHERE employee_id = employee_id_in;

    RETURN l_employee;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
       /* Return an empty record. */
       RETURN l_employee;
END one_employee;

In Oracle Database 11g, however, we can add a line to the header of this function as follows:  
FUNCTION one_employee (employee_id_in 
IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
   RESULT_CACHE RELIES_ON (employees)
IS
    l_employee   employees%ROWTYPE;
BEGIN
.
.
.
 
This RESULT_CACHE clause tells Oracle Database that it should remember (store in a special in-memory result cache) each record retrieved for a specific employee ID number. And when a session executes this function and passes in an employee ID that was previously stored, the PL/SQL runtime engine will not execute the function body, which includes that query.
Instead, it will simply retrieve the record from the cache and return that data immediately. The result is much faster retrieval.
In addition, by specifying RELIES_ON (employees), we inform Oracle Database that if any session commits changes to that table, any data in the result cache drawn from the table must be invalidated. The next call to the one_employee function would then have to execute the query and retrieve the data fresh from the table.
Because the cache is a part of the System Global Area (SGA), its contents are available to all sessions connected to the instance. Furthermore, Oracle Database will apply its "least recently used algorithm" to the cache, to ensure that the most recently accessed data will be preserved in the cache.
Prior to Oracle Database 11g, a similar kind of caching was possible with package-level collections, but this cache is session-specific and located in the Process Global Area (PGA). This means that if I have 1,000 different sessions running the application, I could use up an enormous amount of memory in addition to that consumed by the SGA.
The PL/SQL function result cache minimizes the amount of memory needed to cache and share this data across all sessions. This low memory profile, plus the automatic purge of cached results whenever changes are committed, makes this feature of Oracle Database 11g very practical for optimizing performance in PL/SQL applications.

Analyze Performance and PGA Memory Impact

To test the improvement in performance and the impact on PGA memory over repeated queries of the data, I put together a set of scripts, available at oracle.com/technetwork/oramag/oracle/07-sep/o57plsql.zip, that compares three different ways to retrieve a row of employee data:
1. Execute the query repeatedly
2. Cache all the rows of the employees table in a packaged collection and then retrieve the data from that cache
3. Use the PL/SQL function result cache to avoid repetitive querying
To try this out yourself, unzip the o57plsql.zip file and run the 11g_emplu.tst script. It should take about five or six seconds to complete, and then you should see results like this: 
PGA before tests are run:
session PGA:  910860 bytes

Execute query each time 
Elapsed: 4.5 seconds. 
session PGA:  910860 bytes

Cache table in PGA memory 
Elapsed: .11 seconds. 
session PGA: 1041932 bytes

Oracle Database 11
                               g result cache 
Elapsed: .27 seconds. 
session PGA: 1041932 bytes
                            
Here are my conclusions from this admittedly incomplete analysis: 
  • The Oracle Database 11g PL/SQL function result cache is, indeed, much faster than repetitive querying. In this test, it was over an order of magnitude faster.  
  • A packaged collection cache is even faster, most likely because the PL/SQL runtime engine can access the data from PGA memory rather than SGA memory. Unfortunately, this also means that the consumption of memory occurs on a per-session basis, which is not very scalable. 
  • The packaged collection approach consumed additional PGA memory, but the Oracle Database 11g function result cache did not.
And then, of course, there are the other key advantages of the function result cache: automatic invalidation of cache contents when a dependent table is changed, the fact that the cache is shared across sessions, and the application of the least recently used algorithm to the memory in the cache.

So Why Should You Care Now?

"All right," you may be saying to yourself, "It's cool. Super cool. But I still can't use it for two years or more, so what good does that do me now?"
You may not be able to use the PL/SQL function result cache yet, but you can write your code now so that when you eventually upgrade to Oracle Database 11g, you will be able to quickly and easily use this cache in your application code.
In other words, you can and should prepare now for this future feature.
How do you do that? By placing all your queries (at least those against tables that change infrequently but are queried often) inside functions, so that you can easily add the RESULT_CACHE clause.
Think about it: today you probably don't do that. Instead, whenever you need data from the database, you write the required query, right there in the application logic you are writing (whether that logic resides in the back end—other PL/SQL programs—or the front end—languages such as Java).

Next Steps



And that same query (or some minor variation on it) will likely appear in multiple places in your application code. Why not? It is so easy to write those SQL statements; that's one of the beauties of PL/SQL. But that ease of use in executing SQL inside PL/SQL makes us all take SQL for granted, and when you upgrade to Oracle Database 11g, you will pay the price.
If after upgrading, you want to take advantage of RESULT_CACHE, you will have to find every affected SQL statement and either put the RESULT_CACHE hint inside that query (that's right, this feature is available natively within SQL as well as for functions) or construct the function, put the query inside it, find each of the applicable queries, and replace the query with the function call.
Certainly both of these approaches are eminently doable, but they are also very unlikely to happen. IT managers are loathe to go into existing, working production code and upset the applecart by making lots of changes.
If, conversely, you start right now , in Oracle9i Database or Oracle Database 10g, to place your queries inside functions, you will almost instantly be able to upgrade your code to use this fantastic new feature when you upgrade to Oracle Database 11g.
And, best of all, the application code that calls the function will not have to be changed at all! Your manager will be very impressed.
And that is why you should learn about the new features of Oracle Database 11g. Today.