Home » SQL & PL/SQL » SQL & PL/SQL » export `create table`, 'insert into`...
export `create table`, 'insert into`... [message #203306] Tue, 14 November 2006 13:07 Go to next message
uml_user
Messages: 6
Registered: November 2006
Junior Member
Hello!

I want to export all my data (tables,rows in tables,views,etc)
to the file like

CREATE TABLE MYTABLE (
ID NUMBER(2) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
....
PRIMARY KEY(ID)
);

how can I do it using default oracle queries or queries and pl/sql?
Re: export `create table`, 'insert into`... [message #203317 is a reply to message #203306] Tue, 14 November 2006 15:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You mean, you want the DDL of the objects?
look into DBMS_METATDATA
or
Take an export with rows=n
and import with show=y logfile=somelogfie
THis somelogfile will have all the ddl you need.
Search the forum for more examples.
Re: export `create table`, 'insert into`... [message #203564 is a reply to message #203317] Wed, 15 November 2006 09:37 Go to previous messageGo to next message
uml_user
Messages: 6
Registered: November 2006
Junior Member
i've found some scripts in pl/sql for DDL of creating tables, adding constraints to them, etc

but i can't find the `insert into` DDL scripts

how can i organize it if there are many FK an PK dependencies so i can't insert into table1 if there aren't rows in table2 where foreign keys are of table1?

And is there a solution for creating DDL for `insert into` using DBMS_METADATA ?
Re: export `create table`, 'insert into`... [message #203565 is a reply to message #203564] Wed, 15 November 2006 09:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want the data as well, then you need to look at the EXP utility.
Re: export `create table`, 'insert into`... [message #203566 is a reply to message #203564] Wed, 15 November 2006 09:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
That is inserting data. That is not DDL.
Right?
Export/import will do it for you(primary/foreign keys etc). You really don't want an insert statement for each row!.
If you are doing it manually, disable the constraints,load data and enable constraints.

If the volume of data is large, this is practically very painful.
Options are
1. export and import data
or
2. If the volume is small, you can spool the data to an OS file and use sql*loader to load data back. Again, this a waste of everything. Data is already inside oracle database. So getting it out in particular format and inserting again is not elegant.

So,what is your need?
Why are you doing this?
If the idea is to transport the data between databases (oracle databases), use export/import/dblink.





Re: export `create table`, 'insert into`... [message #203581 is a reply to message #203566] Wed, 15 November 2006 10:23 Go to previous messageGo to next message
uml_user
Messages: 6
Registered: November 2006
Junior Member
Mahesh Rajendran wrote on Wed, 15 November 2006 09:47
That is inserting data. That is not DDL.
Right?
Export/import will do it for you(primary/foreign keys etc). You really don't want an insert statement for each row!.
If you are doing it manually, disable the constraints,load data and enable constraints.

If the volume of data is large, this is practically very painful.
Options are
1. export and import data
or
2. If the volume is small, you can spool the data to an OS file and use sql*loader to load data back. Again, this a waste of everything. Data is already inside oracle database. So getting it out in particular format and inserting again is not elegant.

So,what is your need?
Why are you doing this?
If the idea is to transport the data between databases (oracle databases), use export/import/dblink.




Yeah!
Disabling the constraints is very good idea:))
coz at first i'm creating table scripts,
then i'm altering tables and adding constraints.
before 2nd turn i'll write insert into script.
thank u very much)
Re: export `create table`, 'insert into`... [message #203605 is a reply to message #203581] Wed, 15 November 2006 15:05 Go to previous messageGo to next message
uml_user
Messages: 6
Registered: November 2006
Junior Member
but when trying to write `insert into` script i've got some problems...

declare
v_tname  varchar2( 30 );
v_numRows number;
begin
for t in( select table_name from user_tables ) loop
v_tname := t.table_name;
execute immediate 'select count(*) from ' || v_tname into v_numRows; 
if v_numRows > 0 then
    dbms_output.put_line( 'INSERT INTO '||v_tname||' values(' );

-- here i want to fetch all the values

end if;
end loop;
end;
/


how can i get all the data from the rows in order to get
smth like that:
`insert into my_table values(
'val1','','val3','666',...,'valN' )`

?
Re: export `create table`, 'insert into`... [message #203608 is a reply to message #203605] Wed, 15 November 2006 15:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Seems you are interested in re-inventing the wheel.
Many GUI tools will have this functionaliy.
Last time i checked, Oracle's native tools SQL Developer has this feature. You can download the tool here
http://www.oracle.com/technology/software/products/sql/index.html
and it is free of cost.
Re: export `create table`, 'insert into`... [message #203740 is a reply to message #203608] Thu, 16 November 2006 03:10 Go to previous messageGo to next message
uml_user
Messages: 6
Registered: November 2006
Junior Member
I know that i'm reinventing the wheel.
I know all the export/import tools.
BUT this is the part of my task in university.
Re: export `create table`, 'insert into`... [message #203743 is a reply to message #203740] Thu, 16 November 2006 03:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If it is _your_ task in university, _you_ should build this, not us.
Re: export `create table`, 'insert into`... [message #203749 is a reply to message #203743] Thu, 16 November 2006 03:40 Go to previous messageGo to next message
uml_user
Messages: 6
Registered: November 2006
Junior Member
i dont ask you to write code 4 me,
i just want to know how i can get some data and
how it will be better to do it.
of course it's easier to write `rtfm`,`use exp.exe`, etc
Re: export `create table`, 'insert into`... [message #203759 is a reply to message #203749] Thu, 16 November 2006 04:11 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
We already gave you the hint
>> you can spool the data to an OS file
You seem not to be interested in that.
Your pl/sql will not work.
Use the methods discussed here
http://asktom.oracle.com/~tkyte/flat/
Just tweak the output to include insert statments and quotes.
>>how it will be better to do it.
That is what we are trying to do it for. If you are not willing to accept, good luck.
Previous Topic: Error compiling in 10g
Next Topic: Regular Expression
Goto Forum:
  


Current Time: Wed Dec 07 16:44:59 CST 2016

Total time taken to generate the page: 0.08225 seconds