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

tejascool

• May 23rd, 2017

A unique key constraint can be applied to single field or combination of fields which uniquely identifies a record.
unique key constraint allows null values as null !=null. so multiple null values are allowed

Piyush Kumar Sharma

• Dec 28th, 2016

We can insert multiple NULL values in Unique key constraints.

Answer Question  Select Best Answer

sunshine60india

 Profile Answers by sunshine60india Questions by sunshine60india

• Jun 18th, 2011

Primary KEY is the combination of UNIQUE and NOT NULL constraint

neha.awasthi1

 Profile Answers by neha.awasthi1 Questions by neha.awasthi1

• May 19th, 2011

Both primary key and unique contain unique value.1: primary key cant be nullUnique key can be null.2: Multiple column of table van be defined as unique key.Only one column can defined as primary key

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

Which is faster, Drop or Truncate? Explain

masthan

• May 30th, 2017

Truncate is faster compared to Drop

Rajan

• Mar 17th, 2017

1. TRUNCATE is a DDL command whereas DELETE is a DML command. 2. TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first, then del...

Answer Question  Select Best Answer

manisha

• May 27th, 2012

Use

Code
 

Neeraj Siddhey

• Mar 20th, 2012

This will work:-

Code
 

Answer Question  Select Best Answer

Radha

• Jul 13th, 2011

Yes we can add NOT NULL constraint for the column already containing data as: If the data doesn't have null values then - Alter table modify constraint ; If the data contains null values...

monoranjang

 Profile Answers by monoranjang Questions by monoranjang

• Jan 24th, 2011

Yes possible, try the below query:ALTER TABLE test add c1 VARCHAR2(10) DEFAULT ('a') not nullThanksMonoranjan

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

ravivenkat1234

 Profile Answers by ravivenkat1234 Questions by ravivenkat1234

• Apr 28th, 2009

Functions can have multiple out Parameters one with Return Parameter and others can be IN OUT Parameter to the function. this way a function can return multiple out parameters.Eg : Function ( a in num...

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 7th, 2008

1.Functions are used to computer a value.2.Function can return more than one value using out parameter. But it is not a good practice to return more than one value in functions.3.Proced...

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