2 Replies Latest reply: Aug 16, 2012 3:05 PM by notnovice RSS

    Using PL/SQL in OMS Reports

    notnovice
      I've written a pl/sql block report and I'd like to add it to the OMS reports.
      Are there any examples or hints on how to do this ?
      Thanks
        • 1. Re: Using PL/SQL in OMS Reports
          EricvdS
          You could take a look at MOS note: How To Define a Report With an Element Table From SQL That Uses PL/SQL Statement Type [ID 1373273.1]

          Eric
          • 2. Re: Using PL/SQL in OMS Reports
            notnovice
            Cloned canned report Oracle Database Configuration Summary.
            Removed all input constraints from query so it reads as follows:
            begin
            declare
            type cursor_type IS REF CURSOR;
            result_cursor_out CURSOR_TYPE;
            tgt_guid_in MGMT_IP_TGT_GUID_ARRAY;
            start_date_in DATE DEFAULT NULL;
            end_date_in DATE DEFAULT NULL;
            query_string VARCHAR(6000);
            begin
            result_cursor_out := ?;
            tgt_guid_in := ?;
            start_date_in := ?;
            end_date_in := ?;
            query_string :=
            'select o.database_name "DATABASE_NAME",
            o.characterset "CHARACTERSET",
            o.instance_name "INSTANCE_NAME",
            o.national_characterset "NATIONAL_CHARACTERSET",
            o.global_name "GLOBAL_NAME",
            o.log_mode "LOG_MODE",
            o.banner "BANNER",
            o.default_temp_tablespace "DEFAULT_TEMP_TABLESPACE"
            from mgmt$db_dbninstanceinfo o ';

            open result_cursor_out for query_string ;
            end;
            end;

            Original report allows target selection from a list of databases, I changed to a blank specific target, since I want an unrestricted listing.
            When I try to run, I get this error:
            Error rendering element. Exception: Closed Statement

            I've tried various ways to get around the error to no avail.
            What did I do incorrectly ?
            Thanks