Trevor found an unusual bug. Every customer had a GUID, but for some reason, their JSON API failed if there were ever more that 75 results.

He checked the web layer, only to find that it didn’t actually build the JSON- it just returned a string. The string itself came from their Oracle database. That’s where this procedure came from: 

create or replace PROCEDURE GET_CUSTOMERS
(
  InCustomerGuid IN VARCHAR2,
  OutCustomerGuidArray OUT VARCHAR2,
  OutResult OUT NUMBER,
  OutResultMessage OUT VARCHAR2
) AS
  vCustomerGuidArray VARCHAR2(4000);
  vCustomerGuid VARCHAR2(40);
BEGIN
  OutResult := 0;
  OutResultMessage := NULL;

  vCustomerGuid := UPPER(RTRIM(LTRIM(InCustomerGuid, '{'), '}'));
  
  IF( InCustomerGuid IS NOT NULL )
  THEN
    SELECT '{"keys":[{"key":"' || Customer_Guid || '","type":"CUSTOMER"}]}'
    INTO vCustomerGuidArray
    FROM Customers
    WHERE Customer_Guid = vCustomerGuid;      
  ELSE
    vCustomerGuidArray := '{"keys":[';

    FOR Customer_Item IN (SELECT Customer_Guid FROM Customers) LOOP
      vCustomerGuidArray := vCustomerGuidArray || '{"key":"' || Customer_Item.Customer_Guid || '","type":"CUSTOMER"},';
    END LOOP;

    vCustomerGuidArray := RTRIM(vCustomerGuidArray, ',');
    vCustomerGuidArray := vCustomerGuidArray || ']}';
  END IF;
  
  OutCustomerGuidArray := vCustomerGuidArray;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      OutCustomerGuidArray := NULL;
      
    WHEN OTHERS THEN
    OutResult := 20000;
    OutResultMessage := 'too many customer guids to send back to caller';
    RETURN;

  COMMIT;
  
END GET_CUSTOMERS;



Like the Nintendo game, somebody’s missing the point of JSON

Pick your WTF: generating JSON in the database, generating JSON by string concatenation , not being more careful about sizing a VARCHAR2 variable, or using Oracle.













[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!