2

Facing these errors when processing errors from a web to lead form:

Error 1: Reason: common.exception.SfdcSqlException: ORA-20414: ORA-06512: at "DOPEY.MCAMPAIGNMEMBER", line 303 ORA-06512: at line 1

{call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

{call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} Lead Capture Page: Not available.

Error 2: Reason: common.exception.SfdcSqlException: ORA-20414: ORA-06512: at "SNEEZY.MCAMPAIGNMEMBER", line 303 ORA-06512: at line 1

{call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

{call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} Lead Capture Page: Not available.

Rising from a after insert trigger where nothing is on the line mentioned.

My Code:

trigger PELEstimatorWebLead on Lead (After insert) {    

Map<String,lead>   mapleads = new Map<String,lead>();
Map<String,lead>   mapemail = new Map<String,lead>();
List<note> notes = new List<note>();
List<CampaignMember> CampaignMemberList = new List<CampaignMember>();
List<Task> tasks = new List<Task>();
List<Opportunity> opplist = new List<Opportunity>();
List<ID> newLeadIDS = new List<ID>();
boolean paccountflag =true;
boolean paccountSSOflag =true;
boolean leadSSOflag =true;
List<Lead> leadlist = new List<Lead>();
List<Lead> leadlistemail = new List<Lead>();
Set<ID>  PELcampLeadids = new set<ID>();
 Database.LeadConvert lc = new database.LeadConvert();
Household__c newhh=new Household__c();
Set<ID> foundLeads = new Set<ID>();
List<CampaignMember> PELcampMembers = new List<CampaignMember>();


List<Campaign> PELcamp = new List<Campaign>();
    PELcamp=[select name,id from campaign where name = 'PEL Rate Estimator 2014'];
  PELcampMembers=[SELECT id,ContactId,LeadId FROM campaignMember WHERE CampaignId =:PELcamp[0].id];

    for(campaignMember cm:PELcampMembers){

     PELcampLeadids.add(cm.LeadId);   
    }


//filter all the PEL  Estimator webleads into a list
    for(Lead l:Trigger.new)
    {           
        if(l.LeadSource == 'PEL Estimator')
        {
            newLeadIDS.add(l.id);
      if(!PELcampLeadids.contains(l.id)){
            CampaignMember mem =new CampaignMember(
            LeadId=l.id,
            CampaignId = PELcamp[0].id
            );
            CampaignMemberList.add(mem); 
      }
         if(l.SSO__c !=null)
         {
             mapleads.put(l.SSO__c, l);
         }           
         if(l.email !=null)
         {
               mapemail.put(l.Email, l);
         }
      }
    }

    upsert CampaignMemberList;
    System.debug(paccountflag);        
   //check for person accounts with matching SSO(first preference in matching to SSO) 
   List<Account> paccount=[select id,personEmail,Alternate_Email__pc,Email_opt_in__c,PersonHasOptedOutOfEmail,Opt_in_Source__c,SSO__pc,
                               (select Opportunity.id,Opportunity.OwnerID,Opportunity.StageName from Account.Opportunities WHERE Type_Dependent__c ='PEL' AND LeadSource ='PEL Estimator' LIMIT 1)   
                                 from Account 
                                  where recordtype.name='Person Account' 
                                  and SSO__pc IN:mapleads.keySet()Limit 1];

   //check for person accounts without matching SSO and matching Email  
   List<Account> paccountemail=[select id,personEmail,Alternate_Email__pc,Email_opt_in__c,PersonHasOptedOutOfEmail,Opt_in_Source__c,SSO__pc, 
                                  (select Opportunity.id,Opportunity.OwnerID,Opportunity.StageName from Account.Opportunities WHERE Type_Dependent__c ='PEL' AND LeadSource ='PEL Estimator'LIMIT 1)
                                  from Account 
                                  where recordtype.name='Person Account' 
                                  and SSO__pc NOT IN:mapleads.keySet() AND (personEmail IN:mapemail.keySet() OR Alternate_Email__pc IN:mapemail.keySet())Limit 1]; 

    //businnes from when a corresponding person account with matching sso is found
    for(Account a:paccount){
       paccountflag = false;
       paccountSSOflag = false; 
        Lead l = mapleads.get(a.SSO__pc);
        foundLeads.add(l.id);      
        if(a.PersonEmail == null){
            a.PersonEmail = l.Email;
        } else {
            a.Alternate_Email__pc = l.Email;
          }

        if(!a.PersonHasOptedOutOfEmail){
            a.Email_opt_in__c = true;
            a.Opt_in_Source__c = 'PEL Estimator';

        }
        if(a.Opportunities.size()> 0){ 


                   if(a.Opportunities.get(0).StageName =='Prospecting'){
                       a.Opportunities.get(0).StageName = 'Expressed interest';
              }
                      a.Opportunities.get(0).type = 'Umbrella';    
                          upsert a.Opportunities.get(0);
                    lc.setLeadId(l.id);
                    lc.setAccountId(a.ID);

                    lc.setDoNotCreateOpportunity(true); 
                    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
                    lc.setConvertedStatus(convertStatus.MasterLabel);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);                
                    System.assert(lcr.isSuccess()); 

             Note note=new Note(
                parentId=a.Opportunities.get(0).id,
                Title='PEL Estimator',
                Body= 'SSO:'+l.SSO__c + 'Email:'+l.Email +'State:'+l.State__c +'Number of Vechicles:'+l.Number_of_vehicles__c +'Number of Residences'+ l.Number_of_residences__c +'Accidents or violations'+l.violations_or_accidents__c+'Number of drivers:'+l.Number_of_drivers__c +'Premium Amounts:'+l.Premium_Amounts__c
                );       
                insert note;

            Task task = new Task(ActivityDate = System.Today(),OwnerId = a.Opportunities.get(0).OwnerID,Subject ='PEL Estimator, Notes = ‘Customer completed PEL estimator. Please follow up ' );
               tasks.add(task);

        }else{
                    lc.setLeadId(l.id);
                    lc.setAccountId(a.ID); 
                    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
                    lc.setConvertedStatus(convertStatus.MasterLabel);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);                
                    System.assert(lcr.isSuccess());
                     Opportunity Opportunitie = new Opportunity();
                     Opportunitie.StageName = 'Expressed interest';
                     Opportunitie.type = 'Umbrella'; 
                     Opportunitie.ID   = lcr.getOpportunityId();
                          upsert Opportunitie;


               Note note=new Note(
                parentId=lcr.getOpportunityId(),
                Title='PEL Estimator',
                Body= 'SSO:'+l.SSO__c + 'Email:'+l.Email +'State:'+l.State__c +'Number of Vechicles:'+l.Number_of_vehicles__c +'Number of Residences'+ l.Number_of_residences__c +'Accidents or violations'+l.violations_or_accidents__c+'Number of drivers:'+l.Number_of_drivers__c +'Premium Amounts:'+l.Premium_Amounts__c
                );

            insert note;

        }   

    } 

    //business process when person account with matching email(SSO not matching) is found
    if(paccountSSOflag){
    for(Account a:paccountemail){
        paccountflag = false;

        Lead l = mapemail.get(a.PersonEmail);
        foundLeads.add(l.Id);
        if(l == null){
            l = mapemail.get(a.Alternate_Email__pc);

        }



        if(a.PersonEmail == null){
            a.PersonEmail = l.Email;
        } else {
            a.Alternate_Email__pc = l.Email;
          }

        if(!a.PersonHasOptedOutOfEmail){
            a.Email_opt_in__c = true;
            a.Opt_in_Source__c = 'PEL Estimator';

        }
        if(a.Opportunities.size()>0 ){
                lc.setLeadId(l.id);
                    lc.setAccountId(a.ID);
                    lc.setDoNotCreateOpportunity(true); 
                    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
                    lc.setConvertedStatus(convertStatus.MasterLabel);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);                
                    System.assert(lcr.isSuccess()); 

             if(a.Opportunities.get(0).StageName =='Prospecting' ){
                       a.Opportunities.get(0).StageName = 'Expressed interest';
               }

                      a.Opportunities.get(0).type = 'Umbrella';    
                          upsert a.Opportunities.get(0);

               Note note=new Note(
                parentId=a.Opportunities.get(0).id,
                Title='PEL Estimator',
                Body= 'SSO:'+l.SSO__c + 'Email:'+l.Email +'State:'+l.State__c +'Number of Vechicles:'+l.Number_of_vehicles__c +'Number of Residences'+ l.Number_of_residences__c +'Accidents or violations'+l.violations_or_accidents__c+'Number of drivers:'+l.Number_of_drivers__c +'Premium Amounts:'+l.Premium_Amounts__c
                );       
              insert note;

            Task task = new Task(ActivityDate = System.Today(),OwnerId = a.Opportunities.get(0).OwnerID,Subject ='PEL Estimator, Notes = ‘Customer completed PEL estimator. Please follow up ' );
               tasks.add(task);



        }else{
            lc.setLeadId(l.id);
                    lc.setAccountId(a.ID); 
                    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
                    lc.setConvertedStatus(convertStatus.MasterLabel);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);                
                    System.assert(lcr.isSuccess());
                       Opportunity Opportunitie = new Opportunity();
                     Opportunitie.StageName = 'Expressed interest';
                     Opportunitie.type = 'Umbrella'; 
                     Opportunitie.ID   = lcr.getOpportunityId();
                          upsert Opportunitie;
               Note note=new Note(
                parentId=lcr.getOpportunityId(),
                Title='PEL Estimator',
                Body= 'SSO:'+l.SSO__c + 'Email:'+l.Email +'State:'+l.State__c +'Number of Vechicles:'+l.Number_of_vehicles__c +'Number of Residences'+ l.Number_of_residences__c +'Accidents or violations'+l.violations_or_accidents__c+'Number of drivers:'+l.Number_of_drivers__c +'Premium Amounts:'+l.Premium_Amounts__c
                );
            insert note;
        }   
    } 
    }  
    if(paccountflag){
    //Starting businees process when no person account is found
     leadlist=[select id,Street,City,State,Country,PostalCode,Email,Alternate_Email__c,SSO__c,FirstName,LastName
                                  from Lead 
                                  Where SSO__c IN:mapleads.keySet() AND isconverted = False AND ID NOT IN:newLeadIDS Limit 1 ];


      leadlistemail=[select id,Street,City,State,Country,PostalCode,Email,Alternate_Email__c,SSO__c,FirstName,LastName
                                  from Lead 
                                  Where SSO__c NOT IN:mapleads.keySet() AND Email IN:mapemail.keySet() AND isconverted = False AND ID NOT IN:newLeadIDS Limit 1]; 

    for(integer i=0;i<leadlist.size();i++){
       Lead l=leadlist.get(i);
       leadSSOflag = false;
       Lead newlead = mapleads.get(l.SSO__c);

         if(newlead !=null){
         if(l.Email == null){
            l.Email = newlead.Email;
        } else {
           l.Alternate_Email__c = newlead.Email;
          }
        l.Stage__c = 'expressed interest';

        Merge l newlead;
             foundLeads.add(newlead.Id);
        if(l.City != null && l.PostalCode != null && l.Country != null && l.State != null && l.Street != null ){

                     newhh.Name=l.lastname+l.ODS_Person_PK__c;
                     newhh.Mailing_City__c=l.city;
                     newhh.Mailing_State__c=l.state;
                     newhh.Mailing_Street__c=l.street;
                     newhh.Mailing_ZIP__c=l.postalcode;
                     insert newhh;

                    Account peraccount=new Account();

                    peraccount.Email_opt_in__c = true;
                    peraccount.Opt_in_date__c = System.today();
                    peraccount.Opt_in_Source__c = 'PEL Estimator';    
                    peraccount.FirstName=l.firstname;
                    peraccount.LastName=l.LastName;
                    peraccount.PrimaryHouseholdId__c=newhh.id;
                    insert peraccount;

                    lc.setLeadId(l.id);
                    lc.setAccountId(peraccount.ID);
                    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
                    lc.setConvertedStatus(convertStatus.MasterLabel);
                    Database.LeadConvertResult lcr = Database.convertLead(lc);                
                    System.assert(lcr.isSuccess());
                       leadlist.remove(i);
                      Opportunity Opportunitie = new Opportunity();
                     Opportunitie.StageName = 'Expressed interest';
                     Opportunitie.type = 'Umbrella'; 
                     Opportunitie.ID   = lcr.getOpportunityId();
                          upsert Opportunitie;
            Note note=new Note(
                parentId=lcr.getOpportunityId(),
                Title='PEL Estimator',
                Body= 'SSO:'+newlead.SSO__c + 'Email:'+newlead.Email +'State:'+newlead.State__c +'Number of Vechicles:'+newlead.Number_of_vehicles__c +'Number of Residences'+ newlead.Number_of_residences__c +'Accidents or violations'+newlead.violations_or_accidents__c+'Number of drivers:'+newlead.Number_of_drivers__c +'Premium Amounts:'+newlead.Premium_Amounts__c
                );
            insert note;               


        }
           Note note=new Note(
                parentId=l.id,
                Title='PEL Estimator',
                Body= 'SSO:'+newlead.SSO__c + 'Email:'+newlead.Email +'State:'+newlead.State__c +'Number of Vechicles:'+newlead.Number_of_vehicles__c +'Number of Residences'+ newlead.Number_of_residences__c +'Accidents or violations'+newlead.violations_or_accidents__c+'Number of drivers:'+newlead.Number_of_drivers__c +'Premium Amounts:'+newlead.Premium_Amounts__c
                );
           insert note; 

     }
      } 






        //business process when lead with matching Email is found
        if(leadSSOflag){
            if(leadlistemail.size()>0){
            for(integer i=0;i<leadlistemail.size();i++){
             Lead l=leadlistemail.get(i);
             Lead newlead = mapemail.get(l.Email);
             if(newlead == null){
            l = mapemail.get(l.Alternate_Email__c);

        }
         if(newlead != null){
         if(l.Email == null){
            l.Email = newlead.Email;
        } else {
            l.Alternate_Email__c = newlead.Email;
          } 
           l.Stage__c = 'expressed interest';

             merge  l newlead;  
             foundLeads.add(newlead.Id);
        if(l.City != null && l.PostalCode != null && l.Country != null && l.State != null && l.Street != null ){

                    newhh.Name=l.lastname+l.ODS_Person_PK__c;
                     newhh.Mailing_City__c=l.city;
                     newhh.Mailing_State__c=l.state;
                     newhh.Mailing_Street__c=l.street;
                     newhh.Mailing_ZIP__c=l.postalcode;
                     insert newhh;
                    System.debug(l.firstname);
                    Account peraccount=new Account();
                    peraccount.FirstName=l.firstname;
                    peraccount.LastName=l.LastName;
                    peraccount.PrimaryHouseholdId__c=newhh.id;
                    insert peraccount;

                    lc.setLeadId(l.id);
                    lc.setAccountId(peraccount.ID);
                    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
                    lc.setConvertedStatus(convertStatus.MasterLabel);
                   Database.LeadConvertResult lcr = Database.convertLead(lc);                
                   System.assert(lcr.isSuccess());
                        Opportunity Opportunitie = new Opportunity();
                     Opportunitie.StageName = 'Expressed interest';
                     Opportunitie.type = 'Umbrella'; 
                     Opportunitie.ID   = lcr.getOpportunityId();
                          upsert Opportunitie;          

            Note note=new Note(
                parentId=lcr.getOpportunityId(),
                Title='PEL Estimator',
                Body= 'SSO:'+newlead.SSO__c + 'Email:'+newlead.Email +'State:'+newlead.State__c +'Number of Vechicles:'+newlead.Number_of_vehicles__c +'Number of Residences'+ newlead.Number_of_residences__c +'Accidents or violations'+newlead.violations_or_accidents__c+'Number of drivers:'+newlead.Number_of_drivers__c +'Premium Amounts:'+newlead.Premium_Amounts__c
                );

            insert note;
       }

        }   


   }
            } 
           }

    }
           for(Lead newlead:Trigger.new)
           {
               if(!foundLeads.contains(newlead.Id)){
                   if(newlead.LeadSource == 'PEL Estimator'){
                  Note note=new Note(
                parentId=newlead.Id,
                Title='PEL Estimator',
                Body= 'SSO:'+newlead.SSO__c + 'Email:'+newlead.Email +'State:'+newlead.State__c +'Number of Vechicles:'+newlead.Number_of_vehicles__c +'Number of Residences'+ newlead.Number_of_residences__c +'Accidents or violations'+newlead.violations_or_accidents__c+'Number of drivers:'+newlead.Number_of_drivers__c +'Premium Amounts:'+newlead.Premium_Amounts__c
                );

            insert note; 
                   }
               }
           }           

   upsert tasks;
   update  paccount;
   update paccountemail;
   upsert notes;

}

  • Does this happen all the time? or is it intermediate issue? – AtulRajguru9 May 29 '14 at 10:31
  • can you share your code ?Also share your external Ids on objects in which trigger is targetted – Mohith Shrivastava May 29 '14 at 10:35
  • 3
    These are errors generated by the underlying Oracle database that you should normally never see. For example ORA-06512: "Cause: Backtrace message as the stack is unwound by unhandled exceptions. Action: Fix the problem causing the exception or write an exception handler for this condition. Or you may need to contact your application administrator or DBA.". The "application" in this case is Salesforce itself so one thing you should do is to create a case with Salesforce. – Keith C May 29 '14 at 10:37
4

Any time you see an Oracle error (with one of the seven dwarfs mentioned no less!), you've run into a bug with Salesforce and should absolutely log a case with a support rep as soon as possible.

This is not normal behavior, and it's possible it could be a widespread issue. Even if there is a workaround that you find or a community member finds, Salesforce should be alerted of the issue so that it can be addressed.

| improve this answer | |
0

As Ray suggested its always a good idea to log a case with salesforce,but keeping our delivery dates in mind we have to check the feasibility on how we can solve this.

The response from salesforce is

*

''Thanks for contacting Salesforce Support. My name is xxxxx and I have taken ownership of your case. I understand that You were getting Oracle errors when passing leads from a web form to SFDC. Please be informed we don't have enough resources to analyze the oracle error message that you have provided, our usual standard t/s regarding web-to-lead is to make sure the normal or default web-to-lead template is working without problems. By the way what is the name of the oracle program that is connected to your Sales-Force Org regarding this web-to-lead process?''

*

I have contacted salesforce support team a lot of times by now and I am glad to say almost every-time it was a happy ending. But what we really need to understand in this scenario is, there are people working on the other end who needs to understand your actual business process and code. Obviously this takes time and we are not expected to miss our deliveries because of this.

As this is not the ideal behavior its always recommended to log a case with salesforce in addition to trying to fix our code.

Let me get into our scenario,the three errors we were getting are:

Error 1: Reason: common.exception.SfdcSqlException: ORA-20414: ORA-06512: at "DOPEY.MCAMPAIGNMEMBER", line 303 ORA-06512: at line 1 {call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} {call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} Lead Capture Page: Not available.
Error 2: Reason: common.exception.SfdcSqlException: ORA-20414: ORA-06512: at "SNEEZY.MCAMPAIGNMEMBER", line 303 ORA-06512: at line 1 {call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} {call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} Lead Capture Page: Not available.
Error 3: Reason: common.exception.SfdcSqlException: ORA-20414: ORA-06512: at "BASHFUL.MCAMPAIGNMEMBER", line 303 ORA-06512: at line 1 {call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} {call mCampaignMember.insert_campaignmembers(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)} Lead Capture Page: Not available.

As said by Ray Dehlerthere are 7 types of these errors.There might be several reasons for these errors,but the primary one i have noticed is adding duplicate members to campaign.

In our case campaign id was mentioned as a hidden field in webform and we were adding the leads in a trigger also. Duplication can rise from not properly handling the same in the triggers also.(For ex:not applying proper filters in SOQL queries etc...).

| improve this answer | |
  • Hi @Sravan Alaparthi, We are facing similar kind of problem in our org, can you please share some more details on 'what do you mean by Duplicate Records in Campaign Member' i.e. on what criteria you are identifying records as duplicates - what all fields will be part of this duplicate process ? – Chirag Verma Mar 8 '17 at 12:25

Your Answer

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