Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: Dynamically call DBMS_XMLINDEX to drop parameter and exit with exception

    Hi,

    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...

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    just for my interest, here is link to the same thread at OTN: https://forums.oracle.com/forums/thr...63487&tstart=0

    As others said there: there is nothing dynamic in the procedure call so there is no reason for calling it dynamically (until you plan to use it on Oracle prior 11gR2, where it was introduced; it would fail different error anyway).

    Just curious, how did you come to ORA-12004: REFRESH FAST cannot be used for materialized view "string"."string"? Because, that procedure fails with different exception which is easy to spot when you do not "handle" it:
    Code:
    SQL> exec dbms_xmlindex.dropparameter( 'dummy' )
    BEGIN dbms_xmlindex.dropparameter( 'dummy' ); END;
    
    *
    ERROR at line 1:
    ORA-44737: Parameter dummy did not exist.
    ORA-06512: at "XDB.DBMS_XMLINDEX", line 80
    ORA-06512: at line 1
    After examining the Oracle response, it is easy to catch and ignore that particular exception in static code:
    Code:
    declare
      no_parameter EXCEPTION;
      PRAGMA EXCEPTION_INIT(no_parameter,-44737);
    BEGIN
      DBMS_XMLINDEX.dropparameter( 'dummy' );
    EXCEPTION
      WHEN no_parameter THEN
        null;
    END;
    /
    Note that I removed DBMS_OUTPUT calls as the caller does not have to use it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •