Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to output a RETURN value in a function

Posted on 2013-01-14
4
Medium Priority
?
491 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 200 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 668 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 1132 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

609 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