Sign in or 
Location: Oracle Wiki Discussion Forum
Discussion: Oracle tabe creation
Category:
Discussion Forum /
General Discussion
Share this
Showing 6 posts
|
subudhiroshan |
Oracle tabe creation
Jan 11 2009, 5:00 AM EST
Is it possible to dynamically create tables in oracle?Can we assign table names in a dynamic fashion?If yes,how can we do so.... If no,are there any other options... I may sound vague but i am a rookie and just recently installed oracle 11g on my system and trying out some stuff...please do help... Do you find this valuable? |
|
dnunknown |
1. RE: Oracle tabe creation
Jan 12 2009, 8:32 AM EST
Can you explain exactly what you are trying to create here, that might help. There is no dynamic way of creating scripts for DDL, only DML in my experience. I would assume that you are trying to create TABLE_2 the same as TABLE_1, of that can be done w/CREATE TABLE TABLE_2 AS (SELECT * FROM TABLE_1); either keep or truncate TABLE_2 rows Or you can get the output of the DDL for the table to a spool file, alter it to the table which you want and then run the new spooled script; SPOOL X.SQL SET LONG 20000 SET PAGESIZE 0 SELECT DBMS_METADATA.get_ddl ('<OBJECT_TYPE>','<OBJECT_NAME>','<SCHEMA_OWNER>') FROM dual; Other than that you would need to be creative w/ inner selects using the tables; DBA_TABLES DBA_TAB_COLS DBA_COMMENTS DBA_TAB_COMMENTS Do you find this valuable? |
|
Yoram |
2. RE: Oracle tabe creation
Jan 13 2009, 2:41 AM EST
you can simple build the "create table" statement into a string and - in pl/sql use execute immediateSomething like begin execute immediate 'create table aa (id number(2))' ; end ; Another way (old but good) is to write all the create statements into a file and from sqlplus simple run it ... Do you find this valuable? |
|
subudhiroshan |
3. RE: Oracle tabe creation
Jan 13 2009, 5:21 AM EST
well...dnunknown....it is not the same table i want to replicate but create different tables....and yoram...i think ur solution might help me...but i am not good in PL/SQL... like is it possible to do something like.... string s="create table example\' " + i + " \ ' (name varchar(2))"; for(int i=1;i<10;i++) { stmt.executeUpdate(s); } to create tables "example1","example2","example3"...."example10" and so on...in java... please do help... Do you find this valuable? |
|
Yoram |
4. RE: Oracle tabe creation
Jan 13 2009, 8:53 AM EST
Yeap - that will do it
Do you find this valuable?
|
|
subudhiroshan |
5. RE: Oracle tabe creation
Jan 13 2009, 2:07 PM EST
ok yoram...i will try that out..and one more thing...i have vista installed on my system...so i installed oracle 11g since i heard that it is only compatible....so wat is the procedure after that....i mean do i have to set any classpath..etc. or change any other settings??? and i installed java SDK EE....donno wat it does but it was a 176MB download... please help me out... Do you find this valuable? |
