Oracle equivalent of SQL Server table-valued functions Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Oracle equivalent of SQL Server table-valued functions

  1. #1
    Join Date
    Dec 2008

    Oracle equivalent of SQL Server table-valued functions


    The company i have started working for supports both SQL Server and Oracle for the product we release to clients.

    Being from a SQL Server background, i am basically new to Oracle. Where i am stumped at the moment is the equivalent of
    table-valued functions in Oracle. In SQL Server we have functions which using recursion, pass back a table of hierachal data (e.g.
    a hierachical list of people and their managers or reports). Then, joins etc are done with the table set returned from the function to manipulate other data.

    In Oracle, i'm having trouble finding an equivalent of this, and given the tight timescales i don't have much of a time window
    to i would appreciate any advice here from those in the Oracle know-how!

    A simplified example of what we are doing is below, and contains exactly the same logic as we have in our SQL Server database.
    This is basically getting a list of people underneath someone, and the departments they work in.
    Any Oracle equivalent code/suggestions would be much appreciated.

    People table (id int,name varchar,parentid int, departmentid int)

    Department (id, name)

    ---recursive function to get a list of people underneath someone (starting with their ID) -----

    create function getChildItems(@startingID int)
    returns @results table(id int, name varchar(50), parentid int, departmentid int)
    with recurseChildren(id,name,parentid,departmentid)
    select id, name, parentid,departmentid
    from people
    where id=@startingID

    union all

    select,,p2.parentid, p2.departmentid
    from people P2
    inner join recurseChildren RC on

    INSERT INTO @results(id, name, parentid,departmentid) select * from recurseChildren

    ---other code which references the function and joins ----

    select,,, p.parentid
    from department d
    inner join getChildItems(2) p on


  2. #2
    Join Date
    Jul 2002
    Lake Worth, FL

    Talking Hierachical Query

    In Oracle you would need to use a Hierarchical query.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width