Taal: PL/SQL
Doel: Converteren van een kolom in een tabel naar EURO (in dit geval vanaf BEF)
Usage: exec pck_convert(<<tabelnaam>>,<<kolomnaam>>,<<sleutel1>>,<<optional: sleutel2>>,<<optional: sleutel 3>>);
rem Title:pck_log.sql
rem Version: 1.0
rem Last update date: 04-DEC-01
rem Purpose: This package is used as a logging package for the euro conversion.
rem It contains a procedure for cleaning the log.
rem The procedure log exists more than once. This is a technique called
rem overloading. Oracle will decide automatically which procedure fits
rem by checking the parameters.
rem Dependencies: The eur_log table has to be created
rem Parameters: 1. username
rem 2. password
rem 3. database
rem
rem History:
rem 04-DEC-01 JLCreation of this script
rem
prompt define parameters as follows
prompt [1] user [2] password [3] database
define p_username = '&1'
define p_password = '&2'
define p_database = '&3'
connect &p_username/&p_password@&p_database
create or replace package pck_log
is
procedure clear_log
(p_scriptname eur_log.scriptname%type);
--
procedure log
( p_scriptname eur_log.scriptname%type
, p_notes eur_log.notes%type);
--
procedure log
( p_scriptname eur_log.scriptname%type
, p_tablename eur_log.table_name%type
, p_columnname eur_log.column_name%type
, p_type eur_log.type%type
, p_notes eur_log.notes%type);
--
end pck_log;
/
show errors
create or replace package body pck_log
is
--
procedure clear_log
(p_scriptname eur_log.scriptname%type)
is
begin
--
delete from eur_log
where scriptname = p_scriptname;
--
exception
when others then
log (p_scriptname,'Cleaning log failed : ' || substr (sqlerrm, 1, 100));
end;
--
procedure log
(p_scriptname eur_log.scriptname%type
,p_notes eur_log.notes%type)
is
begin
insert
into eur_log
( timestamp
, scriptname
, table_name
, column_name
, type
, notes)
values
( sysdate
, p_scriptname
, 'NONE'
, 'NONE'
, 'FAIL'
, p_notes);
end;
procedure log
(p_scriptname eur_log.scriptname%type
,p_tablename eur_log.table_name%type
,p_columnname eur_log.column_name%type
,p_type eur_log.type%type
,p_notes eur_log.notes%type)
is
begin
--
insert
into eur_log
( timestamp
, scriptname
, table_name
, column_name
, type
, notes)
values
( sysdate
, p_scriptname
, p_tablename
, p_columnname
, p_type
, p_notes);
--
end log;
--
end pck_log;
/
rem Title: pck_convert.sql
rem Version: 1.4
rem Last Update Date: 12-DEC-01
rem Author: Jacco Landlust
rem Purpose: This package is used for converting data to euro.
rem The package works as follows:
rem 1. clear log
rem 2. build pl/sql block dynamicly according to the input
rem parameters
rem 3. execute code
rem ad1. clearing the log is done by the log-handling package
rem called pck_log.
rem ad2. the pl/sql block is build by different functions.
rem for each part of the pl/sql block a seperate function
rem exists. This way small changes on this block are easier
rem to implement.
rem ad3. executing the code is done through native pl/sql, using
rem the execute immediate function. (EXECUTE IMMEDIATE <<CODE>>)
rem this is only possible in Oracle 8.0 databases or newer.
rem If this package is needed in older Oracle databases,
rem a exec_sql function should be build, using
rem the dbms_sql.exec_sql function to execute the code.
rem
rem = Short description of the dynamicly build PL/SQL block =
rem The "header" of the sql-block is the variable definition
rem also a exception and a pragma exeception init get defined
rem this exception will handle the oracle error "snapshot too old"
rem This error is a well know error that occurs while using cursors
rem with a lot of rows that get updated.
rem Cursor definition depends on the keys that are given into
rem the procedure.
rem The body part of the pl/sql block is the part where all the
rem "action" is going on. Values get divided by the euro constant
rem that is defined in the package. Also logging of the rounding
rem differences and the update of the data happenes here. The
rem global constant gc_test defines whether data actually gets
rem updated, or if the outcome is written to the logtable (test
rem conversion). The only tricky part in the body is the snapshot
rem too old handling. If the cursor breaks and comes back with the
rem snapshot too old message, the pl/sql block rolls back to the last
rem savepoint. The cursor is getting rebuild from the point where it
rem broke, and it will continue automaticly.
rem The footer is the logging and exception handling part of the code.
rem
rem Paramaters: 1. username
rem 2. password
rem 3. database
rem
rem History:
rem 04-DEC-01 JL Creation of this document
rem 05-DEC-01 JL Added the pk's as parameters. The primary keys help the script
rem to identify the record.
rem 05-DEC-01 JL Replaced the exec_sql procedure (uses dbms_sql package) with
rem execute immediate (native). Also split up the build_code
rem function into different functions, to make additions easier.
rem 06-DEC-01 JL Added changes for changed logging
rem 12-DEC-01 JL Introduced gc_test constant, to be able to run this package on a
rem production environment. The outcome will be inserted into a log
rem table, instead of updating the "to be converted" table.
rem
prompt define parameters as follows
prompt [1] username [2] password [3] database
define p_usr = '&1'
define p_psw = '&2'
define p_db = '&3'
set verify off
whenever sqlerror exit failure
connect &p_usr/&p_psw@&p_db
create or replace package pck_convert is
procedure to_euro
( p_tablename all_tab_columns.table_name%type
, p_columnname all_tab_columns.column_name%type
, p_pk1 all_tab_columns.column_name%type
, p_pk2 varchar2 default null
, p_pk3 varchar2 default null);
function build_code
( p_tablename all_tab_columns.table_name%type
, p_columnname all_tab_columns.column_name%type
, p_pk1 all_tab_columns.column_name%type
, p_pk2 varchar2 default null
, p_pk3 varchar2 default null)
return varchar2;
end pck_convert;
/
show errors
create or replace package body pck_convert
is
--
-- Global variable definition
--
gv_script varchar2(30) := 'pck_convert';
gv_eur_value number := 40.3399;
gc_test boolean := false;
function build_header
( p_tablename all_tab_columns.table_name%type
, p_columnname all_tab_columns.column_name%type
, p_pk1 all_tab_columns.column_name%type
, p_pk2 varchar2 default null
, p_pk3 varchar2 default null)
return varchar2
is
-- Purpose: this function will build the first part of the pl/sql block
-- that is used for conversion to euro.
--
-- local variable definitions
--
lv_statement varchar2(10000);
begin
lv_statement := 'declare ' ||
' gv_euro_value number; ' ||
' lv_script eur_log.scriptname%type; ' ||
' lv_new_value number; ' ||
' lv_pk1 ' || p_tablename || '.' || p_pk1 || '%type; ';
-- if there is only 1 primary key, the other key variable usages are not need
if p_pk2 is not null then
lv_statement := lv_statement || ' lv_pk2 ' || p_tablename || '.' || p_pk2 || '%type; ';
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' lv_pk3 ' || p_tablename || '.' || p_pk3 || '%type; ';
end if;
lv_statement := lv_statement ||
' lv_rounding_difference number; ' ||
' lv_rows_processed pls_integer; ' ||
' lv_wrong pls_integer; ' ||
' lv_valid_snapshot boolean; ' ||
' snapshot_too_old exception; ' ||
' pragma exception_init (snapshot_too_old, -1555); ';
return (lv_statement);
exception
when others then
pck_log.log (gv_script || '.build_header',p_tablename, p_columnname, 'Fail: ', substr (sqlerrm, 1, 100));
end build_header;
function build_cursor
( p_tablename all_tab_columns.table_name%type
, p_columnname all_tab_columns.column_name%type
, p_pk1 all_tab_columns.column_name%type
, p_pk2 varchar2 default null
, p_pk3 varchar2 default null)
return varchar2
is
-- Purpose: This function is used for building the cursor that is used in
-- the pl/sql block that is used for the conversion to euro.
-- local variable definitions
--
lv_statement varchar2(20000);
begin
lv_statement := ' cursor c_values ' ||
' ( cp_pk1 ' || p_tablename || '.' || p_pk1 || '%type default null ';
-- use statements for primary keys only is needed.
if p_pk2 is not null then
lv_statement := lv_statement || ' , cp_pk2 ' || p_tablename || '.' || p_pk2 || '%type default null ';
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' , cp_pk3 ' || p_tablename || '.' || p_pk3 || '%type default null ';
end if;
lv_statement := lv_statement || ') ' ||
' is ' ||
' select ' || p_columnname || ' cn ' ||
' , ' || p_pk1 || ' pk1 ';
if p_pk2 is not null then
lv_statement := lv_statement || ' , ' || p_pk2 || ' pk2 ';
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' , ' || p_pk3 || ' pk3 ';
end if;
lv_statement := lv_statement ||
' from ' || p_tablename ||
' where ' || p_pk1 || ' >= nvl (cp_pk1, ' || p_pk1 || ') ';
if p_pk2 is not null then
lv_statement := lv_statement || ' and ' || p_pk2 || ' >= nvl (cp_pk2, ' || p_pk2 || ') ';
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' and ' || p_pk3 || ' >= nvl (cp_pk3, ' || p_pk3 || ') ';
end if;
lv_statement := lv_statement ||
' order by ' || p_pk1;
if p_pk2 is not null then
lv_statement := lv_statement || ', ' || p_pk2;
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ', ' || p_pk3;
end if;
lv_statement := lv_statement || '; ';
return (lv_statement);
exception
when others then
pck_log.log (gv_script || '.build_cursor', p_tablename, p_columnname, 'Fail: ', substr (sqlerrm, 1, 100));
end build_cursor;
function build_body
( p_tablename all_tab_columns.table_name%type
, p_columnname all_tab_columns.column_name%type
, p_pk1 all_tab_columns.column_name%type
, p_pk2 varchar2 default null
, p_pk3 varchar2 default null)
return varchar2
is
-- Purpose: This function will create the body text of the pl/sql block
-- that is used for conversion to euro. All "main" converting
-- will occurr in this part.
-- local variable definitions
--
lv_statement varchar2(10000);
begin
lv_statement := 'begin ' ||
' lv_script := ' || '''' || 'pck_convert.build_code' || '''' || '; ' ||
' pck_log.clear_log (lv_script); ' ||
' commit; ' ||
' lv_rows_processed := 0; ' ||
' lv_wrong := 0; ' ||
' lv_pk1 := null; ';
-- only use key variables if needed
if p_pk2 is not null then
lv_statement := lv_statement || ' lv_pk2 := null; ';
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' lv_pk3 := null; ';
end if;
lv_statement := lv_statement ||
' lv_valid_snapshot := false; ' ||
' gv_euro_value := 40.3399; ' ||
' while lv_valid_snapshot = false loop ' ||
' lv_valid_snapshot := true; ' ||
' begin ' ||
' for r_values in c_values ' ||
' (lv_pk1 ';
if p_pk2 is not null then
lv_statement := lv_statement || ' , lv_pk2 ';
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' , lv_pk3 ';
end if;
lv_statement := lv_statement || ') ' ||
' loop ' ||
' begin ' ||
' savepoint euro_conv; ' ||
' lv_rounding_difference := 0; ' ||
' lv_new_value := r_values.cn / gv_euro_value; ' ||
' lv_pk1 := r_values.pk1; ';
if p_pk2 is not null then
lv_statement := lv_statement || ' lv_pk2 := r_values.pk2; ';
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' lv_pk2 := r_values.pk3; ';
end if;
lv_statement := lv_statement ||
' lv_rounding_difference := round (lv_new_value, 2) - lv_new_value; ' ||
' lv_new_value := round (lv_new_value, 2); ';
-- updating depends on the constant gc_test which indicates if it is a testconversion or a
-- definitive conversion.
if gc_test = true then
lv_statement := lv_statement ||
'pck_log.log (lv_script, ' || '''' || p_tablename || '''' || ', ' || '''' || p_columnname || '''' || ', ' ||
'''' || 'TEST' || '''' || ', ' || '''' || p_pk1 || ' = ' || '''' || '||' || ' lv_pk1' || '||';
if p_pk2 is not null then
lv_statement := lv_statement ||
'''' || p_pk2 || ' = ' || '''' || '||' || ' lv_pk2 ' || '|| ' ;
end if;
if p_pk3 is not null then
lv_statement := lv_statement ||
'''' || p_pk3 || ' = ' || '||' || 'lv_pk3 ' || '|| ' ;
end if;
lv_statement := lv_statement ||
'''' || ' old_value = ' || '''' || '||' || 'r_values.cn' || '||' || '''' ||
' new_value = ' || '''' || '||' || 'lv_new_value)';
else
lv_statement := lv_statement ||
' update ' || p_tablename ||
' set ' || p_columnname || ' = lv_new_value ' ||
' where ' || p_pk1 || ' = r_values.pk1 ';
if p_pk2 is not null then
lv_statement := lv_statement || ' and ' || p_pk2 || ' = ' || 'r_values.pk2 ';
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' and ' || p_pk3 || ' = ' || 'r_values.pk3 ';
end if;
end if;
lv_statement := lv_statement || '; ' ||
' pck_log.log (lv_script, ' || '''' || p_tablename || '''' ||',' || '''' || p_columnname || '''' || ',' || '''' || 'ROUND' || '''' || ',' || ' lv_rounding_difference ); ' ||
' lv_rows_processed := lv_rows_processed + 1; ' ||
' commit; ' ||
' exception ' ||
' when snapshot_too_old then ' ||
' raise snapshot_too_old; ' ||
' when others then ' ||
' rollback to euro_conv; ' ||
' pck_log.log (lv_script, ' || '''' || p_tablename || '''' || ',' || '''' || p_columnname || '''' || ',' || '''' || 'FAIL' || '''' || ',' || '''' || p_pk1 || ' = ' || '''' || '||' || 'lv_pk1 ';
if p_pk2 is not null then
lv_statement := lv_statement || ' || ' || '''' || p_pk2 || ' = ' || '''' || '||' || ' lv_pk2 ' ;
end if;
if p_pk3 is not null then
lv_statement := lv_statement || ' || ' || '''' || p_pk3 || ' = ' || '''' || '||' || ' lv_pk3 ' ;
end if;
lv_statement := lv_statement || '||' || '''' || ' ' || '''' || '||' || 'substr(sqlerrm, 1, 100)); ';
return (lv_statement);
exception
when others then
pck_log.log (gv_script || '.build_body' , p_tablename, p_columnname, 'FAIL' , substr (sqlerrm, 1, 100));
end build_body;
function build_footer
(p_tablename all_tab_columns.table_name%type
,p_columnname all_tab_columns.column_name%type)
return varchar2
is
-- Purpose: This function will build the error handling and logging part of the pl/sql block
-- that is used for converting to euro.
--
-- local variable definitions
--
lv_statement varchar2(10000);
begin
lv_statement := ' commit; ' ||
' lv_wrong := lv_wrong + 1; ' ||
' end; ' ||
' end loop; ' ||
' exception ' ||
' when snapshot_too_old then ' ||
' rollback; ' ||
' lv_valid_snapshot := false; ' ||
' end; ' ||
' end loop; ' ||
' pck_log.log (lv_script, ' || '''' || p_tablename || '''' || ',' || '''' || p_columnname || '''' || ',' || '''' || 'RP' || '''' || ',' || 'lv_rows_processed); ' ||
' pck_log.log (lv_script, ' || '''' || p_tablename || '''' || ',' || '''' || p_columnname || '''' || ',' || '''' || 'RF' || '''' || ',' || 'lv_wrong); '||
' commit; ' ||
'exception ' ||
' when others then ' ||
' pck_log.log (lv_script '|| ', ' || '''' || p_tablename || '''' || ',' || '''' || p_columnname || '''' || ',' || '''' || 'FAIL' || '''' || ',' || ' substr (sqlerrm, 1, 100)); ' ||
'end; ';
return (lv_statement);
exception
when others then
pck_log.log (gv_script || '.build_footer', p_tablename, p_columnname ,'FAIL' , substr (sqlerrm, 1, 100));
end build_footer;
function build_code
( p_tablename all_tab_columns.table_name%type
, p_columnname all_tab_columns.column_name%type
, p_pk1 all_tab_columns.column_name%type
, p_pk2 varchar2 default null
, p_pk3 varchar2 default null)
return varchar2
is
-- local variable definitions
--
lv_statement varchar2(30000);
begin
-- reset variables
lv_statement := null;
-- build code
lv_statement :=
build_header (p_tablename, p_columnname, p_pk1, p_pk2, p_pk3) ||
build_cursor (p_tablename, p_columnname, p_pk1, p_pk2, p_pk3) ||
build_body (p_tablename, p_columnname, p_pk1, p_pk2, p_pk3) ||
build_footer (p_tablename, p_columnname);
return (lv_statement);
end build_code;
procedure to_euro
( p_tablename all_tab_columns.table_name%type
, p_columnname all_tab_columns.column_name%type
, p_pk1 all_tab_columns.column_name%type
, p_pk2 varchar2 default null
, p_pk3 varchar2 default null)
is
lv_statement varchar2(30000);
begin
-- clear log
pck_log.clear_log (gv_script);
-- build code
lv_statement := build_code(p_tablename, p_columnname, p_pk1, p_pk2, p_pk3);
-- execute code
execute immediate lv_statement;
commit;
exception
when others then
pck_log.log(gv_script || '.to_euro', p_tablename, p_columnname, 'FAIL' , substr (sqlerrm, 1, 100));
commit;
end to_euro;
end pck_convert;
/
show errors
Extra toelichting
De logging package is nodig, omdat het teruggeven van foutmeldingen vanuit Native niet mogelijk is...
Egoist: A person of low taste, more interested in themselves than in me