When use Before and After Trigger? Please any help for this one

chaithanya

• Jul 1st, 2014

Before trigger is used while performing DML operations and whereas After Trigger is used for after performing DML operations Real time Example for before trigger: in banking systems: ...

Mrutyunjay Nayak

• Jul 1st, 2014

Before Trigger is used only when u want to restrict the invalid data entry in a table.After trigger is used when u perform the operation in one table that will effect on other table.

Answer Question  Select Best Answer

Raviteja

 Profile Answers by Raviteja Questions by Raviteja

• Aug 23rd, 2014

Instead of cursor is not there,, instead of trigger is used incase of complex views. when we use views with joins

Kumar

• Jul 25th, 2014

Its instead of trigger not cursor and it is used to apply trigger on views.

Answer Question  Select Best Answer

What is recursive function, explain with example?

Vishant Gupta

• Oct 11th, 2014

It is function that calls itself to implement some repetitive task

VIVEK

• Sep 24th, 2014

Function is calling the same function

Answer Question  Select Best Answer

How can you move the entire data from one table to another? If in same instance and in different instances, both cases?

KARTHIKA

• Nov 7th, 2014

Use the below code to perform the task

Code
 

A MOHAN KUMAR

• Nov 7th, 2014

INSERT INTO < old table > SELECT * FROM < new table > Note the table should have same column name and their respective data types as old table

Code
 

Answer Question  Select Best Answer

How to optimise your pl/sql code? Not using explain plan which is for tuning the quey

Nikhil Inamdar

 Profile Answers by Nikhil Inamdar Questions by Nikhil Inamdar

• Jul 8th, 2016

You can use DBMS_PROFILER to check performance bottlenecks and then change the code where the PLSQL code is taking more time and improve performence.

For more details go through below link.
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_profil.htm

ace1234567

 Profile Answers by ace1234567 Questions by ace1234567

• Mar 4th, 2015

The best technique used is to avoid repeatative coding by putting repeated logic in a subroutine/function. Save the DML operations after a specific interval or records insertion/updation/deletions , depending on your archive log size.

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

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

Can we keep trigger name same as table name? Explain

Manisha

• Sep 21st, 2016

Trigger names must be unique with respect to other triggers in the same schema. Trigger names need not be unique with respect to other schema objects, such as tables, views, and subprograms. For examp...

Govind

• Sep 13th, 2016

No. Object Names should be unique.

Answer Question  Select Best Answer

How will you check performance of the query, if the query was running earlier but today its taking time to run?

inamdnik

 Profile Answers by inamdnik Questions by inamdnik

• Feb 10th, 2017

There are various ways to check performance of query. 1. Use EXPLAIN PLAN if you want to check execution plan used by oracle optimizer to run a query(This is more session specific.) 2. Use AUTOTRACE...

Subhapratim

• Feb 9th, 2017

We can check from AWR report

Answer Question  Select Best Answer

Can we use nested procedures? What is the limit?

Ram

• Mar 15th, 2017

Yes, we can use and the limit is 255

Raghabendra Sahoo

• Feb 6th, 2017

Yes , We can Use

Answer Question  Select Best Answer

What is Private Procedure in Oracle PL/SQL?

Atiqur Rahman

• May 18th, 2017

Private Procedure: If a procedure is declared and defined within the package body known as private procedures. Also this cannot be executed by other procs of the package. Public Procedure: If a proce...

arundhati

• Apr 20th, 2017

A procedure define only in package body is a private procedure this is not available for external calls

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

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

What are the advantages of Gathering Statistics?

Shivam Softa

• Sep 19th, 2018

Helps Oracle engine to create better execution plan to execute the query.

ushodaya

• May 20th, 2018

We should generate statistics for all tables and clusters and all types of indexes accessed by your SQL statements before using the cost-based approach. If the size and data distribution of your table...

Answer Question  Select Best Answer

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

 Some  scalar  data  types  such  as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.Some composite data types such as RECORD & TABLE.

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Feb 16th, 2008

ScalarCHARVARCHAR2 LONGLONG RAWNUMBER [(precision, scale)]BINARY_INTEGERPLS_INTEGERBOOLEAN Composite TypesRECORDTABLEVARRAYLOB TypesBFILEBLOBCLOBNCLOBReference TypesREF CURSORREF object_type

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Oct 26th, 2006

Following are the datatype supported in oracle PLSQLScalar TypesBINARY_INTEGERDECDECIMALDOUBLE PRECISIONFLOATINTINTEGERNATURALNATURALNNUMBERNUMERICPLS_INTEGERPOSITIVEPOSITIVENREALSIGNTYPESMALLINTCHARC...

Answer Question  Select Best Answer

 Mutation of table occurs.

rudra pratap

• Jul 5th, 2007

In order to avoid mutation table error we need to have a row level as well as a statment level trigger.

Sunil Yadav

• Oct 26th, 2005

To avoid the mutation table error ,the procedure should be declared as an AUTONOMOUS TRANSACTION.By this the procedure will be treated as an separate identity.

Answer Question  Select Best Answer

          I.  done using Database triggers.          ii. done using Integarity Constraints. I & ii. Exception :

Srinatha Reddy

 Profile Answers by Srinatha Reddy Questions by Srinatha Reddy

• May 21st, 2008

create table a ( b number, c number check (c >100) );create or replace trigger t1 before insert on a begindbms_output.put_line('this is before insert trigger'); end;create or replace trigge...

prashant

• Jul 6th, 2006

 ii. done using Integarity Constraints

Answer Question  Select Best Answer

 SQLCODE returns the latest code of the error that has occurred.SQLERRM returns the relevant error message of the SQLCODE.

g_sidhu

 Profile Answers by g_sidhu Questions by g_sidhu

• Jan 31st, 2008

• SQLCODE: Returns the numeric value for the error code• SQLERRM: Returns the message associated with the error numberSQLCODE Value          &nbs...

acpsrinivas

 Profile Answers by acpsrinivas Questions by acpsrinivas

• Feb 25th, 2006

Pl / Sql Provides Error Information via two Built-in functions, SQLCODE & SQLERRM.SQLCODE Returns the Current Error Code.            &nb...

Answer Question  Select Best Answer

 Function is called as part of an expression.   sal := calculate_sal ('a822');   procedure is called as a PL/SQL statement   calculate_bonus ('A822');

idonknowyou

 Profile Answers by idonknowyou Questions by idonknowyou

• May 22nd, 2007

Procedure can be called in the following waysa) CALL <procedure name> direcb) EXCECUTE <procedure name> from calling environmentc) <Procedure name> from other procedures or functions...

M.KUMARAN

• Nov 13th, 2005

Function is called as part of an expression.   sal := calculate_sal ('a822');   procedure is called as a PL/SQL statement   calculate_bonus ('A822');KKK

Answer Question  Select Best Answer