nade_rs

 Profile Answers by nade_rs Questions by nade_rs

• Jun 25th, 2011

We can also  make use of compound trigger (new feature in  11 g to avoid mutating error)

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

The? row level trigger based on a table,the trigger body can't read thedata? from same table, if you are trying to this it gives mutating errorto avoid that you must use statement level trigger

Answer Question  Select Best Answer

Hi,My questions is " Can function return more than one value".I have gone through the all answers relevant to my question was been posted earlier. But all answers are confusing me.Few people said directly that function can't return more than one value and few are telling that function can return more that one value using OUT parameter or ref cursor.My understanding is if out can't use as a parameter...

sen_sam86

 Profile Answers by sen_sam86 Questions by sen_sam86

• Jul 23rd, 2009

Yes this can be possible by TABLE FUNCTION STEP - 1    CREATE TYPE ex_tabl_fun_obj AS OBJECT (emp_name varchar2(20), emp_id NUMBER(2));STEP - 2     CREATE TYPE ex_tabl_fu...

javedans

 Profile Answers by javedans Questions by javedans

• Jul 8th, 2009

Its a universal truth that function can return only one value, but there are some tricks by using that you can get more than one value , while function returning only one value.When you define functio...

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

What is the best technique of debugging large and fragmented dynamic SQL.

kumardba

 Profile Answers by kumardba Questions by kumardba

• Oct 7th, 2010

You can try to debug Dynamic SQL using SQL Advisor Tool or TKPROF tool

shivaranjani.p

 Profile Answers by shivaranjani.p Questions by shivaranjani.p

• Oct 6th, 2010

Hi,You need to store each and every line of the dynamic sql in a file using UTL_FILE Package before you could execute it, so that you can refer the same file after executing the code, this here serves...

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

Write a PL/SQL anonymous block with exception handling to print your name 10 times?

Sri

• Feb 7th, 2020

Begin
i varchar2(10) :=xxxxxxx
begin
for x in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/

Bibhudatta Panda

• Jun 18th, 2019

Code
 

Answer Question  Select Best Answer

What is Force View. Why Oracle Give Facility to create force view? What is the use of force View

sonam kanungo

• Oct 15th, 2011

As per my knowledge when view is created without base table.then the view is called as forced view.

in this type of view is created with compilation error.

mohan

• Oct 14th, 2011

If we create a view without having base table, that view called as forced view. it will create with compile error. it will be in invalid till base table create.

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

No answer

samareshp

 Profile Answers by samareshp Questions by samareshp

• Feb 13th, 2010

While creating a package, we can use Show error Command to get the line number thats has error. And can be corrected. Even we can check in the data-dictonary view i.e. user_errors to figure out the er...

gem023

 Profile Answers by gem023 Questions by gem023

• Feb 11th, 2010

On SQL Plus you can give the command - show errors.   This would show the line number and reason for the compilation error.

Answer Question  Select Best Answer

Ashish

• Jun 12th, 2014

You can use MERGE statements in Oracle to avoid looping through Conventional cursors. It will save lot of time

KSPRADEEP

• Oct 17th, 2012

Yes we can ..
Instead of cursor we using bulk collection or bulk binding or using %type and %rowtype

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

kiran_marla

 Profile Answers by kiran_marla Questions by kiran_marla

• Mar 10th, 2010

There is no difference between function and package, except function must and should return a value while procedure do not. But it is not a good practice to use out mode in functions.However a default...

vishadk

 Profile Answers by vishadk Questions by vishadk

• Mar 7th, 2010

I believe you are asking about the use of 'out' in the function declaration.One can use 'Out' mode in the functions in the manner simlar to procedure i.e.Create or Replace function fun_out_example(par...

Answer Question  Select Best Answer

suvijyo

• Feb 2nd, 2015

"c declare s1 char(10):=name; s2 varchar2(10):=name; begin if s1=s2 then dbms_output.put_line(both are equal); else dbms_output.put_line(both are equal); end if; dbms_ou...

chinnu

• Jan 27th, 2015

Char datatype is fixed,but it cant be reused. eq: char(10) -- elena,first 5 characters will be filled remaining field are padded.
varchar2 datatype fixed,but it can be reused. eq: varchar2(10) -- elena, first 5 characters will be filled and remaining fields are reused.

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

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

PRADEEP

• Oct 16th, 2012

Rowid is octal decimal values it is stored permanently in a oracle database. It will created while creating a table.

rownum is also octal decimal number but it is for only specific time span it will arise after completed a task.

Dilip

• Sep 3rd, 2012

Here I am providing all the psudo columns of Oracle ROWNUM, ROWID,NEXTVAL, CURRVAL, SYSDATE, USER, LEVEL

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

saeed

• Jul 25th, 2011

Compare EXISTS and IN Usage with advantages and disadvantages.

be_viral

 Profile Answers by be_viral Questions by be_viral

• Jul 14th, 2008

We should use Exists or In based on the no of records inner and outer query is expected to return.Using Exists : Here the outer query is executed first and then inner query is excecuted for each value...

Answer Question  Select Best Answer

 A set of related declarations and procedural statements is called block.

Read Best Answer

Editorial / Best Answer

Answered by: Chandra Shekhar

• Oct 3rd, 2005

---

PL/SQL Block contains :

Declare : optional

Variable declaration

Begin  : Manadatory

Procedural statements.

Exception : Optional

any errors to be trapped

End :  Mandatory

So only begin and end keywords are required, as needed, to process a pl/sql block.

vamsi

• Jun 8th, 2017

Cursors
collections
procedures
functions
triggers

Kevin Korir

• Dec 10th, 2016

Declare: optional
Begin:mandatory-program logic of SQL&plsql statements
Exception: optional (error handling)
End:mandatory
(Forward slash / terminates)

Improve Answer

if i am updating one record in a table at that time no other user can't able insert ,update the record in same table How is it possible

Aditya

• Feb 28th, 2018

Lock table < table name > in exclusive mode;

Smruti R Das

• Sep 26th, 2012

GRANT SELECT ON table_name TO user_name

Answer Question  Select Best Answer