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

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

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

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

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

Read Best Answer

Editorial / Best Answer

Answered by: answertoyourquery

• Nov 9th, 2005

---

Yes, we can use the TCL commands(commit/rollback) in the exception block of a stored procedure/function. The code in this part of the program gets executed like those in the body without any restriction. You can include any business functionality whenever a condition in main block(body of a proc/func) fails and requires a follow-thru process to terminate the execution gracefully!

Leena Roja

• Jul 13th, 2015

Correct Subhash..

Susil Kumar Nagarajan

• Jan 21st, 2012

Yes. You can very well use COMMIT or ROLLBACK in exception block. But it is not a good idea to use, as it will commit the previous transactions in the same PLSQL block. Make use of creating SAVEPOINTS and commiting them when required.

Improve Answer

igornem

• Jun 6th, 2015

There is no term "Atomic transaction". There is term "Transaction" which is must have ACID properties, and first "A" in "ACID" is - "Atomicity", which is defines that series of operations included in ...

madhaviguthula

 Profile Answers by madhaviguthula Questions by madhaviguthula

• Feb 28th, 2007

Autonomous transactions are independent transactions that can be called from within another transaction. An autonomous transaction lets you leave the context of the calling transaction, perform some S...

Answer Question  Select Best Answer

Is it possible to define a RETURN statement in the exception part of a function?
Explain it with example.

Aryan

• May 26th, 2015

Yes it is possible to use Return in exception block of pl/sql. Example: "oracle8 CREATE OR REPLACE FUNCTION [(input/output variable declarations)] RETURN return_type [AUTHID ...

Kamal

• May 23rd, 2015

Yes, you can return from exception block. example: create or replace function TestReturn return varchar2 is Result varchar2(1);--size 1 begin Result := 123;--size 3 return Test; except...

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

During preparation of report in *.RDF , there are two things on date & to date

on date is the i/p value , but to date = 1st day of this month on date

how ?

pradeep

• Apr 24th, 2015

SELECT TRUNC(SYSDATE,MM) FROM DUAL

Praveen

• Apr 22nd, 2015

SELECT ADD_MONTHS(LAST_DAY(SYSDATE),-1)+1 FROM DUAL;

Answer Question  Select Best Answer

What is the impact of using a stored proc or a function on the performance? Which has a better performance? Also, why is it not possible to use DML in the functions called from a select query?

Nupur

• Apr 24th, 2015

Functions and procedures are both named blocks of statement . A function must return a value . Performance wise if you want the program to return a?single value use a ?function . You can use out par...

Suchith Shivali

• Apr 17th, 2015

1)In stored procedure you may or may not return value but in the function return is compulsory. 2)If you want to return more than one value go for Stored procedure. 3)Once SP or function is created,...

Answer Question  Select Best Answer

We have a oracle package that package is calling in user defined package ?from this user defined package how will u debug the oracle package?

ChandraSekhar thimmapthruni

• Apr 16th, 2015

Use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Rohit

• Jan 6th, 2013

Using trace_on utility

eg:- For HRMS hr_utility.trace_on ;
expression(here call the package which you want to debug);
HRMS hr_utility.trace_off;

Answer Question  Select Best Answer

A table has both before insert and after insert trigger. Also we have declared both the trigger as pragma autonomous transaction for saving the error record alone in the error table if any exception occurs. Now if we have a error in the trigger will the before insert trigger will get executed and the error data will be stored in the error table?. what is the exact use of before and after insert trigger...

DInesh Shukla

• Apr 16th, 2015

As we declared pragma here. It will insert the record in error table if got fail during execution.

sukrathik

 Profile Answers by sukrathik Questions by sukrathik

• Mar 6th, 2015

If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exc...

Answer Question  Select Best Answer

Abhijit Musale

• Mar 23rd, 2015

We can create a table in procedure by executing a string statement using EXECUTE IMMEDIATE or using DBMS_SQL.EXECUTE.

veera

• Nov 25th, 2013

It is possible by using Dynamic SQL.
By using EXECUTE IMMEDIATE.

Answer Question  Select Best Answer

What is the difference in CREATE or [REPLACE] Procedure and to drop a procedure and create it again? What does REPLACE do, Does it drop the existing one and create the new one with same name?

Abhijit Musale

• Mar 23rd, 2015

PLSQL objects and views can be replaced if pre-exists using REPLACE keyword.

souvik

• Nov 16th, 2014

If you drop the procedure, then all the privileges are gone which you will have to give again while creating the program. But CREATE OR [REPLACE] command will keep the privileges intact.

Answer Question  Select Best Answer

Explain the real-time situation where would you use Oracle Hits

Abhijit Musale

• Mar 23rd, 2015

To override the flow of execution and priority of the execution we can use oracle optimizer hints.(e.g. PARALLEL,APPEND,etc.)

kalpana

• Oct 28th, 2014

Optimizer hints are used with SQL statements to alter execution plans.We are suggesting oracle how the statement should be executed.
syntax : /*+RULE */

Answer Question  Select Best Answer

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

Please find the template. Using out type parameter we can return more than one value from a function.

Code
 

shyam

• Mar 2nd, 2015

Yes, we return more than one value in function using package. Example::

Code
create package pack
is
type mul_val is record
(emp_id employees.employee_id%type,
emp_name employees.last_name%type);
end pack;

and the return type of function is that pack type.....

Answer Question  Select Best Answer

How to access cursor output from web application and present the record set in tabular form?

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

Using ref cursor and returning output in the plsql table we can return the result in tabular format.

Answer Question  Select Best Answer

What are indexes used for ? What are the different types of Indexes?

lakshmi

• Feb 20th, 2015

Index is schema object.which is an entry for each value that are appear index column.index used to give a direct access and also increase the performance .

types of indexes:

simple,composite,function based,bitmap,bit treeindex

Abhijit Musale

• Feb 18th, 2015

It is not necessary that adding an index will always help. When u have less no. of records then there is no need to add index. An index is an additional database object to improve the data i/o operations.

Answer Question  Select Best Answer

Can we give COMMIT or ROLLBACK within a trigger?

Abhijit Musale

• Feb 19th, 2015

Using PRAGMA AUTONOMOUS_TRANSACTION we can treat the child transaction as a separate transaction, committing which wont affect the parent transaction.

Tushar

• Feb 17th, 2015

No we cant used it.because it affected on logical processing. It is possible using Pragma

Answer Question  Select Best Answer