ORA-31600: invalid input value 200000001 for parameter HANDLE in function FETCH_CLOB

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Further to my last posting about DBMS_METADATA related ORA-31607, here is a slightly modified version of an Oracle supplied sample of procedure performing the "dump" of all the objects belonging to the current user. In red color, i highlighted the cause of ORA-31600.
The original program used a table to store all the CLOBs, but i commented out the relevant lines and i merely print out the initial 255 characters of the DDL along with a counter.

PROCEDURE get_schema_md IS

-- Define local variables.
h       NUMBER;         -- handle returned by OPEN
th      NUMBER;         -- handle returned by ADD_TRANSFORM
doc     CLOB;           -- metadata is returned in a CLOB
objs    INTEGER := 0;   -- object counter
BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');

-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(h,'SCHEMA', user);

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

-- Fetch the objects.
LOOP
                            -- the correct handle is h, not th
doc := DBMS_METADATA.FETCH_CLOB(th, FALSE, DBMS_LOB.CALL);

-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;
objs := objs + 1;
-- Store the metadata in the table.
-- INSERT INTO my_metadata(md) VALUES (doc);
-- COMMIT;
dbms_output.put_line(dbms_lob.substr(doc,255,1));
dbms_lob.freetemporary(doc);

END LOOP;

-- Release resources.
DBMS_METADATA.CLOSE(h);
dbms_output.put_line(objs);
END;

ORA-31600: invalid input value 200000001 for parameter HANDLE in function FETCH_CLOB
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 555
ORA-06512: at "SYS.DBMS_METADATA", line 3801
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at "TOOLS.GET_SCHEMA_MD", line 21

This is a rather trivial error, it is sufficient to replace the wrong handle name (th), with the correct one (h) to get the program to work. Each of these opaque handlers returned by the DBMS_METADATA functions serves as input to other procedures or functions and this assortment of variables can lead to some confusion, i.e. the exception illustrated in this posting.
The scheme below summarizes the pattern of usage of these handlers, where the number returned by the function on the left hand side can be used as input for the function on the right hand side:

OPEN -> SET_FILTER
    -> SET_COUNT
    -> SET_PARSE_ITEM
    -> ADD_TRANSFORM
    -> FETCH_xxx
    -> GET_QUERY
    -> CLOSE

OPENW -> CONVERT
     -> PUT
     -> SET_PARSE_ITEM
     -> ADD_TRANSFORM
     -> CLOSE

ADD_TRANSFORM -> SET_TRANSFORM_PARAM
             -> SET_REMAP_PARAM

Note also that if you comment out the line in green color, you'll get an XML formatted output instead of plain text DDLs.

See message translations for ORA-31600 and search additional resources.