Home » SQL & PL/SQL » SQL & PL/SQL » Getting Insert Statements (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0)
Getting Insert Statements [message #445224] Fri, 26 February 2010 09:31 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

I have a read access on all the 200+ tables in a schema,
How can i generate 200+ .sql files with insert statements
on all the tables of schema.



Thanks

Re: Getting Insert Statements [message #445231 is a reply to message #445224] Fri, 26 February 2010 09:46 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post an example of what you EXACTLY want.

Regards
Michel
Re: Getting Insert Statements [message #445232 is a reply to message #445231] Fri, 26 February 2010 09:52 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for Quick reply,

Basically want to have .sql files with insert statements of the tables, and to use them on a new schema build from a exp of Master schema creation file,

These files will insert records in that empty schema, these tables are treated as Basic Static Data Tables.


Thanks
Re: Getting Insert Statements [message #445233 is a reply to message #445224] Fri, 26 February 2010 09:53 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why not just use datapump?
Re: Getting Insert Statements [message #445235 is a reply to message #445233] Fri, 26 February 2010 09:59 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
<code>Why not just use datapump? </code>

I have read access only,I am not the DB box, accessing the DB with my read priviliges, I guess datapump will not work.

Any other hints Please,
Re: Getting Insert Statements [message #445237 is a reply to message #445235] Fri, 26 February 2010 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use export.
Export works well with read access.

Regards
Michel
Re: Getting Insert Statements [message #445238 is a reply to message #445237] Fri, 26 February 2010 10:06 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks,


can you please give me Exp parameters, which will generate in Insert statements. and which i can extract as .sql.


Thanks

Re: Getting Insert Statements [message #445239 is a reply to message #445224] Fri, 26 February 2010 10:07 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why do you think you need insert statements?
What's the problem with using imp/exp in the normal way?
Re: Getting Insert Statements [message #445240 is a reply to message #445239] Fri, 26 February 2010 10:11 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
To use those .sql insert statement files where we want across different release/Regions ( dev/ Ist/...)
This will be Static data .

Since we don't have to engage DBA time by asking him to exp/ Imp always.

If there is a way that will help us,

Thanks for looking into it

Re: Getting Insert Statements [message #445242 is a reply to message #445240] Fri, 26 February 2010 10:16 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
faiz_hyd wrote on Fri, 26 February 2010 16:11
To use those .sql insert statement files where we want across different release/Regions ( dev/ Ist/...)
This will be Static data .

Not sure what you mean by that.

faiz_hyd wrote on Fri, 26 February 2010 16:11

Since we don't have to engage DBA time by asking him to exp/ Imp always.


Why do you need a DBA? We've got scripts that do imp/exp which were written by developers and we just use those.
Re: Getting Insert Statements [message #445248 is a reply to message #445240] Fri, 26 February 2010 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
faiz_hyd wrote on Fri, 26 February 2010 17:11
To use those .sql insert statement files where we want across different release/Regions ( dev/ Ist/...)
This will be Static data .

Since we don't have to engage DBA time by asking him to exp/ Imp always.

If there is a way that will help us,

Thanks for looking into it

No problem with region for exp/imp
You don't need a DBA to exp/imp just to read the documentation.
But "exp owner=XXX" and "imp full=y" should do the trick for you.

Regards
Michel

[Updated on: Fri, 26 February 2010 10:43]

Report message to a moderator

Re: Getting Insert Statements [message #445370 is a reply to message #445248] Mon, 01 March 2010 04:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you use Oracle's SQL Developer, on the Tools menu there is a Database Export option.
This doesn't use the standard EXP, but instead creates a file containing SQL statements to create all the tables and insert the data into them.

Or, it would be fairly straight forward to write a piece of code that would look at *_TAB_COLUMNS and create a set of INSERT statements for each table.
Re: Getting Insert Statements [message #445546 is a reply to message #445224] Tue, 02 March 2010 07:33 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
cookiemonster, not all companies work the same way. For example, my company has for many years been tightening access to all its oracle databases. This always translates into less capability for everyone. As an example, I have no access to exp/imp or datapump. Indeed, no access to any database server tools.

Indeed, this is actually getting quite common. I would even go so far as to say that most people who work with Oracle, do not have this access anymore. Think of it like this:

for many developers, if you can't do it from windows, you can't do it.

So... how would a developer run exp/imp from windows against a database installed on a restricted access Unix server? The developer is not allowed direct access to the database server.

Of course this does bring up a question... why does the OP need to do what they are asking? Is this for a production job? If so then maybe they should be given more privileges for it, or put in touch with other developers that have access to the necessary locations and tools.

With SECURITY becomming a higher priority for major companies, working with Oracle is not like the old days.

Good luck, Kevin
Re: Getting Insert Statements [message #445562 is a reply to message #445546] Tue, 02 March 2010 08:25 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Tue, 02 March 2010 13:33
cookiemonster, not all companies work the same way. For example, my company has for many years been tightening access to all its oracle databases. This always translates into less capability for everyone. As an example, I have no access to exp/imp or datapump. Indeed, no access to any database server tools.

Indeed, this is actually getting quite common. I would even go so far as to say that most people who work with Oracle, do not have this access anymore. Think of it like this:

for many developers, if you can't do it from windows, you can't do it.

So... how would a developer run exp/imp from windows against a database installed on a restricted access Unix server? The developer is not allowed direct access to the database server.

Good points, which I do know but tend to overlook sometimes since I'm not subject to such restrictions.

Kevin Meade wrote on Tue, 02 March 2010 13:33

Of course this does bring up a question... why does the OP need to do what they are asking? Is this for a production job? If so then maybe they should be given more privileges for it, or put in touch with other developers that have access to the necessary locations and tools.


Indeed. I would generally avoid getting insert statements as a method for moving large chunks of data between dbs as it's not particularly efficient.
If it's a one shot deal then performance probably doesn't matter that much.
If on the other hand this is something that's going to be done on a semi-regular basis then an alternative using imp/exp or datapump is probably needed.
There's also the question of data integrity - depending on how the inserts are generated they may not be a read-consistent set (If you're using PL/SQL developer or similar to do multiple tables from a live system they probably won't be). Again we need to know the OPs intention to know if it matters.
Previous Topic: update block issue
Next Topic: cursor prblem?
Goto Forum:
  


Current Time: Thu Sep 29 20:58:38 CDT 2016

Total time taken to generate the page: 0.13590 seconds