-
Hi Friends!! Can anybody answer what are the constraints on Mutating tables? How to remove the mutating errors in triggers? Urgent plzzzzzzzzzzzzzzzzThanks in Advance..Thanks&RegdsRamki,TCS
amahanit
- Oct 13th, 2007
1) make the trigger as a statement level trigger.2) if u want to use :old and :new qualifer then create 2 trigger ,1st before insert trigger and use PL/SQL table to select the values,2nd after insert trigger and insert the values from PL/SQL tables.
-
What is the diff between %Rowtype and %type?
manoj_lanser
- Jul 20th, 2011
%type is used when we have to define the datatype of a variable similar to another existing column data-type. eg- v_id employess.employee_id%type. In this example we have make a variable v_id similar...
Radha
- Jul 14th, 2011
We use %type and %rowtype in PL/SQL programming part. %Type: The variable will assign the datatype which contains in the column of that particular table we mentioned. Ex: v_eno employee.eno%type; ...
-
Hi,Can anyone tell me the difference between instead of trigger, database trigger, and schema trigger?Thanks.
g_sidhu
- Feb 4th, 2008
Triggers on system events can be defined at the database or schema level. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only whe...
Nasim
- Dec 17th, 2006
Hiif we have created a view that is based on join codition then its not possibe to apply dml operations like insert, update and delete on that view. So what we can do is we can create instead off trig...
-
-
What is the difference between right join and right outer join..
samareshp
- Feb 13th, 2010
Both are same.outer join : We have 3 types of outer joins. Left Oute, Right Outer and Full Outer join.Left outer join:Select * from lefttable, righttable where lefttable.col1=righttable.col1(+)This wi...
krishnaindia2007
- May 13th, 2008
Both are same. Right outer join returns all the reocrds that satisfy the join condtion + rest of the records from right (or second) table.
-
How do you encrypt the function to prevent accessing from users without specific permission. ?
krishnaindia2007
- May 13th, 2008
Using wrap utility we can encrypt source code of a function to byte code (Not a readable format). Becareful there is no decrypt utility to convert source co...
nitingalande
- Jan 13th, 2008
Function can be encrypted using Oracle Wrap utility. this will prevent accessing from users without permission
-
How do you call procedure have a DDL or commit/rollback statement from a trigger?
VITHAL
- Sep 16th, 2011
To handle this type of procedures we should use pragma autonomous trigger
-
Debug Procedure
How to debug a procedure in SQL Prompt
srimanta sahoo
- Oct 4th, 2011
You can debug the procedure by writting following command :
alter procedure procedure name
debug ;puneet.kush
- Apr 17th, 2008
You can debug the procedure. Oracle gives a util programme DBMS_DEBUG by which you can debug a procedure.the full detail you can see at http://www.oracleutilities.com/wiki/index.php?title=Dbms_debugan...
-
Example for calling procedure
Give an example for calling procedure with user and system exception
krishnaindia2007
- May 29th, 2008
Simple example for predefined exception CREATE OR REPLACE PROCEDURE TEST_PROC AS A NUMBER ; BEGIN A := 'TEST'; DBMS_OUTPUT.PUT_LINE('THE VALUE OF A = '||A); EXCEPTION WHEN VALUE_ERROR ...
ravenkumar
- Mar 17th, 2008
set serveroutput on;create or replace procedure proc1 (emp_id in emp.employee_id%type, emp_na out emp.last_name%type, emp_sal out emp.salary%type, emp_job out emp.job_id%type) is &...
-
PL/SQL Block output
begin For i in 1..5 loop insert into A values(i); savepoint 1; end loop; rollback to savepoint 1; commitend;--initially there are no data in table A. So my question is after execution of this block what should be the data present in table A?
krishnaindia2007
- May 29th, 2008
The first mistake in this code is savepoint 1;You can't give 1 as identifier. Identifier name must begin with a letter.insert into A values(i);savepoint x;Here you are defining save...
satyam_Ora
- May 6th, 2008
The flow of execution will be as below:step1: loop will execute 5 times and the variable value(i) will store in the table a in series of values from 1 to 5.step2: all 5 values in series from 1..5 w...
-
What are the advantages of packages ?
laxma823
- Aug 7th, 2008
Package have more advantages ModularityEasily Application designBetter PerformenceInformation Hiding
-
Debug PL SQL
How can we generate debugging output from PL/SQL?
KD09714
- Jun 28th, 2008
Stshekhar is right, oracle supplied package DBMS_OUTPUT with procedures PUT, PUT_LINE, NEW_LINE etc in case of debugging pl/sql
-
Purpose of Ref cursor and OUT parametr in PLSQL Function?
What is the purpose of Ref Cursor and OUT Parameter in PLSQL Function? Give Examples for each?
muraligrandhi
- Jun 9th, 2010
Main advantage of Ref cursor is you can get the output as record set. For example if you are using Java or .Net or cristal reports for reporting purpose then you can use Ref cursors and write a subpro...
pratap557
- Oct 13th, 2009
By Ref cursor we can send record set to the calling programLike .NET VB page can get recordset as datatable from ref cursor returned by a procedure.OUT parameter sends only one value from procedure
-
About dbms_output.put_line( ) package
What is the maximum size of the message that we can give in dbms_output.putline();
jp_valapad
- Aug 5th, 2008
255 bytes was the size limits of earlier version of oracle..ie prior to 10 g ..in 10g 32767 byte is the limit..
-
Indexed Select Statement
How can we find the select statement is indexed or not?
Read Best Answer
Editorial / Best Answer
promisinganuj- Member Since Sep-2009 | Sep 13th, 2009
1. using AUTOTRACE
SQL> SET AUTOTRACE ON TRACEONLY
SELECT *
FROM emp
WHERE emp_name = 'ABC';
2. Using EXPLAIN PLAN
SQL> EXPLAIN PLAN for
SELECT *
FROM emp
WHERE emp_name = 'ABC';
The advantage of using EXPLAIN PLAN over the AUTOTRACE is that former does not require the query to be actually run. The TRACEONLY option in AUTOTRACE just supress the query ouput but the query still runs before the explain plan is displayed.promisinganuj
- Sep 13th, 2009
1. using AUTOTRACESQL> SET AUTOTRACE ON TRACEONLYSELECT * FROM emp WHERE emp_name = 'ABC';2. Using EXPLAIN PLANSQL> EXPLAIN PLAN forSELECT * FROM...
-
DeCode Performance
Performance wise which is a better option if-else construct or a decode? Why
muraligrandhi
- Jun 7th, 2010
DECODE is faster than IF-THEN-ELSEREASON:Decode is an Oracle built-in function, so no need to pass parameters to this function. Because all the parameters are internally defined but If-then-else statements we should define explicitly, so takes time for execution and converting into P-code.
sanjeevlabh
- Jan 22nd, 2010
The answer to this question would actually depend on the type of applicability of the usage that is being done. i.e. is it required in a query or in a PLSQL block. If it is in a query we cannot use if...
-
Bulk Data Operations
You are developing a PL/SQL block designed for bulk data operations. When attemping to store table data for multiple rows in a PL/SQL variable, which one of the following choices identifies the mechanism best suited for this task (choose one)? A. cursorB. varchar2C. recordD. table of records
rahulg5211
- Oct 25th, 2010
Table of record type is best option to perform this operation.becouse first thing pl/table has unlimited size.second thing for fetching bunch of record into pl/table,besides of INTO clause we ne...
shukla.prashant76
- Apr 28th, 2010
Answer is D. We need multiple rows on a variable so we use here Table of records .it uses just like as collection of records.Cursor is used for selecting multiple rows from stored table.Var...
-
COLLECTIONS
1-WHAT IS DIFF BETWEEN VARRAY AND TABLE?2-WHAT IS SQL SUPPORT FOR NESTED TABLE?3-WHAT IS INLINE STORAGE AND OUT OF LINE STOGARE OF THE NESTED TABLE?4-WHAT ARE COMMON EXCEPTION RELATED WITH COLLECTION?5-CAN U DECLARE VARRAY OF VARRAY OF VARRAY OF TABLE?6-GIVE SAMPLE DEFINATION OF VARRAY OF OBJECT AND TABLE OF OBJECT?7-WHAT ARE COMMON METHODS OF HANDLING EXCEPTIONS?
subhashishchaki
- Oct 7th, 2013
Collections similarities and differences Collection Type Elements Limit Subscript Type Uninitialized Status Always Dense ----------------------------------------------------------...
basukolur014
- Feb 28th, 2011
Exceptions related to collections are...1.collection_is_null2.collection subscript beyond count3.collection subscript outside limit4.no_data_found .these are more common exceptions related to collections.
-
PL/SQL Procedure
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?
-
What command would you use to encrypt a PL/SQL application?
satya
- Sep 20th, 2011
You can use "Decode" also inspite of Translate:
Ex: select decode( 'abcdefghijk','abcdefghijk','zyxwvutsrqp') enript from dual;
PL/SQL Interview Questions
Read Best Answer
Ans