A SOQL from a community portal user could fail with 'Missing Expression ORA-00936'
Last updated 2018-09-08 ·Reference W-2501905 ·Reported By 10 users
A SOQL from a community portal user could fail with Missing Expression ORA-00936 if conditions in the WHERE clause are concatenated with the OR operators and one of the conditions refers to a foreign key table which has MASTER-DETAIL relationship fields.
1. Create Custom Objects
1-1. Create a custom object, say "MyObject".
1-2. Create a Master-Detail Relationship data type field on MyObject. Select "Contact" as the related object. And set "Contact" to Field Name.
1-3. Create another custom object, say "MyObject2".
1-4. Create a Lookup Relationship date type field on MyObject2. Select "MyObject" as the related object. And set "MyObject" to Field Name.
1-5. Create another Lookup Relationship date type field on MyObject2. Select "Contact" as the related object. And set "Contact" to Field Name.
2. Create a community and activate it.
3. Create a community portal user
3-1. Create a custom profile using "Customer Community". And add Read & Create & Edit & Delete permissions to custom objects "MyObject" and "MyObject2".
3-2. Create an Account.
3-3. Create a Contact on the Account created the above. Enable Customer User and set the profile created at 3-1.
4. Set View Field Accessibility for the custom fields created at 1-4 and 1-5. Set Editable to the profile created at 3-1.
5. Edit Sharing Settings as follows.
<Object> : <Default Internal Access> : <Default External Access>
Account and Contract : Private : Private
Contact : Controlled by Parent : Controlled by Parent
MyObject : Controlled by Parent : Controlled by Parent
MyObject2 : Private : Private
6. Go to workbench and click on Settings on the top. Look for Portal Organization Id field and populate it with your org id and Click on apply settings button. Then, login to workbench.
7. Run the following SOQL:
select id from MyObject2__c where Contact__c in ('003XXXXXXXXXXXX') or MyObject__r.Contact__c in ('003XXXXXXXXXXXX')
And you will see the error, as follows:
UNKNOWN ERROR: UNKNOWN_EXCEPTION: An unexpected error occurred. Please include this ErrorId if you contact support: 2063350125-430 (1975509607)
There is no workaround at this time.
Is it Fixed?
Any unreleased services, features, statuses, or dates referenced in this or other public statements are not currently available and may not be delivered on time or at all. Customers who purchase our services should make their purchase decisions based upon features that are currently available.