This discussion is archived
6 Replies Latest reply: Feb 25, 2013 8:44 PM by BillyVerreynne RSS

Pinning a package or associative array in memory

user12625 Newbie
Currently Being Moderated
I have a lookup table that i would like to load into an associative array and use the results of that during processing throughout the day.

I know i can load a package into memory so it's always resident but my question is how do i load the package into memory and call the routine to populate the associative array upon initiaizliation so that it is already preloaded once i start using the package?

For example table a in the database has a few columns that i want to store as an table by varchar array.

type gt_array is table of ofa_lookup%type
index by varchar2(30);

ltbl_lookup gt_array;


Select olbi.beta_cd, olbi.beta_val
from ofa_lookup_beta_interaction olbi
where olbi.beta_end_dt is null
and olbi.beta_void_ind='n';

begin
for lrec in ocsr_betas loop
ltbl_lookup(lrec.beta_cd) := lrec.beta_val;
end loop;
end;


The result would be something like this
ltbl_lookup('payroll') := '200000';
ltbl_lookup('marketSegment') := 'All Other';


Is it possible to do this? So that my procedure's just can call the package and grab the value from the already populated associative array?

Thanks for your help in this
  • 1. Re: Pinning a package or associative array in memory
    Justin Cave Oracle ACE
    Currently Being Moderated
    You can initialize the collection in the package's initialization block
    CREATE OR REPLACE PACKAGE BODY package_name
    AS
      PROCEDURE procedure_name ...
    
      FUNCTION function_name
    BEGIN
      <<your initialization code here>>
    END;
    Since a collection in a package has session scope, the collection will have to be loaded separately for each database session. The initialization block in the package body will be called as soon as the session invokes any method in the package. That is, presumably, what you want to happen. But that is not going to happen just once at the point that the database starts.

    If you are concerned because loading the collection takes a long time, implying that there are going to be a large number of elements in the collection, I would tend to be very hesitant to have that collection in PGA memory for every session that needs it. That could end up consuming a hefty amount of the available RAM on the server. Depending on the Oracle version that you haven't mentioned, is it possible that you could use the function result cache instead?

    Justin
  • 2. Re: Pinning a package or associative array in memory
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
    >
    I have a lookup table that i would like to load into an associative array and use the results of that during processing throughout the day.
    >
    Why would you want or need to do that?

    Instead of focusing on a solution why don't you start by telling us what the problem is (if any) you are trying to solve.

    How do you plan to use this data? Is this a high volume use or a low volume use?

    Have you considered using a GLOBAL context? That would make the values available to ALL sessions and you would only need to load the values once.

    Then any query can get those values by using the SYS_CONTEXT function. See the SQL Language doc for the function
    http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions182.htm
    >
    SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
    FROM DUAL;
    >
    And the same doc for the CREATE CONTEXT info and examples
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5002.htm
    >
    ACCESSED GLOBALLY
    This clause indicates that any application context set in namespace is accessible throughout the entire instance. This setting lets multiple sessions share application attributes.

    Examples

    Creating an Application Context: Example
  • 3. Re: Pinning a package or associative array in memory
    user12625 Newbie
    Currently Being Moderated
    Here is the result from my query against the version.

    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE     10.2.0.4.0     Production
    TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    If you require a broader description here goes. I have a bunch of lookup tables that include variables that i need to compare that could change and there are about 30 different cuts. Below is one of the table.
    create table OFA_LOOKUP_BETA_INTERACTION
    (
    beta_cd VARCHAR2(30) not null,
    beta_eff_dt DATE not null,
    beta_data_source_nm VARCHAR2(30) not null,
    loss_time_3yr_hist_freq_ind CHAR(1) not null,
    beta_actual_value NUMBER not null,
    beta_relational_operator VARCHAR2(2) not null,
    beta_and_source_nm VARCHAR2(30) not null,
    beta_and_actual_value VARCHAR2(30) not null,
    beta_and_relational_operator VARCHAR2(2),
    beta_return_value NUMBER(5,4),
    beta_end_dt DATE,
    beta_void_ind CHAR(1)
    )

    Here is one group of values
         BETA_CD     BETA_EFF_DT     BETA_DATA_SOURCE_NM     LOSS_TIME_3YR_HIST_FREQ_IND     BETA_ACTUAL_VALUE     BETA_RELATIONAL_OPERATOR     BETA_AND_SOURCE_NM     BETA_AND_ACTUAL_VALUE     BETA_AND_RELATIONAL_OPERATOR     BETA_RETURN_VALUE     BETA_END_DT     BETA_VOID_IND
    1     b_payroll_mkt_seg     1/1/2013     payroll     n     200000     <     market_segment     Construction     =     1.0000          n
    2     b_payroll_mkt_seg     1/1/2013     payroll     n     200000     <     market_segment     Manufacturing     =     1.0000          n
    3     b_payroll_mkt_seg     1/1/2013     payroll     n     200000     <     market_segment     Health Services     =     1.0000          n
    4     b_payroll_mkt_seg     1/1/2013     payroll     n     200000     <     market_segment     All Other     =     1.0000          n
    5     b_payroll_mkt_seg     1/1/2013     payroll     y     200000     >=     market_segment     Construction     =     1.0000          n
    6     b_payroll_mkt_seg     1/1/2013     payroll     y     200000     >=     market_segment     Manufacturing     =     1.0000          n
    7     b_payroll_mkt_seg     1/1/2013     payroll     y     200000     >=     market_segment     Health Services     =     1.0000          n
    8     b_payroll_mkt_seg     1/1/2013     payroll     y     200000     >=     market_segment     All Other     =     1.1794          n

    With this information I want to be able to build my logic/case statement on the fly based on what is in the table similar to below.

    when loss_time_3yr_hist_freq is null and payroll < 200000 and market_segment= All Other then 1
    when loss_time_3yr_hist_freq is null and payroll < 200000 and market_segment= Construction then 1
    when loss_time_3yr_hist_freq is null and payroll < 200000 and market_segment= Health Services then 1
    when loss_time_3yr_hist_freq is null and payroll < 200000 and market_segment= Manufacturing then 1
    when loss_time_3yr_hist_freq is not null and payroll >= 200000 and market_segment= All Other then 1.1794
    when loss_time_3yr_hist_freq is not null and payroll >= 200000 and market_segment= Construction then 1
    when loss_time_3yr_hist_freq is not null and payroll >= 200000 and market_segment= Health Services then 1
    when loss_time_3yr_hist_freq is not null and payroll >= 200000 and market_segment= Manufacturing then 1

    My next question was going to be once i have this information how can i implement it in my program effectively. My thinking was to store this information in an associative array and have the program go through the array and compare the variables in the case statement.
    If you have any other better options then that would be great.

    Thanks in advance for your help.
  • 4. Re: Pinning a package or associative array in memory
    user12625 Newbie
    Currently Being Moderated
    Hi,

    Please see my answer to the other post below, any suggestons based on that?

    Thanks in advance for your help
  • 5. Re: Pinning a package or associative array in memory
    rp0428 Guru
    Currently Being Moderated
    Please edit your last post and use \
     on the line before and the line after your code or data samples to preserve formatting.
    
    This is unreadable
    Here is one group of values
    BETA_CD BETA_EFF_DT BETA_DATA_SOURCE_NM LOSS_TIME_3YR_HIST_FREQ_IND BETA_ACTUAL_VALUE BETA_RELATIONAL_OPERATOR BETA_AND_SOURCE_NM BETA_AND_ACTUAL_VALUE BETA_AND_RELATIONAL_OPERATOR BETA_RETURN_VALUE BETA_END_DT BETA_VOID_IND
    1 b_payroll_mkt_seg 1/1/2013 payroll n 200000 < market_segment Construction = 1.0000 n
    There is no way to tell when one value ends and the next begins or what the column label is.
    
    Provide a SIMPLE example of what you are trying to do. Show the data at the start of the example, the change you want to make to that data, and why, and what the data looks like at the end of the example.
    
    STOP focusing on code and focus on what you need to do with the data (the business rule). The code will be based on the business rule.
    
    You are showing what appears to be a flag with a 'y/n' value
    loss_time_3yr_hist_freq_ind CHAR(1) not null
    But then your examplel doesn't include that flag. Or if it does it checks for null
    when loss_time_3yr_hist_freq is null and payroll < 200000 and market_segment= All Other then 1
    You didn't answer two of the questions that I ask above
    {quote}
    How do you plan to use this data? Is this a high volume use or a low volume use?
    {quote}
    It looks like you are trying to use dynamic business rules for updating some data somewhere.
    
    Why? Why do you think you need to code this dynamically?
    
    If you load these rules at the start of the day (like you originally said) that suggests that they CANNOT change during the day. Which raises the question: when can the rules (less than, greater than, etc) change. How do those changes in the rules affect what you processed before the change?
    
    Who is changing the business rules? How are they changing them? In a GUI? 
    
    Who is using your data after it has been changed? Is the change part of the load process for a data warehouse? Or is the change part of data presentation to the user?
    
    Doing things dynamically can appear to be very flexible but it is usally not very performant and can also have a LOT of problems with exception handling, recovery and writing bullet-proof code, not to mention testing.
    
    Dynamic processes should usually only be used if a static process cannot get the job done.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 6. Re: Pinning a package or associative array in memory
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    user12060467 wrote:
    I have a lookup table that i would like to load into an associative array and use the results of that during processing throughout the day.
    Lookup tables are not slow. SQL is not slow. Unless you design these to be.

    So what have you identified as the performance problem? How sure are you that performance of SQL against lookup tables are the problem, and not mere shoddy design and shoddy code?

    I assume that the data you need to process and use lookup tables for, also comes from database tables? Also needs SQL to be processed?

    In which case, it makes a lot more performance sense, and scalability sense, to rather crunch the data, and lookup tables, in SQL. And not use PL/SQL.

    There's a very basic concept for designing and writing effective Oracle applications: Maximise SQL. Minimise PL/SQL (or Java/.Net/etc).

    Use the very powerful SQL language to its full extent. Do not use PL/SQL to emulate SQL. Do not use expensive arrays in PL/SQL to play database tables.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points