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

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

Anonymous PLSQL subquery from a file

23 Mar 2021 🔖 databases sql integration
💬 EN

In PL/SQL Nested Queries, we learned how to leverage compiled-into-the-database Oracle SQL record & table-of-record types in anonymous PL/SQL to save the results of one SELECT into a variable, then use them as a filter in another SELECT. But what if we’d rather populate our variable from a file?

For the anonymous PL/SQL variable t_person_id_objects to actually be useful in any subsequent SQL or DML, you have be able to leverage the presence of precompiled SQL record & table-of-records types, as described in PL/SQL Nested Queries.

I’m sure you could write from a file using code a lot like the code below into a t_person_id_objects variable that was a simple table of PERSON_DETAILS.PERSON_ID%TYPE, but in anonymous PL/SQL, you can’t use that to filter subsequent SQL or DML.

The magic that lets the code below use t_person_id_objects in a SQL query or DML statement is:

  1. Having a database-precompiled SCHEMA_NAME.PERSON_REC_TYPE whose r_personid field is of type PERSON_DETAILS.PERSON_ID%TYPE.
  2. Having a database-precompiled SCHEMA_NAME.PERSON_TBL_TYPE that’s a table of SCHEMA_NAME.PERSON_REC_TYPE.
DECLARE
    v_filedir               VARCHAR2(50)                    := '&p_in_filedir';
    v_filename              VARCHAR2(40)                    := '&p_in_filename';
    v_file_handle           UTL_FILE.FILE_TYPE;
    t_person_id_objects     SCHEMA_NAME.PERSON_TBL_TYPE     := SCHEMA_NAME.PERSON_TBL_TYPE();

    FUNCTION get_id_from_file_line (i_line VARCHAR2) RETURN PERSON_DETAILS.PERSON_ID%TYPE IS
    BEGIN
        -- In this case, there's simply 1 6-digit ID per line
        RETURN(TRIM(SUBSTR(i_line,1,6)));
    END get_id_from_file_line;

    PROCEDURE read_file_to_variable IS
        v_loop_counter      pls_integer                     := 1;
        v_loop_record       VARCHAR2(255);
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Reading the following record IDs into memory from external file:');
        UTL_FILE.FCLOSE_ALL;
        v_file_handle := UTL_FILE.FOPEN(v_filedir, v_filename, 'R');
        LOOP
            BEGIN
                UTL_FILE.GET_LINE(v_file_handle, v_loop_record);
                t_person_id_objects.extend;
                t_person_id_objects(v_loop_counter) := SCHEMA_NAME.PERSON_REC_TYPE(get_id_from_file_line(v_loop_record), NULL, NULL, NULL, NULL);
                DBMS_OUTPUT.PUT_LINE(t_person_id_objects(v_loop_counter).r_personid);
                v_loop_counter := v_loop_counter + 1;
            EXCEPTION
                WHEN NO_DATA_FOUND
                THEN EXIT;
            END;
        END LOOP;
        UTL_FILE.FCLOSE_ALL;
        DBMS_OUTPUT.PUT_LINE('Read ' || t_person_id_objects.COUNT || ' record IDs into memory');
    END read_file_to_variable;

--=================================================== 
-- MAIN
--=================================================== 

BEGIN
    DBMS_OUTPUT.PUT_LINE ('my_script.sql started at ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    read_file_to_variable();
    -- Here, do some SQL/DML that filters against:
    --   SELECT r_personid FROM TABLE ( CAST ( t_person_id_objects as SCHEMA_NAME.PERSON_TBL_TYPE ) ) 
    -- instead of filtering against an ordinary list/select of IDs.
    DBMS_OUTPUT.PUT_LINE ('my_script.sql ended at ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END;
/
--- ---