What are purity rules for functions? Why they use ? What effects if not follow these rules?
In function and procedure the parameter pass is "call by value" or "call by reference"?
IN :- actual parameter is passed by reference (a pointer to the value is passed in) OUT:- actual parameter is passed by value (a copy of the value is passed out) unless NOCOPY is specified I...
Take this:
IN parameters are passed by reference, so the value cannot be changed in the procedure and functions.
Out and IN-OUT parameters are passed by value, so the values can be changed inthe procedures and fucnctions.
What can be the maximum size of a PL/SQL block?
The maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure. SQL> select * from dba_object_size where name = 'procedure_name'
select * from dba_object_size where name = 'procedure_name';
What is difference between a procedure & function ?
a function is always returns a value using the return statement. a procedure may return one or more values through parameters or may not return at all.
Read Best Answer
Editorial / Best Answer
Answered by: krishnaindia2007
View all answers by krishnaindia2007
Member Since Sep-2007 | Answered On : May 3rd, 2008
1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not.
9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
What is the difference between system procedure and system function?.
Also Procedure can be called from triggers but Function cannot be called from trigger. As function returns value. And no value can be returned to trigger.
User Panel
Categories
Interview Coaching
Interview Question
Career Counselling
NH
The purity level defines what structure the function reads or modifies.the types of purity level:1)wnds--write no database stage I.e&n...
To be callable from SQL statements, a stored function (and any subprograms called by that function) must obey certain "purity" rules, which are meant to control side effects:When called from a SELECT ...