user defined function

Posted on 2011-10-28
Last Modified: 2013-12-07
The following restriction has been mentioned in the oracle documentation while invoking an user defined function from an sql expression:
•It must be a row function, not a column (group) function; that is, its argument cannot be an entire column.

Can somebody please explain this with an example? How can the entire column be passed as an argument to an user defined function?

Thanks in advance.
Question by:subratoc
    LVL 73

    Expert Comment

    aggregates,  like MAX, MIN, AVG  work on a column for all rows in a group
    LVL 73

    Expert Comment

    as opposed to something like  TO_CHAR  which works on the value within a single row

    Author Comment

    Hi sdstuber,
    Thanks for your responses but can you please explain this in the context of an user defined function?
    MAX, MIN, AVG  are aggregate functions predefined by oracle but
    on reading the documenation "argument cannot be an entire column" I was wondering
    how can the entire column be passed as an argument to an user defined function?

    e.g. I run the query,

    select col1, fn_user_def(col1) from tab1;

    No problems. I am passing the value of col1 here for every row to the function fn_user_def.
    But how is it possible to pass the entire column to fn_user_def
    as if I am calling "fn_user_def(entire_column_parameter)"?
    LVL 73

    Accepted Solution

    I'm not sure what you mean?

    That is exactly what I'm trying say.

    you can ONLY pass an entire column to a function if that function is an aggregate.
    and, when you do that,  it's implicit, the aggregate knows how to process the column

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This post first appeared at Oracleinaction  ( 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 shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now