Can we call a function in sql query and what are the pre requisites to call a function in sql query?

  • Interview Candidate
  • 9
  • 36944

Editorial / Best Answer

Answered by: Jayakumar M

  • Nov 8th, 2005

Yes. We can call Functions from SQL statements.

To be callable from SQL statements, a stored function must obey the following

"purity" rules, which are meant to control side effects:

 When called from a SELECT statement or a parallelized INSERT, UPDATE, or

DELETE statement, the function cannot modify any database tables.

 When called from an INSERT, UPDATE, or DELETE statement, the function

cannot query or modify any database tables modified by that statement.

 When called from a SELECT, INSERT, UPDATE, or DELETE statement, the

function cannot execute SQL transaction control statements (such as COMMIT),

session control statements (such as SET ROLE), or system control statements

(such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as

CREATE) because they are followed by an automatic commit.

Showing Answers 1 - 9 of 9 Answers

Jayakumar M

  • Nov 8th, 2005
 

Yes. We can call Functions from SQL statements.

To be callable from SQL statements, a stored function must obey the following

"purity" rules, which are meant to control side effects:

 When called from a SELECT statement or a parallelized INSERT, UPDATE, or

DELETE statement, the function cannot modify any database tables.

 When called from an INSERT, UPDATE, or DELETE statement, the function

cannot query or modify any database tables modified by that statement.

 When called from a SELECT, INSERT, UPDATE, or DELETE statement, the

function cannot execute SQL transaction control statements (such as COMMIT),

session control statements (such as SET ROLE), or system control statements

(such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as

CREATE) because they are followed by an automatic commit.

  Was this answer useful?  Yes

ardsouza

  • May 16th, 2006
 

Moreover, apart from the ones mentioned by Jaykumar, the function should not have any OUT or INOUT parameters.

  Was this answer useful?  Yes

amahanit

  • Oct 8th, 2007
 

to call a user function from a sql query the function should meet the purity level using
pragma restrict_refrences. the purity levels are WNDS,RNDS,WNPS,RNPS.
other than that it should be a stored  function,it should accept only IN parameters by positional notation,it should  only return sql datatypes.

  Was this answer useful?  Yes

rajakumar_na

  • Nov 4th, 2007
 

Yes. One more point if we call a function in sql query we are using only SQL data types in that function not PL/SQL data types like BOOLEAN etc...

  Was this answer useful?  Yes

rajakumar_na

  • Nov 17th, 2007
 

Restrictions on Calling Functions from SQL Expressions

To be callable from SQL expressions, a user-defined function mus:

  • Be a stroed function
  • Accept only IN parameters
  • Accept only valid SQL data types, not PL/SQL specific types, as parameters
  • Return data types that are valid SQL data types, not PLSQL specific types

  Was this answer useful?  Yes

Imran_Javed

  • May 8th, 2008
 

Yes we can do this. Look at the following example


/*Function to display max of salary within a department*/
CREATE  OR REPLACE FUNCTION max_sal(ideptno IN INTEGER)
RETURN INTEGER
IS
vsal INTEGER;
BEGIN
    SELECT MAX(sal) INTO vsal
    FROM   emp
    GROUP BY deptno
    HAVING deptno = ideptno;
RETURN vsal;
END;

/*Call the function in SQL query*/
select max_sal(10) from dual;

  Was this answer useful?  Yes

nis1981

  • Aug 3rd, 2008
 

Yes, you can call function in sql query, these functions should be in-built or predefined in the DBMS.

  Was this answer useful?  Yes

planet100

  • Apr 19th, 2011
 

I have a question... what if the function returns long??
i am not able to use in the select query.It says
" ORA-00997: illegal use of LONG datatype " .Is there any other way to address this, other than changing the long to clob??i cannot change due to some constraints from my application side..

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions