Stored Procedure and Function

[hawspipe]

Hi All,
I am working with a database that uses a store procedure that calls a function in order to retrieve a "next id". I've been asked to combined the two by putting the logic of the function into the store procedure. After looking many different places on Tek-Tips and the Internet, I can't seem to find out whether or not it can be done.
Here is the function and stored procedure as they currently exists.

CREATE OR REPLACE FUNCTION TCISP_GET_NEXT_ID
                          (F_ID_NAME     IN   CHAR,
                           F_BLOCK_SIZE  IN   NUMBER)
  Return Number Is         F_NEXT_ID  NUMBER;

BEGIN
  Select NEXT_ID + F_BLOCK_SIZE
   Into F_NEXT_ID
    From NEXT_IDS
     Where ID_NAME = F_ID_NAME
      For Update; --With(ROWLOCK)
                  -- adding 'NOWAIT' makes it a NoWait lock.
  Return F_NEXT_ID;

EXCEPTION
  When NO_DATA_FOUND Then
     Return Null;
END;
/

CREATE OR REPLACE PROCEDURE TCISP_NEXT_ID
          (F_ID_NAME     IN   CHAR,
           F_BLOCK_SIZE  IN   NUMBER,
           F_NEXT_ID     OUT  NUMBER)
AS

BEGIN
  F_NEXT_ID := TCISP_GET_NEXT_ID(F_ID_NAME, F_BLOCK_SIZE);

  If F_NEXT_ID Is Not Null Then
    Update NEXT_IDS
    Set NEXT_ID = F_NEXT_ID
    Where ID_NAME = F_ID_NAME;
    Commit;
  End if;

EXCEPTION
  When Others Then
     Rollback;
END;
/

I was hoping that something like the below code could be done, but it doesn't work.

CREATE OR REPLACE PROCEDURE TCISP_NEXT_ID
          (F_ID_NAME     IN   CHAR,
           F_BLOCK_SIZE  IN   NUMBER,
           F_NEXT_ID     OUT  NUMBER)
AS

BEGIN
  Select NEXT_ID + F_BLOCK_SIZE
   Into F_NEXT_ID
    From NEXT_IDS
     Where ID_NAME = F_ID_NAME;
     
    Update NEXT_IDS
    Set NEXT_ID = F_NEXT_ID
    Where ID_NAME = F_ID_NAME;
    Commit;
  End if;

EXCEPTION
  When Others Then
     Rollback;
END;

Any advice or pointing me in the right direction would be greatly appreciated.
Thanks,
Trey

 

[SantaMufasa]

...it doesn't work.

So, do you receive:

1) a syntax error [won't even execute due to an ORA-???? error],
2) a run-time error [starts to execute, but encounters a fatal error during the execution], or
3) a logic error [runs to completion, but the results do not match your expectations]?

Regardless, please post a copy-and-paste of the results of your attempt.

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]

 

[hawspipe]

Sorry, I should have been more specific.

I get the following - Warning: Procedure created with compilation errors.

 

[carp]

Get rid of the orphaned "End if;".

 

[BJCooperIT]

create or replace procedure TCISP_NEXT_ID [COLOR=red][b]is[/b][/color]

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[carp]

If I might make a couple of suggestions that may save you some aggrevation down the road:

1. I would recommend you change the CHAR to VARCHAR2 in your code and (if necessary and possible) in your next_ids table. Otherwise, you may run into problems where character strings are not the same length, since Oracle will right-pad any "short" strings with trailing blanks. Consequently, even though strings may LOOK identical, they are really different. This makes debugging a real pain.

2. Be sure to set the value of f_next_id in your exception handler to something (e.g., NULL or a negative number) to indicate that something went wrong (or better yet, raise an exception). This way, the code that calls this procedure will be able to tell that something has gone wrong and react accordingly.

3. Finally, if you use the following approach, your code only has to visit the table once instead of twice:

CREATE OR REPLACE PROCEDURE tcisp_next_id (f_id_name    IN  VARCHAR2,
                                           f_block_size IN  NUMBER,
                                           f_next_id    OUT NUMBER)
AS
BEGIN  
    UPDATE next_ids
       SET next_id = next_id + f_block_size
     WHERE id_name = f_id_name
    RETURNING next_id INTO f_next_id;
    
    COMMIT;
EXCEPTION
  WHEN OTHERS THEN
     f_next_id := NULL; 
     Rollback;
END;

 

[Beilstwh]

May I ask why you just don't use an oracle sequence. With sequences, you have zero chance of having duplicate ID numbers and will never have to lock the row. What you are attempting to do is what sequences were designed for.

Bill
Oracle DBA/Developer
New York State, USA

 

[hawspipe]

I couldn't agree with you more about "sequences" it would make things much easier. The database was created before I arrived, so I had no input during its creation. However, I have tried to convice the "higher ups" about the benifits of using sequences, but they don't want to make the change yet.

 

[SantaMufasa]

Trey,

I agree, whole-heartedly, with Bill.

For your "higher ups" to delay a replacement-implementation of sequences is similar to having to dig a trench that is 5 meters wide, 5 meters deep, and 2 kilometers long, but the "diggers" have "always used hand shovels", so the higher-ups, in an effort to "manage risk", dismiss/postpone the no-cost use of machinery built specifically for that task from Caterpillar Heavy Equipment Division because "we are familiar with hand shovels, we know that that method works, and we are concerned about the implications of changing over, mid-dig, to using a different methodology/technology.

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]