11 Replies Latest reply: Nov 13, 2012 5:12 AM by user8941550 RSS

    Dynamically call DBMS_XMLINDEX to drop parameter and exit with exception

    user8941550
      Hi All,

      I am trying to create a procedure to dynamically call DBMS_XMLINDEX.dropparameter.
      If the parameter does not exist then gracefully exit with exception as 'Parameter does not exist'.
      But I keep on getting errors due to dynamically passing.
      Procedure is something like:


      Create PROCEDURE drop_parameter(p_parameter IN VARCHAR2)
      IS
      no_parameter EXCEPTION;
      PRAGMA EXCEPTION_INIT(no_parameter,-12004);
      BEGIN
      EXECUTE IMMEDIATE 'DBMS_XMLINDEX.dropparameter( ''||p_parameter||'') ';
      dbms_output.put_line('Parameter dropped');
      EXCEPTION
      WHEN no_parameter THEN
      dbms_output.put_line('Parameter does not exist');
      END drop_parameter;

      For implementaion we can try creating parameter as :

      BEGIN
      DBMS_XMLINDEX.REGISTERPARAMETER (
      'Param1',
      'PATH TABLE PathTB1
      PATHS (INCLUDE ( /a/b/c
      /d/e/f
      ))
      ');
      END;
      /

      The above parameter should be dropped or if not existing, then exit gracefully.
      Please suggest.

      Thanks...