Call a DB function / procedure from Delphi

[jofemalo]

Hello,

- Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
- Delphi Version 7 (Build 4.453)
- ODAC Version 6.10.1.10 for Delphi 7

I have a database package where I have defined multiple functions, the functions return a custom type.
This custom type is found in the specification section of the Oracle package.

Code: Select all

  
  
  -- Estructura Operación
  TYPE TOperacion IS RECORD 
      (operacion_id        acceso.via_operacion.ope_id%TYPE,                       -- Identificador de la Operación.
       operacion_desc      acceso.via_operacion.ope_descripcion%TYPE,              -- Descripción de la Operación.
       valor               acceso.via_valor.val_id%TYPE);                          -- Valor de la Operación (0:Sin permiso - 1:Con permiso).

  TYPE TaTOperacion IS TABLE OF TOperacion INDEX BY PLS_INTEGER;
  

  -- Estructura Criterio
  TYPE TCriterio IS RECORD 
      (criterio_id         acceso.via_criterio.cri_id%TYPE,                        -- Identificador del Criterio.
       criterio_desc       acceso.via_criterio.cri_descripcion%TYPE,               -- Descripción del Criterio.
       operacion           TaTOperacion);                                          -- Lista de operaciones asociadas al Criterio.       

  TYPE TaTCriterio IS TABLE OF TCriterio INDEX BY PLS_INTEGER;
  
  
  -- Estructura Permisos
  TYPE TPermisos IS RECORD 
      (usuario             acceso.usuarios.nusuario%TYPE,                          -- Identificador del usuario.
       usuario_desc        acceso.usuarios.desusu%TYPE,                            -- Nombre del usuario.
       servicio_id         acceso.area.area%TYPE,                                  -- Identificador del Servicio.
       servicio_desc       acceso.area.texto%TYPE,                                 -- Descripción del Servicio.
       puesto_id           acceso.rh_puestos_trabajo.ptr_puesto%TYPE,                -- Identificador del Puesto.
       puesto_desc         acceso.rh_puestos_trabajo.ptr_descripcion%TYPE,           -- Descripción del Puesto.
       criterio            TaTCriterio);                                           -- Lista de criterios.
       

There is a function that returns a type TPermisos, due to the failure (which I will now comment on), I converted this function into a procedure where one of the parameters was output and of the type TPermisos.

From Delphi I have tried to call the function and the procedure, without success.

I have used the TOraStoredProc component (this is an example but I have done several different tests):

Code: Select all

  OraStoredProc1.StoredProcName := 'ACCESO.PCK_VISIBILIDAD_ACCESO.PRC_PERMISO';   // FNC_PERMISO
  OraStoredProc1.Params.CreateParam(ftInteger, 'pi_usuario', ptinput);
  OraStoredProc1.Params.CreateParam(ftADT, 'po_permiso', ptoutput);  // ftUnknown  // ftReference // ftArray // ftTypedBinary // ftCursor // ftVariant
  OraStoredProc1.Prepare;
  OraStoredProc1.ParamByName('pi_usuario').Value := 322;
  OraStoredProc1.Execute;
  

What is the problem?
The first parameter of CreateParam defines the type of the parameter, and the type is defined by TFieldType.

Code: Select all

  TFieldType = (ftUnknown, ftString, ftSmallint, ftInteger, ftWord,
    ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime,
    ftBytes, ftVarBytes, ftAutoInc, ftBlob, ftMemo, ftGraphic, ftFmtMemo,
    ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftFixedChar, ftWideString,
    ftLargeint, ftADT, ftArray, ftReference, ftDataSet, ftOraBlob, ftOraClob,
    ftVariant, ftInterface, ftIDispatch, ftGuid, ftTimeStamp, ftFMTBcd);
    

I can't figure out how to find a type that swallows the structure I have defined in the Oracle package spec.
"The error it gives is that the number of arguments or type is wrong."
The number of arguments is not, there are two parameters, one input and one output, the type of the first is ftInteger, and the type of the second ... I have tried several types and nothing.

I do not know if you can think of how to do it or if you would do it in another way.

I hope I have explained myself, if it has not been clear to ask myself that I will try to be clearer.

Thanks in advance.

[MaximG]

Thank you for your patience. We will investigate the described issue and let you know the results shortly.