bulk table creation using union

raj2k8mca

Hi Experts,

    please provide me  select query to create an table output like this?

 

	Table_name	column_name
create table	REGIONS(	COUNTRY_ID
		COUNTRY_NAME	)
create table	COUNTRIES(	REGION_ID
		LOCATION_ID
		STREET_ADDRESS	)
create table	LOCATIONS(	POSTAL_CODE
		CITY
		STATE_PROVINCE
		COUNTRY_ID
		REGION_ID
		REGION_NAME
create table	DEPARTMENTS(	DEPARTMENT_ID
		DEPARTMENT_NAME
		MANAGER_ID
		LOCATION_ID
create table	EMPLOYEES(	EMPLOYEE_ID
		EMPLOYEE_ID
		START_DATE
		END_DATE	)

Frank Kulash

Hi,

raj2k8mca wrote:Hi Experts, please provide me select query to create an table output like this?Table_namecolumn_namecreate tableREGIONS(COUNTRY_IDCOUNTRY_NAME)create tableCOUNTRIES(REGION_IDLOCATION_IDSTREET_ADDRESS)create tableLOCATIONS(POSTAL_CODECITYSTATE_PROVINCECOUNTRY_IDREGION_IDREGION_NAMEcreate tableDEPARTMENTS(DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_IDcreate tableEMPLOYEES(EMPLOYEE_IDEMPLOYEE_IDSTART_DATEEND_DATE)

So, you want a query, which is some code that gets stuff out of tables.

Are the table- and column names stored in a table (or maybe more than one)?  Post CREATE TABLE and INSERT statements for that table (or those tables).  See the Forum FAQ:

If the table- and column names are not stored in a table, you can write a query that selects literals from the dual table, but it would be much simpler just to write the output you want in a text editor.

BEDE

Do you already have those tables in some Oracle database? If the answer is yes, then use the facilities od the dbms_metadata paclkage. That would mean calling the procedure dbms_metadata.get_ddl for each table for which you wish to extract. RTM on that.

Other ways of doing somehting more or less as you said wold be using exp and imp utilities or expdp and impdp. Or using the facilities of sqldeveloper or some oter tool to export and import tables with data or without data.

If you do not have those tables anywhere, as long as you are not giving any logic on how those table names, column names and data types are to be deduced from some data found somewhere in the database, then the only way is just to write by hand each create table statement to create the tables as you need them to be.

By the way: until now I've never heard of "bulk table creation". If there is such a thing, please enlighten me!

Billy Verreynne

What about column data types, constraints, physical attributes (e.g. index table as oppose to a hash table), and so on?

Please explain the WHY? behind your question. Just what business requirement are you trying to meet?