Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Arrow Unanswered: Procedure Return Status

    i am using oracle 8.1.7 and sql server 2000

    In SQL Server

    This example executes the checkcontract stored procedureand stores the return status indicating success or failure in @retstat.

    DECLARE @retstat int
    EXECUTE @retstat = checkcontract '409-56-4008'

    I used the above statments inside stored procedure and its return 0 or 1 to @retstat variable. How could i replicate the same thing in oracle?

    My Question here is how could i get the return status value for the procedure in oracle?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In PL/SQL, a stored procedure with a return value is a function:
    Code:
    create function checkcontract
      return integer
    is
    begin
       ...
       return 1;
    end;
    You would call it like this:
    Code:
    declare
      l_retstat integer;
    begin
      l_retstat := checkcontract;
    end;
    However, if you are moving to Oracle you should learn the Oracle way of working rather than just reworking SQL Server syntax. For example, you could use BOOLEAN rather than integer and use the function like this:

    Code:
    begin
      if checkcontract then
        ... -- Do something when checkcontract successful
      end if;
    end;
    Also, if this status variable is being used to check for error conditions, then you should be using EXCEPTIONS instead of status variables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •