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

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