Page Comparison

Applies to development and acceptance. Do not use this procedure to insert test CAME accounts, for developers, on production.

When should an eMER account be backed up?

...

• CAMIS developer (Avro Arrows and Medevac) as well business support accounts being used on acceptance should be backed up before the refresh.

• If you have an account on development and wish to recreate it on acceptance (or vice versa). You will have to modify some values in the scripts that are exported, due to sequences that exist on some tables, as explained in this document.

• other occassionsoccasions, as needed.

Identifying the accounts to backup:

...

Exporting data to “sql” script files:

...

• Take note of the PERSON_ID of the account in YA096_PERSON from the script run earlier in this document.

• Retrieve the account to be backed up;
  For example:
  

select * from YA096_PERSON 
where PERSON_ID = 762818; -replace the PERSON_ID with the one corresponding to the account.

• Right click on the result set.

• Select “Export Dataset”.

• Set the export format to “Insert Statements”
  

...

• Set the file’s path and name.
  

  Image Added

Click the OK button to export the data.

• The expected result is a sql file containing an insert statement similar to the one below, with the actual data content corresponding to the record being exported:
  

  Code Block

  Insert into CAMIS_DATA_ADMIN.YA096_PERSON (PERSON_ID, LAST_NAME_NM, FIRST_NAME_NM, EMAIL_TXT, LANGUAGE_CORRESPONDENCE_CD, LANGUAGE_SERVICE_CD, EXTERNAL_PERSON_IND, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID) Values (762818, 'DERTINGER', 'Noreen', 'noreen.dertinger@tc.gc.ca', 'B', 'B', 1, TO_DATE('2023/06/05 7:58:11 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/06 2:48:16 PM', 'YYYY/MM/DD HH:MI:SS AM'), 733962);

• Repeat these steps to generate SQL files for the remaining seven tables

  • Combine the exported SQL scripts into a single file in the exact order the table names were listed in this document to avoid constraint errors.

Updating the script for compliance with the sequences

Before running the script with the combined insert statements, the following values have to be checked for compliance with the sequences. If that is not done constraint errors will occur.

Sample Script:

SET DEFINE OFF;
Insert into CAMIS.YA096_PERSON
(PERSON_ID, LAST_NAME_NM, FIRST_NAME_NM, EMAIL_TXT, LANGUAGE_CORRESPONDENCE_CD,
LANGUAGE_SERVICE_CD, EXTERNAL_PERSON_IND, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
Values
(558662, 'AAAATestAcct', 'Noreen', 'noreen.dertinger@tc.gc.ca', 'B',
'B', 1, TO_DATE('2023/07/21 7:43:59 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/21 7:45:47 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);

Insert into CAMIS.YA001_MEDICAL_EXAMINER
(MEDICAL_EXAMINER_PERSON_ID, APPOINTMENT_START_DTE, APPOINTMENT_END_DTE, COMMENT_TXT, HOME_ADDRESS_PRIVATE_IND,
MAILING_ADDRESS_PRIVATE_IND, EXAMINER_STATUS_CD, EMAIL_PRIVATE_IND, SEARCH_IND, DATE_CREATED_DTE,
DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
Values
(558662, TO_DATE('2023/07/19', 'YYYY/MM/DD'), TO_DATE('2027/07/19', 'YYYY/MM/DD'), 'Test account - noreen', 'Y',
'Y', '1', 'N', 'Y', TO_DATE('2023/07/21 7:43:59 AM', 'YYYY/MM/DD HH:MI:SS AM'),
TO_DATE('2023/07/21 7:45:47 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);

Insert into CAMIS.YA006_PERSON_TELEPHONE
(TELEPHONE_ID, PERSON_ID, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
Values
(166100, 558662, TO_DATE('2023/07/21 7:46:15 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/21 7:46:15 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);

Insert into CAMIS.UA031_MEDICAL_EXAMINER_OFFICE
(MEDICAL_EXAMINER_PERSON_ID, MEDICAL_OFFICE_ADDRESS_ID, CAMIS_REGION_CD, DATE_CREATED_DTE, DATE_LAST_UPDATE_DTE,
USER_LAST_UPDATE_ID)
Values
(558662, 346591, '3642', TO_DATE('2023/07/21 7:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'), TO_DATE('2023/07/21 7:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'),
557838);

Insert into CAMIS.UA045_STAMP
(STAMP_NUMBER_TXT, MEDICAL_EXAMINER_PERSON_ID, MEDICAL_OFFICE_ADDRESS_ID, STAMP_ASSIGNED_DTE, DATE_CREATED_DTE,
DATE_LAST_UPDATE_DTE, USER_LAST_UPDATE_ID)
Values
('2159', 558662, 346591, TO_DATE('2023/07/01', 'YYYY/MM/DD'), TO_DATE('2023/07/21 7:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'),
TO_DATE('2023/07/21 7:45:01 AM', 'YYYY/MM/DD HH:MI:SS AM'), 557838);
COMMIT;