maany / db-task-plsql-cern-ld Goto Github PK

Make sure that the actions are logged into the log_messages table even if the data manipulation fails and the transaction needs to be rolled back.

Implement error handling for the procedure "copy_settings".

Transform individual functions and procedures into package(s).

Implement a routine to query all settings for a given parameter/process, regardless of the underlying value type. Take into account that the volume of data returned can be very large.

Introduce a constraint such that only one setting per parameter/process combo can be active (is_active = 'Y'), with no limitation on the number of non-active settings.

Task 1 : Analyse the existing DB schema including the PL/SQL functions and procedures and verbally describe what they do by adding comments to the code.

Task 2: Identify general issues, caveats and weaknesses present in the provided code and verbally propose improvements. This may include but not be limited to error handling and PL/SQL coding best practices. Please write down your findings in the comments.txt file.

Task 3: Consider that in a production system you have to deal with millions of settings. Identify the potential performance issues in the code. Please explain your reasoning in the comments.txt file.