ALL_REPCAT_USER_PARM_VALUES

This view describes the template parameters for all deployment templates accessible to the current user. The DBA has the option of building a table of user parameters prior to distributing the template for instantiation. When a template is instantiated by a specified user, the values stored in the ALL_REPCAT_USER_PARM_VALUES view for the specified user are used automatically.

Related Views:

• DBA_REPCAT_USER_PARM_VALUES describes the template parameters for all deployment templates in the database.

• USER_REPCAT_USER_PARM_VALUES describes the template parameters for all deployment templates owned by the current user.

  Column	Datatype	NULL	Description
  REFRESH_TEMPLATE_NAME	VARCHAR2(30)	NOT NULL	The name of the deployment template for which a user parameter value has been defined.
  OWNER	VARCHAR2(30)	NOT NULL	The name of the owner of the deployment template.
  REFRESH_GROUP_NAME	VARCHAR2(30)	NOT NULL	Name of the refresh group to which the template objects are added to during the instantiation process.
  TEMPATE_COMMENT	VARCHAR2(2000)		User specified comment.
  PUBLIC_TEMPLATE	VARCHAR2(1)		If Y then the deployment template is public. If N then the deployment template is private.
  PARAMETER_NAME	VARCHAR2(30)	NOT NULL	The name of the parameter for which a user parameter value has been defined.
  DEFAULT_PARM_VALUE	CLOB(4000)		The default value for the parameter.
  PROMPT_STRING	VARCHAR2(2000)		The prompt string for the parameter.
  PARM_VALUE	CLOB(4000)		The parameter value that has been defined for the specified user.
  USER_NAME	VARCHAR2(30)	NOT NULL	The username of the user for whom the specified parameter value has been defined.

Because DEFAULT_PARM_VALUE and PARM_VALUE columns are defined as CLOBs, you receive an error if you simply try to perform a SELECT on the ALL_REPCAT_USER_PARM_VALUES view. If you do not need to see the default or user parameter values, then use the following select statement (be sure to exclude DEFAULT_PARM_VALUE and PARM_VALUE):

SELECT REFRESH_TEMPLATE_NAME, OWNER, REFRESH_GROUP_NAME, TEMPLATE_COMMENT, 
  PUBLIC_TEMPLATE, PARAMETER_NAME, PROMPT_STRING, USER_NAME 
  FROM DBA_REPCAT_USER_PARM_VALUES;

The following script uses cursors and the DBMS_LOB package to view the entire contents of the ALL_REPCAT_USER_PARM_VALUES view. Use this script to view the entire contents of the ALL_REPCAT_TEMPLATE_PARMS view, including the DEFAULT_PARM_VALUE and PARM_VALUE columns:

SET SERVEROUTPUT ON

DECLARE
  CURSOR mycursor IS
        SELECT REFRESH_TEMPLATE_NAME, OWNER, REFRESH_GROUP_NAME,
          TEMPLATE_COMMENT, PUBLIC_TEMPLATE, PARAMETER_NAME, DEFAULT_PARM_VALUE,
          PROMPT_STRING, PARM_VALUE, USER_NAME
          FROM DBA_REPCAT_USER_PARM_VALUES;
  tempstring VARCHAR2(1000);
  tempstring2 varchar2(1000);
  len NUMBER;
BEGIN
  FOR myrec IN mycursor LOOP
        len := DBMS_LOB.GETLENGTH(myrec.default_parm_value);
        DBMS_LOB.READ(myrec.default_parm_value, len, 1, tempstring);
        DBMS_OUTPUT.PUT_LINE(myrec.refresh_template_name||' '||
          myrec.owner||' '||myrec.refresh_group_name||' '||
          myrec.template_comment||' '||myrec.public_template||' '||
          myrec.parameter_name||' '||tempstring||' '||myrec.prompt_string||' '||
          tempstring2||' '||myrec.user_name);
  END LOOP;
END;
/

See Also: Oracle9i Application Developer's Guide - Fundamentals for more information on using cursors. Also, see Oracle9i Application Developer's Guide - Large Objects (LOBs) for more information on using the DBMS_LOB package and LOBs in general.