sábado, 29 de septiembre de 2007

Problemas utilizando Analytic Functions junto con PL/SQL en 8i

La idea de éste post no es explicar el funcionamiento de las Analytic Functions, sino el error PLS-00103 al utilizar Analytic Functions.
La mejor manera de explicar éste problema, es realizando un ejemplo.

La tabla de prueba que vamos a utilizar en nuestro ejemplo, contiene los siguientes registros:

SQL> SELECT * FROM test ;

ID NIVEL SALARIO
---------- ---------- ----------
10 1 2500
20 2 3000
30 1 3500
40 2 4000
50 1 4500
60 2 5000
70 1 5500
80 2 6000
90 1 6500
100 2 7000

10 rows selected.

Primero vamos a realizar el ejemplo en una base de datos 8.1.7.4.0

SQL_8i> SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY salario DESC) AS rank
2 FROM test ;

ID NIVEL SALARIO RANK
---------- ---------- ---------- ----------
90 1 6500 1
70 1 5500 2
50 1 4500 3
30 1 3500 4
10 1 2500 5
100 2 7000 1
80 2 6000 2
60 2 5000 3
40 2 4000 4
20 2 3000 5

10 rows selected.

Como podemos observar, si ejecutamos esa consulta con Analytic Functions en SQL*Plus, funciona a la perfección. Veamos qué sucede si intentamos ejecutar la consulta dentro de PL/SQL:

SQL_8i> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC
) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /

Warning: Procedure created with compilation errors.

SQL_8i> show errors

Errors for PROCEDURE PR_TEST:

LINE/COL ERROR
----------- ------------------------------------------------------------------------
5/63 PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from

Este error se debe a que según la Nota 147808.1, no podemos utilizar Analytic Functions dentro de PL/SQL en versiones anteriores a 9i. Podemos utilizar éste tipo de funciones en SQL, pero no en PL/SQL. Desde las versiones de la 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.

Hay 2 formas de solucionar éste problema:
- Crear vistas utilizando Analytic Function y luego hacer referencia a esas vistas dentro de PL/SQL.
- Usar Dynamic SQL.

Veamos la implementación de esas 2 soluciones:

Utilizando una vista...

SQL_8i> CREATE VIEW pr_test_view AS
2 SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY nivel ORDER BY s
alario DESC) AS rank
3 FROM test ;

View created.

SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, rank
6 FROM pr_test_view ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /

Procedure created.

SQL_8i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Utilizando Dynamic SQL...

SQL_8i> CREATE OR REPLACE PROCEDURE pr_test
2 IS
3 --
4 TYPE mi_cursor IS REF CURSOR ;
5 cur mi_cursor ;
6 --
7 l_consulta VARCHAR2(1000) ;
8 l_id test.id%TYPE ;
9 l_nivel test.nivel%TYPE ;
10 l_salario test.salario%TYPE ;
11 l_rank NUMBER ;
12 --
13 BEGIN
14 --
15 l_consulta := 'SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION
BY nivel ORDER BY salario DESC) AS rank FROM test' ;
16 --
17 OPEN cur FOR l_consulta ;
18 --
19 LOOP
20 --
21 FETCH cur INTO l_id, l_nivel, l_salario, l_rank ;
22 --
23 EXIT WHEN cur%NOTFOUND ;
24 --
25 dbms_output.put_line('ID: '||l_id||
26 ' - NIVEL: '||l_nivel||
27 ' - SALARIO: '||l_salario||
28 ' - RANK: '||l_rank) ;
29 --
30 END LOOP ;
31 --
32 CLOSE cur ;
33 --
34 END pr_test ;
35 /

Procedure created.

SQL_8i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Ahora vamos a realizar el ejemplo anterior pero en una base de datos 9.2.0.8.0

SQL_9i> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /

Procedure created.

SQL_9i> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Realizamos el ejemplo anterior en una base de datos 10.1.0.2.0, veremos el mismo resultado que en 9i...

SQL_10g> CREATE PROCEDURE pr_test
2 IS
3 BEGIN
4 --
5 FOR cur IN ( SELECT id, nivel, salario, DENSE_RANK() OVER (PARTITION BY
nivel ORDER BY salario DESC) AS rank
6 FROM test ) LOOP
7 --
8 dbms_output.put_line('ID: '||cur.id||
9 ' - NIVEL: '||cur.nivel||
10 ' - SALARIO: '||cur.salario||
11 ' - RANK: '||cur.rank) ;
12 --
13 END LOOP ;
14 --
15 END pr_test ;
16 /

Procedure created.

SQL_10g> EXEC pr_test

ID: 90 - NIVEL: 1 - SALARIO: 6500 - RANK: 1
ID: 70 - NIVEL: 1 - SALARIO: 5500 - RANK: 2
ID: 50 - NIVEL: 1 - SALARIO: 4500 - RANK: 3
ID: 30 - NIVEL: 1 - SALARIO: 3500 - RANK: 4
ID: 10 - NIVEL: 1 - SALARIO: 2500 - RANK: 5
ID: 100 - NIVEL: 2 - SALARIO: 7000 - RANK: 1
ID: 80 - NIVEL: 2 - SALARIO: 6000 - RANK: 2
ID: 60 - NIVEL: 2 - SALARIO: 5000 - RANK: 3
ID: 40 - NIVEL: 2 - SALARIO: 4000 - RANK: 4
ID: 20 - NIVEL: 2 - SALARIO: 3000 - RANK: 5

PL/SQL procedure successfully completed.

Como pudimos observar, de la versión 9i en adelante, podemos utilizar Analytic Functions tanto en SQL como en PL/SQL.

1 comentario:

ale gelmini dijo...

Leo que alegría verte en tu blog! espero estés bien.
Te encontré buscando info en google sobre funciones analíticas en tablas particionadas oara oracle 8, y me alegro verificar que la solución que encontré en forma "casera" para trabajar con funciones de cubo en oracle 8 (o sea, meterla dentro de una vista) coincida con tu recomendación.
El problema que tenía es que anduvo muy bien en los tres ambientes de testing y al llegar a producción, la consulta tenía un rendimiento espantoso.
En principio es un insert select donde el select tiene sobre la función de cubo cuatro o cinco consultas pesadas que contienen reglas de negocio; sobre el insert tengo un hint parallel 4 , el 4 lo puse medio a ojo, pero sobre el select que contiene la funcion analitica no tengo ningun hint; ya vi tu consejo que no es necesario hacer ningun hint, pero en este caso ya no sé que inventar para que el insert-select en producción se comporte igual que en los distintos ambientes de testing.
Bueno Leo sigo experimentando, y te mando un fuerte abrazo avellanediano.
Alejandro - TP.