gayu_rec

 Profile Answers by gayu_rec 

• Feb 8th, 2006

 

(1).Only in parameters are allowed.No inout & out parameters are allowed.

(2).A DML statement containing a function cannot perform another DML operation on the same table.

Venu

• Feb 11th, 2006

 

We can use IN and IN OUT parameters with function, in Oracle 9i ver. 9.2. See below,

create or replace function f1 (num in out number) return number
as
begin
dbms_output.put_line ('Inside f1 num='||num);
num:=100;
return 10;
end;
/

declare
n1 number := 1;
begin
dbms_output.put_line('return val='||f1(n1));
dbms_output.put_line('out param='||n1);
end;
/

Anoop Sagar Pradhan

• Feb 17th, 2006

 

Restrictions of Functions:

1: It has to return a value.

2:Cannot return multiple values.

gomathi

• Mar 3rd, 2006

 

note:we can return more then one value using INOUT parameters.

pankaj_gauba

 Profile Answers by pankaj_gauba Questions by pankaj_gauba

• Jun 22nd, 2006

 

Restrictions on Calling Functions from

SQL Expressions

? Functions called from SQL expressions cannot

contain DML statements.

? Functions called from UPDATE/DELETE statements

on a table T cannot contain DML on the same table

T.

? Functions called from an UPDATE or a DELETE

statement on a table T cannot query the same table.

? Functions called from SQL statements cannot

contain statements that end the transactions.

? Calls to subprograms that break the previous

restriction are not allowed in the function.

venkatesh

• Jul 19th, 2007

 

Function cannot have DML statemets and we can use select statement in function
If you create function with DML statements we get message function will be created
But if we use in select statement we get error

Chandra K. Ravi

• Sep 18th, 2007

 

Functions cannot return LONG datatype value.

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 7th, 2008

 

We can use all three types of parameters in Functions
Here is a simple example

CREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C  OUT NUMBER)  RETURN NUMBER IS     D NUMBER(3); BEGIN     B := B + A;     C := B;     D := A + B + C;     RETURN D; END;   DECLARE    A1 NUMBER(3) := 10;    B1 NUMBER(3) := 20;    C1 NUMBER(3) := 30;    D1 NUMBER(3) := 40;  BEGIN    D1 := TEST_FUNC(A1,B1,C1);    DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);  END;

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 7th, 2008

 

In functions we can use all three types of parameters
here is a simple example

CREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C  OUT NUMBER)  RETURN NUMBER IS
    D NUMBER(3);
BEGIN
    B := B + A;
    C := B;
    D := A + B + C;
    RETURN D;
END;
 
DECLARE
   A1 NUMBER(3) := 10;
   B1 NUMBER(3) := 20;
   C1 NUMBER(3) := 30;
   D1 NUMBER(3) := 40;
 BEGIN
   D1 := TEST_FUNC(A1,B1,C1);
   DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);
 END;

samareshp

 Profile Answers by samareshp 

• Apr 15th, 2009

 

Function  having a return type as Boolean ,can't be used in select statement.