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

PRADEEP

• Oct 16th, 2012

Ref cursor is having return type where as cursor does not have .
syntax of ref cursor:- type ref_type_name is ref cursor [return return_type].

syntax of cursor:- declare
cursor cursor name select statement..

pradeep

• Jan 8th, 2012

Normal cursors do have a return type. ex:

Code
 

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

Hasan

• Dec 15th, 2012

Truncate is a ddl...delete is dml
cannot rollback data in truncate...rollback data in delete
truncate -delete entire data not structure...it delete specific datas

VASU

• Oct 20th, 2012

By using delete we have to delete particular records only..
If we use truncate we cannot retrieve the data permanently...

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