5.1.6.3 Frequently Asked Questions

This topic covers the frequently asked questions.

1. What to expect during custom PLSQL archive deployment request?

   Answer:

   1. Submitted archives should always be cumulative of all earlier submissions. If a custom artefact is no longer required, it should be excluded from the archive.
      1. Sequence value will not be reset during a subsequent archive-deploy request.
   2. If the archive omits database functions that were included in the earlier version of the archive and such functions are referred within one or more batches the archive deployment will be aborted.
      1. Such batches need to be modified to remove dependency tasks or tasks which are no longer required and then request for deployment.
   3. All database functions will be available for inclusion into a batch under the batch component name “Custom PLSQL Definition”.
2. What is the signature for database Functions?

   Answer:

   On-prem and SaaS signature comparison of Data Transformation parameters

   ICC - On-Prem Parameters

   Parameter Name	Data type
   BatchID	Varchar2
   MisDate	Varchar2

   ICC - SaaS Parameters

   Parameter Name	Data type
   BatchID	Varchar2
   MisDate	Varchar2

   Batch Id: This is a mandatory parameter expected in the custom PLSQL function. Value will be submitted by the application during runtime.

   MisDate: This is a mandatory parameter expected in the custom PLSQL function. Value will be submitted by the application during runtime.

   Sample PLSQL Function with expected parameters in the Custom PLSQL Extension is mentioned below for reference:

   Default declaration of the function will be as follows:

   create or replace FUNCTION FNUPDATERECORD_CUSTOM (BatchID Varchar2, MisDate Varchar2, custom_param1 varchar2)

   return number is

   vstatus number;

   /*

   Signature of function with AUTHID CURRENT_USERwhen redaction is enabled is provided below:

   create or replace FUNCTION FNUPDATERECORD_CUSTOM (BatchID Varchar2, MisDate Varchar2, custom_param1 varchar2)

   return number authid current_user is

   vstatus number;

   authid current_user: The user extension function should be created with AUTHID CURRENT_USER invoker rights. DML operation on any redacted tables will be allowed only for functions defined with AUTHID CURRENT_USER invoker rights.

   Batch Id: This is a mandatory parameter expected in the custom PLSQL function. Value will be submitted by the application.

   Misstate: This is a mandatory parameter expected in the custom PLSQL function. Value will be submitted by the application.

   custom_param1: Optional parameters which custom PLSQL functions can have to meet their requirements. You have to provide the parameter value during Batch definition under the Custom PLSQL Parameter field. This field is available in the UI at the time of defining the Task.

   */

   begin

   /* Functional requirement based DML operations */

   /* Calling Logger */

   PKG_CUSTEXT_LOGGER.PR_LOG_MESSAGE(Piv_message => Message ', Piv_msg_severity_cd => 20);

   commit;

   return 1;

   Exception

   PKG_CUSTEXT_LOGGER.PR_LOG_MESSAGE(Piv_message => 'ERROR IN FUNCTION ', Piv_msg_severity_cd => 75);

   return 0;

   end;

3. How will the custom database functions or procedures or packages access parameters?

   Answer:

   The parameters are batch run ID, Task ID, component ID, MisDate, tenant ID, workspace ID, service ID, process ID, etc.

   These parameters are generally used for logging by the Logger functions. The application passes these parameter values into the logger table during runtime and custom PLSQL functions need not have to pass these parameters explicitly.

   However, if the custom procedures still want to include these parameter they can do so by using the below parameter names in the custom procedures

   Parameter Name	Description	Data type
   Gv_workspace_id	Workspace Id	Varchar2
   Gv_tenant_id	Tenant Id	Varchar2
   Gv_service_id	Service Id	Varchar2
   Gv_process_id	Process id	Varchar2
   Gv_batch_run_id	Batch Run Id	Varchar2
   Gv_misdate	Misdate	Varchar2

4. How to perform Logging?

   Answer:

   To perform Logging, use below Package:

   Package Name	Parameter No	Parameter Name	Data Type	Details
   PKG_CUSTEXT_LOGGER. PR_LOG_MESSAGE	1	Piv_message	VARCHAR2
   	2	Piv_msg_severity_cd	VARCHAR2 DEFAULT 20	20 for Info 75 for Error

   1. Add your message in the Piv_message field.
   2. The Piv_msg_severity_cd should be fixed to 20 for Information type messages and 75 for Error messages.

   For more details on sample PLSQL function, see Question 2.

   Other Examples:

   /* Calling Logger */

   PKG_CUSTEXT_LOGGER.PR_LOG_MESSAGE(Piv_message => ’My Custom message here’ ', Piv_msg_severity_cd => 20);

   PKG_CUSTEXT_LOGGER.PR_LOG_MESSAGE(Piv_message => SQLCODE ', Piv_msg_severity_cd => 20);

   PKG_CUSTEXT_LOGGER.PR_LOG_MESSAGE(Piv_message => SQLERRM ', Piv_msg_severity_cd => 20);

5. How to view the view the logs in the starter kit schema?

   Answer: To view the logs in Starter Kit Schema, use below query:

   SELECT * FROM AAICL_MESSAGE_LOG