How to delete duplicate Keys through PL-SQL in single query.

anonymous

• Sep 27th, 2011

Code
 

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 21st, 2011

Assuming that ename and job is the same or First and Last name...

SELECT * FROM (SELECT empno, ename, job ,RANK() OVER (PARTITION BY ename, job ORDER BY empno) AS SeqNo FROM emp1) WHERE SeqNo > 1

Answer Question  Select Best Answer

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 27th, 2011

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value. It is not a cursor, but a variable that points to a cursor. It is not limited t...

sunil

• Jul 26th, 2011

CURSOR A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. A cursor can be used in realtime when u nee...

Answer Question  Select Best Answer

Art11

 Profile Answers by Art11 Questions by Art11

• Sep 27th, 2011

A hint is an instruction to the optimizer. You can use optimizer hints with SQL statements to alter execution plans.

muraligrandhi

 Profile Answers by muraligrandhi Questions by muraligrandhi

• Jun 23rd, 2011

     The optimizer hints determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified ...

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

How to debug a procedure in SQL Prompt

srimanta sahoo

• Oct 4th, 2011

You can debug the procedure by writting following command :
alter procedure procedure name
debug ;

puneet.kush

 Profile Answers by puneet.kush Questions by puneet.kush

• Apr 17th, 2008

You can debug the procedure. Oracle gives a util programme DBMS_DEBUG by which you can debug a procedure.the full detail you can see at http://www.oracleutilities.com/wiki/index.php?title=Dbms_debugan...

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

What is Force View. Why Oracle Give Facility to create force view? What is the use of force View

sonam kanungo

• Oct 15th, 2011

As per my knowledge when view is created without base table.then the view is called as forced view.

in this type of view is created with compilation error.

mohan

• Oct 14th, 2011

If we create a view without having base table, that view called as forced view. it will create with compile error. it will be in invalid till base table create.

Answer Question  Select Best Answer

If a deadlock has occured and if you have given with the dump, how will you identify that where the deadlock has occured, how the deadlock occured and how to rectify the same.

kari

• Oct 16th, 2011

the steps necessary to identify and rectify code causing deadlocks are: * Locate the error messages in the alert log. * Locate the relevant trace file(s). * Identify the SQL statements i...

skumar.t15

 Profile Answers by skumar.t15 Questions by skumar.t15

• Aug 5th, 2010

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that c...

Answer Question  Select Best Answer

How to schedule an email in oracle reports?

KARI

• Oct 29th, 2011

UTL_MAIL: type procedure in UTL_MAIL 1)SEND (send the message without attachment) "plsql Begin UTL_MAIL.SEND(sender =>'karimuth@amazon.com',recipients=>'karimuthu.bala@gmail.com'...

zahar

 Profile Answers by zahar Questions by zahar

• Jun 7th, 2011

Using utl_mail

Answer Question  Select Best Answer

priya dharan raj

 Profile Answers by priya dharan raj Questions by priya dharan raj

• Nov 14th, 2011

Code
 

Nitesh Srivastava TechM

• Nov 10th, 2011

See the code below

Code
span style="color: #ff0000;">'EMP''EMP'

Answer Question  Select Best Answer

Shiyamala Devi

• Jan 6th, 2012

Code
 

This query will display 4th highest salary from the player table

sunshine60india

 Profile Answers by sunshine60india Questions by sunshine60india

• Jun 18th, 2011

SELECT TOP 1 * FROM (    SELECT TOP 4 * FROM EMPLOYEE ORDER BY CTC desc)AORDER BY CTC

Answer Question  Select Best Answer

ABHISHEK TYAGI

• Jan 6th, 2012

D:oracleproduct10.2.0db_2BIN>expdp scott/tiger@ace directory=test_dir dumpf ile=testver.dmp tables=testversion remap_tablespace=users_tbs:users Import: Release 10.2.0.1.0 - 64bit Production on Sunday...

osden

• Oct 6th, 2007

No. You cannot export a higher version to the lower version.

Answer Question  Select Best Answer

How does index work? How to check whether Index is pickup?

z_ashwini

 Profile Answers by z_ashwini Questions by z_ashwini

• Jan 13th, 2012

Indexes are created for better performance of the DB ; for faster retrival of data from DB. There are diffierent types of indexes... (1) B tree index (2) Bitmap map index (3) Function based index (4) ...

Answer Question  Select Best Answer

Susil Kumar Nagarajan

• Jan 21st, 2012

Use the combination of To_char and MIN functions.. to_char(min(created),dd-Mon-yyyy hh:mm:ss) Example:

Code
 

Hitendra Yadav

• Dec 15th, 2011

Just use min() function and find minimum timestamp from a table.

Answer Question  Select Best Answer

In the exception part,what will happen if u don’t give (when others) where will it show error compile time or run time

NAND

• Feb 27th, 2012

It will give the error at run time if it is not able to find the type of exception thrown in the current PL/SQL block or the outer block.
It will not throw the compile time error.

Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Feb 27th, 2012

Now, to clarify, I am taking the following statement; "in the exception part, what will happen if you don't give the (when others) statement" to mean an exception block with no other when statements a...

Answer Question  Select Best Answer

pankti

• Apr 6th, 2012

Nested table Unbounded ...

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 6th, 2008

A Table is a basic unit of storage in oracle.A nested table is a collection type. The main advantage of collections is instead of processing data sequentially, we may process all the date&nb...

Answer Question  Select Best Answer

Outline the List of PRAGMA'S used in Oracle.

amrapali

• Apr 21st, 2012

PRAGMA The instruction is a statement that provides some instructions to the compiler. Pragmas are defined in the declarative section in PL/SQL. The following pragmas are available: AUTONOMOUS_TRA...

bobbyiiit

 Profile Answers by bobbyiiit Questions by bobbyiiit

• Apr 9th, 2010

PRAGMAPragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compile.Types Of Pragmas :-1 - AUTONOMOUS_TRANSACTION2 - EXCEPTION_INIT3 - RESTRICT_REFERENCES4 - SERIALLY_REUSABLE

Answer Question  Select Best Answer

vizzi

• May 16th, 2012

When we use a select query in the from clause, it is called as a inline view. It is used to reduce complexity of using so many join conditions.

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

Aniruddha Das

• Jun 11th, 2012

If you want to take care of performance tuning you need to take care of sub-queries as they takes a lot of time to executing in side the pl-sql blocks.

ptmich

 Profile Answers by ptmich Questions by ptmich

• May 28th, 2012

Ref cursor is used to declare a pointer variable. When you return a ref cursor, you are returning a pointer variable.

Answer Question  Select Best Answer