CODEWITHSUNDEEP
plsql
Sahar
Sahar

Reputation: 31

How to restrict PL/SQL code from executing twice with the same values to the input parameters?

I want to restrict the execution of my PL/SQL code from repetition. That is, I have written a PL/SQL code with three input parameters viz, Month, Year and a Flag. I have executed the procedure with the following values for the parameters: Month: March Year : 2011 Flag: Y

Now, If I am trying to execute the procedure with the same values to the parameters as above, I want to write some code in the PL/SQL to restrict the unwanted second execution. Can anyone help. I hope the question is no ambiguous.

Upvotes: 3

Views: 2254

Answers (5)

André Fonseca
André Fonseca

Reputation: 79

Make an insert in the beginning of the procedure, and do a select for update tolock the table so no one else can process any data and if everything goes ok with the procedure, commit and release the table 😀

Upvotes: 0

TTT
TTT

Reputation: 2375

You can use the function result cache: http://www.oracle-developer.net/display.php?id=504 . So Oracle can do this for you.

Upvotes: 4

Mat
Mat

Reputation: 206689

Why not set up a table to track what arguments you've already executed it with?

In your procedure, first check that table to see if similar parameters have already been processed. If so, exit (with or without an error).

If not, insert them and do the processing necessary.

Depending on how tight the requirements are, you'll need to get a exclusive lock on that table to prevent concurrent execution.

A nice plus would be an extra column with "in progress"/"done"/"error" status so that you can check if things are going on properly. (Maybe a timestamp too if that's important/interesting.)

This setup allows you to easily clear some of the executions (by deleting some rows) if you find things need to be re-done for whatever reason.

Upvotes: 1

Evan Larsen
Evan Larsen

Reputation: 9965

Your going to need to keep "State" of the last call somewhere. I would recommend creating a table with a datetime column.

When your procedure is called update this table. So, next time when your procedure is called.. check this table to see when was the last time your procedure was called and then proceed accordingly.

Upvotes: 2

Lost in Alabama
Lost in Alabama

Reputation: 1653

I would create another table that would store the 3 parameters of each request. When your procedure is called it would first check the "parameter request" table to see if the calling parameters have beem used before. If found, then exit the procedure. If not found, then save the parameters and execute the rest of the procedure.

Upvotes: 2

Related Questions