Home » RDBMS Server » Server Utilities » Export/Import tables based on date condition (Oracle 10g)
Export/Import tables based on date condition [message #561881] Thu, 26 July 2012 01:23 Go to next message
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!
Re: Export/Import tables [message #561884 is a reply to message #561881] Thu, 26 July 2012 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
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
Re: Export/Import tables [message #561886 is a reply to message #561884] Thu, 26 July 2012 01:43 Go to previous messageGo to next message
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'
Re: Export/Import tables [message #561887 is a reply to message #561886] Thu, 26 July 2012 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
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
Re: Export/Import tables [message #561889 is a reply to message #561887] Thu, 26 July 2012 02:04 Go to previous messageGo to next message
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 >
Re: Export/Import tables [message #561890 is a reply to message #561889] Thu, 26 July 2012 02:06 Go to previous messageGo to next message
Littlefoot
Messages: 21595
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.
Re: Export/Import tables [message #561891 is a reply to message #561890] Thu, 26 July 2012 02:16 Go to previous messageGo to next message
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
Re: Export/Import tables [message #561892 is a reply to message #561891] Thu, 26 July 2012 02:20 Go to previous messageGo to next message
Littlefoot
Messages: 21595
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.
Re: Export/Import tables [message #561893 is a reply to message #561892] Thu, 26 July 2012 02:32 Go to previous messageGo to next message
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!
Re: Export/Import tables [message #562030 is a reply to message #561893] Fri, 27 July 2012 03:05 Go to previous messageGo to next message
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!
Re: Export/Import tables [message #562032 is a reply to message #562030] Fri, 27 July 2012 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your OS, version and shell?

Regards
Michel
Re: Export/Import tables [message #562033 is a reply to message #562032] Fri, 27 July 2012 03:13 Go to previous messageGo to next message
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!
Re: Export/Import tables [message #562034 is a reply to message #562033] Fri, 27 July 2012 03:42 Go to previous message
Michel Cadot
Messages: 67370
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
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:
  


Current Time: Sun Sep 27 13:04:01 CDT 2020