Which is faster, Drop or Truncate? Explain

masthan

• May 30th, 2017

Truncate is faster compared to Drop

Rajan

• Mar 17th, 2017

1. TRUNCATE is a DDL command whereas DELETE is a DML command. 2. TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first, then del...

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

adarh katiyar

• Jun 17th, 2017

Use PRAGMA AUTONOMOUS_TRANSACTION in trigger block,
issue never comes ...

Amit Bhardwaj

• Feb 9th, 2017

If you are trying to access or modify the triggering table into the same trigger and that too for a row level trigger then Mutating Trigger error occurred.

Answer Question  Select Best Answer

How many types of PL/SQL Triggers are there? What are they?

Chandra sekhar

• Jun 23rd, 2017

Two types
ROW LEVEL
STATEMENT LEVEL

Yogesh

• Jun 2nd, 2017

Basically the are 5 type of trigger on basis of triggering events
1. DML
2. DDL
3. System or schema level
4. Compound
5. Instead of

Answer Question  Select Best Answer

If have 5 procedures in my specification part and used 4 in my body part while compiling will it throw error or not?

Shridevi Bhat

• Jun 30th, 2017

Yes it will throw error. all the declared procedures and functions must be there in package body with implementation.

Error(15,22): PLS-00323: subprogram or cursor GET_REPORT_URL1 is declared in a package specification and must be defined in the package body

vishal pathak

• Jun 7th, 2017

Yes, If procedure declared in specification not defined in body will through compilation error saying:- "procedure declared in package specification must defined in body". but vice-versa will not throw any error

Answer Question  Select Best Answer

gauravy

• Jul 5th, 2017

Use pipeline functions

durgadas.menon

 Profile Answers by durgadas.menon Questions by durgadas.menon

• Aug 13th, 2008

Unlike tables ref cursors do not store data. They act like mold to hold data during the execution of the piece of code so proper coding and use of bind variables can help decrease the burden of proces...

Answer Question  Select Best Answer

How do you find the impact for the change and how to find the tables which are connected to this procedure or package?

Nand Kumar Sahu

• Jul 13th, 2017

You can check any object which is going to referred by any other objects using dba_dependency data dictionary.

SELECT * FROM dba_dependencies WHERE refereced_name = OBJECT_NAME;

Arundhati

• Apr 20th, 2017

You can check ALL_DEPENDENCIES, DBA_DEPENDENCIES, user_DEPENDENCIES

Answer Question  Select Best Answer

Nand Kumar Sahu

• Jul 13th, 2017

Bulk binding is very useful in performance tunning scenarios. BULK COLLECT and FORALL are tow main key of bulk binding. in bulk binding any DML operation is performed in a single execute so context swiching is not going to happening.

Rajan

• Mar 17th, 2017

Within a PL SQL code if there is any DML statement, it involves context switching. Every time there is a context switch, the cost is more. To handle this we have a functionality called bulk binding w...

Answer Question  Select Best Answer

Create an anonymous PL/SQL block to enroll a student in a particular class. You will use the ENROLLMENTS table. Accept a STU_ID and CLASS_ID as input parameters. Use "today's date" for the ENROLLMENT_DATE and the string "Enrolled" for the STATUS.

Mohana Thangavelu

• Sep 9th, 2017

Begin
Insert into enrollments values(&stud_Id,&class_Id,sysdate);
Commit;
End;

Answer Question  Select Best Answer

How can we hide a package from other users?

Anshul soni

• Sep 27th, 2017

Answer 1: using wrapper utility
Answer 2: using authid definer.

Arundhati

• Apr 20th, 2017

To Hide a object, dont give any grants

Answer Question  Select Best Answer

What is Global temporary table? and what are the benefits of it?

Prabhat Sahu

 Profile Answers by Prabhat Sahu Questions by Prabhat Sahu

• Oct 24th, 2017

Global Temporary Table(GTT): --------------------------------------- --> GTT operation perform in-memory(RAM) so reading data from GTT is a "Logical Read" -> hence its faster. --> GTT are of 2 type...

vanishavadlya

 Profile Answers by vanishavadlya Questions by vanishavadlya

• Oct 24th, 2009

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for t...

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

krishnaindia2007  

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• Member Since Sep-2007 | May 13th, 2008

---

DUAL is a part data dictionary and owned by SYS. You should not make modifications to this   table. 
It contains only one row and  one column of VARCHAR2 datatype.
Used to refer an object which does not have any pysical reference in database table.
Ex:- Select sysdate from dual.

Aditya Kumar

• Dec 17th, 2017

If it contains only one column with varchar2 data type then how can it return a date through sysdate.

krishnaindia2007

 Profile Answers by krishnaindia2007 Questions by krishnaindia2007

• May 13th, 2008

DUAL is a part data dictionary and owned by SYS. You should not make modifications to this   table. It contains only one row and  one column of VARCHAR2 datatype.Used to refer an object which does not have any pysical reference in database table.Ex:- Select sysdate from dual.

Improve Answer

Case 1:
Create package with body only it contains only one private sub program
CREATE PACKAGE BODY no_spec AS
PROCEDURE myproc AS BEGIN null;
END;
END no_spec;
/

Case 2:
Create package with body only it contains no sub programs
CREATE PACKAGE BODY no_spec AS

END no_spec;
/

shriram

• Jan 5th, 2018

To add one more point to the disadvantages of packages :
We cannot declare ref cursor in a package.
also if a dependent object is dropped the entire package body is invalid until it is recompiled.

Kavitha

• Nov 19th, 2007

Disadvantages of Triggers    It is easy to view table relationships , constraints, indexes, stored procedure in database but triggers are difficult to view.    Triggers execu...

Answer Question  Select Best Answer

Explain what is Simple Cursor and What is Parameterized Cursor? And Difference between both??

sandeep

• Feb 1st, 2018

In real time we are using more than one cursor in PL/SQL block. so in this we are sending one cursor values into another cursor so the receiving cursor must be parameterised cursor

Akash Mohan singh

• Dec 26th, 2017

A parameterized cursor are static cursor that can accept passed in parameter values when they are opened. Parameterized can only reference to its own parameter parameterized cursor cannot reference to local variable.

Answer Question  Select Best 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

Amol Bakane

• Apr 2nd, 2018

The following hierarchy is followed when a trigger is fired. 1) BEFORE statement trigger fires first. 2) Next BEFORE row level trigger fires, once for each row affected. 3) Then AFTER row level tr...

Baji Shaik

 Profile Answers by Baji Shaik Questions by Baji Shaik

• Jun 5th, 2011

The orders of triggers execution is like this???? ? 1.before statement level?? 2.before row level?? 3. after row level?? 4 after statement level

Answer Question  Select Best Answer

If I create N number of small procedures with one PL/SQL block (or) procedure why we need package?

Prasad Bagaregari

• May 11th, 2018

An embedded object where all the required units can be in one place.

Tarika Bhutani

• May 25th, 2017

Whenever we run any subprogram of a package the whole package get loaded in SGA. Which actually reduces the number of context switching and hence increases speed and better performance. Whereas in procedures we the context switching is more. Procedures are useful for writing small modules.

Answer Question  Select Best Answer

Difference between RANK TRANSFORMATION TYPE and RANGE by PARTITION. Simple Code in loading data into FACT Table using the above Two methods

Explain when do we use a Procedure and when do we prefer using functions.

Mabula Shaik

• Aug 18th, 2018

We can add few more points, Those points are: 1) procedure can allow select as well as DML statements in it, Where as function can allow only select statements. 2) a function can be called in proc...

Deva

• Feb 10th, 2018

Procedure and function both are db objects..both will be stored as pre-compiled objects in our db.. the major difference is 1) Procedure may or may not return a value..but function must return a valu...

Answer Question  Select Best Answer