Banner Unit Test Framework

25 Feb 2019 🔖 sql plsql banner linux grep
💬 EN

It’s ironic that my old blog was called “Oracle to Salesforce” and chronicled the headaches of learning the Salesforce ropes as someone trained on an Oracle database. I’ve come full circle and am about to declare that I miss part of Salesforce while working with Oracle!

I miss knowing exactly what I’m doing when it comes to unit testing.

I never thought I’d prefer something about coding in Apex compared to SQL & PL/SQL (the latter of which I never got very deep into before I ventured off to Salesforce-land), but now that I’ve been to Salesforce and back …

…I miss having a testing environment that starts with a 100% truncated database!

(I also miss storing procedures, but them’s the breaks.)

Okay, so … I work at a Banner school.

Stored procedures have had a history of causing compilation errors when we upgrade Banner, so we often do small jobs as anonymous PL/SQL blocks. That way, suddenly-incompatible scripts fail at runtime, not while everybody’s working on a weekend to do an upgrade.

Just in case you’re wondering why I’m not writing more code as stored procedures.

Anyway, here’s what I’ve done so far towards creating easy-to-clean-up “unit test” data: so far, I’ve just managed to throw some records into SPRIDEN as if to imitate a human creating a new record in the SPAIDEN data entry screen.

The goal is to write a master script where I can quickly “set up a few people in Banner,” “set up a few people in Banner’s staging tables,” “match a few people between staging tables and main-Banner,” “set up some people in custom staging tables we created,” and … CLEAN IT ALL UP when I’m done, so I can run the same test 3 minutes later w/o having to type in fresh test data.

It’s slow going, as evidenced by the fact that I’ve only gotten through “populating” one table.

It is, however, a good project for learning PL/SQL, thinking about PL/SQL anonymous block best practices for modularity / maintainability / reusability, etc.

set serveroutput on size 1000000;
declare

    v_loop_counter      pls_integer := 1;
    v_curr_idxkey       spriden.spriden_origin%TYPE;   -- Let's keep these short and sweet so they're easy to type.  Please keep them unique per "test person."
    v_curr_spriden      spriden%ROWTYPE;
    type jelstn_inhab_type is record (
        idxkey          v_curr_idxkey%TYPE,
        pidm            spriden.spriden_pidm%TYPE,
        bannerid        spriden.spriden_id%TYPE,
        spridrowid      ROWID,
        lname           varchar2(52),  -- Presumes spriden.spriden_last_name%TYPE remains varchar(60).  Leaves room for an 8-digit sysdate stamp as part of name.
        fname           varchar2(52)  -- Presumes spriden.spriden_first_name%TYPE remains varchar(60).  Leaves room for an 8-digit sysdate stamp as part of name.
    );
    type map_varchar is table of jelstn_inhab_type index by v_curr_idxkey%TYPE;
    type idxkeys_array_t is table of spriden.spriden_origin%TYPE;
    type pidms_array_t is table of spriden.spriden_pidm%TYPE;

    l_jel               jelstn_inhab_type;
    l_map               map_varchar;
    idxkeys             idxkeys_array_t := idxkeys_array_t();
    pidms               pidms_array_t := pidms_array_t();

    v_current_db        global_name.global_name%TYPE;

    v_date_str          char(8) := TO_CHAR(sysdate, 'YYYYMMDD');
    
    cursor get_spriden_cur (p_idxkey in v_curr_idxkey%TYPE) is
        select * from spriden
        where spriden_change_ind is null
        and spriden_data_origin='SomeInterestingPhrase'
        and spriden_origin=p_idxkey
        order by spriden_pidm
    ;

begin

    -- Begin:  Avoid running in any non-whitelisted database.  (PLEASE DO NOT whitelist production.)
    select global_name
    into v_current_db
    from global_name;
    if v_current_db not in ('test database') then
        dbms_output.put_line('Not executing against a whitelisted database; exiting.  Current database is: ' || v_current_db);
        return;
    end if;
    -- End:  Avoid running in any non-whitelisted database.  (PLEASE DO NOT whitelist production.)
    
    -- Begin:  Populate l_map with "Jellystone Park inhabitants"
    begin
        v_curr_idxkey := '01YB';
        l_map(v_curr_idxkey).idxkey := v_curr_idxkey;
        l_map(v_curr_idxkey).lname := 'Bear'||v_date_str;
        l_map(v_curr_idxkey).fname := 'Yogi'||v_date_str;
        v_curr_idxkey := '02BB';
        l_map(v_curr_idxkey).idxkey := v_curr_idxkey;
        l_map(v_curr_idxkey).lname := 'Bear'||v_date_str;
        l_map(v_curr_idxkey).fname := 'Boo Boo'||v_date_str;
    end;
    -- End:  Populate l_map with "Jellystone Park inhabitants"
    
    -- Begin:  Grab SPRIDEN details if record exists; add to SPRIDEN and backfill into l_map if not.  Also populate pidms and idxkeys.
    begin
        v_loop_counter := 1;
        v_curr_idxkey := l_map.first;
        while v_curr_idxkey is not null
        loop
            open get_spriden_cur(v_curr_idxkey);
            fetch get_spriden_cur into v_curr_spriden;
            if get_spriden_cur%NOTFOUND then
                -- Create SPRIDEN and store IDs
                l_map(v_curr_idxkey).bannerid := 'GENERATED';
                dbms_output.put_line(l_map(v_curr_idxkey).fname);
                gb_identification.p_create (
                    p_id_inout=>l_map(v_curr_idxkey).bannerid,
                    p_last_name=>l_map(v_curr_idxkey).lname,
                    p_first_name=>l_map(v_curr_idxkey).fname,
                    p_change_ind=>NULL,
                    p_entity_ind=>'P',
                    p_user=>'BESTTESTER',
                    p_origin=>v_curr_idxkey,
                    p_data_origin=>'SomeInterestingPhrase',
                    p_pidm_inout=>l_map(v_curr_idxkey).pidm,
                    p_rowid_out=>l_map(v_curr_idxkey).spridrowid
                );
            else
                -- Store first IDs found
                l_map(v_curr_idxkey).pidm := v_curr_spriden.spriden_pidm;
                l_map(v_curr_idxkey).bannerid := v_curr_spriden.spriden_id;
            end if;
            close get_spriden_cur;  -- Don't bother to go to any subsequent records found in cursor.
            idxkeys.extend();
            idxkeys(v_loop_counter) := v_curr_idxkey;
            pidms.extend();
            pidms(v_loop_counter) := l_map(v_curr_idxkey).pidm;
            v_curr_idxkey := l_map.next(v_curr_idxkey);
            v_loop_counter := v_loop_counter + 1;
        end loop;
        --commit; -- PRODUCTION:  TURN BACK ON
    end;
    -- End:  Grab SPRIDEN details if record exists; add to SPRIDEN and backfill into l_map if not.  Also populate pidms and idxkeys.
    
    -- Begin:  Upsert SPBPERS for all Jellystone Park inhabitants
    begin
        null;
    end;
    -- End:  Upsert SPBPERS for all Jellystone Park inhabitants
    
end;

You can check that your data populated correctly with the following query:

select *
from spriden
where and spriden_data_origin = 'SomeInterestingPhrase';

Thank you, East Carolina University, for leaving Banner’s API Googleable.

e.g. gb_identification.p_create

Note that you have to create variables for p_id_inout and p_pidm_inout (as well as for p_rowid_out).

Don’t try to directly pass 'GENERATED' and NULL to these “id” & “pidm” parameters.
They’re “inout” parameters, so they’re expecting to “write out” data to something. They can’t write their “return values” to literals; they need a variable whose contents they can overwrite.
- Set them to 'GENERATED' and NULL as their initial values.

Misc:

Notes about a command called grep that searches files in, and beneath, a given Linux folder for a keyword in their contents to see if your coworkers have already written code using a function that’s erroring out on you.

Tweet Share on LinkedIn

--- ---

Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Banner Unit Test Framework