Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    18

    Unanswered: DBMS_METADATA PL/SQL Supplied Package

    Hi,

    I am using dbms_metadata PL/SQL supplied package to extract all the
    objects from the oracle database into files. So far I have generated the source code for Functions & Triggers. Functions seems to be absolutely fine. But there seems to be some kind of a problem with Triggers. Some of the triggers were'nt extracted completely and some were. Not quite sure why this is happening. I am using exactly the same code for functions & triggers.

    Is anybody aware as to what could be the problem. Or if there is a bug
    with the dbms_metadata package.

    Any help is highly appreciated.

    Thanks in advance.
    Hari

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Are you extracting the CLOB into a varchar2 field as there is a limit of 4000 chars for varchar2 which may mean you may truncate large triggers/funcions/procs.

    Alan

  3. #3
    Join Date
    Jun 2003
    Posts
    18
    Alan,
    I appreciate your prompt response. I dont think that should be a problem since , there are larger triggers which are getting extracted completely. Anyways for your reference here is the code (which is derived from the oracle documentation):

    PROCEDURE write_lob(doc IN CLOB, fileHandle UTL_FILE.FILE_TYPE) IS

    outString varchar2(32760);
    cloblen number;
    offset number := 1;
    amount number;

    BEGIN

    cloblen := dbms_lob.getlength(doc);

    WHILE cloblen > 0
    LOOP

    IF cloblen > 32760 THEN
    amount := 32760;
    ELSE
    amount := cloblen;
    END IF;

    outString := dbms_lob.substr(doc, amount, offset);
    utl_file.put(fileHandle, outString);
    utl_file.fflush(fileHandle);
    offset := offset + amount;
    cloblen := cloblen - amount;

    END LOOP;
    RETURN;
    END;

    Please do let me know if you can think of any solution to this.

    Thanks in advance,
    Hari

    Originally posted by AlanP
    Are you extracting the CLOB into a varchar2 field as there is a limit of 4000 chars for varchar2 which may mean you may truncate large triggers/funcions/procs.

    Alan

Posting Permissions

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