1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Can a value from a SQL query be used in a RETURN statement in a function?

Discussion in 'SQL PL/SQL' started by pointspreadpros, Dec 20, 2013.

  1. pointspreadpros

    pointspreadpros Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    In my function, I need to return a numeric value from a Select statement like this:

    Return (Select 1 from DUAL)

    Is there a way to accomplish this without using pl/sql variables?


    I also don't want to create another function that would return a value into a variable (i.e)

    I don't want this either:
    Return Func_Get_My_Val;


    Maybe this isn't possible I am just wondering if there are any other solutions out there.


    Thanks everyone in advance.
    -Jim
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    You have a function that presumably uses PL/SQL and you want that function to return a value to whatever called it via a SQL statement rather than using a variable within that same function?

    Do I understand that correctly?
     
  3. pointspreadpros

    pointspreadpros Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Yep, exactly.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You 'need' to return a numeric value from a select statement?? Why? Why 'can't' you use a PL/SQL variable from that same function? The example you provide makes no sense as 'return 1' IS valid and would return the same value as your invalid statement 'return (select 1 from dual)':


    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION yoopa_noopy(p_ubnot NUMBER)
      2  RETURN NUMBER
      3  AS
      4  BEGIN
      5          IF p_ubnot IS NOT NULL THEN
      6                  RETURN (SELECT 1 FROM dual);
      7          ELSE
      8                  RETURN (SELECT 2 FROM dual);
      9          END IF;
     10  END;
     11  /


    Warning: FUNCTION created WITH compilation errors.


    SQL>
    SQL> SHOW errors FUNCTION yoopa_noopy



    Errors FOR FUNCTION YOOPA_NOOPY:


    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/11     PLS-00103: Encountered the symbol "SELECT" WHEN expecting one OF
             the following:
             ( - + CASE MOD NEW NOT NULL <an identifier>
             <a double-quoted delimited-identifier> <a bind variable>
             continue avg COUNT CURRENT EXISTS MAX MIN prior SQL stddev
             SUM variance EXECUTE forall MERGE TIME TIMESTAMP INTERVAL
             DATE <a string literal WITH CHARACTER SET specification>
             <a number> <a single-quoted SQL string> pipe
             <an alternatively-quoted string literal WITH CHARACTER SET
             specification>
             <an alternat


    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/30     PLS-00103: Encountered the symbol ";" WHEN expecting one OF the
             following:
             ) , AND OR
    8/11     PLS-00103: Encountered the symbol "SELECT" WHEN expecting one OF
             the following:
             ( - + CASE MOD NEW NOT NULL <an identifier>
             <a double-quoted delimited-identifier> <a bind variable>
             continue avg COUNT CURRENT EXISTS MAX MIN prior SQL stddev
             SUM variance EXECUTE forall MERGE TIME TIMESTAMP INTERVAL


    LINE/COL ERROR
    -------- -----------------------------------------------------------------
             DATE <a string literal WITH CHARACTER SET specification>
             <a number> <a single-quoted SQL string> pipe
             <an alternatively-quoted string literal WITH CHARACTER SET
             specification>
             <an alternat
    8/30     PLS-00103: Encountered the symbol ";" WHEN expecting one OF the
             following:
             ) , AND OR


    SQL>
    SQL> SELECT yoopa_noopy(empno) FROM emp;
    SELECT yoopa_noopy(empno) FROM emp
           *
    ERROR at line 1:
    ORA-06575: Package OR FUNCTION YOOPA_NOOPY IS IN an invalid state

    SQL>
    SQL> CREATE OR REPLACE FUNCTION yoopa_noopy(p_ubnot NUMBER)
      2  RETURN NUMBER
      3  AS
      4  BEGIN
      5          IF p_ubnot IS NOT NULL THEN
      6                  RETURN 1;
      7          ELSE
      8                  RETURN 2;
      9          END IF;
     10  END;
     11  /


    FUNCTION created.


    SQL>
    SQL> SHOW errors FUNCTION yoopa_noopy



    No errors.



    SQL>
    SQL> SELECT yoopa_noopy(empno) FROM emp;


    YOOPA_NOOPY(EMPNO)
    ------------------
                     1
                     1
                     1
                     1
                     1
                     1
                     1
                     1
                     1
                     1
                     1


    YOOPA_NOOPY(EMPNO)
    ------------------
                     1
                     1
                     1


    14 ROWS selected.


    SQL>
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> CREATE OR REPLACE FUNCTION yoopa_noopy(p_ubnot NUMBER)
      2  RETURN NUMBER
      3  AS
      4          v_retval NUMBER;
      5  BEGIN
      6          IF p_ubnot IS NOT NULL THEN
      7                  SELECT 1 INTO v_retval FROM dual;
      8          ELSE
      9                  SELECT 2 INTO v_retval FROM dual;
     10          END IF;
     11
     12          RETURN v_retval;
     13  END;
     14  /


    FUNCTION created.


    SQL>
    SQL> SHOW errors FUNCTION yoopa_noopy



    No errors.



    SQL>
    SQL> SELECT yoopa_noopy(empno) FROM emp;


    YOOPA_NOOPY(EMPNO)
    ------------------
                     1
                     1
                     1
                     1
                     1
                     1
                     1
                     1
                     1
                     1
                     1


    YOOPA_NOOPY(EMPNO)
    ------------------
                     1
                     1
                     1


    14 ROWS selected.


    SQL> SELECT yoopa_noopy(mgr) FROM emp;


    YOOPA_NOOPY(MGR)
    ----------------
                   1
                   1
                   1
                   1
                   1
                   1
                   1
                   1
                   2
                   1
                   1


    YOOPA_NOOPY(MGR)
    ----------------
                   1
                   1
                   1


    14 ROWS selected.


    SQL>
     
  6. pointspreadpros

    pointspreadpros Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Because I am tasked with trying to do as much code as I can without using pl/sql variables. The code is going to have to be as close to ANSI compliant as I can get it. So if there is a way around using variables I am open for suggestions.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yet you have written a PL/SQL function ...
     
  8. pointspreadpros

    pointspreadpros Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    As I said I am trying to do as little pl/sql specific as I can.......Im not saying I can do away with all of it. The goal is for me to turn over code that can be easily migrated to Vertica which needs things ANSI compliant.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    'return (select 1 from dual)' doesn't work as you can see from my previous example; also 'select 1 from dual' returns 1, so 'return 1' does the same thing. I can only suppose you want to return a numeric value from some other table via this exercise, but you cannot do it without populating a variable as my second example illustrates.


    Making code 'application' or 'database' agnostic is not the best method for development as you very often times shoot yourself in the foot, performance-wise, by ignoring database-specific enhancements in deference to 'ease of migration'. I can't count the times I've dealt with such 'wisdom'; sometimes I win, sometimes not, but hopefully the client is made aware that performance will suffer with the existing code. I have to ask who's idea this is, to write this 'universal' code. Trust me it isn't the best idea.