Home » Developer & Programmer » Reports & Discoverer » Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1
icon5.gif  Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407310] Tue, 09 June 2009 09:55 Go to next message
cornwall
Messages: 36
Registered: June 2009
Member
Is it possible to capture any input parameters and write then to a table when a user runs a report in Oracle Reports 6i?

Also i need to record the Date and time that the report is run and the Report name itself ?

Kind Regards,

bradsj
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407337 is a reply to message #407310] Tue, 09 June 2009 15:37 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do that in the After Parameter Form trigger.

Parameters, as well as current date/time are easy. For the report name, check the SRW built-in package.
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407426 is a reply to message #407337] Wed, 10 June 2009 02:55 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
Do you know how to capture the user name of the person running the report and the workstation number of the pc that the report is being run from.

I have managed to capture the report name and parameter values now.

Kind Regards
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407436 is a reply to message #407426] Wed, 10 June 2009 03:20 Go to previous messageGo to next message
ADNANTARIQ_85
Messages: 18
Registered: June 2009
Location: Faisalabad
Junior Member
OK


create table "REPORTLOG" have blow columns

USER, PATH, SYSDATE, terminal_ip

now crate a procedure

PROCEDURE MenuLog (path varchar2)IS
BEGIN
INSERT INTO reportLOG VALUES(USER, PATH, SYSDATE, terminal_ip);
commit;
end

call it where you want

OK

are you satisfied ?

M. Adnan Tariq
JK Group Of companies (FSD Pakistan)

Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407520 is a reply to message #407426] Wed, 10 June 2009 06:59 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one way to do that:
select 
  user, 
  sys_context('userenv', 'ip_address') ip_address
from dual
If SYS_CONTEXT doesn't work in your Reports Builder version, create a (stored) function in the database and call it from the report.

ADNANTARIQ_85
are you satisfied ?

Can't speak for @bradsj, but - your code is bunch of nonsense.

How do you plan to create a table with those ("USER, PATH, SYSDATE") column names? (There is a way, but you didn't show it.)

What on Earth are "path" and "terminal_ip"? How can you insert something that doesn't exist?

Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407523 is a reply to message #407520] Wed, 10 June 2009 07:04 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
This is what i have coded in the After Report Trigger in case anyone needs something similar.

I decided not to audit parameter values as i require the audit to go into all of our production reports. Each report contains a different number of parameters and this would make the code different in each report.



function AfterReport return boolean is

v_report XXLBS.XXBS_AUDIT_MODULES.module_id%TYPE;
v_user XXLBS.XXBS_AUDIT_MODULES.network_user%TYPE;
v_network_pc XXLBS.XXBS_AUDIT_MODULES.network_pc%TYPE;

begin

-- Get Report ID
SRW.GET_REPORT_NAME(v_report);

-- Get Network User
Select Nvl(Translate(osuser, '?', ' '), User)
Into v_user
From v$session
Where audsid = Userenv('sessionid');

-- Get Workstation ID
Select terminal
Into v_network_pc
From v$session
Where audsid = Userenv('sessionid');

-- Add Audit Record
Insert into XXLBS.XXBS_AUDIT_MODULES
(
TIME_STAMP,
MODULE_ID,
MODULE_TYPE,
NETWORK_PC,
NETWORK_USER
)
Values
(
SYSDATE,
v_report,
'Report',
v_network_pc,
v_user
);
return (TRUE);
end;
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407533 is a reply to message #407523] Wed, 10 June 2009 07:21 Go to previous messageGo to next message
ADNANTARIQ_85
Messages: 18
Registered: June 2009
Location: Faisalabad
Junior Member
Littlefoot

you know what uses of "terminal_ip" basically you are 8i user non update senior member first search "terminal_ip" then go back listen no need reply me . i don not like taking with non technical prod user



understand Littlefoot
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407538 is a reply to message #407533] Wed, 10 June 2009 07:33 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sorry, but I don't understand.

Quote:
basically you are 8i user
Am I?

Quote:
non update senior member
Huh?

Quote:
first search "terminal_ip" then go back
Where, exactly? Reports Online Help System doesn't recognize "terminal_ip". Google search for "oracle terminal_ip" through English and Croatian pages returned 6 (six) results, none of them being useful in this issue.

Quote:
i don not like taking with non technical prod user
How do you describe "non technical prod user"? What is a "prod user"?
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407541 is a reply to message #407523] Wed, 10 June 2009 07:41 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@bradsj,

thank you for the feedback. You could, though, rewrite the whole procedure as follows - it's easier to read it:
  v_report audit_modules.module_id%TYPE;
begin
  SRW.GET_REPORT_NAME(v_report);

  Insert into AUDIT_MODULES
   (TIME_STAMP,
    MODULE_ID,
    MODULE_TYPE,
    NETWORK_PC,
    NETWORK_USER
   )
   (select 
      sysdate,
      v_report,
      'Report',
      terminal,
      Nvl(Translate(osuser, '?', ' '), User)
    from v$session
    where audsid = Userenv('sessionid')
   );

  return (TRUE);
end;
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407561 is a reply to message #407541] Wed, 10 June 2009 08:59 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
Hi Littlefoot,

Thanks for the update. Your code is more compact.

Thanks,

BRADSJ
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #408946 is a reply to message #407310] Thu, 18 June 2009 07:32 Go to previous messageGo to next message
cornwall
Messages: 36
Registered: June 2009
Member
Littlefoot,

I wonder if i could ask for some help once again....

Regarding the piece of code in the ARfterReport Trigger.

I would like to create a stored procedure on the database and then call the procedure from within the oracle report.

The procedure will just run the insert statement.

One additional problem that i have is that i cannot use the package srw to retreive the report name so presumably i would need to still get this from the srw package in the report and then pass it to the procedure ?

Kind Regards

Bradsj
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #409023 is a reply to message #408946] Thu, 18 June 2009 15:28 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's a table which will store report name and time it was executed; a stored procedure that does the job accepts report name (we'll get it from the SRW Reports built-in package):
SQL> create table test (rep_name varchar2(30), run_time date);

Table created.

SQL> create or replace procedure prc_rep_insert (par_rep_name in char) is
  2  begin
  3    insert into test (rep_name, run_time)
  4      values (par_rep_name, sysdate);
  5    commit;
  6  end;
  7  /

Procedure created.

This is the After Parameter Form trigger; it fetches report name and calls the stored procedure:
function AfterPForm return boolean is
  l_rep_name varchar2(30);
begin
  srw.get_report_name(l_rep_name);
  prc_rep_insert (l_rep_name);
  return (TRUE);
end;

After the report is executed, check contents of a table:
SQL> select rep_name, to_char(run_time, 'dd.mm.yyyy hh24:mi:ss') run_time from test;

REP_NAME                       RUN_TIME
------------------------------ -------------------
MODULE1                        18.06.2009 22:23:58

SQL>
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #409121 is a reply to message #409023] Fri, 19 June 2009 08:38 Go to previous message
cornwall
Messages: 36
Registered: June 2009
Member
Thanks Littlefoot

Thats just what i needed.

Regards

Bradsj
Previous Topic: Outline and cell merging in excel using OLE2 package
Next Topic: Cross Tab Report in discoverer
Goto Forum:
  


Current Time: Fri Apr 26 09:19:52 CDT 2024