Recogida de datos desde Delphi sobre Oracle
Estoy migrando mi aplicación Delphi 5 de InterBase a Oracle y desearía saber si existe una estructura parecida a la de InterBase.
En InterBase los Procedures pueden tener parámetros de retorno como las funciones de PL/SQL en Oracle, pero además puedes definir un conjunto de parámetros de retorno y entonces hacer un bucle en el procedure de tipo FOR ... DO
Y a cada iteración del bucle hacer un SUSPEND que te devuelve un registro con todos los parámetros definidos de salida, así consigues que desde Delphi se vea una tabla completa con los campos definidos por los parámetros de retorno sólo con una llamada al procedure.
Consigo hacer lo mismo atacando Oracle con una función PL/SQL que me retorna un Cursor por referencia ( REF CURSOR ), esto me funciona perfectamente en caso que desee una fila como respuesta o un conjunto de filas que se extraen de una sentencia Select SIN MODIFICARLAS !
En cambio en InterBase puedo añadir código después del DO y antes del SUSPEND de forma que las filas de salida están modificadas. Con los cursores Oracle esto no lo he logrado hacer.
Por otro lado en InterBase puedo utilizar llamadas a los procedures en la clausula FROM de las sentencias SELECT dado que me devuelven una tabla como ya he expuesto antes, en Oracle tampoco lo he logrado.
En conclusión, ¿cómo haces para recuperar desde Delphi 5 un conjunto de datos ( tabla ) que provienen de una sentencia SELECT MODIFICADA?.
Te envío un ejemplo de lo que se puede hacer en InterBase:
CREATE PROCEDURE PRUEBA RETURNS (TIPODOC)
AS
Begin
FOR
SELECT TABLA.TIPODOC,
FROM TABLA
INTO :TIPODOC
DO
:TIPODOC = :TIPODOC * 2;
SUSPEND;
END
Este procedimiento devuelve TODOS los valores del campo TIPODOC de la tabla TABLA multiplicados por dos y puedo usarlo así:
SELECT *
FROM PRUEBA
WHERE TIPODOC = 200;
En InterBase los Procedures pueden tener parámetros de retorno como las funciones de PL/SQL en Oracle, pero además puedes definir un conjunto de parámetros de retorno y entonces hacer un bucle en el procedure de tipo FOR ... DO
Y a cada iteración del bucle hacer un SUSPEND que te devuelve un registro con todos los parámetros definidos de salida, así consigues que desde Delphi se vea una tabla completa con los campos definidos por los parámetros de retorno sólo con una llamada al procedure.
Consigo hacer lo mismo atacando Oracle con una función PL/SQL que me retorna un Cursor por referencia ( REF CURSOR ), esto me funciona perfectamente en caso que desee una fila como respuesta o un conjunto de filas que se extraen de una sentencia Select SIN MODIFICARLAS !
En cambio en InterBase puedo añadir código después del DO y antes del SUSPEND de forma que las filas de salida están modificadas. Con los cursores Oracle esto no lo he logrado hacer.
Por otro lado en InterBase puedo utilizar llamadas a los procedures en la clausula FROM de las sentencias SELECT dado que me devuelven una tabla como ya he expuesto antes, en Oracle tampoco lo he logrado.
En conclusión, ¿cómo haces para recuperar desde Delphi 5 un conjunto de datos ( tabla ) que provienen de una sentencia SELECT MODIFICADA?.
Te envío un ejemplo de lo que se puede hacer en InterBase:
CREATE PROCEDURE PRUEBA RETURNS (TIPODOC)
AS
Begin
FOR
SELECT TABLA.TIPODOC,
FROM TABLA
INTO :TIPODOC
DO
:TIPODOC = :TIPODOC * 2;
SUSPEND;
END
Este procedimiento devuelve TODOS los valores del campo TIPODOC de la tabla TABLA multiplicados por dos y puedo usarlo así:
SELECT *
FROM PRUEBA
WHERE TIPODOC = 200;
4 Respuestas
Respuesta de nmanas
1
No se si te entiendo bien cuando hablas de modificar el select, supongo que querrás cambiar ciertos valores antes de devolverlos. No he necesitado nunca realizar lo que describes, pero creo que Oracle no te lo va a permitir, se me ocurre que quizás devolviendo el cursor a otra función, podrías recorrer el cursor desde la llamante y aplicar algún cambio.
Otra cosa, que no tiene nada que ver, he visto que en la definición de la tabla utilizas VARCHAR, te recomiendo que uses VARCHAR2 porque en las futuras implementaciones de Oracle el VARCHAR puede desaparecer o cambiar de funcionalidad.
Otra cosa, que no tiene nada que ver, he visto que en la definición de la tabla utilizas VARCHAR, te recomiendo que uses VARCHAR2 porque en las futuras implementaciones de Oracle el VARCHAR puede desaparecer o cambiar de funcionalidad.
Respuesta
1
Nunca he trabajado con Delphi ni con Interbase, pero con PL/SQL si he trabajado bastante. El tema de los cursores por referencia es bastante peliagudo, yo he trabajado con ellos pero solo a nivel interno de PL/SQL, nunca los he utilizado desde herramientas visuales. Aun así, creo que la única manera de llamar procedimientos PL desde sentencias SQL es cuando se trata de funciones que retornan un valor escalar. Desde luego no puedes meter el nombre de un procedure en la clausula FROM. Sólo puedes usar tablas, vistas o bien querys entre paréntesis.
Mi consejo es que te crees una vista con una columna calculada que realice la operación que sea, y que hagas tu consulta sobre la vista. Si tu procedure resulta que es muy complejo, por ejemplo que dependiendo de un parámetro saque la información de diferentes querys, siempre puedes crear la vista con UNION ALL, metiendo una constante en cada SELECT para diferenciarlas, y luego filtrando la vista por esa constante.
Tu ejemplo sencillo quedaría así :
CREATE VIEW prueba AS
SELECT tipodoc * 2 AS tipodoc
FROM tabla ;
SELECT * FROM prueba
WHERE tipodoc = 200 ;
Mi consejo es que te crees una vista con una columna calculada que realice la operación que sea, y que hagas tu consulta sobre la vista. Si tu procedure resulta que es muy complejo, por ejemplo que dependiendo de un parámetro saque la información de diferentes querys, siempre puedes crear la vista con UNION ALL, metiendo una constante en cada SELECT para diferenciarlas, y luego filtrando la vista por esa constante.
Tu ejemplo sencillo quedaría así :
CREATE VIEW prueba AS
SELECT tipodoc * 2 AS tipodoc
FROM tabla ;
SELECT * FROM prueba
WHERE tipodoc = 200 ;
Hola, ya había valorado antes la posibilidad de usar vistas, pero resulta que mi aplicación utiliza muchas consultas de este tipo y debería crear muchas vistas ( y borrarlas en algún caso ), ¿qué sabes acerca del rendimiento de crear y borrar vistas?, ¿Y del tiempo de acceso a sus datos no materializados?
Es que el tiempo de respuesta del SGBD es un factor crítico de mi sistema.
Por otro lado que opinas del uso de variables de tipo TABLE, ¿puede ser una opción alternativa al uso de vistas?
Gracias por todo nuevamente, es que esta opción que me planteas ya la había valorado y sino encuentro nada mejor, finalmente tendré que implementarlo así.
Es que el tiempo de respuesta del SGBD es un factor crítico de mi sistema.
Por otro lado que opinas del uso de variables de tipo TABLE, ¿puede ser una opción alternativa al uso de vistas?
Gracias por todo nuevamente, es que esta opción que me planteas ya la había valorado y sino encuentro nada mejor, finalmente tendré que implementarlo así.
¿Quieres decir que tendrías que crear una vista cada vez? ¿Y luego destruirla? Esto plantea un problema de seguridad, porque el usuario con el que te conectaras a la aplicación Delphi tendría que tener perfil RESOURCE ( para poder crear objetos en la BD ). Si la seguridad no es problema, no te preocupes por el rendimiento, Oracle trata las vistas como cualquier otra consulta que lances dinámicamente. Hombre, si las reutilizas varias veces mucho mejor, puesto que una vez compiladas ( plan de acceso creado ) Oracle las guarda en una cache especial para querys. O sea, que no te preocupes por el rendimiento, a no ser que sea una vista con DISTINCT, GROUP BY o ORDER BY, en cuyo caso el problema no seria de la vista, sino de la query en si.
Ah y olvida las TABLE, no son más que arrays, no son tablas temporales como podría parecer. Van bien para pasar conjuntos pequeños de datos entre procedimientos PL/SQL, pero nada más. Con ellas no puedes hacer filtros, ni ordenaciones, ni agrupaciones.
También se me ha ocurrido que quizás lo que necesitas es utilizar SQL dinámico, en lugar de ir creando y destruyendo vistas. Es que al no conocer la problemática de tu aplicación, se me hace difícil. Ahora estoy en un proyecto donde se utiliza una combinación de SQL dinámico + tablas temporales para crear listados. Cada listado tiene un procedimiento PL/SQL que recibe un grupo de parámetros, monta querys dinámicas mediante el package DBMS_SQL y llena una tabla que mapea exactamente el listado. Para que no haya problemas, cada ejecución genera un identificador único, que se usa como parte de la clave primaria de la tabla de listado. Es una de las mejores soluciones que conozco para listados muy complejos, y te aseguro que es muy rápida.
Espero que ahora lo tengas + claro !
Ah y olvida las TABLE, no son más que arrays, no son tablas temporales como podría parecer. Van bien para pasar conjuntos pequeños de datos entre procedimientos PL/SQL, pero nada más. Con ellas no puedes hacer filtros, ni ordenaciones, ni agrupaciones.
También se me ha ocurrido que quizás lo que necesitas es utilizar SQL dinámico, en lugar de ir creando y destruyendo vistas. Es que al no conocer la problemática de tu aplicación, se me hace difícil. Ahora estoy en un proyecto donde se utiliza una combinación de SQL dinámico + tablas temporales para crear listados. Cada listado tiene un procedimiento PL/SQL que recibe un grupo de parámetros, monta querys dinámicas mediante el package DBMS_SQL y llena una tabla que mapea exactamente el listado. Para que no haya problemas, cada ejecución genera un identificador único, que se usa como parte de la clave primaria de la tabla de listado. Es una de las mejores soluciones que conozco para listados muy complejos, y te aseguro que es muy rápida.
Espero que ahora lo tengas + claro !
Respuesta de jakare
1
Tienes una tabla en oracle y quieres poder acceder a esa tabla desde delphi pero con los mismos datos pero modificados (por ejemplo multiplicando por 2 el campo importe).
Si es eso, que no se si lo he entendido bien,
pues puedes crear una vista modificada de la tabla y acceder a esta desde delphi 5 si es que lo que quieres es ver los datos.
Si es eso, que no se si lo he entendido bien,
pues puedes crear una vista modificada de la tabla y acceder a esta desde delphi 5 si es que lo que quieres es ver los datos.
Respuesta de ssmo
1
Me encontraba enfermo, con hepatitis, y hace muchos meses que no venía a trabajar. Tal vez ya resolviste el problema (no lo dudo), pero de cualquier manera, ahí lo que propongo:
Si el tema es pasar datos de Interbase a Oracle, la más simple de todas es:
- Hacer una procedimiento en Delphi que invoque a una función PL/SQL que devuelva una PL/SQL table (o un procedure con una argumento del tipo de la tabla PL/SQL, no sé si Delphi soporta algo así). El procedimiento PL/SQL puede abrir el conjunto activo obtenido por un cursor, recorrerlo con un lazo, y almacenar en la tabla PL/SQL que se obtiene como resultado haciendo modificaciones en cualquier campo.
Supongamos:
Tienes un tabla POR con dos campos A y B, ambos numéricos (por simpleza)
Tu tabla PL/SQL se declara
type t_X is table of X%rowtype;
Posteriormente, declaras una variable que sea de este tipo, o un argumento de un subprograma cualquiera (evidentemente, un paquete es la mejor solución para encapsular todo esto)
En el cuerpo del subprograma, abres el cursor, de cualquier manera, incluyendo el uso del for, que lo abre, lo evalúa, obtiene el conjunto activo, y lo cierra todo implícitamente, sin necesidad de declaraciones.
Recorres y vas almacenando en la tabla PL/SQL, previa operación que modifique los valores que deseas.
Terminas, se devuelve la tabla.
Repito, desarrollo ORACLE desde plataforma Developer, por lo que me parece muy sencillo todo esto, ya que son herramientas nativas. Pero las funcionalidades de los tipos de datos ORACLE debe ser exportadas por cualquier entorno de desarrollo que intente acoplarse con una db Oracle. Por eso supongo que Delphi lo tiene todo previsto. Si no, prueba con Visual C++, lo he usado más, y usando OLE DB, es posible hacer todo esto.
Si el tema es pasar datos de Interbase a Oracle, la más simple de todas es:
- Hacer una procedimiento en Delphi que invoque a una función PL/SQL que devuelva una PL/SQL table (o un procedure con una argumento del tipo de la tabla PL/SQL, no sé si Delphi soporta algo así). El procedimiento PL/SQL puede abrir el conjunto activo obtenido por un cursor, recorrerlo con un lazo, y almacenar en la tabla PL/SQL que se obtiene como resultado haciendo modificaciones en cualquier campo.
Supongamos:
Tienes un tabla POR con dos campos A y B, ambos numéricos (por simpleza)
Tu tabla PL/SQL se declara
type t_X is table of X%rowtype;
Posteriormente, declaras una variable que sea de este tipo, o un argumento de un subprograma cualquiera (evidentemente, un paquete es la mejor solución para encapsular todo esto)
En el cuerpo del subprograma, abres el cursor, de cualquier manera, incluyendo el uso del for, que lo abre, lo evalúa, obtiene el conjunto activo, y lo cierra todo implícitamente, sin necesidad de declaraciones.
Recorres y vas almacenando en la tabla PL/SQL, previa operación que modifique los valores que deseas.
Terminas, se devuelve la tabla.
Repito, desarrollo ORACLE desde plataforma Developer, por lo que me parece muy sencillo todo esto, ya que son herramientas nativas. Pero las funcionalidades de los tipos de datos ORACLE debe ser exportadas por cualquier entorno de desarrollo que intente acoplarse con una db Oracle. Por eso supongo que Delphi lo tiene todo previsto. Si no, prueba con Visual C++, lo he usado más, y usando OLE DB, es posible hacer todo esto.