OraFAQ Forum: Server Utilities » Export/Import tables based on date condition

Home » RDBMS Server » Server Utilities » Export/Import tables based on date condition (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Export/Import tables based on date condition [message #561881]

Thu, 26 July 2012 01:23

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

Hi,
i'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition.
and the date will be the parameter. my code is like this:

exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query=\"where <date> between &start_date AND &end_date\";

is it possible to do like this, that it should prompt you to enter the start and end date?

then my import script:
imp <username>/<password>@<database> dumpfile=<table_name>.dmp tables=<target_table>;

the idea is get only the records from ProdDB based on the date condition, and append it to the MISDB.

thanks!

Report message to a moderator

Re: Export/Import tables [message #561884 is a reply to message #561881]

Thu, 26 July 2012 01:29

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

is it possible to do like this, that it should prompt you to enter the start and end date?

&var that prompts for a value is a SQL*Plus stuff not an OS one (from which you launch the exp/imp command).
You have to do it in an OS batch script. I don't know how you do it in Windows/DOS.

Anyway, the query should not be \"where <date> between &start_date AND &end_date\" but \"where <date> between TO_DATE\(%start_date%, '<here the format you choose for your date>'\) AND TO_DATE\(%end_date%, '<here the format you choose for your date>'\)\"

Regards
Michel

Report message to a moderator

Re: Export/Import tables [message #561886 is a reply to message #561884]

Thu, 26 July 2012 01:43

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

hi, thanks for your quick response. i'll try to do it first in command line. and the data type of the column PROCESSING_DATE is actually a number.

i tried to do this:

query=\"where processing_date between \%20120601%\ AND \%20120601%\";

but still getting error LRM-00112: multiple values not allowed for parameter 'query'

Report message to a moderator

Re: Export/Import tables [message #561887 is a reply to message #561886]

Thu, 26 July 2012 01:47

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You don't need % if you give constant value.
You need %VAR% to say to Windows to replace the variable VAR by its value in the expression.

I think you should better first post your question in a windows forum to get the basics of Windows scripting and come back here for the part that is Oracle specific.

Regards
Michel

Report message to a moderator

Re: Export/Import tables [message #561889 is a reply to message #561887]

Thu, 26 July 2012 02:04

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

hi,
i saw one of your responses from previous discussion same as this, and thus i went to C:\ drive

C:\>exp cardpro/cardpro@bosmis file=sample.dmp tables=cp_agltmp query="'where agl_processing_date between 20120601 AND 20120601'"

and was able to connect to Oracle but with error

EXP-00028: failed to open sample.dmp for write
Export file: EXPDAT.DMP >

Report message to a moderator

Re: Export/Import tables [message #561890 is a reply to message #561889]

Thu, 26 July 2012 02:06

Littlefoot
Messages: 21759
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You don't have write privileges on C disk's root directory. Go to, for example, your user's "My Documents" and try again there.

Report message to a moderator

Re: Export/Import tables [message #561891 is a reply to message #561890]

Thu, 26 July 2012 02:16

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

Yup! i got it. Smile

so i have DMP file now, but when i tried to import this to another table, since i created a temp table, where it should be the target table for import, it has error.

C:\export\imp cardpro/cardpro@bosmis file=cp_agltmp.dmp tables=tmp_cp_agltmp

IMP-00033: Warning: Table "TMP_CP_AGLTMP" not found in export file

Report message to a moderator

Re: Export/Import tables [message #561892 is a reply to message #561891]

Thu, 26 July 2012 02:20

Littlefoot
Messages: 21759
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You exported a table named CP_AGLTMP.
You want to import into a table named TMP_CP_AGLTMP.

You can't do that. At least, not into the same schema. Which is rather obvious. Furthermore, useless - you could have done the same using CTAS:

create table tmp_cp_agltmp as
  select *
  from cp_agltmp
  where <your condition here>

You could, though, perform import into another schema. Omit TABLES parameter, you don't need it.

Report message to a moderator

Re: Export/Import tables [message #561893 is a reply to message #561892]

Thu, 26 July 2012 02:32

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

I see.. got it!
i imported it to another schema and it worked. Smile

thanks for your response, appreciate it!

Report message to a moderator

Re: Export/Import tables [message #562030 is a reply to message #561893]

Fri, 27 July 2012 03:05

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

Hi,
i have a question: how can you define a parameter where the value of the dates should be the first and last day of the previous month?
on this sample: todaydate=`date +"%Y%m%d"`, it would return 20120727 only.
what i need should be: firstdate = 20120601, lastdate = 20120630.
Thanks a lot!

Report message to a moderator

Re: Export/Import tables [message #562032 is a reply to message #562030]

Fri, 27 July 2012 03:09

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is your OS, version and shell?

Regards
Michel

Report message to a moderator

Re: Export/Import tables [message #562033 is a reply to message #562032]

Fri, 27 July 2012 03:13

bhibe_17
Messages: 24
Registered: July 2012

Junior Member

OS is unix, not sure with the version though.

this is for exporting of tables with the date range condition.

thanks!

Report message to a moderator

Re: Export/Import tables [message #562034 is a reply to message #562033]

Fri, 27 July 2012 03:42

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Which shell? Which Unix?
Possible features on dates depend on this.
Actually it is more a Unix question and you will sure get a better and faster answer on a Unix forum (or even using Google, I found many when I needed them).

Regards
Michel

Report message to a moderator

Previous Topic:	export excel sheet data in database table
Next Topic:	Running exp and imp command on 11.2.0.1.0 Production version

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 23 06:07:39 CDT 2023