Here are some of the common interview questions for PL/SQL sourced from the internet.
Q1: What is PL/SQL Programming?
- Distinct to Oracle, PL/SQL is the procedural language extension to the structured query language i.e. SQL. It unites a database language with a procedural programming language, which is built on a basic unit called a block. By compiling and storing executable blocks, Oracle can process the PL/SQL quickly and easily.
Q2. What is the difference between SQL and PL/SQL?
Both SQL and PL/SQL are languages used to access data within Oracle databases.
SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn’t include all the things that normal programming languages have, such as loops and IF…THEN…ELSE statements.
PL/SQL is a normal programming language that includes all the features of most other programming languages. But, it has one thing that other programming languages don’t have: the ability to easily integrate with SQL.
Some of the differences:
- SQL is executed one statement at a time. PL/SQL is executed as a block of code.
- SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).
- SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.
- You can embed SQL in a PL/SQL program, but you cannot embed PL/SQL within a SQL statement.
Q3. How does one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This will allow you to easily revert to previous code should someone make any catastrophic changes. A better approach is to create an external CVS or SVN repository for the scripts that install the PL/SQL code. The canonical version of what’s in the database must match the latest CVS/SVN version or else someone would be cheating.
Q4. How can one see if somebody modified any code?
The source code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the TIMESTAMP and LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';
Note: If you recompile an object, the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated. If you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are updated.
Q5. How do you encrypt a PL/SQL application?
By using the WRAP Utility. Wrap Utility, a standalone programming utility that encrypts PL/SQL source code which is generally used to deliver PL/SQL applications without exposing the source code.
For more info on WRAP click here.
The utility use human-readable PL/SQL source code as input, and writes out portable binary object code (somewhat larger than the original). The binary code can be distributed without fear of exposing your proprietary algorithms and methods. Oracle will still understand and know how to execute the code. Just be careful, there is no “decode” command available. So, don’t lose your source!
Q6. How to find which stored PL/SQL code is wrapped?
The following query gives the list of all wrapped PL/SQL code:
select owner, name, type
where line = 1
and instr(text, ' wrapped'||chr(10))+instr(text, ' wrapped '||chr(10)) > 0
order by 1, 2, 3
Q7. How to print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus by using the SET SERVEROUTPUT ON; command. DBMS_OUTPUT is useful for debugging PL/SQL programs.
Q8. How do you read/write files from PL/SQL?
The UTL_FILE database package can be used to read and write operating system files.
A DBA user needs to grant you access to read from/ write to a specific directory before using this package.
Q9. How do you call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the “EXECUTE IMMEDIATE” statement (native SQL).
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
begin execute Immediate 'TRUNCATE TABLE emp'; end;
Q10. How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.