What are the uses of Cursor?
What are its advantage and disadvantage?

Habib Ali

• May 10th, 2016

Advantage: Gets multiple-data inside the buffer cache, for faster DML operations

Disadvantage: Consumes memory, has to be freed properly to avoid Buffer cache congestion

WebSpider

• Feb 11th, 2016

Performance is reduced using cursor

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

NOCOPY suggests the PLSQL compiler to use Original Variable values instead of creating Temporary Variables & passing it on. Very useful, if your variable is an Array or other composite data type

ahamed79

 Profile Answers by ahamed79 Questions by ahamed79

• Mar 7th, 2007

Pls try this link:http://www.databasejournal.com/features/oracle/article.php/1558021Cheers- Faizal

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

Copy your REFCURSOR output to a RECORD datatype, and LOOP through it for each record

gouthami kodangal

• Aug 13th, 2006

by making use of sql%rowfound. 

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

Normal Cursor > Your SELECT Query is determined already before BEGIN i.e. Table, Column Names
Ref Cursor > Your SELECT Query is determined at runtime after BEGIN i.e.Table, Column Names

syed_ansar

• Oct 18th, 2006

We can use Ref cursor as an IN OUT parameter .

Answer Question  Select Best Answer

Habib Ali

• May 10th, 2016

PRAGMA is instruction to compiler
AUTONOMOUS TRANSACTION means running an SQL transaction inside a PLSQL block of code independently i.e Making changes to DB regardless of other transactions & their locks/latches

sujata

• Jan 19th, 2016

Pragma AUTONOMOUS_TRANSACTION instructs the compiler to treat the PL/SQL block following the pragma as autonomous (independent) from the calling transaction. Pragma can be applied on:- 1) Anonymous PL...

Answer Question  Select Best Answer

How to optimise your pl/sql code? Not using explain plan which is for tuning the quey

Nikhil Inamdar

 Profile Answers by Nikhil Inamdar Questions by Nikhil Inamdar

• Jul 8th, 2016

You can use DBMS_PROFILER to check performance bottlenecks and then change the code where the PLSQL code is taking more time and improve performence.

For more details go through below link.
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_profil.htm

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

The best technique used is to avoid repeatative coding by putting repeated logic in a subroutine/function. Save the DML operations after a specific interval or records insertion/updation/deletions , depending on your archive log size.

Answer Question  Select Best Answer

vinodkumar

• Jul 14th, 2016

In cursor we dont have the facility of moving directly to a nth record.
We have to loop through the cursor to reach the nth record but even though after reaching you cant move backward.
Its good to use PL/SQL tables for this kind of situation

Habib Ali

• May 10th, 2016

Cursor is bi-directional.... You have to start either from FIRST or LAST record. You can however access nth record directly too, if not required by any logic

Answer Question  Select Best Answer

Delete duplicate records without using sub query? Is That possible

Aak

• Sep 15th, 2016

Use a temp table, store distinct rows. Either delete the old table and rename this new table with the old table name or truncate old table and insert rows from temp table to the old table.

kadari ashok

• Jul 14th, 2016

We can do through co-related sub query

Answer Question  Select Best Answer

aak

• Sep 15th, 2016

No, You can add the proc in body and not declare it in the spec.. this is ok as it would mean that that proc is a private procedure but visa versa is not possible. It would give compilation error.

Sudhanshu

• Jul 26th, 2016

"oracle8 Create or replace package Test_pkg as 2 procedure Proc1; 3 end; 4 / Package created create or replace package body Test_pkg as 2 3 procedure proc1 is...

Answer Question  Select Best Answer

Can we keep trigger name same as table name? Explain

Manisha

• Sep 21st, 2016

Trigger names must be unique with respect to other triggers in the same schema. Trigger names need not be unique with respect to other schema objects, such as tables, views, and subprograms. For examp...

Govind

• Sep 13th, 2016

No. Object Names should be unique.

Answer Question  Select Best Answer

Abdul

• Oct 1st, 2016

We can use DBMS_job.isubmit procedure to schedule the job automatically..

Pran

• Jul 12th, 2016

I am trying this, but it says that procedure name is already exist.

Answer Question  Select Best Answer

How will you check performance of the query, if the query was running earlier but today its taking time to run?

inamdnik

 Profile Answers by inamdnik Questions by inamdnik

• Feb 10th, 2017

There are various ways to check performance of query. 1. Use EXPLAIN PLAN if you want to check execution plan used by oracle optimizer to run a query(This is more session specific.) 2. Use AUTOTRACE...

Subhapratim

• Feb 9th, 2017

We can check from AWR report

Answer Question  Select Best Answer

Can we use nested procedures? What is the limit?

Ram

• Mar 15th, 2017

Yes, we can use and the limit is 255

Raghabendra Sahoo

• Feb 6th, 2017

Yes , We can Use

Answer Question  Select Best Answer

Where will the Procedure have is memory and location?

If amount s transfers from one account to another in banking, how will you update it with packages?

Can you invoke a Procedure inside Package P1 from the other package P2 in the same schema? Explain

Tanvi Singh

• Mar 24th, 2017

Yes we can invoke a procedure from another package if the procedure is of type public.

Answer Question  Select Best Answer

ANIL KUMAR PASUPULETI

• Mar 27th, 2017

Use SQL%NOTFOUND to check whether the update is success or not and then take the action accordingly

ddkdhar

 Profile Answers by ddkdhar Questions by ddkdhar

• Mar 7th, 2007

Question was not clearsorrythis is my questionupdate a table A using cursor if it updated then insert into another table B.if not insert into same table A and insert into B.handle every exceptions in the code.nikhil ok

Answer Question  Select Best Answer

What is Private Procedure in Oracle PL/SQL?

Atiqur Rahman

• May 18th, 2017

Private Procedure: If a procedure is declared and defined within the package body known as private procedures. Also this cannot be executed by other procs of the package. Public Procedure: If a proce...

arundhati

• Apr 20th, 2017

A procedure define only in package body is a private procedure this is not available for external calls

Answer Question  Select Best Answer

Atiqur Rahman

• May 18th, 2017

There is a tables called ALL_TRIGGERS which contains column STATUS (ENABLE/DISABLE). This column tells us that whether a trigger is enable or disable. Also current user can modify these values if user have CREATE ANY TRIGGER privilege.

Kumar Prateek

• May 4th, 2017

To know weather the Trigger status is Active/Inactive AND which trigger is used on which Table, SELECT T.[name] as TableName, TR.[Name] as TriggerName, CASE WHEN 1=OBJECTPROPERTY(TR.[id], ExecIsTri...

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