_IV_PROCEDURE_PARAMETERS System Query

The system query _iv_procedure_parameters returns a list of procedure parameters stored on the server.

There is one row for each parameter in the query result. There is an extra record for the return value of the function.

Column description

Column name	Type	Contents
`Schema_name`	`CHAR(31)`	Name of the schema the procedure belongs
`Procedure_name`	`CHAR(31)`	Name of the procedure
`Parameter_name`	`CHAR(31)`	Name of the parameter (empty for the return value of a function)
`Ordinal_position`	`INT`	The ordinal position of the parameter, beginning with 1 (0 for the return value)
`Data_type`	`INT`	The type of the parameter or return value
`Length`	`INT`	Length (only for variable-length types)
`Precision`	`INT`	The number of decimal digits (only for float type numbers)
`Direction`	`INT`	The direction of parameter passing (1 = IN, 2 = OUT, 3 = INOUT, 0 = return value)
`Specif`	`INT`	Specific parameter type (e.g. string sorting method)

Example 1:

This query returns the procedure parameter descriptions from the procedure PROC in the schema APL1.

SELECT * FROM _iv_procedure_parameters 
WHERE schema_name=´APL1´ AND procedure_name=´PROC´

Example 2:

This query returns a list of routines in the opened application, specifies the type of each routine (procedure or function) and the parameter count according to their type (IN, OUT and INOUT). Since the system query _iv_procedure_parameters contains only routines with parameters, the routine list must be selected from the object table.

SELECT Procs.PROC_NAME,COALESCE(SPP.PROC_TYPE,'procedure') AS PROC_TYPE,
  COALESCE(SPP.IN_PARAMS,0) AS `#IN_PARAMS`, COALESCE(SPP.OUT_PARAMS,0) AS `#OUT_PARAMS`,  
  COALESCE(SPP.INOUT_PARAMS,0) AS `#INOUT_PARAMS` 
FROM 
  (SELECT A.obj_name AS PROC_NAME FROM Objtab A, Objtab B 
   WHERE A.apl_uuid=B.apl_uuid AND B.obj_name=CURRENT_APPLICATION AND B.category=Chr(CATEG_APPL) 
     AND A.category=Chr(CATEG_PROC)
  ) AS Procs
  LEFT OUTER JOIN
  (SELECT PROCEDURE_NAME,
     CASE WHEN MIN(direction)=0 THEN 'function' ELSE 'procedure' END AS PROC_TYPE,
     SUM(CASE direction WHEN 1 THEN 1
                        ELSE 0 END) AS IN_PARAMS, 
     SUM(CASE direction WHEN 2 THEN 1
                        ELSE 0 END) AS OUT_PARAMS,
     SUM(CASE direction WHEN 3 THEN 1
                        ELSE 0 END) AS INOUT_PARAMS
  FROM _iv_procedure_parameters WHERE schema_name=CURRENT_APPLICATION
  GROUP BY PROCEDURE_NAME
  ) AS SPP
  ON(Procs.PROC_NAME=SPP.procedure_name)
GROUP BY PROC_NAME

_IV_FOREIGN_KEYS System Query

System Queries

_IV_LOCKS System Query