CODEWITHSUNDEEP
c#.netoracle-databasestored-procedures
maephisto
maephisto

Reputation: 5182

Oracle function that returns a bool value if succedeed or failed

I'm developing an app using Oracle 11g and ODP.NET, C# and I'm a beginner in Oracle stored procedures. What I need is to write a stored function that will delete a record from a table.

Let's say function fu_delete_user(p_user_id) that deletes the user with that id from table Users. But, maybe due to a constraint, the delete statement threw an exception. In this case I want the function to return false, else it should return true.

How should I write this function / call it from c# ? Thank you!

Upvotes: 0

Views: 1759

Answers (2)

someuser2
someuser2

Reputation: 71

Unless you can guarantee this will be used only within pl/sql, then you may want to make return value a number (1=true, 0=false). SQL doesn't understand boolean (besides, my old school brain thinks in terms of 1s and 0s ;)

Upvotes: 1

Ollie
Ollie

Reputation: 17538

Here is an idea of the code, it would be better in your case to use a function as they are designed to always return a value. Procedures can return values through OUT parameters (as in the example I have given).

Using a function:

CREATE OR REPLACE
FUNCTION p_delete_user (
   p_user_id IN NUMBER
)
   RETURN BOOLEAN
IS
BEGIN
   DELETE FROM users
     WHERE user_id = p_user_id;
   --
   RETURN true;
EXCEPTION
   WHEN others
   THEN
      -- Log error
      RETURN false;
END p_delete_user;

Using a procedure:

CREATE OR REPLACE
PROCEDURE p_delete_user (
   p_user_id IN NUMBER,
   p_success OUT BOOLEAN
)
IS
BEGIN
   DELETE FROM users
     WHERE user_id = p_user_id;
   --
   p_success := true;
EXCEPTION
   WHEN others
   THEN
      -- Log error
      p_success := false;
END p_delete_user;

Hope it helps...

Upvotes: 3

Related Questions