DDL for whole Database [message #555844] |
Tue, 29 May 2012 05:58  |
 |
chrissi
Messages: 10 Registered: May 2012
|
Junior Member |
|
|
Hi,
I would like to create a script, that lets me rebuild a database while calling it.
For this, I call :
select dbms_metadata.get_ddl('TABLE','Table-name','Schema') from dual;
Is there a way to call this for the whole Database?
I tried it like that:
SET serveroutput ON format wraped;
Declare
Ergebnis varchar2(9000);
BEGIN
FOR Tabelle IN
(SELECT table_name FROM all_tables WHERE owner='schema')
LOOP
dbms_output.put_line(Tabelle.table_name);
select dbms_metadata.get_ddl('TABLE',Tabelle.table_name,'schema') into Ergebnis from dual;
dbms_output.put_line(Ergebnis);
END LOOP;
END;
but this won't work.
How can I get this working?
Thx!
|
|
|
|
|
|
Re: DDL for whole Database [message #555856 is a reply to message #555848] |
Tue, 29 May 2012 07:55   |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
In ORACLE 11.2.0.2 i can't use DBMS_METADATA.get_ddl for nested tables, so I have do write:
SELECT DBMS_METADATA.get_ddl ('TABLE', table_name)
FROM user_all_tables
WHERE nested = 'NO';
|
|
|
|
|
|
|
|
|