Problemas con ejecución de funcion en Oracle

[jlct]

Hola Amigos de FDW, tengo creada esta función (no estoy seguro si en realidad funciona, soy novato con oracle)

Código SQL:

CREATE OR REPLACE TYPE T_Record IS OBJECT
(
Account_ID NUMERIC, 
DATEACCT TIMESTAMP,
AMTSourceDR NUMERIC,
AMTSourceCR NUMERIC,
AMTSourceN NUMERIC
);
 
CREATE OR REPLACE TYPE T_RecordNode IS OBJECT
(
nodeid NUMERIC
);
 
CREATE OR REPLACE TYPE T_RecordEV IS OBJECT
(
elementvalue NUMERIC,
reportline NUMERIC
);
 
-- Creado el Tipo de Objeto
 
DROP TYPE T_TableRecordNode
 
CREATE OR REPLACE TYPE T_TableRecord AS TABLE OF T_Record;
CREATE OR REPLACE TYPE T_TableRecordEV AS TABLE OF T_RecordEV;
CREATE OR REPLACE TYPE T_TableRecordNode AS TABLE OF T_RecordNode;
 
-- Creado el Tipo de Tabla
 
CREATE OR REPLACE FUNCTION FN_TableRecord(valor1 IN NUMERIC, valor2 IN NUMERIC)
RETURN T_TableRecord
AS
rs T_TableRecord;
recor T_TableRecordNode;
rsl T_TableRecordEV;
sumary CHAR(1);
BEGIN
RAISE 'entro';
IF $1 IS NULL THEN 
    FOR 
    SELECT rsl IN SELECT rsu.c_elementvalue_id, rsu.pa_reportline_id FROM c_elementvalue ev INNER JOIN pa_reportsource rsu ON ev.c_elementvalue_id = rsu.c_elementvalue_id WHERE ev.ad_client_id = $2
        LOOP
            FOR rs IN SELECT rsl.pa_reportline_id, rsl.c_elementvalue_id,DATEACCT, AmtSourceDR, AmtSourceCR, AmtSourceN FROM TABLE(FN_TableRecord(rsl.c_elementvalue_id,$2))
            LOOP
                RETURN rs;
            END LOOP;
        END LOOP;
ELSE
    sumary := (SELECT issummary FROM c_elementvalue WHERE c_elementvalue_id = $1);
    IF sumary = 'Y' THEN
        FOR recor IN SELECT node_id FROM ad_treenode tn INNER JOIN ad_tree t ON tn.ad_tree_id = t.ad_tree_id WHERE parent_id = $1 AND t.treetype = 'EV'
            LOOP
                FOR rs IN SELECT * FROM TABLE(FN_TableRecord(recor.node_id,$2))
                LOOP
                    RETURN rs;
                END LOOP;
            END LOOP;
    ELSE
        FOR rs IN SELECT Account_ID, DATEACCT, SUM(AmtSourceDR) AS AmtSourceDR,SUM(AmtSourceCR) AS AmtSourceCR,SUM(AmtSourceDR-AmtSourceCR) AS AmtSourceN FROM FACT_ACCT WHERE Account_ID = $1 GROUP BY Account_ID,DATEACCT
        LOOP
            RETURN rs;
        END LOOP;
    END IF;
END IF;
END;
LANGUAGE 'PL/SQL';
 

Pero cuando la voy a ejecutar

Código SQL:

SELECT * FROM TABLE(FN_TableRecord(NULL,1000000))
 

Me genera este error:
ORA-06575: La función o el paquete FN_TABLERECORD tienen en un estado no válido
Línea de script 69, línea de instrucción 1, columna 20

Espero me puedan ayudar, Gracias.

[huesos52]

ORA-06575: La función o el paquete FN_TABLERECORD tienen en un estado no válido

La función se compiló, pero con errores.

Revisa que errores tiene la función al comilarla nuevamente.

[jlct]

Gracias por tu respuesta, pero como muestro los errores xq leí por allí que era con el Show Errors pero no funcionó o no supe como implementarlo.

por eso dije no se si la función que creé esta bien.

[gnzsoloyo]

por eso dije no se si la función que creé esta bien.

No se creó bien. Precisamente por eso te da error.
Oracle no funciona como otros DBMSs, donde si una rutina almacenada (SP o SF), no compila bien, no se crea. En Oracle se crea, pero no es válida.
En otras palabras: Existe, pero no funciona.

Revisa la función creada y verifica qué errores contiene. cuando los hayas resuelto, vuelve a compilarla.

[jlct]

Allí quisiera me ayudaran un poco, porque a mi parecer no le veo algún error (se que existe porque no funciona), pero como mencione anteriormente soy nuevo con oracle y hay ciertas cosas que son muy distintas para mi que en otros DBMSs.

Espero me puedan echar una manito.. igual sigo investigando a ver que consigo hacer. Gracias.

[gnzsoloyo]

Normalmente algunas interfases como el SQL Developer, al mostrarte el código fuente, si es inválido, también te muestran un mensaje explicativo de cuál es el error que se encuentra, y hasta te pueden posicionar el cursor en la primera aparición de el o los errores encontrados.
¿Con qué lo estás editando?

[jlct]

Actualmente estoy trabajando con AquaFold Datastudio, el cual me permite trabajar con cualquier DBMSs solo definiendo la conexión al servidor que tiene alojada la BD y ps con ese programa no me muestra un msj explicativo, solo me dice que existe una advertencia. pero como dije anteriormente intente ejecutar el show errors y no me funcionó

[gnzsoloyo]

¿Qué versión de Oracle usas?

[jlct]

Es la versión 10g.

[gnzsoloyo]

OK.
Preguntaba, porque hay algunas cosas de la sintaxis que no reconozco (usualmente trabajo con la 8i o la 11g), o al menos nunca he visto usar.
Cuando llegue a casa probaré lo que pusiste.
Por lo pronto, el primer DROP TYPE no tiene cierre de sentencia.

[jlct]

Ok perdón dejame limpiar algo ese codigo, ese drop esta allí xq habia definido mal el tipo objeto T_RecordNode pero no lo podia modificar ya que estaba asociado al tipo de tabla T_TableRecordNode

[jlct]

Iniciado por jlct

Código SQL:

CREATE OR REPLACE TYPE T_Record IS OBJECT
(
Account_ID NUMERIC, 
DATEACCT TIMESTAMP,
AMTSourceDR NUMERIC,
AMTSourceCR NUMERIC,
AMTSourceN NUMERIC
);
 
CREATE OR REPLACE TYPE T_RecordNode IS OBJECT
(
nodeid NUMERIC
);
 
CREATE OR REPLACE TYPE T_RecordEV IS OBJECT
(
elementvalue NUMERIC,
reportline NUMERIC
);
 
-- Creado el Tipo de Objeto
 
CREATE OR REPLACE TYPE T_TableRecord AS TABLE OF T_Record;
CREATE OR REPLACE TYPE T_TableRecordEV AS TABLE OF T_RecordEV;
CREATE OR REPLACE TYPE T_TableRecordNode AS TABLE OF T_RecordNode;
 
-- Creado el Tipo de Tabla
 
CREATE OR REPLACE FUNCTION FN_TableRecord(valor1 IN NUMERIC, valor2 IN NUMERIC)
RETURN T_TableRecord
AS
rs T_TableRecord;
recor T_TableRecordNode;
rsl T_TableRecordEV;
sumary CHAR(1);
BEGIN
IF $1 IS NULL THEN 
    FOR 
    SELECT rsl IN SELECT rsu.c_elementvalue_id, rsu.pa_reportline_id FROM c_elementvalue ev INNER JOIN pa_reportsource rsu ON ev.c_elementvalue_id = rsu.c_elementvalue_id WHERE ev.ad_client_id = $2
        LOOP
            FOR rs IN SELECT rsl.pa_reportline_id, rsl.c_elementvalue_id,DATEACCT, AmtSourceDR, AmtSourceCR, AmtSourceN FROM TABLE(FN_TableRecord(rsl.c_elementvalue_id,$2))
            LOOP
                RETURN rs;
            END LOOP;
        END LOOP;
ELSE
    sumary := (SELECT issummary FROM c_elementvalue WHERE c_elementvalue_id = $1);
    IF sumary = 'Y' THEN
        FOR recor IN SELECT node_id FROM ad_treenode tn INNER JOIN ad_tree t ON tn.ad_tree_id = t.ad_tree_id WHERE parent_id = $1 AND t.treetype = 'EV'
            LOOP
                FOR rs IN SELECT * FROM TABLE(FN_TableRecord(recor.node_id,$2))
                LOOP
                    RETURN rs;
                END LOOP;
            END LOOP;
    ELSE
        FOR rs IN SELECT Account_ID, DATEACCT, SUM(AmtSourceDR) AS AmtSourceDR,SUM(AmtSourceCR) AS AmtSourceCR,SUM(AmtSourceDR-AmtSourceCR) AS AmtSourceN FROM FACT_ACCT WHERE Account_ID = $1 GROUP BY Account_ID,DATEACCT
        LOOP
            RETURN rs;
        END LOOP;
    END IF;
END IF;
END;
LANGUAGE 'PL/SQL';
 

Así es como lo tengo actualmente, el raise era solo para comprobar si entraba o no en la función, pero ya me explicaste que quedo mal compilada y por ende no funciona.

[gnzsoloyo]

Hay un par de cosas que no me terminan de cerrar (aún considerando que llevo sólo un año y medio en Oracle):
1) Las llamadas de tipo $1 y $2, son más propias de la ejecución de scripts Oracle por bash (consola), que de un stored procedure. Al menos en ninguno de los SP que están en la base de nuestra empresa existen parámetros usados así...
2) Pareciera que pretendieras usar cursores, pero no están declarados. Y el uso de cosas como "SELECT rsl IN SELECT rsu.c_elementvalue_id...", o "FOR rs IN SELECT Account_ID, DATEACCT..." simplemente no las encuentro ni en los manuales.
¿Estás seguro de no estar usando sintaxis de PostgreSQL o Firebird?

Lo que sí he encontrado documentado como para Oracle 11g, es una forma como esta:

Código SQL:

FOR i IN ( SELECT prim_key j FROM data_table )
loop
   SELECT 'Very long descrip of prim_key:', prim_key
      FROM data_table WHERE prim_key = i.j ;
   SELECT 'Very long descrip of field 1:', field1
      FROM data_table WHERE prim_key = i.j ;
   SELECT 'Very long descrip of field 2:', field2
      FROM data_table WHERE prim_key = i.j ;
END loop
 

Donde, puedes ver, la subconsulta de origen está encerrada entre paréntesis. Aunque, claro, no es un modo muy ortodoxo de hacerlo. Hay algunas otras formas mucho más eficientes.

Fuera de eso, insisto, da la impresión de que estás trayendo sintaxis de un DBMS a otro, y no existe un PL/SQL estándar...

[huesos52]

Totalmente deacuerdo.
$1,$2 etc son notaciones de postgresql para referirse en orden a los parametros de entrada de una función.

Por otro lado, IS OBJECT si es de oracle y no de postgresql.

Sospecho que estas afrondando una migración de motores y todavia existen secuelas de esto.

El codigo que pones, requiere de muchas correcciones para que compile sin errores.

Por otro lado, si estas teniendo problemas para ver los errores, abre una consola de sqlplus y pega tu codigo. En esta consola SHOW ERRORS si funciona y sabras cada uno de los errores.

[jlct]

Hola Gracias por sus respuestas, en efecto estoy tratando de migrar una función realizada en postgresql a oracle, como hice mención anteriormente soy novato en esto.

lo que quiero implementar es que las consultas se recorran como si fuera un record. y la manera como indagando por internet conseguí fue esa, definir un tipo de objeto, luego definir una tabla del tipo de objeto que se definió previamente y retornar ese tipo de tabla.

he indagado en varias paginas sin poder encontrar una manera lo más apegada a como esta estructurado la función pero no ha sido posible.