tag:blogger.com,1999:blog-5882766990283082528.post1429710275069515728..comments2023-08-15T11:19:40.997-04:00Comments on Phil's Oracle Exadata, SQL, and PL/SQL tips: DBMS_METADATA Across Database Links!Philhttp://www.blogger.com/profile/01851424617108510453noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-5882766990283082528.post-13493944710375337762018-01-18T05:32:20.369-05:002018-01-18T05:32:20.369-05:00It works fine on 11.2.
Thank you very much!It works fine on 11.2.<br />Thank you very much!Anonymoushttps://www.blogger.com/profile/00422621124695658635noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-14657992570473348742012-01-17T01:41:34.413-05:002012-01-17T01:41:34.413-05:00remote_dbms_metadata.get_ddl is not extracting com...remote_dbms_metadata.get_ddl is not extracting complete code always any thought.Anishttps://www.blogger.com/profile/02859638930568913688noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-80030449746152574062011-09-16T17:24:39.180-04:002011-09-16T17:24:39.180-04:00Thanks man it works really good...Thanks man it works really good...Suresh Panavallihttps://www.blogger.com/profile/10714821767514451435noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-55434751465335770582011-09-16T16:53:02.147-04:002011-09-16T16:53:02.147-04:00This comment has been removed by the author.Suresh Panavallihttps://www.blogger.com/profile/10714821767514451435noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-88311776897972737662010-02-11T17:39:07.423-05:002010-02-11T17:39:07.423-05:00Works like a charm!
Thanks for sharing.Works like a charm!<br /><br />Thanks for sharing.konfushushttps://www.blogger.com/profile/15206965414070702764noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-1170317727083702472009-11-03T07:25:41.433-05:002009-11-03T07:25:41.433-05:00Hello Phil,
Thanks, I have been able to work arou...Hello Phil,<br /><br />Thanks, I have been able to work around the constraints and all. <br /><br />There is still one problem left, is it possible to change the datatype of the sys.METASYLESHEET; i.e. my own copy from CLOB to VARCHAR2?<br /><br />If not, which way do you think transfer of metadata can be done across a database link using DBMS_METADATA?<br /><br />Oracle doesnt support transfer of CLOB datatype across the database link yet; so I tweaked my function to return varchar2; meaning the max it can return is 32767.<br /><br />Many thanks.Tundehttps://www.blogger.com/profile/03777271782599296530noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-5942669300223639712009-11-02T11:04:58.660-05:002009-11-02T11:04:58.660-05:00Hi Tunde,
DBMS_METADATA uses the "SYS.METAST...Hi Tunde,<br /><br />DBMS_METADATA uses the "SYS.METASTYLESHEET" table - specifically the "XSL" stylesheets with CLOB contents in column: "STYLESHEET" to transform XML into usable DDL when you use the "GET_DDL" function.<br /><br />I've hacked those stylesheets by storing them in my own table, and by using them as transforms when programmatically using DBMS_METADATA - so that I can do things like rename the table being created, put it in another schema, turn on or off the partitions, the constraints, etc.<br /><br />I would recommend that you use the Oxygen XML editor if you plan on modifying the stylesheets - and do NOT change the original ones contained in the sys.METASTYLESHEET table (create your own).<br /><br />Another approach would be to use regular expressions to find and change patterns in the SQL generated by DBMS_METADATA.<br /><br />Good luck!Philhttps://www.blogger.com/profile/01851424617108510453noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-53185945072452317582009-10-30T06:24:15.346-04:002009-10-30T06:24:15.346-04:00You are welcome anytime Phil, I am still having so...You are welcome anytime Phil, I am still having some trouble with recreating tables with dependencies on tables not yet imported.<br /><br />Could you by any chance have found a way around tweaking the table metadata not to include foreign key constraints.<br /><br />Many thanks.<br /><br />Regards,<br />TundeTundehttps://www.blogger.com/profile/03777271782599296530noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-74393205976445306042009-10-30T00:18:43.744-04:002009-10-30T00:18:43.744-04:00Hi Tunde, Thanks for the suggestions! I'll de...Hi Tunde, Thanks for the suggestions! I'll definitely check it out.<br /><br />Sincerely,<br /><br />PhilPhilhttps://www.blogger.com/profile/01851424617108510453noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-66758190924655526162009-10-29T06:18:50.440-04:002009-10-29T06:18:50.440-04:00example of call:
select demo_fn@db_link('TABL...example of call:<br /><br />select demo_fn@db_link('TABLE','DEMO') FROM DUAL;Tundehttps://www.blogger.com/profile/03777271782599296530noreply@blogger.comtag:blogger.com,1999:blog-5882766990283082528.post-444137746486485002009-10-29T06:16:04.829-04:002009-10-29T06:16:04.829-04:00Wow, that's a nice one you've got there.
D...Wow, that's a nice one you've got there.<br />DBMS_METADATA.GET_DDL is sure a powerful tool but I don't like the format he retrieves the ddl so I tweaked it a bit to remove the owner of the database object and then return string instead of clob to allow transfer across a database link (I think urs is a better option though). You could also incorporate removing the schema names and double quotes if u want here's my own version of the get_ddl function:<br /><br />CREATE OR REPLACE function DEMO_FN<br />(object_type varchar2, table_name varchar2) return varchar2 <br />is <br /><br />v_longstrings varchar2(32223);<br />c_doublequote constant char(1) := '"'; <br />begin <br />v_longstrings := dbms_metadata.get_ddl(object_type,table_name);<br /><br />-- Remove double quotes from DDL string:<br />v_longstrings := replace(v_longstrings, c_doublequote || user || c_doublequote || '.','');<br /><br />-- Remove the following from DDL string:<br /> -- 1) "new line" characters (chr(10))<br /> -- 2) leading and trailing spaces<br />v_longstrings := ltrim(rtrim(replace(v_longstrings, chr(10), '')));<br />return v_longstrings; <br />end;<br />/<br /><br />Cheers,<br />TundeTundehttps://www.blogger.com/profile/03777271782599296530noreply@blogger.com