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;

Answer Posted / 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



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

How global cursor can be declare with dynamic trigger ?

1721


What are the types of join in sql?

493


How to generate a salary slip like jan 1000 1000 feb 1000 2000 ... dec 1000 12000

1613


What is the file extension for sql database?

496


Can a foreign key be a duplicate?

483






What does inner join mean?

528


How do I run a sql script?

491


What is oracle and pl sql?

635


1) Synonyms 2) Co-related Subquery 3) Different Jobs in Plsql 4) Explain Plan 5) Wrap 6) Query Optimization Technique 7) Bulk Collect 8) Types of index 9) IF primary key is created then the index created ? 10) Foreign Key 11) Exception Handling 12) Difference Between Delete and Trunc 13) Procedure Overloading 14) Grant Revoke 15) Procedure Argument types. 16) Functions. 17) Joins

1121


What is sql injection owasp?

526


Can a primary key be a foreign key?

558


Are there any features that are decommissioned in 11g that are not present in 11g?

1567


How do I order by ascending in sql?

510


Can we declare a column having number data type and its scale is larger than pricesionex: column_name number(10,100),column_name numbaer(10,-84)

513


how to extract a unit value from a date and time? : Sql dba

529