I have a working SP that checks for the presence of a certain record and, if it is not present, inserts the record.

My hope is to find a way to return a string to my calling ASP.NET page that gives the user a clue as to whether the record already existed or a new record was added.

My working SP, without any code to return my status phrases, is as follows:

create or replace
PROCEDURE ADDPROSPECTIVEMEMBER
( P_FirstName IN VARCHAR2
, P_LastName IN VARCHAR2
, P_Email IN VARCHAR2
) AS
v_recordexists NUMBER;
BEGIN
-- Check to see if a record already exists
  SELECT COUNT (*)
  INTO   v_recordexists
  FROM   Members
  WHERE  FirstName = P_FirstName
  AND Lastname = P_LastName
  AND Email = P_Email;
-- If Prospective Member does not exist, insert record
  IF v_recordexists = 0 THEN
   -- Insert row into Members table
  INSERT INTO Members (FirstName,LastName,Email)
  values (P_FirstName,P_LastName,P_Email);
  End If;
  
-- No errors; perform COMMIT
  COMMIT;

-- Exception handling section
  WHEN OTHERS THEN
    -- Enable standard output
    DBMS_OUTPUT.ENABLE;
    -- Put line into the standard output
    DBMS_OUTPUT.PUT_LINE('Error');
    -- Rollback all changes
    ROLLBACK;

END ADDPROSPECTIVEMEMBER;

The above SP works correctly.

Following is one of the unsuccessful ways I revised the procedure in an attempt to return my status message. Note the additional code in bold letters.

 create or replace
PROCEDURE ADDPROSPECTIVEMEMBER
( P_FirstName IN VARCHAR2
, P_LastName IN VARCHAR2
, P_Email IN VARCHAR2

, P_Status OUT NOCOPY VARCHAR2

) AS
v_recordexists NUMBER;
BEGIN
-- Check to see if record already exists
  SELECT COUNT (*)
  INTO   v_recordexists
  FROM   Members
  WHERE  FirstName = P_FirstName
  AND Lastname = P_LastName
  AND Email = P_Email;
-- If Prospective Member does not exist, insert record
  IF v_recordexists = 0 THEN
   -- Insert row into Members table
  INSERT INTO Members (FirstName,LastName,Email)
  values (P_FirstName,P_LastName,P_Email);

--Send status message back to user
  P_Status := 'Record Added'; 

  End If;
  
 If v_recordexists = 1 THEN
 P_Status := 'Record Not Added';
 End If;

-- No errors; perform COMMIT
  COMMIT;

-- Exception section

EXCEPTION
  WHEN OTHERS THEN
    -- Enable standard output
    DBMS_OUTPUT.ENABLE;
    -- Put line into the standard output
    DBMS_OUTPUT.PUT_LINE('Error');
    -- Rollback all changes
    ROLLBACK;

END ADDPROSPECTIVEMEMBER;

One of the interested things about this particular version of my code is that compiling the above SP does not generate an error and I don't get an indication of an error via the web page that calls the SP.  What the above code does not produce is an inserted record and it does not produce an outputted status string in my web page label.

My asp.net code for calling the procedure, based on a button click event, is as follows:

******** END OF CALLING CODE **********************************************************************8 

I do know that the connection opens. I also know that if I comment out the output-related vb code on the web page and within the SP a record is successfully added based on the IF statement. 

Thanks for your help.

Discovered the problem ...

Made a change to the VB Stored Procedure calling code on my ASP.Net page.

My incorrect output parameter code was as follows:

Dim objStatusParam As New OracleParameter("Status", OracleDbType.Varchar2)

The output parameter code that worked looks like this: