Solved

How to output a RETURN value in a function

Posted on 2013-01-14
4
477 Views
Last Modified: 2013-01-15
CREATE OR REPLACE FUNCTION INOUT_FN(OUTPARM IN OUT VARCHAR2) RETURN VARCHAR2 IS

              PROCEDURE W(STR VARCHAR2) IS
              BEGIN
                            DBMS_OUTPUT.PUT_LINE(STR);
              END;

BEGIN
              OUTPARM := 'Coming out';
              W(OUTPARM);
              RETURN 'return param';
              
END INOUT_FN;

Open in new window


how can I output this line:
RETURN 'return param';

Open in new window

?

I've tried several ways but keep getting errors
0
Comment
Question by:Mark_Co
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 total points
ID: 38776043
Define 'ouptut it'.

I assume you are working form the example in the link below.

It has a test of the function in the page.

What are your errors?

http://psoug.org/snippet/FUNCTIONS-IN-OUT-parameter_873.htm
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 167 total points
ID: 38776092
maybe your example is oversimplified  but based on what you have posted,  change the parameter to not be an IN OUT

that is...

change  

(OUTPARM IN OUT VARCHAR2)

to be  

(OUTPARM IN  VARCHAR2)


you can't have OUT parameters in a sql statement if you're trying something like...


select inout_fn('test') from dual;


if it must be an in out parameter then you'll have to set up a pl/sql block as shown in the link from slightwv
0
 
LVL 16

Accepted Solution

by:
Swadhin Ray earned 283 total points
ID: 38777215
Check this function :

CREATE OR REPLACE
  FUNCTION INOUT_FN(
      OUTPARM IN VARCHAR2)
    RETURN VARCHAR2
  IS
  PROCEDURE W(
      STR VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(STR);
  END;
  BEGIN
    --OUTPARM := 'Coming out';
    W(OUTPARM);
    RETURN 'return param';
  END INOUT_FN;

Open in new window


OUTPUT after running the function:

SQL> select inout_fn('Coming out') from dual;

INOUT_FN('COMINGOUT')
--------------------------------------------------------------------------------
return param

SQL>

Open in new window



You can also write a if else condition to check if the desired input is coming or not like as below:

create or replace 
FUNCTION INOUT_FN(OUTPARM IN VARCHAR2) RETURN VARCHAR2 IS

              PROCEDURE W(STR VARCHAR2) IS
              BEGIN
                            DBMS_OUTPUT.PUT_LINE(STR);
              END;

BEGIN
W(OUTPARM);
              if OUTPARM = 'Coming out'
           then             
              RETURN 'return param';
              else 
              return 'no coming out' ;
              end if;
              
END INOUT_FN;

Open in new window



OUTPUT:

SQL> select inout_fn('Coming out') from dual;

INOUT_FN('COMINGOUT')
--------------------------------------------------------------------------------
return param

SQL> select inout_fn('out') from dual;

INOUT_FN('OUT')
--------------------------------------------------------------------------------
no coming out

SQL>

Open in new window

0
 

Author Closing Comment

by:Mark_Co
ID: 38779875
thanks!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 70
how to double quote a string for an inline sql statement. 8 94
format dd/mm/yyyy parameter 16 48
Help with Oracle IF statment 5 36
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question