Home » SQL & PL/SQL » SQL & PL/SQL » can we create a table (Oracle 10g with Linux OS)
can we create a table [message #305260] Mon, 10 March 2008 04:37 Go to next message
rman_yv
Messages: 9
Registered: February 2008
Junior Member
can we create 10 tables that is table1,table2.....table10 with same datatypes in a single procedure?

If yes can please support me with a sample procedure

[Updated on: Mon, 10 March 2008 04:40]

Report message to a moderator

Re: can we create a table [message #305262 is a reply to message #305260] Mon, 10 March 2008 04:42 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
yes ,you can.But it would be a bad idea to do so.


regards,
Re: can we create a table [message #305263 is a reply to message #305260] Mon, 10 March 2008 04:47 Go to previous messageGo to next message
rman_yv
Messages: 9
Registered: February 2008
Junior Member
Actually we are planning to archive the subpartitions data and our subpartitions are list partitions on day field... to exchange the subpartition we need to create new table...so just want to try and if it goes well we will plan to continue ...

can you please forward me the sample procedure in getting this?
Re: can we create a table [message #305264 is a reply to message #305262] Mon, 10 March 2008 04:50 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
DECLARE
  cols VARCHAR2(80) := '(c1 NUMBER, c2 VARCHAR2(20))';
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE t1 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t2 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t3 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t4 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t5 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t6 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t7 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t8 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t8 '||cols;
  EXECUTE IMMEDIATE 'CREATE TABLE t10 '||cols;
END;
/
Re: can we create a table [message #305265 is a reply to message #305260] Mon, 10 March 2008 04:53 Go to previous messageGo to next message
rman_yv
Messages: 9
Registered: February 2008
Junior Member
Thanks very much dhananjay and frank for your response.

I will test the same with my concept.

Thanks once again.
Re: can we create a table [message #305266 is a reply to message #305260] Mon, 10 March 2008 05:03 Go to previous messageGo to next message
rman_yv
Messages: 9
Registered: February 2008
Junior Member
and i do have one more doubt..

If user wants to archive data from some 17th to 29th of month 08.

Is it possible to create the tables as per user given dates and month through procedure?

Even for this can you support me with a sample procedure.
Re: can we create a table [message #305269 is a reply to message #305266] Mon, 10 March 2008 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible to create the tables as per user given dates and month through procedure?

Yes

Quote:
Even for this can you support me with a sample procedure

What about doing it by yourself and posting what you tried?

Regards
Michel
Re: can we create a table [message #305270 is a reply to message #305266] Mon, 10 March 2008 05:10 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
you have been provided with a code by Frank.so, try using the code provided and post where you stuck.

regards,
Re: can we create a table [message #306220 is a reply to message #305260] Thu, 13 March 2008 06:07 Go to previous message
rman_yv
Messages: 9
Registered: February 2008
Junior Member
BEGIN
for X in fromdate..todate
loop
dbms_output.put_line('CREATE TABLE Archiving_month_'||X|| ' (day1 number,month1 number) tablespace ARCHIVING_TS');
--EXECUTE IMMEDIATE 'CREATE TABLE Archiving_month_'||X|| ' '||cols;
end loop;
END;

I created list of tables using this loop

I hope i'm not wrong in creation loop .... even i tested and i got the output

CREATE TABLE Archiving_mnth_6 (day1 number,mnth1 number) tablespace ARCHIVING_TS
CREATE TABLE Archiving_mnth_7 (day1 number,mnth1 number) tablespace ARCHIVING_TS
CREATE TABLE Archiving_mnth_8 (day1 number,mnth1 number) tablespace ARCHIVING_TS
CREATE TABLE Archiving_mnth_9 (day1 number,mnth1 number) tablespace ARCHIVING_TS
if any more suggestions guide me to correct
Previous Topic: char comparision
Next Topic: select with as column header reanming not working
Goto Forum:
  


Current Time: Sat Dec 03 13:48:44 CST 2016

Total time taken to generate the page: 0.07811 seconds