Procedure to hold a sequence number in a table

[amillia]

I am creating a stored procedure to create a new number everyday into a table called Next_NUM. The fields in the table are called Todays_dte and fld_next_num. I am using a sequence for the number to be put into fld_next_num. I should only have one row in the table. This is my first attempt at righting a stored proc and of couse I am getting an error right away at the second line select statement. Please give me any pointers you can. Thank you.


CREATE OR REPLACE PROCEDURE DOCNUM
Select TODAYS_DTE from NEXT_NUM;
Begin
If TODAYS_DTE < SYSDATE
THEN DROP SEQUENCE DOC_NUM
CREATE SEQUENCE DOC_NUM
UPDATE NEXT_NUM
SET (TODAYS_DTE, FLD_NEXT_NUM)=(SYSDATE, DOC_NUM.NEXTVAL)
ELSE IF TODAYS_DTE = SYSDATE
THEN UPDATE NEXT_NUM
SET (TODAYS_DTE, FLD_NEXT_NUM)=(SYSDATE, DOC_NUM.NEXTVAL)
END IF;
COMMIT;
END;
/

 

[SantaMufasa]

Amillia,

We can correct your code to make it compile correctly (there are several syntax issues), but first, we should confirm what you are trying to do.

According to your code, the contents of your table, NEXT_NUM, will ALWAYS contain SYSDATE and "1" in the columns TODAYS_DTE and FLD_NEXT_NUM.

Could you please explain the non-syntactical functionality of what you want your procedure to do (because right now, the functionality does not appear to produce any business-important resutls).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[carp]

CREATE OR REPLACE PROCEDURE DOCNUM AS
   l_dte DATE;
BEGIN
   Select TODAYS_DTE INTO l_dte
     from NEXT_NUM;  
   If (l_dte < SYSDATE) THEN 
      EXECUTE IMMEDIATE 'DROP SEQUENCE DOC_NUM';
      EXECUTE IMMEDIATE 'CREATE SEQUENCE DOC_NUM';
      UPDATE NEXT_NUM 
         SET TODAYS_DTE = sysdate, 
             FLD_NEXT_NUM = DOC_NUM.NEXTVAL;
   ELSIF (TRUNC(l_dte) = TRUNC(SYSDATE)) THEN 
      UPDATE NEXT_NUM
         SET TODAYS_DTE = SYSDATE,
             FLD_NEXT_NUM = DOC_NUM.NEXTVAL;
   END IF;                   
   COMMIT;              
END;
/

A several points here:
1. You can't do direct DDL from PL/SQL - hence the EXECUTE IMMEDIATE calls.
2. Columns and values are paired individually in an update statement.
3. Be very careful when comparing date values to sysdate. Oracle dates include hours, minutes, and seconds - a condition like "todays_dte = sysdate" will almost always return a value of FALSE. By truncating the dates, you eliminate the hours/minutes/seconds and are just comparing day/month/year.
4. When doing a SELECT in PL/SQL, you must give the engine someplace to store the value (e.g., "Select TODAYS_DTE INTO l_dte from NEXT_NUM;"). To set this up, you also must declare the variable before trying to use it.

If you are going to be doing much work in PL/SQL, I would recommend you get a good book to familiarize yourself with the language - something like Steve Feurstein's "Oracle PL/SQL Programming".

 

[amillia]

What i want is for it to contain today's date and then everytime the procedure is run (which will be everytime it is called by a web page)then I want the next num to be incremented. Then if it is a new day then I want the increment to go back to one and start all over.

I hope that makes for sense.

 

[amillia]

Carp this is running without errors when I compile it but it doesn't make any changes to the fields in the database.

 

[amillia]

Okay, we got this going with what carp sent and I had to take out the execute sequence stuff because I didn't have sufficient privileges. Thank you for your help. It was great and I will definately pick up the book you recommended. This is the final code.

CREATE OR REPLACE PROCEDURE DOCNUM AS
l_dte DATE;
LAST_NUMBER NUMBER;
BEGIN
Select TODAYS_DTE, FLD_NEXT_NUM
INTO l_dte, LAST_NUMBER
from NEXT_NUM;
If (TRUNC(l_dte) < TRUNC(SYSDATE)) THEN
UPDATE NEXT_NUM
SET TODAYS_DTE = sysdate,
FLD_NEXT_NUM = 1;
ELSIF (TRUNC(l_dte) = TRUNC(SYSDATE)) THEN
UPDATE NEXT_NUM
SET TODAYS_DTE = SYSDATE,
FLD_NEXT_NUM = LAST_NUMBER + 1;
END IF;
COMMIT;
END;
/

 

[Beilstwh]

Your entire logic is flawed. It will NEVER work. As soon as more then one person is attempting to get a document number AT THE SAME TIME, your logic will fail because they will both receive the same number. What possible reason do you have to NOT use a sequence and what business reason do you have for wanting to reset the document number back to one each day. There are many ways of handling this without doing the classic blunder of setting up a file based sequence.

Bill
Oracle DBA/Developer
New York State, USA