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

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

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

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

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

Is Dual Table updatable? If we update, will it impact the health of the database?

Habib Ali

• May 10th, 2016

DUAL table is owned by SYS, and is updatable only under SYS account. But never make any changes to DUAL, as it is frequently used by Oracle optimizer to test Execution Plans.

vishal

• Mar 29th, 2016

No we can't update dual table because it is a dummy table. But it is possible to create a table named dual and you can insert, update and delete that table.

Answer Question  Select Best Answer

Habib Ali

• May 9th, 2016

Stand Alone Proc: Block of PLSQL code but not defined in the data dictionary, hence not reusable

Stored Procedure: Block of PLSQL code Defined in data dictionary, implements reusability. Check in user_source table

Praveen kumar

• Jan 8th, 2016

Both are same

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

What are the advantages of packages over standalone procedure ?

Habib Ali

• May 9th, 2016

Packages can be cached inside the SGA, resulting in faster execution than a normal stored procedure.

abhijit musale

• Feb 18th, 2015

You can wrap/encrypt the package code .

Answer Question  Select Best Answer

Kiran Birajdar

 Profile Answers by Kiran Birajdar Questions by Kiran Birajdar

• Mar 21st, 2016

EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,An error was encountered - ||SQLCODE|| -ERROR- ||SQLERRM); END; Or you could log the error to a table using the SQLERRM function ...

devi

• Sep 25th, 2007

You can print the 'sqlcode'?as followsException?When others thendbms_output.put_line('error code is'||sqlcode)end;?

Answer Question  Select Best 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

vivek

• Feb 23rd, 2016

1. Install ords.war (Download from Oracle site)
2. Configure Oracle rest data services. Using ords.war file. $ ords.war install advanced. (It will ask for database credentials)
3. Copy the images under i.war file.
4. Deploy the war files in weblogic.
5. Access the application

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Member Since Sep-2007 | May 6th, 2008

---

A trigger may be a
1. DML Trigger on tables
2. Instead of triggers on views
3. System triggers on database or schema

Based on the way it executes statements  triggers are of two types
1. Statement leve trigger
2.  Row level trigger

A trigger fires for three actions
1. Insert
2. Delete 
3.Update

and the trigger can the fired
1. Before action
2. After action.

Pradeep

• Feb 20th, 2016

Basically 5 main types in Oracle Triggers
1. DML Trigger (3 statement * 2 timing * 2 level = 12 types).
2. DDL Trigger.
3. Instead of Trigger (Fired Complex View).
4. System Trigger.
5. Compound Trigger (Introduced by 11g)

tarini sankar das

• Feb 7th, 2014

Actual answer is we have 14 types of triggers in pl sql. For statement level :- 1-before insert 2-before update 3-before delete 4-after insert 5-after update 6-after delete For row level :- 7-before ...

Improve Answer

How to optimize SQL Statements?

Sudhir Kumar

• Jan 20th, 2016

Try to make use of mqts or temporary tables if possible ..try to transfer the conditions from non key columns to key columns

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

update the statistics of all the tables used in the query. Check the explain plan. Check whether query is using the existing indexes. If required add/modify new indexes. Check for the sequence of cond...

Answer Question  Select Best Answer

1. How to load excel data into oracle database by using sql loader?
2. What are the ways to load data from excel file in oracle database ?

Naresh Bandla

• Dec 1st, 2015

You can upload the Excel file into tables by many approaches, I will explain here in two methods. 1. Loading data using Loader programs: Convert your excel file into CSV file and create control as pe...

ashay

• Nov 13th, 2015

Best and most easy way is to use an external table. Keep the file in designated location and file will automatically loaded into table every time you put a new file or update the file. Create trigger on the given table to load the data into respective tables.

Answer Question  Select Best Answer

What are the advantages of bulk binding in PL/SQL?

sachin rasal

• Nov 12th, 2015

A new feature called "bulk binds" was added to PL/SQL back in Oracle 8i. Bulk binds enable a PL/SQL program to fetch many rows from a cursor in one call instead of fetching one row at a time. Bulk bin...

amala

• Jun 24th, 2015

It retreives multiple rows with a single fetch...It increases the performance by reducing no.of context switches

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

Ram Devineni

• Nov 3rd, 2015

Alter session set nls_date_format=mon-dd-yyyy hh:mi:ss
/

Purvesh Khanna

• May 5th, 2015

If you are using sql developer then go to Tools ->> Preference ->> Database ->> NLS -->> Date Format.
Send the format as you want.

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

hardeep singh

• Oct 16th, 2015

The (dbms_output) is library and the (put_line) is the function declared inside library

aravinthancse

 Profile Answers by aravinthancse Questions by aravinthancse

• Dec 27th, 2011

DBMS_OUTPUT - is a Oracle supplied package. put_line() is a DBMS_OUTPUT packages inbuilt procedure. put_line() is a combination of two inbuilt procedures such as put() and new_line(). procedure put()...

Answer Question  Select Best Answer