3

I am getting the following error when calling an Apex controller accessor from a managed package.

System.UnexpectedException: common.exception.SfdcSqlException: ORA-00907: missing right parenthesis

Here is a simplified version of the Apex controller

public with sharing virtual class MyController {

    public String getAccountId() {
        // It fails on this query
        List<Contact> contacts = [SELECT AccountID FROM Contact WHERE Id IN (SELECT ContactId FROM User WHERE Id = :UserInfo.getUserId())];

        if (contacts.size() > 0) {
            return contacts.get(0).accountId;
        } else {
            return null;
        }
    }
}

It happens only when:

  • user has a profile with limited privileges

  • the controller is in a managed package

Some of the things I tried so far

  • reproduced the issue with many users having the same profile.

  • it works fine for admin users

  • the same SOQL query works fine outside of the managed package. It was tested with a custom VisualForce page and Apex controller (with limited user).

  • executing UserInfo.getUserId() from the custom controller works fine and returns a valid user ID.

  • executing the sub-query alone from the custom controller works fine. It returns a single user where contactId is null.

  • this page mentions many special cases involving portal users. At this moment, I don't think it is related because I would expect the query to fail in the custom code as well.

Do you have any idea what could cause the ORA-00907: missing right parenthesis error?

Thanks for your help

4
  • 1
    This is a generic syntax error from an Oracle database deep in the guts of SF infrastructure. Apex code isn't supposed to see this type of error. It might be a temporary issue or a genuine bug in SF platform. You should open a support case and let them know.
    – identigral
    Jul 16 at 21:08
  • 1
    Have you tried replacing the function call in the binding with a simple binding variable initialized from that function call?
    – Phil W
    Jul 17 at 6:15
  • @PhilW I tried that, but using custom code, not the managed package. In Salesforce documentation, I found many occurrences of similar SOQL queries that use UserInfo.getUserId() the same way, so expressions seem to be well supported. Jul 19 at 12:42
  • @identigral Thanks for the advice, the error message sound pretty internal indeed. I'll file a case with Salesforce. Jul 19 at 13:18
1

This is my guess.

This field is only accessible when Digital Experiences (previously known as Communities) are enabled:

SELECT ContactId FROM User

So I suggest making the queries and code as simple and defensive as possible to work around this problem:

public Id getAccountId() {
    Id contactId = queryContactId();
    if (contactId != null) {
        for (Contact c : [
                SELECT AccountID
                FROM Contact
                WHERE ID = :contactId
                ]) {
            return c.AccountId;
        }
    }
}

// Defensively find the ContactId for the Digital Experience User
private Id queryContactId() {
    SObjectType field = Contact.SObjectType.getDescribe().fields.getMap().get('ContactId');
    if (field != null) {
        if (field.getDescribe().isAccessible()) {
            Id userId = UserInfo.getUserId();
            for (User u : [
                    SELECT ContactId
                    FROM User
                    WHERE Id = :userId
                    ]) {
                return u.ContactId;
            }
        }
    }
    return null;
}
3
  • I'll double check the Digital Experiences and see if the contactId is really present or not. I'm still wondering why the SOQL query executes fine from custom code, and not from the package though. Jul 19 at 12:52
  • Hi @LucBergeron, Running in the same org and under the same user? Profiles/licenses have a big impact.
    – Keith C
    Jul 19 at 12:59
  • I confirm, the tests are performed with the same org and user. Jul 19 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.