Main FTS Pages |
---|
FtsRelease22 |
Install |
Configuration |
Administration |
Procedures |
Operations |
Development |
Previous FTSes |
FtsRelease21 |
FtsRelease21 |
All FTS Pages |
FtsWikiPages |
Last Page Update |
Main.unknown 2008-03-28 |
README
to explain what it does.
FTS_REGISTER
with associated schema FTS_PLUGIN
and FTS_PLUGIN_SCHEMA
.
Every package must register itself using the FTS_REGISTER
package.
For each registered package there will be:
FTS_PLUGIN
plugin describing the package and its schema dependencies.
FTS_PLUGIN_SCHEMA
describing the associated schema version for the package.
NULL
if no restriction
NULL
if no restriction
NULL
if no restriction
NULL
if no restriction
major.minor.patch
format, numbers only.
e.g. 1.0.5
is valid. 2.0.3-4
and 2.1
are not valid.
FTS_REGISTER
functionality.
In particular:
It should implement the register 'interface':
registerMe
method that will register the package in the FTS_PLUGIN
table using the fts_register.registerPackage( .. )
method.
fts_register.registerSchema( .. )
method to register the schema version in the FTS_PLUGIN_SCHEMA
table.
fts_register.checkPackage('MODULENAME')
This will check that the package has been registered and that the schema versions are compatible, and will not allow the package to run if this is not the case.
g_packageName varchar2(100) := 'FTS_SERVICESTATE'; g_description varchar2(1024) := 'FTS service state information collection'; g_author varchar2(100) := 'gavin.mccance@cern.ch'; g_packageVersion varchar2(10) := '1.0.0'; g_requirePackageSchemaMin varchar2(10) := '1.0.0'; g_requirePackageSchemaMax varchar2(10) := NULL; g_requireCoreSchemaMin varchar2(10) := '3.1.0'; g_requireCoreSchemaMax varchar2(10) := NULL; g_requireMaxDbState varchar2(15) := 'TEST';and add the registerMe method:
procedure registerMe;
procedure registerMe as begin fts_register.registerPackage( g_packageName, g_description, g_author, g_packageVersion, g_requirePackageSchemaMin, g_requirePackageSchemaMax, g_requireCoreSchemaMin, g_requireCoreSchemaMax, g_requireMaxDbState ); commit; END registerMe;You should also add, at the top of every public method a call to check the package validity, e.g:
procedure countStates as begin fts_register.checkPackage(g_packageName); execute immediate(' : : end countStates;
FTS_PLUGIN_SCHEMA
.
CREATE TABLE m_agent_avail ( -- id : : -- register schema exec fts_register.registerSchema('FTS_SERVICESTATE', '1.0.0');The name should match the package name, upper case.
FTS_PLUGIN_SCHEMA
table, with NULL
schema version.
Add this into your registerMe function, in this case:
procedure registerMe as begin fts_register.registerPackage( g_packageName, g_description, g_author, g_packageVersion, g_requirePackageSchemaMin, g_requirePackageSchemaMax, g_requireCoreSchemaMin, g_requireCoreSchemaMax, g_requireMaxDbState ); fts_register.registerSchema(g_packageName, NULL); commit; END registerMe;
submit_job
method which will submit a DBMS job with a given periodicity in minutes (which should have a reasonable default).
runjob
method which is called by DBMS every time the job is run.
-- The job 'interface' procedure runjob; procedure submit_job (v_minutes binary_integer DEFAULT 1); procedure stop_job;
-- submit the DBMS job procedure submit_job (v_minutes binary_integer) as njob USER_JOBS.JOB%TYPE; begin fts_register.checkPackage(g_packageName); dbms_job.submit(njob, 'begin fts_servicestate.runjob; end;', sysdate, 'SYSDATE + '||to_char(v_minutes)||'/1440'); end submit_job; -- delete the DBMS job procedure stop_job as v_jobid USER_JOBS.JOB%TYPE; begin fts_register.checkPackage(g_packageName); select JOB into v_jobid from user_jobs where what like '%fts_servicestate.runjob%'; dbms_job.remove(v_jobid); end stop_job; -- run the DBMS job procedure runjob as begin -- do whatever is needed, for example: agentStates; countStates; end runjob;
dbms_output.put_line
and exit the script, rolling back any changes as necessary.
It is recommended to catch all other unexpected exceptions and log them using a dbms_output.put_line
and then rethrow them, either as they are or wrapped in a application exception type (set the exception propagation to TRUE
). This way the command line gets a printed log plus a detailed exception of explain where the unexpected problem was.
Example code to catch unexpected exceptions is here:
E_FATAL EXCEPTION; PRAGMA EXCEPTION_INIT(E_FATAL, -20100); : : procedure testRandom as begin dothestuff; exception when OTHERS then rollback; dbms_output.put_line('Error: an unexpected error occurred.'); raise_application_error (-20100, 'Error: an unexpected error occurred', TRUE); end testRandom;
-- Error table CREATE TABLE m_servicestate_err ( LOGTIME TIMESTAMP(6) WITH TIME ZONE, ROUTINE VARCHAR(20), ERR_CODE NUMBER, ERR_MSG VARCHAR2(2048) );with associated function in the package:
-- Log any errors procedure log_error ( routine varchar, errcode number, errmessage varchar) as PRAGMA AUTONOMOUS_TRANSACTION; begin execute immediate('insert into M_SERVICESTATE_ERR (LOGTIME, ROUTINE, ERR_CODE, ERR_MSG) VALUES (:1, :2, :3, SUBSTR(:4, 1, 2048))') using systimestamp, routine, errcode, errmessage; end log_error;The error handling should roll back any transactions, log the error and then exit. Example code is:
procedure agentStates as begin fts_register.checkPackage(g_packageName); dothestuff; commit; exception when OTHERS then rollback; log_error('agentStates', SQLCODE, SQLERRM); end agentStates;This way the job will continue to run but will log any errors in the timestamped error table. Your application or procedures should check this table regularly.
g_
.
Global private variables, those declared in the package body should be prefixed with gp_
.
Parameter variables (i.e. passed into a procedure or function) should be prefixed with p_
.
Procedure local variables should be prefixed with l_
.
Record types should be prefixed with _rec
.
%TYPE
directive should be used when defining variables for the destination of a select .. into
. This makes the code more resilient to schema changes. e.g:
procedure stop_job
as
l_jobid USER_JOBS.JOB%TYPE;
begin
fts_register.checkPackage(g_packageName);
select JOB into l_jobid from user_jobs where what like '%fts_servicestate.runjob%';
dbms_job.remove(l_jobid);
end stop_job;
rec_plugin FTS_PLUGIN%ROWTYPE; : : select F.packageName, F.description, F.author,F.dateAdded, F.packageVersion, F.requirePackageSchemaMin, F.requirePackageSchemaMax, F.requireCoreSchemaMin, F.requireCoreSchemaMax, F.requireMaxDbState INTO rec_plugin FROM FTS_PLUGIN F WHERE F.packageName = l_packageName;
FTS_WRITER_ACCOUNT
where the purpose is clearly to grant access to another schema.
registerSchema( .. )
method call. This merges, so you will not create duplicate entries.
registerPackage( .. )
method call. This merges, so you will not create duplicate entries.
select fts_timing.t_ms( timestamp ) from dual
which returns the number of millseconds since the UTC epoch (midnight Jan 1st 1970).
and:
select fts_timing.t_s( timestamp ) from dual
which returns the rounded number of seconds.
These functions can be used for timing purposes to measure how long a given query or function is taking to run.