Home » SQL & PL/SQL » SQL & PL/SQL » How to take export and import in a procedure (Oracle 9i)
How to take export and import in a procedure [message #426505] Fri, 16 October 2009 01:06 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am getting data in some parking table where data is being dumped in it from other ERP system every day which is populated in other production tables. The master tables are only updated/inserted for new updations/insertions. So if any error occurs while populating data in production tables, then the process would rollback. So no dirty data would be there in production.

In the procedure, for transaction tables the tables data need to be exported, then insert of all record from parking table.

If any error occurs during that population ..may be any validation error or any other error... then the exported table data for transaction tables need to imported back.

I have done export and import on command prompt.

I need to import for specific tables which I do on command prompt using :-

imp file=c:\shlvport.dmp log=c:\shlvport.log fromuser=apps touser=shalina_dummy 
tables =(smp_po_details,smp_good_return_note, smp_inv_transaction,
smp_location_stock, smp_po_schedule, smp_product_plan) 
ignore=y commit=y buffer=100000


Please tell me as how can I do this automatically as the data segregation procedure would run using a schedular. So if any exception or error occurs the exported file taken before dumping data should be imported back to the tables.

How can I write this in the procedure...please help me on this!!

Regards,
Mahi

[Updated on: Fri, 16 October 2009 01:08] by Moderator

Report message to a moderator

Re: How to take export and import in a procedure [message #426507 is a reply to message #426505] Fri, 16 October 2009 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many ways, none direct as there is no direct call to os command in PL/SQL.
You can start to search this (many answers already exist).
You can also do it outside in shell script testing the return code of your process.

Regards
Michel
Re: How to take export and import in a procedure [message #426511 is a reply to message #426505] Fri, 16 October 2009 02:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Upgrade to 10G.
That would give you DBMS_SCHEDULER which will run OS commands, and it would give you DBMS_DATAPUMP, which is a superior version of IMP/EXP and callable from within the database.

Failing that, you'll need to use some Java code to run the os command (see here)
When that command finishes, you'll need to look in the log file using UTL_FILE ans see if there were any errors. If there were, run the commands ro re-import the old file
Re: How to take export and import in a procedure [message #426517 is a reply to message #426511] Fri, 16 October 2009 02:53 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks JRow,
I am looking at it.
Previous Topic: performance tuning / optimization (merged 3)
Next Topic: Pls help me
Goto Forum:
  


Current Time: Wed Sep 28 00:36:23 CDT 2016

Total time taken to generate the page: 0.10062 seconds