-
What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
g_sidhu
- Jan 31st, 2008
PRAGMA EXCEPTION_INIT statement associate the declared exception with the standard Oracle server error number. PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle er...
-
Can procedures have parameters
krishnaindia2007
- May 7th, 2008
Procedures Can have Parameters. But parameters is optional .i.e. we may defined procedures without parameters also.It can take three types of parametersIN , OUT and INOUT.
kanchan Patra
- Aug 7th, 2007
Yes, Procedures can have parameters.
-
">
Talk about "Exception Handling" in PL/SQL?
krishnaindia2007
- May 7th, 2008
A warning or error condition is called an exception. An exception may raise in a pl/sql block due to designing faults, coding mistakes or hardware failure. If an exception raises in a block ...
g_sidhu
- Jan 31st, 2008
You can handle exception in two ways:– Trap it with a handler : If the exception is raised in the executable section of the block, processing branches to the corresponding exception handler...
-
PL/SQL UTL_FILE Package
Which exception is used in UTL_FILE package in PL/SQL?
dinesh.smhdr
- May 19th, 2011
The package specification of UTL_FILE defines seven exceptions.INVALID_PATHINVALID_MODEINVALID_FILEHANDLEINVALID_OPERATIONREAD_ERRORWRITE_ERRORINTERNAL_ERRORNO_DATA_FOUNDVALUE_ERRORINVALID_MAXLINESIZE
-
What is PL/SQL table? SNO MARK ------- ------------------1 592 403 ‘A’4 60 Write a single query to I) Sorted Marks II)First mark III) replace the mark ‘A’ with 0(zero)?
NehaChanda
- May 22nd, 2009
We can not use MAX on PLSQL table. Check if it works.Create a PLSQL Table Sorted_Mark.SNO_MARK(3) := 0; - Replace ASorted in another Collection Typeselect culumn_valuebulk collect into Sorted_MARKfrom table (cast(SNO_MARK as PLSQL_DATATYPE))order by column_valueSorted_MARK(1) - First Value
samareshp
- Apr 15th, 2009
I guess the the question should be replace a with zero then sort the mark and
then show first mark, Let me know if i am wrong.
select max(mark)
from (
SELECT sno, mark
FROM (select sno, decode(mark, 'A', 0, mark) x from t) xt
ORDER BY x
)
-
Suppose thr are 10 DMLs(insert,update,delete ) in the main section of the PL/SQL block .The exception in them is handled as a whole in the exception handling section .....The error may occur in any of this DMLs ,so how can we understand that which DML has failed ??
sandeshshinde000
- Jun 4th, 2011
begin DML 1 ' ' ' ' ' DML 10exceptionwhen others thandbms_output('error at location '||dbms_utility.format_error_backtrace);e...
BakulPatel
- May 7th, 2011
To simplify Remove exception block. Oracle will show line number with error thrownOther method:beginlocation :=1; dml1 location :=2; dml2exception when others than&nb...
-
What is diff between strong and weak ref cursors
Baji Shaik
- Jun 5th, 2011
Strong ref cursor return value, but seek ref cursor it can't return any value
askvenki
- Jul 20th, 2007
A strong REF CURSOR type definition specifies a return type, but a weak definition does not.DECLARE TYPE EmpCurTyp IS REF ...
-
HiWhile creating a table, what is the difference between VARCHAR2(80) and VARCHAR2(80 BYTE)?
Baji Shaik
- Jun 5th, 2011
Varchar2(80) accept upto 80 characters,varchar2(80 byte) accept upto 80*8=640 characters
psingla
- Apr 28th, 2010
If system parameter NLS_LENGTH_SEMANTICS is set to defaultthen no differenceIf system parameter NLS_LENGTH_SEMANTICS is changed from default behavior BYTE to CHAR.then VARCHAR2(80) means VARCHAR...
-
What is difference between "Primary key" and "Unique key"?
neha.awasthi1
- May 19th, 2011
Both primary key and unique contain unique value.1: primary key cant be nullUnique key can be null.2: Multiple column of table van be defined as unique key.Only one column can defined as primary key
-
2)what is meant by forward declaration in functions?">
1)what is the starting "oracle error number"?2)what is meant by forward declaration in functions?
muraligrandhi
- Jun 23rd, 2011
A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. For example, if program A calls program B and program B calls program A,&nb...
KD09714
- Jun 26th, 2008
Example of forward declaration in package : CREATE OR REPLACE PACKAGE pkg_CBRef_Seq AS PROCEDURE sp_CBRCreateDropSeq_scd ( p_cod_CountryCode IN country.country_code%TYPE, ...
-
What is mutatinig trigger? How do you avoid mutating trigger?
nade_rs
- Jun 25th, 2011
We can also make use of compound trigger (new feature in 11 g to avoid mutating error)
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
-
What is difference between ROWNUM and ROWID.
kool.bird9
- Jul 9th, 2011
Oracle ROWID uniquely identifies where a row resides on disk. The information in a ROWID gives Oracle everything it needs to find your row, the disk number, the cylinder, block and offset ...
RajivPrasad
- May 8th, 2007
Also as mentioned by our friend rowid is a unique hexadecimal format integer which takes up the representation as below File: Block: Row One can retrieve rowid by using t...
-
Can you add not null column to a table already containing data ?
Radha
- Jul 13th, 2011
Yes we can add NOT NULL constraint for the column already containing data as: If the data doesn't have null values then - Alter table modify constraint ; If the data contains null values...
monoranjang
- Jan 24th, 2011
Yes possible, try the below query:ALTER TABLE test add c1 VARCHAR2(10) DEFAULT ('a') not nullThanksMonoranjan
-
What is flash back query and trip stop
Radha
- Jul 13th, 2011
Flash Back query is used to get back the deleted rows & dropped tables from the database even they are committed. To get back the dropped table: Flashback table to before drop; To get back...
murthy 1053
- Jul 2nd, 2010
Flash back query is used to retrive the data.For ex if a table is dropped and we want the table again so at that time.syn:=FLASHBACK TABLE table_name TO BEFORE DROP.This will work only if recycled bin is not purged.
-
-
We have a trigger on data base.in the trigger body we have created a body using dbms_output.put_line(********) ;this should be firedwhen ever trigger executed;
Radha
- Jul 14th, 2011
Triggers fire when any condition on such trigger is satisfied...but we don't know when that trigger is going to be fired and if it is fired where can we see output of DBMS_output.put_line.
If we want to see output of trigger when it is fired we should mention Raise_application_error in trigger.pawan_kr2007
- Oct 11th, 2009
If you 'set serveroutput on' and execute the statement which fires the trigger the message will display on prompt otherwise it will not.
-
Based on what conditions can we decide whether to use a table or a view or a materialized view ?
Radha
- Jul 14th, 2011
We use view to be confidential about the data and to avoid the complexity of a query. But Mviews are used for performance problem, we use mviews for faster execution of a query. But we need to do regular refresh for mviews.
pawan_kr2007
- Oct 11th, 2009
View: If a query is being used again and again then its better to create a view with that query. The query gets stored in database as an object.Materialized view: When you want you replicate the main ...
-
-
Current Database Name
How to know the current database name in PL/SQL?
-
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; ...
PL/SQL Interview Questions
Ans