Dear All,



Question for this Week



Find out possible error(s) (either at compile
time or at runtime) in the following PL/SQL block. State
the reason(s) and correct the errors.



Declare

Cursor C1 is select ename, sal, comm from emp;

Begin

For i in C1 Loop

If i.comm between 299 and 999 then

Dbms_output.put_line(i.Ename || ‘
** Good Commission’);

Elsif i.comm > 999 then

Dbms_output.put_line(i.Empno || ‘
** Very Good Commission’);

close C1;

Else

Dbms_output.put_line(i.Ename || ‘
** ’ ||nvl(i.comm,‘O’));

End if;

End Loop;

End;




Answers were Sorted based on User's Feedback



Dear All, Question for this Week Find out possible error(s) (either at c..

Answer / rinson

Nobody mentioned errors..(both runtime and compile time)

let's go through the errors

A. compile time errors

1. Encountered symbol "‘" when expecting single qoutes
double quotes etc.
2. When we replace all these symbols to single quotes
next error -- > empno must be declared will come,
since it is to be declared in cursor C1.Here they
may be expecting ename instead of empno.
3. Then it will show next error-->
Numeric or value error since nvl function contains
different datatypes.we can change this to 0.

Now all compile time errors removed.

B. Run Time Errors

1. Since cursor for loop implicitly open,fetch and close
cursor , there is no need to close cursor in second if
condition.Actually compiler will not allow to close a
cursor in a cursor for loop.

it will throw the run time error
ora-01001 invalid cursor.
By removing close c1; all errors will be removed.

PL/SQL code without any errors is exact as given above
by Ramprasad.

Thanks

Rinson KE

Is This Answer Correct ?    6 Yes 1 No

Dear All, Question for this Week Find out possible error(s) (either at c..

Answer / umadevi

Declare
Cursor C1 is select empno, ename, sal, comm from
emp;
Begin
For i in C1 Loop
If i.comm between 299 and 999 then
Dbms_output.put_line(i.Ename || '**
'||' Good Commission');
Elsif i.comm > 999 then
Dbms_output.put_line(i.Empno || ' **
'||'Very Good Commission');
Else
Dbms_output.put_line(i.Ename || '**
' || ( i.comm||','||'O'));
End if;
End Loop;
End;
/

Is This Answer Correct ?    2 Yes 0 No

Dear All, Question for this Week Find out possible error(s) (either at c..

Answer / ramprasad.s

Hi Anaswer to your question
IS

declare
cursor C1 is select ENAME,SAL,COMM from EMP;
begin
For i IN C1 loop
IF i.comm > 299 AND i.comm < 999 then
dbms_output.put_line(i.ENAME || ' ' || '** Good
commission');
ELSIF i.comm >999 then
dbms_output.put_line(i.ENAME || ' ' || '** Very Good
commission');
ELSE
dbms_output.put_line(i.ENAME || ' ' || NVL(COMM,0));
END IF;
END LOOP;
END;

sun_ramprasad@yahoo.com

Is This Answer Correct ?    1 Yes 0 No




Dear All, Question for this Week Find out possible error(s) (either at c..

Answer / intejar ahmad

invalid single quotes and cursor does not selecy empno

Is This Answer Correct ?    0 Yes 0 No

Dear All, Question for this Week Find out possible error(s) (either at c..

Answer / radha sri seshu.kolla

WHEN YOU ARE USING CURSOR WIH FOR LOOP NO NEED TO SPECIFY
CLOSE CURSOR.
SINGLE CORES ARE LOOKING AS IF THEY ARE SINGLE CORES, BUT
THEY ARE SPECIAL CHARACTERS.
YOU ARE USIGN NVL FUNCTION IN THIS. YOU GAVE FIRST ARGUMENT
AS NUMBER DATATYPE, AND SECOND IS CHARACTER DATA TYPE. 0
AND O LOOK LIKE SAME JUST IT IS TO CONFUSE THE CANDIDATES.

Is This Answer Correct ?    0 Yes 0 No

Dear All, Question for this Week Find out possible error(s) (either at c..

Answer / tanmay agrawal

DECLARE
CURSOR C1
IS
SELECT empno,ename, sal, comm FROM emp;
BEGIN
FOR i IN C1
LOOP
IF i.comm BETWEEN 299 AND 999 THEN
Dbms_output.put_line(i.Ename || ' ** Good Commission');
elsif i.comm > 999 THEN
Dbms_output.put_line(i.Empno || ' ** Very Good Commission');
-- CLOSE C1;
ELSE
Dbms_output.put_line(i.Ename || ' ** ' ||NVL(to_char(i.comm),'O'));
END IF;
END LOOP;
END;

Is This Answer Correct ?    0 Yes 0 No

Dear All, Question for this Week Find out possible error(s) (either at c..

Answer / ganesh sawant from finacle

It will run and will give o/p
*******************************************
set serveroutput on
declare
Cursor C1 is select A_no, A_name from ABC;
Begin
For i in C1 Loop
If i.A_no between 10 and 20 then
Dbms_output.put_line(i.A_name || '** Good Commission******');
Elsif i.A_no > 2000 then
Dbms_output.put_line(i.A_name || '** Very Good Commission##########');
close C1;
Else
Dbms_output.put_line(i.A_name || '&&&&&& ' ||nvl(i.A_name,'O'));
End if;
End Loop;
End;
===========
PL/SQL procedure successfully completed.

RAJ** Good Commission******
RAJ** Good Commission******
RAJ** Good Commission******
RAJ** Good Commission******
BABA** Good Commission******

Is This Answer Correct ?    0 Yes 0 No

Post New Answer



More SQL PLSQL Interview Questions

What are wait events. Describe the wait event tables.

1 Answers   CTS,


Please let me know if UNION ALL and Natural Join does the same operation and are same...

7 Answers   iGate,


can we call a procedure from a function?

9 Answers   Mind Tree,


what is meant by tuning and tk proof?

2 Answers  


25.67,-1 Trunc = 20 Round= 30 HOW????

2 Answers   Karrox,






what is the correct way of selection statement a. select/from/table_name/orderby/groupby/having b. select/from/table_name/groupby/having/orderby

5 Answers   HCL, JPMorgan Chase,


How will you make performance better for application including front end and back

2 Answers   Metric Stream,


How to display the contents of a current record fetched in a reference cursor?

2 Answers  


What is the Query to print out the individual total number of duplicate row in sql.

5 Answers   TCS,


PL/SQL allocates memory for the variable's at runtime or at compile time

3 Answers  


what is the purpose of update command in oracle?

7 Answers   MBT,


if a string is there like s1,s2,s3,s4.How to find count of commas in this.

4 Answers  






Categories