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

sai balaji

• Sep 8th, 2015

Basically we have 13 types of triggers. 1.Row level triggers (6 types) 2.Statement level triggers (6 types) 3.Instead of trigger Row Level Triggers: before insert on each row before update on each r...

mahamadalig

 Profile Answers by mahamadalig Questions by mahamadalig

• Apr 17th, 2012

Before trigger
after trigger
row level trigger
statement level trigger

Answer Question  Select Best Answer

Hasan

• Dec 15th, 2012

Truncate is a ddl...delete is dml
cannot rollback data in truncate...rollback data in delete
truncate -delete entire data not structure...it delete specific datas

VASU

• Oct 20th, 2012

By using delete we have to delete particular records only..
If we use truncate we cannot retrieve the data permanently...

Answer Question  Select Best Answer

Habib Ali

• May 9th, 2016

A view (typically a complex query containing Joins & Aggregates) which stores its result in a DB Table. Can be cached for faster performance. Its highly used in Data Warehousing Environments, but use it carefully as it has lot of restrictions based on clauses and the underlying query

sachin rasal

• Dec 11th, 2015

Materialised view - A table on a disk that contains the result set of a query

Non-materiased view - A query that pulls data from the underlying table

Answer Question  Select Best Answer

Neeraj yadav

• Oct 3rd, 2011

Code
 

OR can pass clause also........................

Code
span style="color: #ff0000;">'CV/2011/12'

gsmanvi

 Profile Answers by gsmanvi Questions by gsmanvi

• Jun 27th, 2008

declarecursor cursor_name isselect col1,col2,col3from tab_name;ab_record cursor_name%rowtype;beginopen cursor_name;loopfetch ab_record into cursor_name;exit when cursor_name%notfound;insert into tab_n...

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

gagan

• Nov 9th, 2015

Oracle uses the work area for its internal processing in order to execute a SQL statement is known as a cursor.

shivigupta

 Profile Answers by shivigupta Questions by shivigupta

• Oct 4th, 2012

Set serveroutput on;
begin
delete from employ where empid = 1;
commit;
dbms_output.put_line(NO of records deleted||sql%rowcount);
end;

Answer Question  Select Best Answer

Saurabh Singh

• Oct 12th, 2015

Table level constraints are defined for compound constraints like primary key involving more than one col and have the flexibility to provide user defined names to these which is not possible in case of col level constraints. Only NOTNULL constraints cant be defined as table level.

krishna mistry

• Oct 10th, 2015

No difference in column level and table level constraints.

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

 %  TYPE  provides  the data type of a variable or a database column to that variable. % ROWTYPE  provides the record type that represents a entire row of a table or view or columns selected in the cursor. The advantages are : I. Need not  know about variable's data typeii.  If  the  database  definition of a column in a table changes, the...

PRADEEP

• Oct 16th, 2012

%rowtype is associated entire table(if we want to declare all columns then we need to declare %rowtype).

%type is associated with one column .(if we want to declare entire column then we need to declare %type).

Yaseen

• Aug 9th, 2012

If using %type and %rowtype , no need to know the the data type of the table column . and once the development is finished , the type of column is changed it will not effect the coding.

Answer Question  Select Best Answer

Hi, What is nocopy parameter in oracle procedure. what is the use of it. In which situation,we can use the nocopy parameter.Thanks,Saravanan.P

Himansu

• Nov 6th, 2012

In procedure,Function there are three types of parameter is there. eg-IN, OUT, INOUT. IN parameter is call by reference and OUT & INOUT are call by value. Always call by reference is faster than call ...

Saket

• Apr 3rd, 2012

Pass by reference: "IN" Case
Pass by value : "Out" or "IN OUT" Case which have the overhead of copying the value to new procedure parameter.
In order to make to Pass by reference we will use NOCOPY
Pass by reference: "OUT NOCOPY" or "IN OUT NOCOPY" which deals which the actual reference value.

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

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

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

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