Hi experts,
I would be creating one PL/SQL or stored proc in Oracle next week but am not very much experienced in PL/SQL hence would like to double-check with you all first.
Basically, I have the basic foundation of PL/SQL so I guess that I am not that novice ;). My upcoming PL/SQL would be a kind of clean-up process such as to copy few tables to other tables (backup), delete records from DB, update DB and etc. So here are my doubts.
1. If in the middle of the process, it failed, will it rollback? Or do we need to specify a kind like transaction for it?
2. For backup process (copy one table to another table), is there any easy and fast way to do it? The backup table has one additional field to indicate the year the data is backed up. For now, I can imagine that I need to select all the records, one by one copy it to the new table.
3. If I would like to make the process failed in the middle of the process (only to simulate the fail process), what are the commands that I need to put in my PL/SQL?
Thanks.
Best Regards
David
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Bah.. helps if one checks ones syntax before one posts.
DECLARE
CURSOR c_original_table
IS
SELECT *
FROM original_table;
BEGIN
FOR r_original_table IN c_original_table
LOOP
INSERT INTO backup_table
VALUES (r_original_table.col1, r_original_table.col2, NULL);
--Note where i have inserted null, you would want to insert the YEAR (just extract from SYSDATE), as a note the columns when you insert need to be in the order of the table you are inserting into so put the additional column wherever it should go.
--Insert Commit Here for a commit after each row
END LOOP;
--Insert Commit Here for a commit after total table processed.
END;
Just thought I would add,
To handle an exception you can use the following sort of structure, lets take the case that there is no data found on the table:
DECLARE
--declare here
BEGIN
--Code here
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--do something
WHEN OTHERS
THEN
--do something
END;
The when others statement will always handle any other exceptions you have not defined.
You can also raise your own exceptions.. For example the below:
DECLARE
exception1 EXCEPTION;
BEGIN
--code here
RAISE EXCEPTION1;
--more code
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--do something
WHEN EXCEPTION1
THEN
--do something else
WHEN OTHERS
THEN
--do something
END;
Hope that sheds a bit more light.
Stuart
Stuart,
That really helps. One more thing. There is one statement inside my brain saying that cursor is actually slow. Is this correct? I am also not sure on where can I get that statement from but I recalled it is from some websites that I can't remember now.
>>"I myself have only been working with PL/SQL for about 5 months so I will give you as best answer as I can. Hopefully someone else will back me up."
Nah, at this point of time, I am looking into the concept or the direction on where should I go so your comments are more than adequate for me :).
ravindran_eee, thanks also but I think that Stuart has answered all my questions but I appreciate your comment as well.
David
Hi David,
Actually cursors are quite effective as you only select the data that you need. In this case granted you are selecting everything in the table but take the case where you only wanted col1 and col2 where you were putting a WHERE clause restriction on it.
ie.
CURSOR BLAH
IS
SELECT col1, col2 from TABLE
WHERE col1 = 'Train';
Now what this does for ya is actually gives you a theoretical temporary table that *ONLY* contains the data that you need, as you are only working with the rows and data that have been selected it is in effect quite efficient (plus they are p'easy to code).
Hope that helps.
As a note if you are looking at doing PL/SQL coding in the future more often I could not recommend more highly the books by Steven Feuerstein, they were recommended to me and are a GREAT starting place.
Cheers
Stuart
Okay,
Thank you. I am accepting the answer now.
In addition,
WHEN NO_DATA_FOUND
THEN
--do something
WHEN EXCEPTION1
THEN
--do something else
WHEN OTHERS
THEN
I think that NO_DATA_FOUND and OTHERS are kind like constants in PL/SQL, is there any URL that I can get the rest of the constants to look at?
Thanks again for all your helps.
David
Business Accounts
Answer for Membership
by: nerdzollPosted on 2007-06-18 at 19:45:32ID: 19312786
Hi David,
I myself have only been working with PL/SQL for about 5 months so I will give you as best answer as I can. Hopefully someone else will back me up.
1. If you have not specified any commit and an application error is raised then Oracle will automatically roll back any changes (as stated that is *only* if you have not specified a commit before the error has occured).
2. This is an easy one, do:
DECLARE
CURSOR c_original_table
IS
SELECT * FROM ORIGINAL_TABLE
BEGIN
FOR r_original_table IN c_original_table
LOOP
INSERT INTO BACKUP_TABLE VALUES(r_original_table.co
--Insert Commit Here for a commit after each row
END LOOP;
--Insert Commit Here for a commit after total table processed.
END;
3. to make the program fail to test, add in something liiike
raise_application_error (-20101, 'No data found to upload'); )that will raise a user defined application error
Hope that helps you out.
Regards
Stuart