iConsole Search Definition Reference Guide › Standard SQL Helper Functions › Constraints on Custom Searches

Constraints on Custom Searches

The following constraints apply when defining a custom search:

• Queries are normally executed by a special database user WgnQueryUser. For the query to run successfully, you must grant this database user access to all customized procedures or functions. To do this, run:

  GRANT EXECUTE ON MyEmailSearch_V2_1 to "WGNQueryUser"
  

• Oracle
  • When you insert data into global temporary tables, these must be committed to the database in the query to release UNDO space.
  • Use a package to simplify the granting of EXECUTE access to a collection of procedures or functions. The standard queries delivered with the iConsole are contained in the Wgn_iConsole package.
  • If you do use an Oracle package, prefix all referenced custom functions with the package name, for example, myPackage.myFunction.
  • All references to secure views (see the list below) must be prefixed with the current user name. This means you will need to use Native Dynamic SQL to construct the query, for example:

    OPEN mycursor FOR 'SELECT * FROM ' || myUser || '.Wgn_V_Event_1';
    

    You can retrieve the current user name using this command:

    SELECT sys_context('userenv','current_user') INTO myUser FROM dual
    

    Secure views at the time of writing:

    Wgn_V_Event_1
    Wgn_V_EventPartpntUser_1
    Wgn_V_User_1
    Wgn_V_Group_1
    Wgn_V_Trigger_1
    

• SQL Server
  • If temporary tables are needed, use local temporary tables (prefixed with #). Any constraints on the table (that is, the primary key) cannot be named as these names are global.
  • SQL Server stored procedures must include the SET NOCOUNT ON directive to ensure correct operation, particularly where the procedure includes more than one SELECT.