I have got a monumentally tiresome task that's to locate several tables from the huge schema, and create the DDL of these tables.
Say, I have got a
schemaA has 1000 tables, I have to find if your
tableA been around within this
schemaA, whether it does, create the DDL and save it to filesystem, if don't, print it's title out or write it to some file. Any ideas?
The DBMS_METADATA package (presuming you are well on a relatively recent version of Oracle) will create the DDL for just about any object within the database. So
SELECT dbms_metadata.get_ddl( 'TABLE' 'TABLEA', 'SCHEMAA' ) FROM dual;
will return a CLOB using the DDL for SchemaA.TableA. You can catch the exception that's tossed the object does not exist, however i would often claim that you query the information dictionary (i.e. DBA_OBJECTS) to ensure that there's a table named TableA in SchemaA, i.e.
SELECT COUNT(*) FROM dba_objects WHERE owner = 'SCHEMAA' AND object_name = 'TABLEA' AND object_type = 'TABLE'
Observe that without having use of DBA_OBJECTS, you could utilize ALL_OBJECTS rather. The concern there, however, is the fact that there might be a TableA in SchemaA you don't have Choose access on. That table wouldn't appear in most_OBJECTS (that has all of the objects that you can get) but it appears in DBA_OBJECTS (that has all of the objects within the database no matter what you can do to gain access to them).
After that you can either write the DDL to some file or, when the count is , indicate the object does not exist. From the saved procedure, you should use the UTL_FILE package to create to some file around the database server. If you're attempting to email personal files around the client file system, you should make use of a language that can access the customer operating system's assets. A little C/ Java/ Perl/ etc. program should have the ability to choose a CLOB and write that data to some file around the client operating-system.