DATABASE & SQL/PLSQL

Oacle interview Questions And Answer
Previous Home Next
Display the number value in Words
SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp; 
output:
The output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP')) 

800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
Display Odd/ Even number of records

Odd number of records:

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp); 
output
1
3
5 

Even number of records:

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp) 
output
2
4
6
Which date function returns number value?

months_between

Any three PL/SQL Exceptions?

Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

What are PL/SQL Cursor Exceptions?

Cursor_Already_Open, Invalid_Cursor

Other way to replace query result null value with a text

SQL> Set NULL N/A

to reset SQL> Set NULL

What are the more common pseudo-columns?

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

What is the output of SIGN function?

  1. 1 for positive value,
  2. 0 for Zero,
  3. -1 for Negative value.

What is the maximum number of triggers, can apply to a single table?

12 triggers.

Previous Home Next