ArcSQL is an Oracle PL/SQL library full of generic functions and procedures that I like to have handy when developing solutions. You can download it from the link below.
1️⃣ Create the ArcSQL user account.
Connect to your database as an administrative user, in my case 'admin'. Open the arcsql_user.sql file and edit the user name and password. Run the file. This script generates the user account you will use to install ArcSQL.
🔒 ArcSQL ends up getting a lot of permissions. I have tried to comment out the ones that are not being used but may be needed someday as I add more functionality.
2️⃣ Run the installation script.
Run the arcsql_install.sql script as the user you created in the first step. This script can be run repeatedly. If you encounter an error, try to fix it, and try to run the script again.
ArcSQL installs some scheduled jobs. You may want to review the jobs and the documentation for the jobs in the arcsql_jobs.sql file. You may consider disabling jobs which are not required or may already be running in another schema. In the screenshot below I am dropping the arcsql_run_sql_log_update job since it is already running in another schema in the same database.
3️⃣ Configure ArcSQL
The last step is to configure ArcSQL. Configuration is stored in one or more of three locations. The ARCSQL_CONFIG table, the ARCSQL_DEFAULT_SETTING package header, and the ARCSQL_USER_SETTING package header.
- The ARCSQL_CONFIG table is the first place the system usually looks for a configuration value.
- Next the system looks in the ARCSQL_USER_SETTING package header. This is initially delivered empty. You need to take the values you find in ARCSQL_DEFAULT_SETTING and redefine them in this package to over-ride the default values.
- If a value is not defined in the first two locations the default value found in ARCSQL_DEFAULT_SETTING package header is used.
The installation script never overwrites the ARCSQL_USER_SETTING package if it already exists so you don't have to worry about your values getting erased.
Documentation for most parameters can be found in the objects mentioned above. I won't cover them all here.
📬 If you want to send email using ArcSQL and are using Oracle Cloud's email service you will want to be sure to define the saas_from_address parameter.
🔑 If you are going to be using my saas_auth PL/SQL package be sure to set the saas_auth_salt value too!
Let me know if you have any questions.