• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Oracle array

Hello All,

I have a trigger on table column and I have an array which I need to call inside the trigger.

Array is populated through a stored procedure, is there a way I can set this array in the session and trigger can use array from session.

Thanks,
0
dojjol
Asked:
dojjol
  • 3
  • 2
2 Solutions
 
sdstuberCommented:
declare the array as a package variable

then it'll be available to any thing within your session, including triggers
0
 
dojjolAuthor Commented:
thanks sdstuber.

I am new to complex pl/sql; :).

Process I am trying to implement is, i have javastoredprocedure which is called from trigger. This storedprocedure does complex time consuming process and return an array, I need to cache this array specific to session, so that if array is in session, trigger will skip executing the storedprocedure and will perform rest of the steps.

so not really sure how to cache array in session and skip calling stored procedure from inside trigger
0
 
flow01Commented:
you can keep calling your stored procedure in the trigger


create or replace package cache_array

procedure return_array (p_array out ...type);

end;

create or replace package body cache_array

pck_cached boolean := false;
pck_array ...type;

procedure return_array (p_array out ...type)
is
begin
if pck_cached then
   null;
else
/*
your current procedure coding but replace p_array with pck_array
*/
end if;
pck_cached := true; -- mark the array as cached
p_array  := pck_array; -- assign p_array from cache
end if;
end;
 
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
dojjolAuthor Commented:
Thanks.
 Can I declare two dimensional array with 2 varchar element.

Is it mandatory to specify array length
0
 
flow01Commented:
Not exactly sure what you are asking.

There are 3 types of arrays with ORACLE

You can define your own 2-dimensional array

declare
TYPE array_lvl1_type IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(20);
TYPE array_lvl2_type IS TABLE OF array_lvl1_type INDEX BY VARCHAR2(20);
array_lvl1 array_lvl1_type ;
array_lvl2 array_lvl2_type ;
  procedure set_array_value(p1 varchar2, p2 varchar2, p_value varchar2)
  is
  begin
     if array_lvl2.exists(p1) THEN
        array_lvl1 := array_lvl2(p1);
     else
        array_lvl1.delete;
     end if;
     array_lvl1(p2) := p_value;
     array_lvl2(p1) := array_lvl1;
  end;
  function get_array_value(p1 varchar2, p2 varchar2)
  return varchar2
  is
    v_return varchar2(100);
  begin
    begin
     array_lvl1 := array_lvl2(p1);
     v_return := array_lvl1(p2);
    exception when no_data_found then
      v_return := null;
    end;
    return v_return;
  end;
begin
  set_array_value('A','B','VALUE1');
  set_array_value('A','C','VALUE2');
  set_array_value('B','C','VALUE3');
  dbms_output.put_line('AA:' || get_array_value('A','A'));
  dbms_output.put_line('AB:' || get_array_value('A','B'));
  dbms_output.put_line('AC:' || get_array_value('A','C'));
  dbms_output.put_line('BA:' || get_array_value('B','A'));
  dbms_output.put_line('BB:' || get_array_value('B','B'));
  dbms_output.put_line('BC:' || get_array_value('B','C'));
end;
/

This was an example of the ORACLE pl/sql index by type of array.
For this type array length specification is not necessary : (you wil hit memory problems if you do use very large arrays)
check your documentation for other type of ORACLE arrays

0
 
dojjolAuthor Commented:
Thank you sdstuber and flow01
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now