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?