TAB - Script package
es Hälferli um s'Handling vo mem_tab basierendi Tabelle basierend uf em mem_tab DDIC
[script 1.0]
script package rbsc$tab
is
/*
-------------------------------------------------------------------------------------------------------------
Bank:
Business Area:
Description: Utility package to assist in managing MEM_TAB based tables.
Completely based on MEM_TAB DDIC replacing the deprecated Avaloq TAB script package.
-------------------------------------------------------------------------------------------------------------
ID Date Name, Company Issue Affected BUs, Description
-------------------------------------------------------------------------------------------------------------
001
-------------------------------------------------------------------------------------------------------------
Example usage:
--------------
import rbsc$tab;
declare
l_tab number;
l_row_id number;
l_val number;
l_sum number;
begin
l_tab := rbsc$tab.tab#new(i_col_spec_list => "a:number,b:text,c:date");
-- Add a column
rbsc$tab.tab#add_col(l_tab, "bp", "obj_bp");
-- Add a row... (without specifying a tag!)
l_row_id := rbsc$tab.tab#add_row(l_tab);
-- ... and set the values by calling procedures
rbsc$tab.row#set_nr_val(l_row_id, "a", 1);
rbsc$tab.row#set_text_val(l_row_id, "b", "Hello world!");
rbsc$tab.row#set_date_val(l_row_id, "c", session.today);
-- Add another row and assign values directly using MEM_TAB_ROW DDIC
with mem_tab_row(rbsc$tab.tab#add_row(l_tab,"xx")) as row do
row.fld("a").number_val := 2;
row.fld("b").text_val := "Another line";
row.fld("c").date_val := lookup.date("soy");
end with;
rbsc$tab.tab#dump(l_tab);
-- Loop over all rows of by calling procedures
l_sum := 0;
for i in 1..rbsc$tab.tab#row_cnt(l_tab) loop
l_row_id := rbsc$tab.tab#row_id_from_seq(l_tab, i);
l_val := rbsc$tab.row#nr_val(l_row_id, "a");
l_sum := l_sum + l_val;
end loop;
-- Loop over all rows directly
l_sum := 0;
for row in mem_tab(l_tab).row_list loop
l_val := rbsc$tab.row#nr_val(row, "a");
l_sum := l_sum + l_val;
end loop;
rbsc$tab.tab#clear(l_tab);
rbsc$tab.tab#dump(l_tab);
rbsc$tab.tab#sort(l_tab, "b");
rbsc$tab.tab#dump(l_tab);
-- Remove a row by a given tag
rbsc$tab.tab#remv_row(l_tab, "xx");
-- Remove a row by a given ID
rbsc$tab.tab#remv_row(l_tab, i_row_id => l_row_id);
rbsc$tab.tab#dump(l_tab);
rbsc$tab.tab#remv(l_tab);
end;
*/ ------------------------------------------------------------------------------
-- IMPORTS
------------------------------------------------------------------------------
import tab;
import util;
import native get_new_id;
------------------------------------------------------------------------------
-- CONSTANTS
------------------------------------------------------------------------------
c_tab_prefix constant text := 't_tab';
-- Valid column types
c_col_type_text constant text := mem_tab.type_text;
c_col_type_number constant text := mem_tab.type_number;
c_col_type_date constant text := mem_tab.type_date;
c_col_type_long constant text := mem_tab.type_long;
c_col_type_boolean constant text := mem_tab.type_bool;
c_col_type_time_tz constant text := 'time_tz';
------------------------------------------------------------------------------
-- @description Lookup a table by name/tag
-- @input i_tag: tag to identify the table
-- @return MEM_TAB-ID
------------------------------------------------------------------------------
function tab_id(
i_tag text
) return id mem_tab
is
l_tab_id number := null;
begin
with mem_tab.tab(i_tag) as tab do
l_tab_id := tab.id;
end with;
return l_tab_id;
exception
when others then
session.raise_fa_err('tab_id');
end tab_id;
------------------------------------------------------------------------------
-- @description Add a new column to the given table
-- @input i_tab_id: MEM_TAB-ID
-- i_col_name: name tag of column
-- i_col_type: type of column (text, number, date, long, boolean, time_tz or a table name)
------------------------------------------------------------------------------
procedure tab#add_col(
i_tab_id number
,i_col_name text
,i_col_type text
)
is
begin
with mem_tab(i_tab_id) as tab do
require(i_col_name is not null, 'Specified column ' || i_col_name || ' has no name.');
if i_col_type in (c_col_type_text, c_col_type_number, c_col_type_date, c_col_type_long, c_col_type_boolean, c_col_type_time_tz) then
tab.cols.add_base(i_col_name, i_col_type);
else
tab.cols.add_id(i_col_name, i_col_type);
end if;
end with;
exception
when others then
session.raise_fa_err('tab#new(' || i_tab_id || ',' || i_col_name || ',' || i_col_type || ')');
end tab#add_col;
------------------------------------------------------------------------------
-- @description Create new memory table and return ID
-- @input i_tag: Optional tag to identify the table
-- i_col_spec_list: Column specification as text list "<col1>:<type1>,<col2>:<type2>, ..."
-- i_use_trx_mgr: Use session transaction manager (session.trx_mgr.commit/rollback)
-- i_use_session_scope: Use tag given as parameter for mem_tab reuse in same oracle session.
-- @return MEM_TAB-ID
------------------------------------------------------------------------------
function tab#new(
i_tag text := null
,i_col_spec_list text := null
,i_use_trx_mgr text := '-'
,i_use_session_scope text := '-'
) return id mem_tab
is
l_tab_id number := null;
l_spec text;
l_col_name text;
l_col_type text;
l_tab_name text;
begin
if i_use_session_scope = '+' then
l_tab_name := i_tag;
else
l_tab_name := c_tab_prefix || get_new_id('list_id_seq');
end if;
with new mem_tab(l_tab_name, '+', i_use_trx_mgr) as tab do
l_tab_id := tab.id;
-- Loop over column specification and setup columns
for i in 1 .. util.item_count(i_col_spec_list, ',') loop
l_spec := util.item(i_col_spec_list, i, ',');
l_col_name := util.item(l_spec, 1, ':');
l_col_type := lower(util.item(l_spec, 2, ':'));
tab#add_col(l_tab_id, l_col_name, l_col_type);
end loop;
end with;
return l_tab_id;
exception
when others then
session.raise_fa_err('tab#new(' || i_tag || ',' || i_col_spec_list || ',' || i_use_trx_mgr || ')');
end tab#new;
------------------------------------------------------------------------------
-- @description Removes the table from memory
-- @input i_tab_id: MEM_TAB-ID
------------------------------------------------------------------------------
procedure tab#remv(
i_tab_id number
)
is
begin
with mem_tab(i_tab_id) as tab do
tab.remv;
end with;
exception
when others then
session.raise_fa_err('tab#remv(' || i_tab_id || ')');
end tab#remv;
------------------------------------------------------------------------------
-- @description Clears all rows
-- @input i_tab_id: MEM_TAB-ID
------------------------------------------------------------------------------
procedure tab#clear(
i_tab_id number
)
is
begin
with mem_tab(i_tab_id) as tab do
tab.row_list.clear;
end with;
exception
when others then
session.raise_fa_err('tab#clear(' || i_tab_id || ')');
end tab#clear;
------------------------------------------------------------------------------
-- @description Returns the number of rows in the given table
-- @input i_tab_id: MEM_TAB-ID
-- @return Number of rows in table
------------------------------------------------------------------------------
function tab#row_cnt(
i_tab_id number
) return number
is
l_row_cnt number;
begin
with mem_tab(i_tab_id) as tab do
l_row_cnt := tab.row_list.count;
end with;
return l_row_cnt;
exception
when others then
session.raise_fa_err('tab#row_cnt(' || i_tab_id || ')');
end tab#row_cnt;
------------------------------------------------------------------------------
-- @description Adds a new row to the given table
-- @input i_tab_id: MEM_TAB-ID
-- i_row_tag: optional tag for the current row. (if rows are numbered 1, 2, 3, ...)
-- @return ID of row
------------------------------------------------------------------------------
function tab#add_row(
i_tab_id number
,i_row_tag text := null
) return id mem_tab_row
is
l_row_id id mem_tab_row;
l_next_row_id number;
l_row_id_found boolean := false;
begin
with mem_tab(i_tab_id) as tab do
l_next_row_id := to_char(tab.row_list.count + 1);
while not l_row_id_found loop
if tab.row(l_next_row_id) is not null then
l_next_row_id := l_next_row_id + 1;
else
l_row_id_found := true;
end if;
end loop;
l_row_id := tab.add_row_id(nvl(i_row_tag, l_next_row_id));
end with;
return l_row_id;
exception
when others then
session.raise_fa_err('tab#add_row(' || i_tab_id || ',' || i_row_tag || ')');
end tab#add_row;
------------------------------------------------------------------------------
-- @description Removes the row with the given tag or the given ID.
-- Either the row tag or the row ID has to be specified
-- @input i_tab_id: MEM_TAB-ID
-- i_row_tag: (optional) tag for the row
-- i_row_id: (optional) id of row to be removed (e.g. returned by tab#row_id)
------------------------------------------------------------------------------
procedure tab#remv_row(
i_tab_id number
,i_row_tag text := null
,i_row_id id mem_tab_row := null
)
is
l_row_id id mem_tab_row := i_row_id;
begin
require(i_row_tag || i_row_id is not null, 'Row tag or row ID has to be specified')
with mem_tab(i_tab_id) as tab do
if l_row_id is null then
l_row_id := tab.row_id(i_row_tag);
end if;
require(l_row_id is not null, 'Valid row specified');
tab.row_list.remv_item(l_row_id);
end with;
exception
when others then
session.raise_fa_err('tab#remv_row(' || i_tab_id || ',' || i_row_tag || ',' || i_row_id || ')');
end tab#remv_row;
------------------------------------------------------------------------------
-- @description returns the row ID for the given row tag
-- @input i_tab_id: MEM_TAB-ID
-- i_row_tag: tag for the row
-- @return ID of row
------------------------------------------------------------------------------
function tab#row_id(
i_tab_id number
,i_row_tag text
) return id mem_tab_row
is
l_row_id id mem_tab_row;
begin
with mem_tab(i_tab_id) as tab do
l_row_id := tab.row_id(i_row_tag);
end with;
return l_row_id;
exception
when others then
session.raise_fa_err('tab#row_id(' || i_tab_id || ',' || i_row_tag || ')');
end tab#row_id;
------------------------------------------------------------------------------
-- @description Returns the row ID for the given table
-- @input i_tab_id: MEM_TAB-ID
-- i_seq_nr: sequence number: 1, 2, 3, ...
-- @return ID of row
------------------------------------------------------------------------------
function tab#row_id_from_seq(
i_tab_id number
,i_seq_nr number
) return id mem_tab_row
is
l_row_id id mem_tab_row;
begin
with mem_tab(i_tab_id) as tab do
l_row_id := tab.row_list(seq_nr => i_seq_nr);
end with;
return l_row_id;
exception
when others then
session.raise_fa_err('tab#row_id_from_seq(' || i_tab_id || ',' || i_seq_nr || ')');
end tab#row_id_from_seq;
------------------------------------------------------------------------------
-- @description Returns whether a value exists within a column in the given table
-- This function is looping over all rows and compares the value of the named column
-- with the value passed as an argument. --> ATTENTION: SLOW ON BIG TABLES!
-- @input i_tab_id: MEM_TAB-ID
-- @input i_col_name: valid name of a column in the table
-- @input i_val: non-null value which we are looking for
-- @return true if the value exist in the column, otherwise false
------------------------------------------------------------------------------
function tab#col_has_val(
i_tab_id number
,i_col_name text
,i_val text
) return boolean
is
l_col_type text;
l_col_val text;
begin
with mem_tab(i_tab_id) as tab do
-- Check precondition: valid column name and non empty value
require(tab.cols.contains(i_col_name) = '+', 'valid column ' || i_col_name);
require(i_val is not null, 'Non-null value');
-- Determine column type
l_col_type := tab.col_list(
col_name => i_col_name
,seq_nr => 1
).base_type;
-- Loop over all rows to check the value of the column
for row in tab.row_list loop
if l_col_type = c_col_type_text then
l_col_val := row.fld(i_col_name).text_val;
elsif l_col_type = c_col_type_number then
l_col_val := row.fld(i_col_name).number_val;
elsif l_col_type = c_col_type_date then
l_col_val := row.fld(i_col_name).date_val;
elsif l_col_type = c_col_type_long then
l_col_val := row.fld(i_col_name).text_val;
elsif l_col_type = c_col_type_boolean then
l_col_val := row.fld(i_col_name).bool_val;
elsif l_col_type = c_col_type_time_tz then
l_col_val := row.fld(i_col_name).number_val;
else
l_col_val := row.fld(i_col_name).id_val;
end if;
-- Compare values
if l_col_val = i_val then
return true;
end if;
end loop;
end with;
return false;
exception
when others then
session.raise_fa_err('tab#col_has_val(' || i_tab_id || ',' || i_col_name || ',' || i_val || ')');
end tab#col_has_val;
------------------------------------------------------------------------------
-- @description Set value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to modify
-- i_text_val: Value to be set (text format)
-- i_date_val: Value to be set (date format)
-- i_bool_val: Value to be set (boolean format: "+" (=true) and "-" (=false))
-- i_nr_val: Value to be set (number format)
-- i_id_val: Value to be set (number format)
------------------------------------------------------------------------------
procedure row#set_val(
i_row_id id mem_tab_row
,i_col_name text
,i_text_val text := null
,i_date_val date := null
,i_bool_val text := null
,i_nr_val number := null
,i_id_val number := null
)
is
l_col_type text;
begin
with mem_tab_row(i_row_id).fld(i_col_name) as fld do
l_col_type := fld.col.base_type;
if l_col_type in (c_col_type_text, c_col_type_long) then
fld.text_val := i_text_val;
elsif l_col_type in (c_col_type_date, c_col_type_time_tz) then
fld.date_val := i_date_val;
elsif l_col_type = c_col_type_boolean then
fld.bool_val := i_bool_val;
elsif l_col_type = c_col_type_number then
fld.number_val := i_nr_val;
else
fld.id_val := i_id_val;
end if;
end with;
exception
when others then
session.raise_fa_err('row#set_val(' || i_row_id || ',' || i_col_name || ',' || i_text_val || ',' || i_date_val || ',' || i_bool_val || ',' || i_nr_val || ',' || i_id_val || ')');
end row#set_val;
------------------------------------------------------------------------------
-- @description Set value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to modify
-- @return value stored in given column (converted to text)
------------------------------------------------------------------------------
function row#val(
i_row_id id mem_tab_row
,i_col_name text
) return text
is
l_col_type text;
l_val text;
begin
with mem_tab_row(i_row_id).fld(i_col_name) as fld do
l_col_type := fld.col.base_type;
if l_col_type in (c_col_type_text, c_col_type_long) then
l_val := fld.text_val;
elsif l_col_type in (c_col_type_date, c_col_type_time_tz) then
l_val := fld.date_val;
elsif l_col_type = c_col_type_boolean then
l_val := fld.bool_val;
elsif l_col_type = c_col_type_number then
l_val := fld.number_val;
else
l_val := fld.id_val;
end if;
end with;
return l_val;
exception
when others then
session.raise_fa_err('row#val(' || i_row_id || ',' || i_col_name || ')');
end row#val;
------------------------------------------------------------------------------
-- @description Set value for the specified column
-- @input i_tab_id: MEM_TAB ID of table
-- i_row_tag: Tag/name of row
-- i_col_name: Name of column to modify
-- @return value stored in given column
------------------------------------------------------------------------------
function tab#row_val(
i_tab_id id mem_tab
,i_row_tag text
,i_col_name text
) return text
is
begin
return row#val(mem_tab(i_tab_id).row(i_row_tag), i_col_name);
exception
when others then
session.raise_fa_err('tab#row_val(' || i_tab_id || ',' || i_row_tag || ',' || i_col_name || ')');
end tab#row_val;
------------------------------------------------------------------------------
-- @description Set a text value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to modify
-- i_val: Value to be set
------------------------------------------------------------------------------
procedure row#set_text_val(
i_row_id id mem_tab_row
,i_col_name text
,i_val text
)
is
begin
with mem_tab_row(i_row_id) as row do
row.fld(i_col_name).text_val := i_val;
end with;
exception
when others then
session.raise_fa_err('row#set_text_val(' || i_row_id || ',' || i_col_name || ',' || i_val || ')');
end row#set_text_val;
------------------------------------------------------------------------------
-- @description Get a text value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to read
-- @return Value of column
------------------------------------------------------------------------------
function row#text_val(
i_row_id id mem_tab_row
,i_col_name text
) return text
is
begin
with mem_tab_row(i_row_id) as row do
return row.fld(i_col_name).text_val;
end with;
exception
when others then
session.raise_fa_err('row#text_val(' || i_row_id || ',' || i_col_name || ')');
end row#text_val;
------------------------------------------------------------------------------
-- @description Set a date value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to modify
-- i_val: Value to be set
------------------------------------------------------------------------------
procedure row#set_date_val(
i_row_id id mem_tab_row
,i_col_name text
,i_val date
)
is
begin
with mem_tab_row(i_row_id) as row do
row.fld(i_col_name).date_val := i_val;
end with;
exception
when others then
session.raise_fa_err('row#set_date_val(' || i_row_id || ',' || i_col_name || ',' || i_val || ')');
end row#set_date_val;
------------------------------------------------------------------------------
-- @description Get a ID value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to read
-- @return Value of column
------------------------------------------------------------------------------
function row#date_val(
i_row_id id mem_tab_row
,i_col_name text
) return date
is
begin
with mem_tab_row(i_row_id) as row do
return row.fld(i_col_name).date_val;
end with;
exception
when others then
session.raise_fa_err('row#date_val(' || i_row_id || ',' || i_col_name || ')');
end row#date_val;
------------------------------------------------------------------------------
-- @description Set a number value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to modify
-- i_val: Value to be set
------------------------------------------------------------------------------
procedure row#set_nr_val(
i_row_id id mem_tab_row
,i_col_name text
,i_val number
)
is
begin
with mem_tab_row(i_row_id) as row do
row.fld(i_col_name).number_val := i_val;
end with;
exception
when others then
session.raise_fa_err('row#set_nr_val(' || i_row_id || ',' || i_col_name || ',' || i_val || ')');
end row#set_nr_val;
------------------------------------------------------------------------------
-- @description Get a number value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to read
-- @return Value of column
------------------------------------------------------------------------------
function row#nr_val(
i_row_id id mem_tab_row
,i_col_name text
) return number
is
begin
with mem_tab_row(i_row_id) as row do
return row.fld(i_col_name).number_val;
end with;
exception
when others then
session.raise_fa_err('row#nr_val(' || i_row_id || ',' || i_col_name || ')');
end row#nr_val;
------------------------------------------------------------------------------
-- @description Set a boolean value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to modify
-- i_val: Value to be set ("+"/"-")
------------------------------------------------------------------------------
procedure row#set_bool_val(
i_row_id id mem_tab_row
,i_col_name text
,i_val text
)
is
begin
with mem_tab_row(i_row_id) as row do
row.fld(i_col_name).bool_val := i_val;
end with;
exception
when others then
session.raise_fa_err('row#set_bool_val(' || i_row_id || ',' || i_col_name || ',' || i_val || ')');
end row#set_bool_val;
------------------------------------------------------------------------------
-- @description Get a boolean value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to read
-- @return Value of column
------------------------------------------------------------------------------
function row#bool_val(
i_row_id id mem_tab_row
,i_col_name text
) return text
is
begin
with mem_tab_row(i_row_id) as row do
return row.fld(i_col_name).bool_val;
end with;
exception
when others then
session.raise_fa_err('row#bool_val(' || i_row_id || ',' || i_col_name || ')');
end row#bool_val;
------------------------------------------------------------------------------
-- @description Set a ID value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to modify
-- i_val: Value to be set
------------------------------------------------------------------------------
procedure row#set_id_val(
i_row_id id mem_tab_row
,i_col_name text
,i_val number
)
is
begin
with mem_tab_row(i_row_id) as row do
row.fld(i_col_name).id_val := i_val;
end with;
exception
when others then
session.raise_fa_err('row#set_id_val(' || i_row_id || ',' || i_col_name || ',' || i_val || ')');
end row#set_id_val;
------------------------------------------------------------------------------
-- @description Get a ID value for the specified column
-- @input i_row_id: ID of row
-- i_col_name: Name of column to read
-- @return Value of column
------------------------------------------------------------------------------
function row#id_val(
i_row_id id mem_tab_row
,i_col_name text
) return number
is
begin
with mem_tab_row(i_row_id) as row do
return row.fld(i_col_name).id_val;
end with;
exception
when others then
session.raise_fa_err('row#id_val(' || i_row_id || ',' || i_col_name || ')');
end row#id_val;
------------------------------------------------------------------------------
-- @description Sort the content of the table along a specific column
-- @input i_tab_id: MEM_TAB-ID of table
-- i_col_name: Name of column to modify
-- i_desc: Sort ascending or descending (default is ascending => '-')
-- i_comp_numeric: Compare values as string or as number (default '-')
-- i_row_offset: Start sorting at this row offset (default 0)
------------------------------------------------------------------------------
procedure tab#sort(
i_tab_id id mem_tab
,i_col_name text
,i_desc text := '-'
,i_comp_numeric text := '-'
,i_row_offset number := 0
)
is
begin
with mem_tab(i_tab_id) as tab do
require(i_col_name is not null, 'Specified column ' || i_col_name || ' has no name.');
tab.sort(
col => tab.col_list(
col_name => i_col_name
,seq_nr => 1
)
,desc => i_desc
,comp_numeric => i_comp_numeric
,row_offset => i_row_offset
);
end with;
exception
when others then
session.raise_fa_err('tab#sort(' || i_tab_id || ',' || i_col_name || ')');
end tab#sort;
------------------------------------------------------------------------------
-- @description Dump the content of the table to the log
-- @input i_tab_id: MEM_TAB-ID of table
------------------------------------------------------------------------------
procedure tab#dump(
i_tab_id id mem_tab
,i_context text := null
)
is
l_col_type text;
begin
with new buf as b do
with mem_tab(i_tab_id) as tab do
-- Put header
b.put("tag");
for col in tab.col_list loop
b.put("," || col.name || " (" || coalesce(col.base_type, col.domn || " " || col.tab_name) || ")");
end loop;
b.put(util.rtn);
--
-- Put rows
for row in tab.row_list loop
b.put(row.name);
for fld in row.fld_list loop
l_col_type := fld.col.base_type;
if l_col_type = c_col_type_text then
b.put(",""" || fld.text_val || """");
elsif l_col_type = c_col_type_number then
b.put("," || fld.number_val);
elsif l_col_type = c_col_type_date then
b.put("," || fld.date_val);
elsif l_col_type = c_col_type_long then
b.put("," || fld.text_val);
elsif l_col_type = c_col_type_boolean then
b.put("," || fld.bool_val);
elsif l_col_type = c_col_type_time_tz then
b.put("," || fld.date_val);
else
b.put("," || fld.id_val);
end if;
end loop;
b.put(util.rtn);
end loop;
session.log_mgr.info(coalesce(i_context, 'Table ' || tab.name), b.text);
end with;
end with;
end tab#dump;
-- function mem_tab2mem_list(i_tab_id id mem_tab, i_col_name text)
-- return number is l_mem_tab2mem_list number; begin
-- with mem_tab(i_tab_id) as l_tab do
-- with new mem_db_list_nr as l_list do
-- for l_row in l_tab.row_list loop
-- l_list.item_list.add(l_row.fld(i_col_name).number_val);
-- end loop;
-- l_list.store_list; l_mem_tab2mem_list := l_list.list_nr_id;
-- end with;
-- end with;
-- return l_mem_tab2mem_list;
-- end mem_tab2mem_list;
--
-- procedure test is begin
---- with new mem_tab2mem_list(mem_tab.tab_id('rbsc$rel$rdr$as$ctx$as'), 'cdcd') as l_list do
-- with new mem_db_list_nr(mem_tab2mem_list(mem_tab.tab_id('rbsc$rel$rdr$as$ctx$as'), 'cdcd')) as l_list do
-- null;
-- end with;
-- end test;
---------------------------------------------------------------------------
-- This function prints the data in i_mem_tab based on the column/table
-- information in i_meta_mem_tab.
---------------------------------------------------------------------------
private function tab#log#print(
i_mem_tab mem_tab
,i_meta_mem_tab mem_tab
) return text
is
l_return text;
l_error text;
l_table_length number := 0;
l_line text;
l_value_line text;
l_column_name text := null;
l_column_type text := null;
l_column_length number := 0;
l_column_text text := null;
b_is_first boolean := true;
l_column_type_short text := null;
begin
------------------------------------------------------------------------------
for meta_row in i_meta_mem_tab.row_list loop
l_table_length := l_table_length + meta_row.fld('column_length').number_val;
end loop;
-- 3 char for each middle column separator -> ' | ' and +2 for start and end (outer borders will be added separately)
l_table_length := (l_table_length + (i_meta_mem_tab.row_list.count - 1) * 3) + 2;
for iterate in 1 .. l_table_length loop
l_line := l_line || '*';
end loop;
l_line := '|' || l_line || '|';
-- main debug message
l_return := 'mem_tab dumped: name=' || i_mem_tab.name || ', id=' || i_mem_tab.id || ', rows=' || i_mem_tab.row_list.count;
------------------------------------------------------------------------------
with new buf as b do
b.put(l_line);
b.put(util.rtn);
------------------------------------------------------------------------------
-- table titles/header
l_column_text := null;
l_value_line := null;
b_is_first := true;
for meta_mem_tab_row in i_meta_mem_tab.row_list loop
l_column_name := meta_mem_tab_row.fld('column_name').text_val;
l_column_type := meta_mem_tab_row.fld('column_type').text_val;
l_column_length := meta_mem_tab_row.fld('column_length').number_val;
case l_column_type
when 'text' then
l_column_type_short := 'T';
when 'number' then
l_column_type_short := 'N';
when 'date' then
l_column_type_short := 'D';
when 'boolean' then
l_column_type_short := 'B';
when 'id' then
l_column_type_short := 'I';
else
l_column_type_short := '?';
end case;
l_column_text := rpad(l_column_name || ' (' || l_column_type_short || ')', l_column_length, ' ');
if b_is_first then
l_value_line := '| ' || l_column_text;
b_is_first := false;
else
l_value_line := l_value_line || ' | ' || l_column_text;
end if;
end loop;
b.put(l_value_line || ' |');
b.put(util.rtn);
------------------------------------------------------------------------------
-- table contents/rows
for mem_tab_row in i_mem_tab.row_list loop
l_value_line := null;
b_is_first := true;
for meta_mem_tab_row in i_meta_mem_tab.row_list loop
l_column_name := meta_mem_tab_row.fld('column_name').text_val;
l_column_type := meta_mem_tab_row.fld('column_type').text_val;
l_column_length := meta_mem_tab_row.fld('column_length').number_val;
case l_column_type
when 'text' then
l_column_text := mem_tab_row.fld(l_column_name).text_val;
when 'number' then
l_column_text := to_char(mem_tab_row.fld(l_column_name).number_val);
when 'date' then
l_column_text := to_char(mem_tab_row.fld(l_column_name).date_val);
when 'boolean' then
l_column_text := to_char(mem_tab_row.fld(l_column_name).bool_val);
when 'id' then
l_column_text := to_char(mem_tab_row.fld(l_column_name).id_val);
else
l_error := l_error || ' "ERROR -> print column type not found: ' || l_column_type || '"';
end case;
if l_column_type in ('number', 'date', 'boolean', 'id') then
l_column_text := lpad(nvl(l_column_text, ' '), l_column_length, ' ');
else
l_column_text := rpad(nvl(l_column_text, ' '), l_column_length, ' ');
end if;
if b_is_first then
l_value_line := '| ' || l_column_text;
b_is_first := false;
else
l_value_line := l_value_line || ' | ' || l_column_text;
end if;
end loop; -- lookup each field with mem_tab row names
b.put(l_value_line || ' |');
b.put(util.rtn);
end loop; -- loop mem_tab rows
b.put(l_line);
session.log_mgr.debug(l_return || l_error, b.text);
end with;
return l_return || l_error;
end tab#log#print;
-------------------------------------------------------------------------------------------------------------
-- This procedure should not be called directly, instead via RBSC$LOG.
-- It will be displayed like this in the text field of the log entry:
-- |*************************************************************************|
-- | Column1 (Col_Type) | Race (T) | Age (N) | Date_Test (D) | Bool_Test (B) |
-- | Text | Text | 6518 | DD.MM.YY | - |
-- | Sam | Hobbits | 39 | 01.09.15 | + |
-- |*************************************************************************|
-- where col_type:
-- - T: text
-- - N: number
-- - D: date
-- - B: boolean
-------------------------------------------------------------------------------------------------------------
procedure tab#log(
i_mem_tab_id id mem_tab
)
is
l_mem_tab_id number := i_mem_tab_id;
l_return text;
l_meta_mem_tab_id number;
l_row_iter number := 0;
l_row_id number := 0;
l_column_name_iter text := null;
l_column_type_iter text := null;
l_column_length number := 0;
l_column_max_length number := 0;
begin
if l_mem_tab_id is null then
session.log_mgr.debug('ERROR -> l_mem_tab_id is null');
end if;
-- load mem_tab
with mem_tab(l_mem_tab_id) as mem_tab do
-- create meta_mem_tab
if mem_tab.col_list.count > 0 then
if tab.tab('rbsc_meta_mem_tab') is not null then
tab.remv('rbsc_meta_mem_tab');
end if;
l_meta_mem_tab_id := tab.new('rbsc_meta_mem_tab');
with mem_tab(l_meta_mem_tab_id) as meta_mem_tab do
meta_mem_tab.cols.add_base(
col_name => 'column_name'
,base_type => 'text'
);
meta_mem_tab.cols.add_base(
col_name => 'column_type'
,base_type => 'text'
);
meta_mem_tab.cols.add_base(
col_name => 'column_length'
,base_type => 'number'
);
-- fill meta_mem_tab with header length
for mem_tab_col in mem_tab.col_list loop
l_row_iter := l_row_iter + 1;
l_row_id := meta_mem_tab.add_row(to_char(l_row_iter));
with mem_tab_row(l_row_id) as row do
row.fld('column_name').text_val := mem_tab_col.name;
row.fld('column_type').text_val := mem_tab_col.base_type;
row.fld('column_length').number_val := length(mem_tab_col.name) + 4; -- 4 chars for type in header 'Column_Name (T)'
end with;
end loop;
-- fill meta_mem_tab with field length
l_row_iter := 0;
for mem_tab_row in mem_tab.row_list loop
for meta_mem_tab_row in meta_mem_tab.row_list loop
l_column_name_iter := meta_mem_tab_row.fld('column_name').text_val;
l_column_type_iter := meta_mem_tab_row.fld('column_type').text_val;
l_column_max_length := meta_mem_tab_row.fld('column_length').number_val;
case l_column_type_iter
when 'text' then
l_column_length := length(mem_tab_row.fld(l_column_name_iter).text_val);
if nvl(l_column_length, 0) > nvl(l_column_max_length, 0) then
meta_mem_tab_row.fld('column_length').number_val := l_column_length;
end if;
when 'number' then
l_column_length := length(mem_tab_row.fld(l_column_name_iter).number_val);
if nvl(l_column_length, 0) > nvl(l_column_max_length, 0) then
meta_mem_tab_row.fld('column_length').number_val := l_column_length;
end if;
when 'date' then
l_column_length := length(mem_tab_row.fld(l_column_name_iter).date_val);
if nvl(l_column_length, 0) > nvl(l_column_max_length, 0) then
meta_mem_tab_row.fld('column_length').number_val := l_column_length;
end if;
when 'boolean' then
l_column_length := length(mem_tab_row.fld(l_column_name_iter).bool_val);
if nvl(l_column_length, 0) > nvl(l_column_max_length, 0) then
meta_mem_tab_row.fld('column_length').number_val := l_column_length;
end if;
when 'id' then
l_column_length := length(mem_tab_row.fld(l_column_name_iter).id_val);
if nvl(l_column_length, 0) > nvl(l_column_max_length, 0) then
meta_mem_tab_row.fld('column_length').number_val := l_column_length;
end if;
else
session.log_mgr.debug('ERROR mem_tab(' || mem_tab.name || ') -> l_column_type_iter not found: ' || l_column_type_iter);
end case;
end loop; -- lookup each field with mem_tab row names
end loop; -- loop mem_tab rows
-- print table with values
l_return := tab#log#print(
i_mem_tab => l_mem_tab_id
,i_meta_mem_tab => l_meta_mem_tab_id
);
end with; -- meta_mem_tab
else
session.log_mgr.debug('ERROR mem_tab(' || mem_tab.name || ') -> mem_tab has no columns');
end if; -- has columns
end with; -- mem_tab
exception
when others then
session.log_mgr.debug('ERROR');
end tab#log;
end rbsc$tab;
