CODEWITHSUNDEEP
sqloracle-databaseplsql
Javi Torre
Javi Torre

Reputation: 824

Oracle SQL Load Variables from Config Table

I have added a procedure in a package (which is executed before the other ones) which reads variables from a configuration table with a select into structure for every variable to be read.

PROCEDURE load_config AS
BEGIN
    idproceduregeneral := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
    user_logs('Start', idproceduregeneral);
    SELECT
        week_offset,
        week_range
    INTO
        week_offset_ibsp,
        week_range_ibsp
    FROM
        dat_report_config
    WHERE
        process = 'EC'
        AND subprocess = 'IBSP';

-- Here I would do one select into for each of the variables I want to fetch.   

    days_offset_ibsp := week_offset_ibsp + week_range_ibsp * 7;
    days_offset_agencies := week_offset_agencies + week_range_agencies * 7;
    user_logs('Load Config', idproceduregeneral);
END load_config;

Is there any simple way of achieving this without having to select into for every single variable?

EDIT: this is how my config table would look like.

enter image description here

Upvotes: 0

Views: 185

Answers (1)

crocarneiro
crocarneiro

Reputation: 144

You can use a ROWTYPE variable for this.

DECLARE
  config DAT_REPORT_CONFIG%ROWTYPE;
BEGIN
  SELECT *
    INTO config
    FROM dat_report_config
   WHERE process = 'EC'
     AND subprocess = 'IBSP';

   /*
    * Do what you gotta do. Now you can use your variables like this:
    * config.week_offset
    * config.week_range
    */
END;

For more info about ROWTYPE check the official documentation: %ROWTYPE Attribute

Also, you can use a collection indexed by a VARCHAR2 to load the variables for every subprocess and then access them by the subprocess name.

DECLARE
   TYPE type_configs IS TABLE OF DAT_REPORT_CONFIG%ROWTYPE INDEX BY VARCHAR2(100);
   configs TYPE_CONFIGS;
BEGIN
   FOR i IN ( SELECT *
                FROM dat_report_config
               WHERE process = 'EC' )
   LOOP
      configs(i.subprocess) := i;
   END LOOP;

   /*
    * Do what you gotta do. Now you can use your variables like this:
    * configs('IBSP').week_offset
    * configs('IBSP').week_range
    */
END;

More informations about collections here: Working with Collections.

This way works fine for little amount of data. But if your table has a lot of rows you will have performance issues. Cause this is referred as “slow-by-slow processing.”. I could not think of a way to do this using SELECT BULK, maybe someone else knows.

Upvotes: 2

Related Questions