1st timer with writing procedures, please assist [message #423004] |
Mon, 21 September 2009 13:26 |
jrichmo
Messages: 4 Registered: September 2009 Location: AZ
|
Junior Member |
|
|
Hi everyone. I am attempting to create a procedure that I can schedule in TOAD to run nightly. I am attempting to update an existing table with matches from a query of another table with identical fields. Here is what I have so far. If someone could review and let me know if I am on the right track that would be great. When I execute the script I get an error that is a result of my Toad version that I am working on updating.
Thanks!
CREATE OR REPLACE PROCEDURE EMPLOYEE_UPDATE (
DATE_ADDED IN DATE,
EMP_NAME IN VARCHAR2(50),
EMP_ID IN VARCHAR2(7)
EMP_SUP IN VARCHAR2(50)
)
IS
BEGIN
INSERT INTO TRBMGR.EMPLOYEE_TABLE
SELECT EMP_NAME, EMP_ID, EMP_SUP
FROM (SELECT * FROM OTHER_EMPLOYEE_TABLE WHERE DATE_ADDED >= TRUNC(SYSDATE-1);
COMMIT;
END;
/
[Updated on: Mon, 21 September 2009 13:27] Report message to a moderator
|
|
|
|
|
Re: 1st timer with writing procedures, please assist [message #423009 is a reply to message #423004] |
Mon, 21 September 2009 15:02 |
jrichmo
Messages: 4 Registered: September 2009 Location: AZ
|
Junior Member |
|
|
Thanks Blackswan. I agree and the table names with _TABLE in there were for illistration only. So are you saying that I do not need the DATE_ADDED, EMP_NAME, EMP_ID, EMP_SUP in the begining of the procedure? If so is this what it would look like? When I execute this I get the error "script was executed with 0 errors and 1 compile errors."
CREATE OR REPLACE PROCEDURE EMPLOYEE_UPDATE
IS
BEGIN
INSERT INTO TRBMGR.EMPLOYEE_TABLE
SELECT DATE_ADDED, EMP_NAME, EMP_ID, EMP_SUP
FROM (SELECT * FROM OTHER_EMPLOYEE_TABLE WHERE DATE_ADDED >= TRUNC(SYSDATE-1);
COMMIT;
END;
/
Thanks again.
|
|
|
|
|
Re: 1st timer with writing procedures, please assist [message #423638 is a reply to message #423319] |
Fri, 25 September 2009 16:19 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
As a general rule, you shouldn't put a commit in the stored proc., you should leave it to the calling program to decide when to commit. If you have several procs and you call them in some sequence, and together that all form 1 big logical unit of work (1 transaction), then you want to commit once at the end, else roll everything back if any step fails.
|
|
|
Re: 1st timer with writing procedures, please assist [message #423653 is a reply to message #423004] |
Sat, 26 September 2009 00:38 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Yes, but if the calling app is a procedure then that procedure will of course have a commit. I would say that eventually there has to be one procedure that has a commit, the outer one. This assumes of course that you want to use the database as the engine for controling the job.
Kevin
|
|
|