Re: dbms_metadata for Synonym

[wallflowers]

I have managed to figure out the solution for the 1st one. All I had to
do was set filter to the current schema. So please just consider the
2nd problem:
2. when generating triggers, certain triggers are not being extracted
completely. I repeat only certain triggers. rest of them are being
extracted ok. can someone please explain why this is happening and
how can i avoid it?

Thanks,
Hari

Originally posted by wallflowers

Hi,

I am using dbms_metadata for generating all the ddl in the current
schema. Although there are a couple of problems i am facing.

1. when i try to generate the ddl for all synonyms, it seems to be
generating the ddl for all public synonyms, i need only the ones
owned by the current schema. how do i do that?

2. when generating triggers, certain triggers are not being extracted
completely. I repeat only certain triggers. rest of them are being
extracted ok. can someone please explain why this is happening and
how can i avoid it?

Any help is highly appreciated. Thanks in advance.

Hari

--
Posted via http://dbforums.com

[Daniel Roy]

DBMS_METADATA.GET_DDL returns a CLOB. Therefore, just change the value
of your variable "long" to a bigger value, and you'll be OK (from
SQL*Plus, "set long 50000").

Daniel

wallflowers <me*********@dbforums.comwrote in message news:<31****************@dbforums.com>...

I have managed to figure out the solution for the 1st one. All I had to
do was set filter to the current schema. So please just consider the
2nd problem:
2. when generating triggers, certain triggers are not being extracted
completely. I repeat only certain triggers. rest of them are being
extracted ok. can someone please explain why this is happening and
how can i avoid it?

Thanks,
Hari

Originally posted by wallflowers

Hi,

I am using dbms_metadata for generating all the ddl in the current
schema. Although there are a couple of problems i am facing.

1. when i try to generate the ddl for all synonyms, it seems to be
generating the ddl for all public synonyms, i need only the ones
owned by the current schema. how do i do that?

2. when generating triggers, certain triggers are not being extracted
completely. I repeat only certain triggers. rest of them are being
extracted ok. can someone please explain why this is happening and
how can i avoid it?

Any help is highly appreciated. Thanks in advance.

Hari

[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

Originally posted by Daniel Roy

DBMS_METADATA.GET_DDL returns a CLOB. Therefore, just change the value
of your variable "long" to a bigger value, and you'll be OK (from
SQL*Plus, "set long 50000").

Daniel

wallflowers wrote in message news:news:...

I have managed to figure out the solution for the 1st one. All I

had to

do was set filter to the current schema. So please just consider

the

2nd problem:
2. when generating triggers, certain triggers are not being

extracted

completely. I repeat only certain triggers. rest of them are

being

extracted ok. can someone please explain why this is

happening and

how can i avoid it?

Thanks,
Hari

Originally posted by wallflowers

Hi,
>
I am using dbms_metadata for generating all the ddl in the

current

schema. Although there are a couple of problems i am

facing.

>
1. when i try to generate the ddl for all synonyms, it seems

to be

generating the ddl for all public synonyms, i need only the

ones

owned by the current schema. how do i do that?
>
2. when generating triggers, certain triggers are not being

extracted

completely. I repeat only certain triggers. rest of them

are being

extracted ok. can someone please explain why this is

happening and

how can i avoid it?
>
Any help is highly appreciated. Thanks in advance.
>

Hari

--
Posted via http://dbforums.com

[wallflowers]

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

[Daniel Roy]

According to the specs, fetch_ddl returns a variable of type KU$_DDLS
(no typo, you read it right!). This is certainly a new data type for
me. I found a good example on
http://download-west.oracle.com/docs...d2.htm#1024601
(at around 75% of the page). Take a look at it, and let us know if
that helps.

Daniel

[wallflowers]

Thats the example based on which I wrote the entire package.
Originally posted by Daniel Roy

According to the specs, fetch_ddl returns a variable of type KU$_DDLS
(no typo, you read it right!). This is certainly a new data type for
me. I found a good example on
http://download-west.oracle.com/docs...appdev.920/a9-
6612/d_metad2.htm#1024601"]http://download-west.oracle.com/docs/cd/B1-
0501_01/appdev.920/a96612/d_metad2.htm#1024601[/url]
(at around 75% of the page). Take a look at it, and let us know if
that helps.

Daniel

--
Posted via http://dbforums.com

[Daniel Roy]

If you want, email me the whole piece of code, and I can take a look
at it (remove the "junk" from my username for my real email address).

Daniel

[Daniel Roy]

I installed your package, and tested with some triggers. So far,
everything works perfectly. Which cases didn't work with you? Does it
seem random? Is it the very triggers with lots of text?

Daniel

If you want, email me the whole piece of code, and I can take a look
at it (remove the "junk" from my username for my real email address).

Daniel