Thanks Bear Bibeault. It has been long time since i saw your reply. How can i do it? A simple way i can guess is to write the java code for copying(to a different location) the backup file (which is somewhere in oracle dir). But i doubt that it will work. What Do you suggest?
Lots of things can be done. However your question is quite vague and it is not easy to give the right answers.
The right question is: what do you need the .sql files for?
If you're serious about using them as a backup, then no - .sql file is never a backup. Oracle backups are managed using a tool called rman and it is not very easy, it is actually far beyond a scope of a post on any internet forum. There is plenty of Oracle documentation for that, actually.
Do you need to move data and table definitions between computers? Then you should definitely look at expdp and impdp - they are far faster and more reliable than SQL scripts.
SQL files can be easily created using SQL Developer. This allows you to include table creation scripts.
And lastly, if you really need to create them using your own Java program, it's all up to you. You obviously need to know the name of the table and names and types of its columns. You can either get this information dynamically using JDBC methods for accessing the database metadata, or you might already know the structure of the table by other means. If you need to include table creation scripts, you can use DBMS_METADATA package.
You'll then write individual INSERT statements into the file, using proper format to encode values (you need to properly escape string data, use to_date function for DATE values, and so on).
(In any case, you may have troubles storing BLOBs, CLOBs or LONGs into SQL files.)
Joined: Jun 05, 2010
Thanks Martin for your reply.
Here is the reason for backups
Actually, I have created a school management application, where if student is promoted (at the end of session), all his old payment details should be deleted (as new data will be saved)after taking out backup. So, why not taking out the backup all info of the user.
Here is the details . I am using status column. When student is admitted, status shows "Admitted", and when he leaves the school, i am changing his status as "Deleted", so in case of promotion, why not set the status as "Promoted".
But, suppose a student is promoted in 2012, all his old records (2011-2012) is set as promoted, his new payment details appears with status as "Admitted". SO far, its good. But, if he gets promotion in 2012 (2012-2013), again his old records will have status as "Promoted". SO, how will i identify whether the records belongs to 2011-2012 session or 2012-2013 session?? I can't afford to change the db structure now, as the project is almost completed. What i thought was to take out the backup, and in pdf format too and then delete the data.
Other solution(i guess a better one) which comes to my mind is to create a table dynamically before the promotion code runs. This table will contain the old payment records. (I will have to create the table and its columns via java code) Will it work??
Just on a very general level: the records should have a date (perhaps just a year) associated with them, so that the database can hold data for many years simultaneously. If the amount of data ever becomes a problem (not very probable, as storage capacity generally grows so fast), Oracle contains mechanisms for archiving old data.
It looks like you've omitted the date column from your DB model, which is a serious flaw. The "backup" solution you're proposing would lead to a "backup" per user and year. Though it might appear to solve your immediate problems, did you think of who and how would manage these "backups"? What if a student disputes a payment several years after it was made? Who will comb through thousands of PDF files to get all the necessary details? And so on.
Creating a table dynamically is slightly better, since the table at least remains in the database, but after several years this will be a mess too. Generally, it is possible to do, though it might complicate or compromise the security of the application. And again, someone will have to manage them somehow. And all this just because of the missing DB column.
You really should rectify your DB model. I'm not sure just adding the date column will solve all your problems, though it seems to me it would solve the problem you've described. This is what you ought to do. Hacking up a workaround can help temporarily, but will hurt you in the long run. You should at least plan how to fix this in the future, if nothing else, and design the workaround to make the future fix easier.
Joined: Jun 05, 2010
Thanks martin fro your reply. My DB has date column. To be more specific date of payment column. But that doesn't solves the problem. Anyways, since the delivery date is near, I will go for dynamically creating table. When the school session ends (march 2013), i will redesign the db.
Kunal Lakhani wrote:I can't afford to change the db structure now, as the project is almost completed...
Oh dear. Where have I heard that one before? Oh yeah, I remember - a lot of failed projects.
There's a lovely old maxim from the 70's that's worth remembering: There's somehow always time and money enough to do it WRONG.
If your database structure is wrong, it needs to be changed; and your project ISN'T "complete" until it is.
What you can do is any of the following:
1. Bite the bullet and do the database change NOW, before you write another line of code.
2. Design the process that needs the change now (including the database change(s) as a pre-requisite), and shelve it until you have the time to do it properly.
3. Implement a BETA version of the process with the existing db, and schedule both the database upgrade and a proper implementation of the process for some future date. But whatever you do, don't take that ".beta" off the project, and don't regard it as complete, until it IS done.
Personally, I like option 2; and if this is for school and I was your prof, I'd dock very few points if you could show me the design documentation and a decent implementation strategy.
If this is for work, you may be forced by office politics to go with either (1) or (3) - you seem to have chosen the latter - but be warned that it's usually the most expensive and least reliable option.
Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Joined: Jun 05, 2010
Thanks Martin and Winston Gutkowski for your reply.
Winston Gutkowski, i will implement your suggestions. But,now it can be done only after the school session ends, i.e march 2013.
I thing which i have realized is that, i am very weak in db designing & i am looking forward to improve my skills on it before the next project starts.
So, Ranchers, please give your valuable suggestions to improve my skills. Also, do recommend any books/sites, as i need it (Its little difficult for me as i am a commerce graduate)
Joined: Jun 05, 2010
I may get 10-15 days extension, for dispatching the project. So, i may opt for re-designing the whole db. So, i need some help. First of all, how to improve my skills in db designing. Give your suggestions. (keeping in mind, that, i am a very bad db designer)
Kunal Lakhani wrote:Give your suggestions. (keeping in mind, that, i am a very bad db designer)
Then I'd suggest finding someone who isn't.
Alternatively, get a good book on the subject. Unfortunately, I started modelling more than 25 years ago, so my suggestions are likely to be way out of date (or possibly even out of print). Mind you, the basics haven't changed much since then, so anything by Ted Codd, Chris Date or Ray Boyce, who are the granddaddies of RDB theory, is probably worth reading - although two of them are now dead.
The main things to understand are the rules of normal form, and for what you want, I doubt if you need much more than 3rd normal form (3NF) and possibly only even 2NF.