Anjani Kumar

• Aug 9th, 2015

We can use oracle supplied package DBMS_HPROF(New in 11g) to see which procedure and inside that which sql is taking taking more time to execute.DBMS_HPROF Package profiles subprogram calls also with ...

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

If i insert record in table A and these record should update in table B by using Trigger.How to achieve this.
Trigger - insert into one table update into another table

Nagendra

• Sep 10th, 2015

"sql CREATE TRIGGER update_trigger ON CUSTOMER_info FOR update AS BEGIN IF UPDATE (cust_name) OR UPDATE (contact) BEGIN INSERT INTO update_log ( cus...

Avinash

• Aug 10th, 2015

We can use instead of trigger in this case

Answer Question  Select Best Answer

How to load tab delimited data using SQL*Loader?

bhargavi

• Oct 4th, 2015

For Tab delimiter You can use 0x 09

gaurav

• Sep 26th, 2015

Fields Terminated by X09 use this

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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