Creating Stored Procedures

[THoey]

Stepping out in a new direction. I currently have several Oracle 8 and 7 databases that I have written nightly clean-up scripts for. Until now, I have done this using a combination of SQL, Unix shell scripting and Cron job calls.

What I want to do is create a stored procedure, and then call it with DBMS_JOBS. I connected to the database using Oracle 7.3.4's Schema Manager and selected to create a new procedure. I gave it a name and selected the appropriate schema. For the source of the procedure, I entered the following:

BEGIN
   DELETE FROM C_SALES
   WHERE DATE_OF_SALE < SYSDATE - 400;
   COMMIT;
   DELETE FROM C_OFFERS
   WHERE DATE_OF_SALE < SYSDATE - 400;
   COMMIT;
   DELETE FROM C_CONTACTS
   WHERE DATE_OF_CONTACT < SYSDATE - 400;
   COMMIT;
END;

Really simple code. I create the procedure and it comes back invalid. When I check the errors, it says the following:

PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
(;is with authid deterministic parallel_enable as compress compiled wrapped
The symbol "is" was substituted for "BEGIN" to continue.

The two books I have are pretty worthless. I am going to try and go get the DBA Handbook and the Complete Reference today, but was wondering if anyone could see what is wrong.

Thanks in advance... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Nebuchednezzar]

the most basic procedure creations syntax looks like...

CREATE PROCEDURE your_procedure_name IS
BEGIN
...


END;
/

Notice the keyword IS...
(You can substitute the work AS for IS above - they are synonymous when used here).

 

[karluk]

Did you get this working? I think Nebuchednezzar is right. I no longer have access to Oracle 7's schema manager, but the Oracle 8 schema manager requires you to start the procedure definition with "is" (or "as&quot. At first glance it seems that GUI tool should supply this syntax for you, but it doesn't.

I find it useful to click the "show sql" option. That shows you the exact "create or replace procedure ..." statement that will be executed when you compile.

Insert "is" right before "begin" in your procedure definition and it will probably compile correctly.

 

[THoey]

I tried that and it still did not work. I did use the show SQL. I copied the whole statement over to a SQL*Plus Worksheet and ran it there with the same results.

I am at home now. I will try again Monday.

Thanks... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[carp]

Please post the complete code you are submitting. So far, you have not shown your CREATE OR REPLACE PROCEDURE command.

 

[THoey]

I finally got this to work. The problem I was having was not even an Oracle problem. I mis-typed one of the table names. The following is the code that worked:

CREATE PROCEDURE EHGRIFF.NIGHTLY_CLEANUP IS
BEGIN
   DELETE FROM C_SALES
   WHERE DATE_OF_SALE < SYSDATE - 400;
   COMMIT;
   DELETE FROM C_OFFERS
   WHERE DATE_OF_SALE < SYSDATE - 400;
   COMMIT;
   DELETE FROM C_CONTACT
   WHERE DATE_OF_CONTACT < SYSDATE - 400;
   COMMIT;
END;

Thanks for your assistance... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.