Accessing Oracle Report's Object Model

[PaulCoop]

Is there a way to access from pl/sql, in a in-built report tigger, the report object, as displayed in the object navigator, to read off the properties of selected object without already knowing what those objects are called? (Particularly the user parameters.)

What I am wanting to do is write some standardized code that can be cut and paste into the "after report" trigger that is able to insert a record containing the report name, user, time-stamp and the list of parameters and their values into a table. This way report usage can be monitored and reported on as well.

Is this at all possible without changing the code each time to reflex the differing parameters used?

Paul Cooper

 

[nagornyi]

This is not possible the direct way you describe. We did something very close to what you need. Instead of pasting the code, we used call to stored procedure that created record in the DB. The procedure was in the template, so users did not have to add it manually. They had to copy and paste names of parameters, however...

 

[PaulCoop]

Doing it as a called procedure is a sensible concept but by putting in a stored procedure means I need a peice of generic code or a different stored procedure for each report.

If I create a table with the list of bind parameter names against the report name I in theory should be able to use a dynamic SQL (method 4) to create the INSERT statement I need.

I tried using DBMS_SQL to grab the bind variable using code copied from O'Reilly's Oracle Built-in Packages which I had to complete. Here is the code:

Val (var_in IN VARCHAR2) RETURN VARCHAR2 IS
   retval VARCHAR2(2000);
   cur BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
   fdbk BINARY_INTEGER;   
BEGIN
   SYS.DBMS_SQL.PARSE 
      (cur, 'BEGIN :val := ' || var_in || '; END;', DBMS_SQL.NATIVE);
   SYS.DBMS_SQL.BIND_VARIABLE (cur, 'val', 'a', 2000);
   fdbk := SYS.DBMS_SQL.EXECUTE (cur);
   SYS.DBMS_SQL.VARIABLE_VALUE (cur, 'val', retval);
   RETURN retval;
END;

This didn't seem to work. I would get plenty of errors:

REP-1401: 'cf_sqlformula': Fatal PL/SQL error occured
ORA-01008: not all variables bound
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1120
ORA-06512: at "SYS.DBMS_SQL", line 323
ORA-06512: at "REPDEV.VAL", line 9

I am total confused over bind variables and their scope. Is there any sensible way to get the bind variable value if you have the name as a string?

FYI I trying to save the bind variable values from a Oracle 6i report to a table without handcrafting the code for each report. I have placed the bind variable names in another table and I have the aim of calling a single procedure (passing the report name) which loook up the bind variable names from a table; grabs the values of these variables; builds a string with this structure: "variable name1 := variable value1 variable name2 :=..."; and insert this it another table along with the name of the report, username and time stamp. All this is done with the idea of monitoring report usage.

Paul Cooper

PS. The database is Oracle 9.2.

 

[nagornyi]

We tried to keep it simple. Our procedure can accept up to 10 parameters. Report developers have to type in parameter names two times: one for the name, one for the value. We didn't fine the way to do it so that report developer wouldn't have to touch the procedure call.