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

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Member Since Sep-2007 | 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.

mahendra pal

• Sep 18th, 2018

Procedure may or may not return value

mangesh patil

• Aug 22nd, 2017

Procedure may or may not return value where as function should return one value.

Improve Answer

Pritty

• Feb 29th, 2016

The GROUP BY keyword helps to retrieve the unique values without using DISTINCT keyword.

Malyadri M

• Jun 22nd, 2015

Code
 

Answer Question  Select Best Answer

HI all
table contain some 1000 records, today I inserted 100 records into table, so I want first 10 records data from 100 records
how can I retrieve

kapil joshi

• Sep 15th, 2012

Select * from employees where rownum

Manoj Kumar

• Aug 28th, 2012

Code
 

Answer Question  Select Best Answer

adarh katiyar

• Jun 17th, 2017

Use PRAGMA AUTONOMOUS_TRANSACTION in trigger block,
issue never comes ...

Amit Bhardwaj

• Feb 9th, 2017

If you are trying to access or modify the triggering table into the same trigger and that too for a row level trigger then Mutating Trigger error occurred.

Answer Question  Select Best Answer

ChevyBlazer

 Profile Answers by ChevyBlazer Questions by ChevyBlazer

• Jul 2nd, 2012

The answer will be between 11-14 . You have to consider two extreme cases where 1. 3 rows with a=10 and 11 rows with b=20 are all distinct rows - then the answer will be 14 2. Where there are 11 rows...

Venkat

• Jun 30th, 2012

The accurate answer is "The result can contain max of 14 records", but this not guaranteed. It can be less also. (Minimum is 10 rows and maximum is 14 rows). It is because there can be some overlpa in the same row between "a" and "b" column.

Answer Question  Select Best Answer

JILEY SINGH

 Profile Answers by JILEY SINGH Questions by JILEY SINGH

• Aug 4th, 2013

Function can be called in select statement but procedure cant be called in select statement. function has return keyword but procedure not

Varun Tiwari

• Jul 4th, 2013

Function Procedure 1. Can be used as column Cant used in select statement in select statement 2. Can be used in where Cant used in where condition c...

Answer Question  Select Best Answer

          Cursor C1 is          Select empno,          ename from emp;          Begin               open C1;           ...

Vivek

• Jun 29th, 2012

After committing first oracle will commit the transaction into redo log file and then Data file. The data block will remain into the memory until the logical reads are going on. Oracle use LRU algorit...

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 27th, 2011

The data will be written from redo log buffer into redo log files.

Answer Question  Select Best Answer

Shivam Softa

• Sep 19th, 2018

SELECT LISTAGG(VAL) WITHIN GROUP(ORDER BY LE) FROM
(select LEVEL LE , substr(SHIVAM,-LEVEL,1) VAL FROM DUAL CONNECT BY LEVEL

ushodaya

• May 20th, 2018

Declare
str1 varchar2(50):=&str;
str2 varchar2(50);
len number;
i number;

begin
len:=length(str1);

for i in reverse 1..len
loop
str2:=str2 || substr(str1,i,1);
end loop;

dbms_output.put_line(Reverse of String is:||str2);
end;
/

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: answertoyourquery

• Nov 9th, 2005

---

Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function. The code in this part of the program gets executed like those in the body without any restriction. You can include any business functionality whenever a condition in main block(body of a proc/func) fails and requires a follow-thru process to terminate the execution gracefully!

Leena Roja

• Jul 13th, 2015

Correct Subhash..

Susil Kumar Nagarajan

• Jan 21st, 2012

Yes. You can very well use COMMIT or ROLLBACK in exception block. But it is not a good idea to use, as it will commit the previous transactions in the same PLSQL block. Make use of creating SAVEPOINTS and commiting them when required.

Improve Answer

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

Please find the template. Using out type parameter we can return more than one value from a function.

Code
 

shyam

• Mar 2nd, 2015

Yes, we return more than one value in function using package. Example::

Code
create package pack
is
type mul_val is record
(emp_id employees.employee_id%type,
emp_name employees.last_name%type);
end pack;

and the return type of function is that pack type.....

Answer Question  Select Best Answer

Write a query to retrieve one entire column data of multiple rows into one single column of single row?

hema nagaraju

• Dec 20th, 2014

SELECT listagg (ename, ,) within group( order by ename) FROM emp

Geet

• Dec 4th, 2014

Code
span style="color: #F00;">"EMPLOYEE NAME"

Answer Question  Select Best Answer

 %  ROWTYPE  is to be used whenever query returns a entire row of a table or view. TYPE  rec  RECORD is to be used whenever query returns columns of differenttable or views and variables.      E.g.  TYPE  r_emp is RECORD (eno emp.empno% type,ename emp ename %type);     e_rec emp% ROWTYPE     cursor...

Nupur

• Apr 30th, 2015

This is my understanding: Declare : eno employee.emp_id%type - specifically tells you that the variable type or data that it will be holding will be same as th e employee_id type in the employee tabl...

Nupur

• Apr 24th, 2015

When we declare a var as %row type, then the variable holds all the column type attributes. Developer does not need to know what the individual column types are. Type record is used when we declare a variable to be of the records of the table.?

Answer Question  Select Best Answer

Ram

• Oct 29th, 2015

You can create n number of triggers on a table but maximum types of triggers you can create are 12.

Ratnesh gupta

• Apr 2nd, 2015

There is no technical limit on the number of triggers you define (or, at least, not one that any rational person has ever hit). It generally wouldnt make sense to have more than one trigger of a given...

Answer Question  Select Best Answer

rayavarapu

 Profile Answers by rayavarapu Questions by rayavarapu

• Jan 23rd, 2013

Null is not a value and not a string. It is unknown it is maximum in descending order and minimum value in ascending order.

Sandeep

• Mar 7th, 2012

Char(0)

Answer Question  Select Best Answer

MURTHY

• Sep 7th, 2011

the question is very nice. yes we can have 2 triggers with same body but with different names. ex: create trigger trg1 .................... ............................... end; create tri...

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

yes, we cant said upto 10g oracle server executes the triggers randomly,but 11g we are having a feature follows clause,by using this we have to control the execution of triggers explicitly by the deve...

Answer Question  Select Best Answer

Dharma

• Oct 14th, 2011

Code
 

mohan

• Oct 14th, 2011

SELECT * FROM TABLE_NAMEWHERE (ROWID, 0) IN (SELECT ROWID, MOD(ROWID, N) FROM TABLE_NAME);

Answer Question  Select Best Answer

 A set of related declarations and procedural statements is called block.

Read Best Answer

Editorial / Best Answer

Answered by: Chandra Shekhar

• Oct 3rd, 2005

---

PL/SQL Block contains :

Declare : optional

Variable declaration

Begin  : Manadatory

Procedural statements.

Exception : Optional

any errors to be trapped

End :  Mandatory

So only begin and end keywords are required, as needed, to process a pl/sql block.

vamsi

• Jun 8th, 2017

Cursors
collections
procedures
functions
triggers

Kevin Korir

• Dec 10th, 2016

Declare: optional
Begin:mandatory-program logic of SQL&plsql statements
Exception: optional (error handling)
End:mandatory
(Forward slash / terminates)

Improve Answer

PRADEEP

• Oct 16th, 2012

Ref cursor is having return type where as cursor does not have .
syntax of ref cursor:- type ref_type_name is ref cursor [return return_type].

syntax of cursor:- declare
cursor cursor name select statement..

pradeep

• Jan 8th, 2012

Normal cursors do have a return type. ex:

Code
 

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

g_sidhu  

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 1st, 2008

---

Before a PL/SQL program can be executed, it must be compiled. The PL/SQL compiler resolves references to Oracle objects by looking up their definitions in the data dictionary. Then, the compiler assigns storage addresses to program variables that will hold Oracle data so that Oracle can look up the addresses at run time. This process is called binding.

How a database language implements binding affects runtime efficiency and flexibility. Binding at compile time, called static or early binding, increases efficiency because the definitions of database objects are looked up then, not at run time. On the other hand, binding at run time, called dynamic or late binding, increases flexibility because the definitions of database objects can remain unknown until then.

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 25th, 2011

This is the best answer I found:

Early binding is checking for existence of objects referred in the program unit during the compilation phase. Late binding will not check the existence of objects till the runtime

pramodh holla

 Profile Answers by pramodh holla Questions by pramodh holla

• Sep 21st, 2010

It uses deferred binding...in the sense it uses both static and dynamic binding.

Improve Answer

In Emp table there is a column which consists of gender (Male or Female) ? How should one

Can update the Values of Male to Female and Female to Male with a single query ?

sandeep

• Nov 22nd, 2014

I need to update in the table not to select can u guys show me how to change directly in the database

NARSIMHA

• Nov 13th, 2014

SELECT DECODE(GENDER,M,F,F,M,NULL) FROM EMP

Answer Question  Select Best Answer