Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to output a RETURN value in a function

Posted on 2013-01-14
4
Medium Priority
?
500 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
4 Comments
 
LVL 79

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 17

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

595 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