Oracle Interview questions and answers

Oracle Interview questions and answers

Q11: Explain implicit cursor.

A11:  It’s a cursor formed by Oracle internally for individual SQL.


Q12: From the following identify the non schema object: packages, triggers, public synonyms, tables and indexes.

A12: Public synonyms.


Q13: Explain PL/SQL.

A13: Oracle’s Procedural Language extension of SQL is PL/SQL.


Q14: Does SQL*Plus have a PL/SQL Engine?

A14: No. SQL*Plus doesn’t contain PL/SQL engine unlike Oracle Forms. Because of which all PL/SQL is sent to database engine to get executed which increases the efficiency. Each SQL statement is individually sent to the database and not stripped off.


Q15: What is the limitation on the block size of PL/SQL?

A15: As of now a compiled/ parsed block of PL/SQL has a maximum size limitation of 64K and max. code size being 100K. The statement for querying existing procedure or package size is as follows:

SQL> select * from dba_object_size where name = ‘procedure_name’


Q16: How to read / write files from PL/SQL?

A16: Oracle 7.3 has a UTL_FILE package included in it which is used to read/ write files. The directory where you want to write to has to be in the INIT.ORA file. Prior to Oracle 7.3 DBMS_OUTPUT with the SQL*Plus SPOOL command was the only way to write a file.


Q17: Explain the methods used to protect source code of PL/SQL.

A17: Source codes of PL/SQL V2.2 made available with Oracle 7.2 are protected by implementing the binary wrapper. A stand alone function does this by transforming the source code of PL/SQL to a portable binary object code. Thus the software can be distributed without the proprietary methods and algorithms getting exposed. Such scripts can still be understood and executed by SQL*DBA and SQL*Plus. The only precaution to be taken is that “decode” command shouldn’t be available.


Q18: Can dynamic SQL be used within PL/SQL?

A18: DBMS_SQL package can be used to execute dynamic SQL in PL/SQL V2.1



Q19: Give the various exception types.

A19: There are two exception types:

a)      User-defined &

b)      Pre defined.


Q20: In a same block can exceptions be defined more than one time?

A20: No.