USER MANUALS

CATALOG_ELEMENTS

Description

The stored procedure CATALOG_ELEMENTS returns a list of the elements (data sources, views, Web services, etc.) of the Virtual DataPort database you are connected to. You can filter the result by several parameters: element name, type, etc.

Note

We recommend you use the procedure GET_ELEMENTS instead of this one because “GET_ELEMENTS” can search on any database, not just in the one you are connected to and it returns the same information.

Syntax

CATALOG_ELEMENTS (
      name : text
    , type:
        {   NULL
          | 'Folders'
          | 'DataSources'
          | 'StoredProcedures'
          | 'Wrappers'
          | 'Views'
          | 'WebServices'
          | 'Widgets'
          | 'Associations'
          | 'JMSListeners'
        }
    , usercreator : text
    , lastusermodifier : text
    , initcreatedate : date
    , endcreatedate : date
    , initlastmodificationdate : date
    , endlastmodificationdate : date
    , description : text
)

  • If invoke this procedure with CALL and you do not want to filter by a parameter, pass NULL to it.

  • When filtering by name, usercreator and/or description, the comparison is performed with the contains operator. E.g.: if usercreator is adm, the procedure will return all the elements which creator contains the string adm.

  • When providing a value for initcreatedate and endcreatedate, or initlastmodificationdate and endlastmodificationdate, the procedure returns the elements between these two intervals. I.e.: when you provide a value for initcreatedate and endcreatedate, the procedure returns the elements created during these two dates.

    If initcreatedate is NULL, the procedure returns all the elements that were created before endcreatedate.

    If endcreatedate is NULL, the procedure returns all the elements that were created after initcreatedate. Searching by initlastmodificationdate and endlastmodificationdate works in the same way.

Privileges Required

No privileges are required to execute this procedure.

Examples

Example 1

SELECT resultName
    ,resultType
    ,resultSubtype
    ,resultUserCreator
    ,resultLastUserModifier
    ,resultCreateDate
    ,resultLastModificationDate
    ,resultDescription
FROM CATALOG_ELEMENTS()
WHERE type = 'WebServices'
    AND initCreateDate = TO_DATE('yyyy-MM-dd hh:mm:ss', '2017-01-01 00:00:00')
    AND endCreateDate = TO_DATE('yyyy-MM-dd hh:mm:ss', '2017-12-31 23:59:59');

The procedure returns all the Web services that were created in the year 2017.

Example 2

CALL CATALOG_ELEMENTS(NULL, NULL, 'user', NULL, NULL, NULL, NULL, NULL, NULL);

Returns all the elements created by user.

Add feedback