-1

Is there a way to achieve this in PL/SQL oracle ?

    RETURN (return_status:=1);

It gives a compilation error when I try to do this. If this is not possible please suggest a better alternative instead of doing 

   return_status := 1;
   RETURN (return_status);
M. Haris Azfar
  • 598
  • 6
  • 16
  • 3
    Are you looking for `RETURN 1;`? – Arun Palanisamy Apr 25 '19 at 07:29
  • Nopes I want to assign a value to return_status IN RETURN in one line rather than doing it two i.e. first assigning the value to return_status and then RETURN(return_status). – M. Haris Azfar Apr 25 '19 at 07:33
  • 2
    What's the problem with doing that in two lines? –  Apr 25 '19 at 07:51
  • 1
    Cause of the confusion: In some programming languages (like Java, where you came from), an assignment can also be used as an expression, so you can write `return a=1`, or `a = b = c = 1`. PL/SQL is not such a language. Assignments are just statements and can't be used like that and you have to do it in two lines. There is no better alternative. – GolezTrol Apr 26 '19 at 05:53

4 Answers4

2

When we execute a RETURN the procedure terminates at that point and control flow passes to the calling program. So there would be no value in this construct … 

 RETURN (return_status:=1);

… because nothing in the program unit could act on return_status after the RETURN.

this is a function and return_status is an OUT param

That's the root of your problem: poor design. Either return a value or have it as an OUT parameter but not both. The accepted practice in PL/SQL is that a function returns a value and has no OUT parameters. Only procedures (which don't have RETURN) have OUT parameters.

So you choices are:

  1. return 1 and don't have an OUT parameter
  2. set OUT parameter = 1 and return something else
  3. make it a procedure instead
APC
  • 144,005
  • 19
  • 170
  • 281
0

I dont understand what the problem is?

If you want to return the value return_status then the second option is just fine(if you are really doing a hardcoded assignment you could just return 1.

And I thought maybe you actually have an external variable return_status you are trying to change the value of by calling this function. In which case, use a procedure and have return_status be an IN OUT variable(maybe even just OUT).

TineO
  • 1,023
  • 8
  • 24
  • 3
    @M.HarisAzfar If you're returning the value, you can't also be using that variable anywhere else afterwards - returning a value is the very last step in a function (unless an exception occurs). You can't have your cake and eat it in this case - you either set the variable somewhere, use it in the function and then return it, or you can just return the value directly, without needing to store it in a variable. – Boneist Apr 25 '19 at 07:50
0

AFAIK, you cannot assign value in the Oracle FUNCTIONS RETURN Statement.

From Oracle Docs, the syntax should be

RETURN [[(] expression [)]];

For expression refer Expressions

Only solution i can think of based on your requirement(1 condition instead of 2) is using case expression. Something like below (if you have any condition)

RETURN
    CASE when return_status is not null  
         THEN 1
Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
0

Functions with OUT parameters are permitted but smell wrong.

DomBrooks
  • 1
  • 1
  • What is the issue ? – M. Haris Azfar Apr 26 '19 at 08:08
  • There is no technical issue. It's more of a philosophical/hygiene thing. From the perspective of looking at the calling code, the "normal" behaviour of a function is to take some IN parameters and return a value. The fact that it returns a value is obvious from the invocation. The fact that the function is also modifying a parameter is only obvious if you look at the implementation/declaration of the function. If you need multiple values returned from a function, I would prefer to return a non-scalar datatype – DomBrooks Apr 26 '19 at 08:47
  • or a procedure with multiple out params – DomBrooks Apr 26 '19 at 08:53