Survey App DB Scripts Conversion From MySQL To Oracle

Sorry right now I don’t have access to the App or an Oracle DB connected to Appian but for sure the CDT’s use different annotations

Somethings that came to my mind
- the CDTs all have to reference each field (@Column)
- the column names on the CDTs should be upper case
- Oracle doesn’t use autoincrement there are sequences which you need to ass on the primary key field on the CDT. (Read normal JPA documentation)
- you might have to tweak a little the data types.
- if any PL/SQL was implemented some tweaks might be required.

Hope this helps
Jose

I fixed the issue with the SQL Query and wrote it in this format to create the view.

CREATE OR REPLACE FORCE VIEW APPIANETASC_BLAB_WRITE.SRVY_SURVEYRESPONSESVIEW
(
ID,
SURVEYID,
SURVEYEEUSERNAME,
RESPONSEDATETIME,
ISUPDATED,
ISIGNORED,
STATUS,
QUESTIONID,
QUESTIONTYPE,
QUESTIONLABEL,
ANSWERVALUE,
QUESTIONOPTIONID
)
AS
SELECT CONVERT (
'id',
response."surveyId"
|| ':'
|| question."questionId"
|| ':'
|| ( CASE
WHEN answer."answerid" IS NULL THEN -1
ELSE answer."answerid"
END
|| ':'
|| CASE
WHEN VALUE."answervalueid" IS NULL THEN -2
ELSE VALUE."answervalueid"
END),
'UTF8'),
response."surveyId",
response."surveyeeusername",
response."responsedatetime",
response."isupdated",
response."isignored",
response."status",
question."questionId",
question."type" "questionType",
question."label" "questionLabel",
VALUE."value" "answerValue",
questionoption."questionoptionid"
FROM "SRVY_SurveyResponse" response
LEFT JOIN "SRVY_SurveyAnswer" answer
ON response."responseid" = answer."surveyResponseId"
LEFT JOIN "SRVY_SurveyAnswerValue" VALUE
ON answer."answerid" = VALUE."surveyAnswerId"
JOIN "SRVY_SurveyQuestion" question
ON answer."questionId" = question."questionId"
LEFT JOIN
"SRVY_SurveyQuestionOption" questionoption
ON VALUE."value" = questionoption."value"
AND answer."questionId" = questionoption."surveyQuestionId";

But , now I get an error when I click on Responses View in the Record --> 2018-12-21 19:47:52,470 [ajp-nio-8009-exec-9] ERROR com.appiancorp.rest.record.data.RecordRestService - Unable to retrieve record summary for record w/ type RecordType [id=921, uuid=66256e67-ee8a-412d-b851-9634390896c6, name=Survey, sourceTypeStr={http://www.appian.com/ae/types/2009}DataStoreEntity, sourceUuidStr=d8579ffe-2fa5-421c-940d-ba2eaa0feaa2@_a-0000dad0-86bd-8000-7e03-010000010000_3187] and id 1
com.appiancorp.exceptions.LocalizedAppianRuntimeException$LocalizedAppianException: Expression evaluation error in rule 'srvy_getsurveyresultsforsurveyid' (called by rules 'srvy_uigridshowsurveydata' > 'srvy_uirecordresultsview') at function a!queryEntity_18r3: An error occurred while retrieving the data. (APNX-1-4198-000)
Caused by: com.appiancorp.core.expr.exceptions.ExpressionRuntimeException$AppianExceptionProvider: Expression evaluation error in rule 'srvy_getsurveyresultsforsurveyid' (called by rules 'srvy_uigridshowsurveydata' > 'srvy_uirecordresultsview') at function a!queryEntity_18r3: An error occurred while retrieving the data. (APNX-1-4198-000)
Caused by: com.appiancorp.suiteapi.common.exceptions.AppianRuntimeException: com.appiancorp.suiteapi.common.exceptions.AppianException: An error occurred while retrieving the data. (APNX-1-4261-002)
Caused by: com.appiancorp.suiteapi.common.exceptions.AppianRuntimeException: com.appiancorp.suiteapi.common.exceptions.AppianException: Unexpected error executing query (type: [SurveyResponsesViewDT21324], query: [queryentity expression], order by: [[Sort[surveyeeUsername asc]]], filters:[(surveyId = TypedValue[it=1,v=1])]) (APNX-1-4164-028)
Caused by: org.hibernate.QueryTimeoutException: could not execute query
Caused by: java.sql.SQLException: ORA-01482: unsupported character set

When I look at the error , it seems to be coming from the CONVERT statement in Oracle.

SELECT CONVERT (
'id',
response."surveyId"
|| ':'
|| question."questionId"
|| ':'
|| ( CASE
WHEN answer."answerid" IS NULL THEN -1
ELSE answer."answerid"
END
|| ':'
|| CASE
WHEN VALUE."answervalueid" IS NULL THEN -2
ELSE VALUE."answervalueid"
END),
'UTF8'),

I fixed the issue with the SQL Query and wrote it in this format to create the view.

CREATE OR REPLACE FORCE VIEW APPIANETASC_BLAB_WRITE.SRVY_SURVEYRESPONSESVIEW
(
ID,
SURVEYID,
SURVEYEEUSERNAME,
RESPONSEDATETIME,
ISUPDATED,
ISIGNORED,
STATUS,
QUESTIONID,
QUESTIONTYPE,
QUESTIONLABEL,
ANSWERVALUE,
QUESTIONOPTIONID
)
AS
SELECT CONVERT (
'id',
response."surveyId"
|| ':'
|| question."questionId"
|| ':'
|| ( CASE
WHEN answer."answerid" IS NULL THEN -1
ELSE answer."answerid"
END
|| ':'
|| CASE
WHEN VALUE."answervalueid" IS NULL THEN -2
ELSE VALUE."answervalueid"
END),
'UTF8'),
response."surveyId",
response."surveyeeusername",
response."responsedatetime",
response."isupdated",
response."isignored",
response."status",
question."questionId",
question."type" "questionType",
question."label" "questionLabel",
VALUE."value" "answerValue",
questionoption."questionoptionid"
FROM "SRVY_SurveyResponse" response
LEFT JOIN "SRVY_SurveyAnswer" answer
ON response."responseid" = answer."surveyResponseId"
LEFT JOIN "SRVY_SurveyAnswerValue" VALUE
ON answer."answerid" = VALUE."surveyAnswerId"
JOIN "SRVY_SurveyQuestion" question
ON answer."questionId" = question."questionId"
LEFT JOIN
"SRVY_SurveyQuestionOption" questionoption
ON VALUE."value" = questionoption."value"
AND answer."questionId" = questionoption."surveyQuestionId";

But , now I get an error when I click on Responses View in the Record --> 2018-12-21 19:47:52,470 [ajp-nio-8009-exec-9] ERROR com.appiancorp.rest.record.data.RecordRestService - Unable to retrieve record summary for record w/ type RecordType [id=921, uuid=66256e67-ee8a-412d-b851-9634390896c6, name=Survey, sourceTypeStr={http://www.appian.com/ae/types/2009}DataStoreEntity, sourceUuidStr=d8579ffe-2fa5-421c-940d-ba2eaa0feaa2@_a-0000dad0-86bd-8000-7e03-010000010000_3187] and id 1
com.appiancorp.exceptions.LocalizedAppianRuntimeException$LocalizedAppianException: Expression evaluation error in rule 'srvy_getsurveyresultsforsurveyid' (called by rules 'srvy_uigridshowsurveydata' > 'srvy_uirecordresultsview') at function a!queryEntity_18r3: An error occurred while retrieving the data. (APNX-1-4198-000)
Caused by: com.appiancorp.core.expr.exceptions.ExpressionRuntimeException$AppianExceptionProvider: Expression evaluation error in rule 'srvy_getsurveyresultsforsurveyid' (called by rules 'srvy_uigridshowsurveydata' > 'srvy_uirecordresultsview') at function a!queryEntity_18r3: An error occurred while retrieving the data. (APNX-1-4198-000)
Caused by: com.appiancorp.suiteapi.common.exceptions.AppianRuntimeException: com.appiancorp.suiteapi.common.exceptions.AppianException: An error occurred while retrieving the data. (APNX-1-4261-002)
Caused by: com.appiancorp.suiteapi.common.exceptions.AppianRuntimeException: com.appiancorp.suiteapi.common.exceptions.AppianException: Unexpected error executing query (type: [SurveyResponsesViewDT21324], query: [queryentity expression], order by: [[Sort[surveyeeUsername asc]]], filters:[(surveyId = TypedValue[it=1,v=1])]) (APNX-1-4164-028)
Caused by: org.hibernate.QueryTimeoutException: could not execute query
Caused by: java.sql.SQLException: ORA-01482: unsupported character set

When I look at the error , it seems to be coming from the CONVERT statement in Oracle.

SELECT CONVERT (
'id',
response."surveyId"
|| ':'
|| question."questionId"
|| ':'
|| ( CASE
WHEN answer."answerid" IS NULL THEN -1
ELSE answer."answerid"
END
|| ':'
|| CASE
WHEN VALUE."answervalueid" IS NULL THEN -2
ELSE VALUE."answervalueid"
END),
'UTF8'),