Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to output a RETURN value in a function

Posted on 2013-01-14
4
Medium Priority
?
496 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 78

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

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…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

578 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