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]

Re: dbms_metadata for Synonym

DBMS_METADATA.G ET_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 <member32010@db forums.comwrote in message news:<3134895.1 058810959@dbfor ums.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]

Re: dbms_metadata for Synonym


Daniel,

Well, I am using it programmaticall y 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.G ET_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]

Re: dbms_metadata for Synonym


Also, I am using the procedure dbms_metadata.f etch_ddl and not the
function dbms_metadata.g et_ddl.

Here is core code :

** Begin Code
LOOP

triggerDDLs := dbms_metadata.f etch_ddl(trigge rOpenHandle);
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(trigg erDDL.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_T YPE) IS

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

BEGIN

cloblen := dbms_lob.getlen gth(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(fi leHandle, 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 programmaticall y 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]

Re: dbms_metadata for Synonym

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/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1024601

(at around 75% of the page). Take a look at it, and let us know if
that helps.

Daniel

[wallflowers]

Re: dbms_metadata for Synonym


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/cd/B10501_01/appdev.920/a9-

6612/d_metad2.htm#10 24601"]http://download-west.oracle.com/docs/cd/B1-
0501_01/appdev.920/a96612/d_metad2.htm#10 24601[/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]

Re: dbms_metadata for Synonym

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]

Re: dbms_metadata for Synonym

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