function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Rob GoldiezRob Goldiez 

ORA-00904 Platform ERROR

I'm getting the platform error described here when a trigger I have is being executed by a community member.  
https://success.salesforce.com/issues_view?id=a1p30000000E19wAAC

Apex error is below.  This trigger works fine from the salesforce web interface.  My understanding from the issue referenced at the above link is that ORA-00904 is a platform issue.

---------------------
Sandbox

Apex script unhandled trigger exception by user/organization: 00561000000Kj0n/00D4B000000CpdH
Source organization: 00D61000000agdX (null)
notifyChatterComments: execution of AfterInsert

caused by: System.UnexpectedException: common.exception.SfdcSqlException: ORA-00904: "T"."COMMUNITY_NICKNAME": invalid identifier


select /*FkJoin*/ "Id",
"Name",
"CommunityNickname"
from (select /*+ ordered use_hash(t) */
ids.num "ids_num",
t.entity_id "Id",
case when substr(t.entity_id,1,3) in ('003','00Q', '005') then trim(t.first_name || ' ' || t.last_name) when substr(t.entity_id,1,3) in ('01p','01q') then t.developer_name else nvl(t.name,trim(t.first_name || ' ' || t.last_name)) end "Name",
t.community_nickname "CommunityNickname"
from (select * from table(cast(? as id_num_array))) ids,
     (select /*+ ordered use_nl(nd) index(nd pkname_denorm) */
     nd.entity_id ids_num,
     nd.organization_id organization_id,
     nd.entity_id entity_id,
     nd.record_type_id,
     nd.name AS name,
          nd.first_name first_name,
     nd.last_name last_name,
     nd.middle_name middle_name,
     nd.preferred_address preferred_address,
     nd.suffix suffix,
     nd.deleted deleted,
     nd.active active,
     nd.archived archived,
     nullif(substr(nd.entity_id,1,3), '000'),
     nd.alias alias,
     nd.email email,
     nd.title title,
     nd.phone phone,
     nd.user_role_id user_role_id,
     nd.profile_id profile_id,
     nd.namespace_prefix namespace_prefix,
     nd.developer_name developer_name,
     nd.table_enum_or_id table_enum_or_id,
     nd.subtype subtype
     from (select * from table(cast(? AS ID_NUM_ARRAY))) tmp,
          core.name_denorm nd
     where (tmp.id = nd.entity_id)
     and (nd.organization_id = '00D4B000000CpdH')
     and (nd.entity_id != '000000000000000')
     and (nd.deleted = '0')
     UNION ALL
     (select /*+ ordered use_nl(t) index(t pkdashboard_component)) */
     t.dashboard_component_id ids_num,
     t.organization_id organization_id,
     t.dashboard_component_id entity_id,
     '' record_type_id,
     NVL(DECODE(t.component_type, 10, t.metric, t.title), 'Untitled Component') AS name,
     '' first_name,
     '' last_name,
     '' middle_name,
     '' preferred_address,
     '' suffix,
     '0' deleted,
     '1' active,
     '0' archived,
     nullif(substr(t.dashboard_component_id,1,3), '000'),
     '' alias,
     '' email,
     '' title,
     '' phone,
     '' user_role_id,
     '' profile_id,
     '' namespace_prefix,
     '' developer_name,
     '' table_enum_or_id,
     '' subtype
     from
(select * from table(cast(? AS ID_NUM_ARRAY))) tmp,
          core.dashboard_component t
     where (tmp.id = t.dashboard_component_id)
     and (substr(tmp.id,1,3) = '01a')
     and (t.organization_id = '00D4B000000CpdH')
)     UNION ALL
     (select /*+ ordered use_nl(t) index(t pkactivity)) */
     t.activity_id ids_num,
     t.organization_id organization_id,
     t.activity_id entity_id,
     '' record_type_id,
     t.subject AS name,
     '' first_name,
     '' last_name,
     '' middle_name,
     '' preferred_address,
     '' suffix,
     t.deleted deleted,
     '1' active,
     '0' archived,
     nullif(substr(t.activity_id,1,3), '000'),
     '' alias,
     '' email,
     '' title,
     '' phone,
     '' user_role_id,
     '' profile_id,
     '' namespace_prefix,
     '' developer_name,
     '' table_enum_or_id,
     '' subtype
     from
(select * from table(cast(? AS ID_NUM_ARRAY))) tmp,
          core.activity t
     where (tmp.id = t.activity_id)
     and (substr(tmp.id,1,3) = '00T')
     and (t.organization_id = '00D4B000000CpdH')
     and (t.deleted = '0'))     UNION ALL
     (select /*+ ordered use_nl(t) index(t pkactivity)) */
     t.activity_id ids_num,
     t.organization_id organization_id,
     t.activity_id entity_id,
     '' record_type_id,
     t.subject AS name,
     '' first_name,
     '' last_name,
     '' middle_name,
     '' preferred_address,
     '' suffix,
     t.deleted deleted,
     '1' active,
     '0' archived,
     nullif(substr(t.activity_id,1,3), '000'),
     '' alias,
     '' email,
     '' title,
     '' phone,
     '' user_role_id,
     '' profile_id,
     '' namespace_prefix,
     '' developer_name,
     '' table_enum_or_id,
     '' subtype
     from
(select * from table(cast(? AS ID_NUM_ARRAY))) tmp,
          core.activity t
     where (tmp.id = t.activity_id)
     and (substr(tmp.id,1,3) = '00U')
     and (t.organization_id = '00D4B000000CpdH')
     and (t.deleted = '0'))) t
where (ids.id = t.entity_id)
and (t.organization_id = '00D4B000000CpdH')
and (t.deleted = '0'))
order by "ids_num" asc nulls first

Trigger.notifyChatterComments: line 20, column 1
 
Martin LittleMartin Little
Hi Rob,

I don't have an answer for you, I'm afraid, but can confirm that a very similar problem has arisen for us in the past few days (possibly in the wake of Spring 16 release?) Here's the main points:
  • We have a packaged Visualforce page that has been in use at a large number of customers and has been working reliably as a core part of our product for some time. Our product (and with it this page) has long been accessed both by internal and Community users.
  • This still works when accessed via the classic SF interface, and can be accessed by system admins without issue via a Customer Community.
  • The same page at the same Customer Community address now fails when accessed by Community type users, with the same kind of error you are seeing, as follows:
Error occurred while loading a Visualforce page. : common.exception.SfdcSqlException: ORA-00904: "SHR"."FOLDER_ID": invalid identifier [message continues to list SOQL that is not obviously related to anything we run directly].

Tried to raise a case on this but we've been told it's a "developer support" matter, despite it appearing to us to be more of a platform issue, as per the Success Community "Known Issue" to which you refer. Indeed, the "ORA" code is direct from the underlying Oracle databases, we believe:

https://docs.oracle.com/cd/B10501_01/server.920/a96525/e900.htm

Perhaps Salesforce Support might be able to look at this again?

Best of luck, Rob.
Martin.

 
Rob GoldiezRob Goldiez
Martin, My issue seemed to have resolved itself after a few days. I never heard anything from salesforce. Good luck, Rob
Martin LittleMartin Little
Ah well... glad the issue went away for you - thanks Rob
Martin LittleMartin Little
Hi Rob,

Just thought I'd add a further comment here as we eventually fixed the problem we faced (without SFDC input). This page of advice concerning what SF Community license types are allowed to access was a starting point:

https://help.salesforce.com/apex/HTViewHelpDoc?id=users_license_types_communities.htm&language=en_US

We noticed that "Customer Community" users do not get Reports & Dashboards. We had a single SOQL query on the Reports table and, despite being in an error trap, this caused the problem: any attempt to access objects not allowed by the license essentially fires an un-trappable error. We tweaked our code to ensure that only suitable user types attempt the query.

Shame the error message and trapping approach aren't a little more helpful. Perhaps this will be of use to others who may be encountering similar ORA-* errors.

Thanks again for your initial posting and replies.
Best regards,
Martin
Rob GoldiezRob Goldiez
Martin,
Thanks for the follow up.  I was having the issue on a trigger that indeed needed to determine the sObject based on Id of the record.  What you're saying makes sense now because my solution involved moving this part of the trigger into a seperate class that executes in the 'system' context (not user context) for a different reason, but your insight ties it all together now.

The inability to trap is definitely an issue.  Better documentation on salesforce's part would help too... I appreciate your insight above.  Hopefully someone else stumbles upon it that needs it!

Regards,
Rob