Logo F2FInterview

SQL Oracle Interview Questions

Q   |   QA

In PL/SQL 2.2 cursor variables cannot be declared in a package.This is because the storage for a cursor variable has to be allocated using Pro*C or OCI with version 2.2, the only means of passing a cursor variable to a PL/SQL block is via bind variable or a procedure parameter. 

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE
INSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-') 

UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to
output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN. 

EXISTS is faster than IN
Because exists returns Boolean value whereas IN returns only value. 

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
For Eg:-
Enter value for n: 2
SAL
---------
3700 

In order to link this F2FInterview's page as Reference on your website or Blog, click on below text area and pres (CTRL-C) to copy the code in clipboard or right click then copy the following lines after that paste into your website or Blog.

Get Reference Link To This Page: (copy below code by (CTRL-C) and paste into your website or Blog)
HTML Rendering of above code: