Skip Headers

Oracle® Application Server Reports Services Publishing Reports to the Web 10g (9.0.4) Part Number B13673-01 |
| Home | Contents | Index

# C Batch Registering Reports in OracleAS Portal

If you have a number of reports that you wish to register in OracleAS Portal, it is often preferable to register them as a group in a batch script rather than individually in the OracleAS Portal user interface. Likewise, if you have a large number of reports that you wish to unregister, a batch script is more efficient.

- Batch Registering Report Definition Files

- Batch Removing Report Packages

- PL/SQL Batch Registering Function

## C.1 Batch Registering Report Definition Files

To batch register reports in OracleAS Portal, you need to perform the following steps:

1. Run rwconverter to Generate a SQL Script

2. Run the Script in SQL*Plus

### C.1.1 Run rwconverter to Generate a SQL Script

To generate a SQL script that you can execute in SQL*Plus to register your reports, do the following:

1. From the operating system prompt (DOS or UNIX), enter the `rwconverter` command with the keywords to batch register the report definition files.

   See Also: Appendix A, " Command Line Options" For information on the `rwconverter` keywords.

   Note: To successfully create a script file with the necessary load functions, you specify the `DTYPE`, `STYPE`, `SOURCE`, and `DEST` options. To create a functional package in OracleAS Portal, you will need to specify the `P_SERVERS`, `P_PRIVILEGE`, `P_TYPES`, `P_FORMATS` in addition to the options used to create the script file.

   Following is an example `rwconverter` command line on Microsoft Windows:

       rwconverter.exe dtype="register" stype="rdffile"
       source="(security.rdf,earnings.rdf,acc_pay.rdf)" dest="(output.sql)"
       p_owner="PORTAL_APP" p_servers="(repserver,acct_server)"
       p_description="restricted report" p_privilege="(SCOTT,JABERS,ACCT)"
       p_availability="production" p_types="(Cache,printer)"
       p_formats="(HTMLCSS,PDF)" p_printers="(sales_printer,acct_printer)"
       p_pformTemplate="public.finance_template"
       p_trigger="Is begin IF UPPER(DESTYPE) = ''PRINTER'' AND
       EMPNAME = ''SMITH'' THEN RETURN(TRUE); ELSE RETURN(FALSE); END IF; end;"

   The above command line would generate a SQL script file named `output.sql` that contains the following:

       SET SERVEROUTPUT ON

       VAR STATUS NUMBER;

       EXEC :STATUS := RWWWVREG.REGISTER_REPORT (P_NAME=>'Security',
       P_OWNER=>'PORTAL_APP', P_SERVERS=>'repserver,acct_server',
       P_FILENAME=>'security.rdf', P_DESCRIPTION=>'restricted report',
       P_PRIVILEGE=>'SCOTT,JABERS,ACCT', P_AVAILABILITY=>'production’
       P_TYPES=>'Cache,printer', P_FORMATS=>'HTMLCSS,PDF)',
       P_PRINTERS=>'sales_printer,acct_printer
       P_PFORMTEMPLATE=>'public.finance_template' P_PARAMETERS=>'(P_LASTNAME)
       (P_SSN)', P_TRIGGER=>'Is begin IF UPPER(DESTYPE) = ''PRINTER'' AND
       EMPNAME = ''SMITH''THEN RETURN(TRUE); ELSE RETURN(FALSE); END IF; end;');

       EXEC :STATUS := RWWWVREG.REGISTER_REPORT (P_NAME=>'Earnings',
       P_OWNER=>'PORTAL_APP', P_SERVERS=>'repserver,acct_server',
       P_FILENAME=>'earnings.rdf', P_DESCRIPTION=>'restricted report',
       P_PRIVILEGE=>'SCOTT,JABERS,ACCT', P_AVAILABILITY=>'production’
       P_TYPES=>'Cache,printer)', P_FORMATS=>'HTMLCSS,PDF)',
       P_PRINTERS=>'sales_printer,acct_printer',
       P_PFORMTEMPLATE=>'public.finance_template',
       P_TRIGGER='Is begin IF UPPER(DESTYPE) = ''PRINTER'' AND EMPNAME = ''JABERS''
       THEN RETURN(TRUE); ELSE RETURN(FALSE); END IF; end;');

       EXEC :STATUS := RWWWVREG.REGISTER_REPORT (P_NAME=>'Acc_pay',
       P_OWNER=>'PORTAL_APP', P_SERVERS=>'repserver,acct_server',
       P_FILENAME=>'acc_pay.rdf', P_DESCRIPTION=>'restricted report',
       P_PRIVILEGE=>'SCOTT,JABERS,ACCT', P_AVAILABILITY=>'production'
       P_TYPES=>'Cache,printer', P_FORMATS=>'HTMLCSS,PDF',
       p_printers=>'sales_printer,acct_printer',
       P_PFORMTEMPLATE=>'public.finance_template'
       P_TRIGGER=>'Is begin IF UPPER(DESTYPE) = ''PRINTER'' AND
       EMPNAME = ''JABERS''THEN RETURN(TRUE); ELSE RETURN(FALSE); END IF; end;');

   For more information about the contents of this SQL script file, refer to PL/SQL Batch Registering Function.

2. Check the `reports.log` file, which is typically written to the current working directory, for errors that may have occurred during the conversion process. If the `reports.log` file was not generated, then no errors were encountered by `rwconverter`.

3. You can now optionally edit the system and user parameter values as desired. For example, the first `RWWWVREG` function in the sample script above generated an additional parameter called `P_PARAMETERS`. This occurred because the security.rdf file contains two user-defined parameters, `P_LASTNAME` and `P_SSN`:

       P_PARAMETERS=>'(P_LASTNAME)(P_SSN)',

   In this case, you can optionally define the default, low, and high values, or a list of values for each user parameter if you want to restrict the values the user may enter at runtime. Similarly, if you want to restrict system parameters, such as `COPIES`, to limit the number of copies a user can make, you do so by using the `P_PARAMETERS` parameter. The edited `P_PARAMETERS` keyword might look like the following:

       P_PARAMETERS=>'(P_LASTNAME, LOV=LASTNAME_LOV)(P_SSN)(COPIES, DEFAULT=1,LOW=1,HIGH=2)'

   This revised code segment imposes the following restrictions on the report:

   - The `P_LASTNAME` user parameter is limited to the values listed in the `LASTNAME_LOV` list of values.

   - A user-supplied value for `P_SSN` is required.

   - The default value of the `COPIES` system parameter is one and the number of printed copies must be in a range from 1 to 2.

4. Save and close the `output.sql` file.

### C.1.2 Run the Script in SQL*Plus

To actually register your reports in OracleAS Portal, you must run the script generated for you by `rwconverter`:

1. Start SQL*Plus and connect to the OracleAS Portal schema that you want to own the packaged procedures.

2. From the SQL*Plus command prompt, execute the script you created with `rwconverter`:

       @ output.sql

   The script will execute and create packages in OracleAS Portal for each report listed in the script with the specified parameters.

3. Log in to OracleAS Portal as a user with `RW_ADMINISTRATOR` privileges.

4. Click the Corporate Documents tab.

5. Click Builder.

6. Click the Administer tab.

7. In the Oracle Reports Security portlet, click Oracle Reports Security Settings.

8. In the Reports Definition File Access portlet, enter the `P_NAME` of one of the reports you batch registered in your SQL script.

9. Click Edit. The Manage Component page is displayed.

10. Click Edit at the bottom of the page to edit the parameters of the report.

11. Review and edit the parameters as desired.

12. Click OK.

13. Click Close.

14. Repeat steps 8 through 13 for each report that you batch registered with your script.

## C.2 Batch Removing Report Packages

To remove many reports from OracleAS Portal at once, do the following:

1. In a text editor, create a SQL script file (e.g., `rmv_rdfs.sql`) that contains one `RWWWVREG.DEREGISTER_REPORT` function call for each report definition file package that you want to remove. For example:

       VAR STATUS NUMBER;
       EXEC :STATUS := RWWWVREG.DEREGISTER_REPORT (P_NAME=>'Security');
       EXEC :STATUS := RWWWVREG.DEREGISTER_REPORT (P_NAME=>'Earnings');
       EXEC :STATUS := RWWWVREG.DEREGISTER_REPORT (P_NAME=>'Acc_pay');

   Note: `P_NAME` is the name of the report definition file package you want to remove from OracleAS Portal.

2. Start SQL*Plus and log in to the OracleAS Portal schema that owns the reports’ packaged procedures.

3. From the SQL*Plus command prompt, execute the script you created in the first step:

       @ rmv_rdfs.sql

   The script will execute and remove the packages from OracleAS Portal for each report listed in the script.

   Note: This procedure will not remove the report definition files from the file system. It only unregisters the reports making them unavailable from OracleAS Portal. If you want to remove the files, you must delete them from the file system.

## C.3 PL/SQL Batch Registering Function

The SQL script that `rwconverter` generates for you to batch register reports in Oracle Application Server consists mainly of calls to the `RWWWVREG.REGISTER_REPORT` function. The syntax of `RWWWVREG.REGISTER_REPORT` is as follows:

    Function Rwwwvreg.register_report(
      p_owner varchar2,
      p_name varchar2,
      p_servers varchar2,
      p_filename varchar2,
      p_description varchar2,
      p_privileges varchar2,
      p_availability varchar2,
      p_types varchar2,
      p_formats varchar2,
      p_printers varchar2,
      p_pdformTemplate varchar2,
      p_parameters varchar2,
      p_trigger varchar2)
      return number;
        --  =0 : succeeded;
        --  !=0 : failed;

The table below describes each of the parameters taken by `RWWWVREG.REGISTER_REPORT`.

Table C-1 `RWWWVREG.REGISTER_REPORT` parameters

Parameter | Description
--- | ---
`P_OWNER` | Is the DB Provider name. The default is the current Oracle Application Server DB Provider that you are connected to when you start the SQL*PLUS script. For example: `P_OWNER=>'PORTAL_APP'`
`P_NAME` | Is the name used to identify the report in OracleAS Portal. `P_NAME` corresponds to the Name field in the Create Report Definition File Access wizard. For example: `P_NAME=>'Earnings'`
`P_SERVERS` | Is the names of the Reports Servers on which the report definition files defined in the `P_SERVERS` parameter have access privileges. The list of Reports Servers is comma delimited. `P_SERVERS` corresponds to the Reports Servers field in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_SERVERS=>'repserver,acct'` Note: The Reports Servers you list for `P_SERVERS` must already be registered in OracleAS Portal. For more information, refer to Chapter 11, " Deploying Reports in OracleAS Portal".
`P_FILENAME` | Is the name of the report definition file that is being registered. `P_FILENAME` corresponds to the Oracle Reports File Name in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_FILENAME=>'earnings.rdf'`
`P_DESCRIPTION` | Is a description of the report. `P_DESCRIPTION` corresponds to the Description field in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_DESCRIPTION=>'restricted report'`
`P_PRIVILEGE` | Is the users or roles given privileges to run the report definition file defined in `P_FILENAME`. This list is comma delimited. `P_PRIVILEGE` corresponds to the Grantee list on the Access tab of the Manage Component page for the report. Note that you must uncheck Inherit Privileges from Portal DB Provider in order to see the Grantee list. For example: `P_PRIVILEGE=>'SCOTT,JABERS,PORTAL90'`
`P_AVAILABILITY` | Is the name of the availability calendar that determines when the report definition file defined in the `P_FILENAME` parameter will be available for processing. `P_AVAILABILITY` corresponds to the Availability Calendar Name field in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_AVAILABILITY=>'production'` Note: The availability calendar must already exist in OracleAS Portal. For more information about creating an availability calendar, see Chapter 11, " Deploying Reports in OracleAS Portal".
`P_TYPES` | Is the destination types to which the report definition file defined in the `P_FILENAME` parameter can be sent (e.g., cache, printer). This list is comma delimited. `P_TYPES` corresponds to the Types multiple select box in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_TYPES=>'CACHE`,printer'
`P_FORMATS` | The destination formats to which the report definition file defined in the `P_FILENAME` parameter can be sent (e.g., HTML, PDF). This list is comma delimited. `P_FORMATS` corresponds to the Formats multiple select box in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_FORMATS=>'HTMLCSS,PDF'`
`P_PRINTERS` | The printers to which the report definition file defined in the `P_FILENAME` parameter can print. This list is comma delimited. `P_PRINTERS` corresponds to the Printers multiple select box in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_PRINTERS=>'sales_printer,acct_printer'` Note: The printers you list for `P_PRINTERS` must already be registered in OracleAS Portal. For more information, refer to Chapter 11, " Deploying Reports in OracleAS Portal".
`P_PFORMTEMPLATE` | Is the parameter form template that determines the page style of the Runtime Parameter Form. `P_PFORMTEMPLATE` corresponds to the Parameter Form Template field in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_PFORMTEMPLATE=>'public.finance_template'`
`P_PARAMETERS` | Is the user and system parameters’ default, high, and low values, or list of values name. Note: The `P_PARAMETERS` parameter does not have a corresponding `rwconverter` option. Hence, if you want to batch import user parameter values, ranges, or lists of values, you must manually edit the SQL script generated by `rwconverter`. `P_PARAMETERS` corresponds to the (parameter) Name, LOV, Low Value, and High Value fields in the Create Report Definition File Access wizard and the Edit Report Definition File page. The default corresponds to the value set in the Runtime Parameter Form for the specified parameter. For example: `P_PARAMETERS=>'(P_LASTNAME, LOV=LASTNAME_LOV) (P_SSN)(COPIES, DEFAULT=1,LOW=1,HIGH=2)'` where: `P_LASTNAME`, `P_SSN`, and `COPIES` are parameter names. `LOV` is the name of the list of values. `DEFAULT` is the default value. `LOW` is the low value in a range of values. `HIGH` is the high value in a range of values.
`P_TRIGGER` | Is the validation trigger written in PL/SQL that returns a boolean statement (e.g., true (succeeded) or false (failed)). `P_TRIGGER` corresponds to the text box in the Create Report Definition File Access wizard and the Edit Report Definition File page. For example: `P_TRIGGER=>'Is begin` `IF UPPER(DESTYPE) = ''PRINTER'' AND` `EMPNAME = ''SMITH''` `THEN` `RETURN(TRUE);` `ELSE` `RETURN(FALSE);` `END IF;` `end;'`