Having
Discoverer Worksheets Write-Back To The
Database
Looking to have Discoverer
write back information to the database
whenever a report is run? According to
"Discoverer Administrator?How to Perform
DML Operations in Discoverer?", the
answer is to embed your update, insert
or select statement within a function,
declare the function as an autonomous
transaction, and add that as an item in
your worksheet.
Oracle Autonomous Transactions
are 'subtransactions' that can take
place independently of any transaction
that calls it. Functions that are called
from Discoverer normally can't perform
any DDL, but if you declare them as
autonomous transactions by using the
Pragma
autonomous_transaction directive
they'll get through.
For example, you might
have a report that lists out salespeople
and their total sales this quarter. You
want to log in an audit table the person
who ran the report, and what salespeople
they displayed in their report. You
could then build a function like this:
create or replace function f_report_audit
(
user varchar2 varchar2(20),
salesperson_id varchar2(20),
sales_this_quarter number(11,2),
when_dt date)
return varchar2
is
pragma
autonomous_transaction;
begin
insert into
Audit_report
values(audit_seq.nextval,
user,
salesperson_id,
sales_per_quarter,
sysdate);
commit;
return null;
end;
You would then register
this function within Discoverer
Administrator, create an item based on
this function and supply the necessary
parameters (from the other items in the
worksheet, plus the 'User' variable that
returns the logged-in userid), then add
the item to the report. As the function
returns a null, there'll be nothing
added to the report, but the key values
in the report will now be written to the
database alongside the userid and time
the report was run.
Insert into
Oracle from Discoverer Worksheets.
and include the URL for the page.
