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!

function

Discussion in 'SQL PL/SQL' started by bnramesh8, Jun 13, 2014.

  1. bnramesh8

    bnramesh8 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    Can we return table through function?Is it possible?
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    A table of data, a table name or a collection of data that can be referred to as a virtual table? Which one are you asking about?
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    bnramesh8 likes this.
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    In addition to returning a collection 'pipelined' that can be SELECTed as if it were a table, you can use a standard (non-pipelined) function that simply returns a variable that is a defined collection:

    Code (Text):
    CREATE package body tf_pkg
    AS
      TYPE   cl_tab IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;

      FUNCTION tab_demo
      RETURN cl_tab;

      PROCEDURE call_tab_demo;

    END tf_pkg;


    CREATE package body tf_pkg
    AS

    FUNCTION tab_demo
    RETURN cl_tab
    AS
      v_tab1  cl_tab;
    BEGIN
      v_tab1(1) := 'Fred';
      v_tab1(2) := 'George';
      v_tab1(3) := 'Robert';
      RETURN v_tab1;
    END tab_demo;

    PROCEDURE call_tab_demo
    AS
      v_tab2   cl_tab;
    BEGIN
      v_tab2 := tf_pkg.tab_demo;

      DBMS_OUTPUT.PUT_LINE('v_tab2(1): ' || v_tab2(1));
      DBMS_OUTPUT.PUT_LINE('v_tab2(2): ' || v_tab2(2));
      DBMS_OUTPUT.PUT_LINE('v_tab2(3): ' || v_tab2(3));
    END call_tab_demo;

    END tf_pkg;


    BEGIN
      tf_pkg.call_tab_demo;
    END;

    v_tab2(1): Fred
    v_tab2(2): George
    v_tab2(3): Robert
     
    bnramesh8 likes this.