Solved

How to output a RETURN value in a function

Posted on 2013-01-14
4
482 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

688 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