Discussions
Categories
- 382.6K All Categories
- 2.1K Data
- 213 Big Data Appliance
- 1.9K Data Science
- 448.4K Databases
- 221.1K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 541 MySQL Community Space
- 469 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 500 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.3K SQL & PL/SQL
- 21.1K SQL Developer
- 293.8K Development
- 9 Developer Projects
- 130 Programming Languages
- 290.6K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 151 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 146 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 200 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 294 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 168 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 234 Portuguese
bulk table creation using union
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 | ) |
Answers
-
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.
-
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!
-
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?
