Home » Other » Client Tools » 1st timer with writing procedures, please assist (TOAD 8.0)
1st timer with writing procedures, please assist [message #423004] Mon, 21 September 2009 13:26 Go to next message
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 #423007 is a reply to message #423004] Mon, 21 September 2009 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you are maybe not, who knows what you want to do?

Regards
Michel

Report message to a moderator

Re: 1st timer with writing procedures, please assist [message #423008 is a reply to message #423004] Mon, 21 September 2009 14:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>EMP_NAME IN VARCHAR2(50),
>EMP_ID IN VARCHAR2(7)
>EMP_SUP IN VARCHAR2(50)

Why are the above being passed into procedure when they are really being obtained via SELECT FROM OTHER_EMPLOYEE_TABLE?

P.S.
"_TABLE" should not be part of table name, IMO.

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 Go to previous messageGo to next message
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.

Report message to a moderator

Re: 1st timer with writing procedures, please assist [message #423010 is a reply to message #423009] Mon, 21 September 2009 15:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
CREATE OR REPLACE PROCEDURE Employee_update 
IS 
BEGIN 
  INSERT INTO trbmgr.employee_table 
  SELECT date_added, 
         emp_name, 
         emp_id, 
         emp_sup 
  FROM   other_employee_table 
  WHERE  date_added >= Trunc(SYSDATE - 1); 
   
  COMMIT; 
END; 
/

Report message to a moderator

Re: 1st timer with writing procedures, please assist [message #423319 is a reply to message #423004] Wed, 23 September 2009 06:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't want to use a piece of 3rd party client software like TOAD to run your scheduled jobs.

You want to use DBMS_JOB/DBMS_SCHEDULER

Report message to a moderator

Re: 1st timer with writing procedures, please assist [message #423638 is a reply to message #423319] Fri, 25 September 2009 16:19 Go to previous messageGo to next message
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.

Report message to a moderator

Re: 1st timer with writing procedures, please assist [message #423653 is a reply to message #423004] Sat, 26 September 2009 00:38 Go to previous message
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

Report message to a moderator

Previous Topic: oracle 9i and Oracle Developer suit Installation problem
Next Topic: Declaring variable in SQLPLUS command
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 09:27:58 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.