Re: dbms_metadata for Synonym

Also, I am using the procedure dbms_metadata.fetch_ddl and not the function dbms_metadata.get_ddl.

Here is core code :

• Begin Code LOOP

        triggerDDLs := dbms_metadata.fetch_ddl(triggerOpenHandle);
        EXIT WHEN triggerDDLs IS NULL;   -- Get out when no more
        triggers

        -- Get the trigger DDL object from the collection although there
        is only one since we requested only 1 DDL per fetch
        triggerDDL := triggerDDLs(1);


          .... some processing code

              -- Write the DDL text to our output file
        write_lob(triggerDDL.ddltext, fileHandle);

Note: write_lob is from the oracle sample program for dbms_metadata

• Package-private routine to write a CLOB to an output file.

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;

• End Code

Hope this helps.

Thanks,
Hari

Originally posted by wallflowers
> Daniel,
>
> Well, I am using it programmatically from a PL/SQL package and its all
> within a loop. It extracts the source code for all triggers and saves
> them in a separate file for each trigger. so when I look at the files,
> certain triggers are extracted completely , while some are not. Thats
> what I am unable to understand, why would it happen for only certain
> trigger. Let me know if you need any further information.
>
> Thanks,
Hari

--
Posted via http://dbforums.com