Oracle Apps PL-SQL Interview Questions – Set 1

Introduction on PL-SQL

Q: What is PL/SQL and what is it used for?

SQL is a declarative language that allows database programmers to write a SQL declaration and hand it to the database for execution. As such, SQL cannot be used to execute procedural code with conditional, iterative and sequential statements. To overcome this limitation, PL/SQL was created.

PL/SQL is Oracle’s Procedural Language extension to SQL. PL/SQL’s language syntax, structure and data types are similar to that of Ada. Some of the statements provided by PL/SQL:

Conditional Control Statements:

IF … THEN … ELSIF … ELSE … END IF;

CASE … WHEN … THEN … ELSE … END CASE;

Iterative Statements:

LOOP … END LOOP;

WHILE … LOOP … END LOOP;

FOR … IN [REVERSE] … LOOP … END LOOP;

Sequential Control Statements:

GOTO …;

NULL;

The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance).

PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

Example PL/SQL blocks:

/* Remember to SET SERVEROUTPUT ON to see the output */

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Hello World’);

END;

/

BEGIN

— A PL/SQL cursor

FOR cursor1 IN (SELECT * FROM table1) — This is an embedded SQL statement

LOOP

DBMS_OUTPUT.PUT_LINE(‘Column 1 = ‘ || cursor1.column1 ||

‘, Column 2 = ‘ || cursor1.column2);

END LOOP;

END;

/

Q: 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.

Q: Should one use PL/SQL or Java to code procedures and triggers?

Both PL/SQL and Java can be used to create Oracle stored procedures and triggers. This often leads to questions like “Which of the two is the best?” and “Will Oracle ever desupport PL/SQL in favour of Java?”.

Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9i supports native compilation of Pl/SQL code to binaries. Not to mention the numerous PL/SQL enhancements made in Oracle 10g and 11g.

PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the similarities and difference between these two language environments:

PL/SQL:

— Can be used to create Oracle packages, procedures and triggers

— Data centric and tightly integrated into the database

— Proprietary to Oracle and difficult to port to other database systems

— Data manipulation is slightly faster in PL/SQL than in Java

— PL/SQL is a traditional procedural programming language

Java:

— Can be used to create Oracle packages, procedures and triggers

— Open standard, not proprietary to Oracle

— Incurs some data conversion overhead between the Database and Java type

— Java is an Object Orientated language, and modules are structured into classes

— Java can be used to produce complete applications

PS: Starting with Oracle 10g, .NET procedures can also be stored within the database (Windows only). Nevertheless, unlike PL/SQL and JAVA, .NET code is not usable on non-Windows systems.

PS: In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.

Q: 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:

SELECT OBJECT_NAME,

TO_CHAR(CREATED,       ‘DD-Mon-RR HH24:MI’) CREATE_TIME,

TO_CHAR(LAST_DDL_TIME, ‘DD-Mon-RR HH24:MI’) MOD_TIME,

STATUS

FROM   USER_OBJECTS

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.

Q: How can one search PL/SQL code for a string/ key value?

The following query is handy if you want to know where certain tables, columns and expressions are referenced in your PL/SQL source code.

SELECT type, name, line

FROM   user_source

WHERE  UPPER(text) LIKE UPPER(‘%&KEYWORD%’);

If you run the above query from SQL*Plus, enter the string you are searching for when prompted for KEYWORD. If not, replace &KEYWORD with the string you are searching for.

Q: 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. Look at this example:

CREATE TABLE SOURCE_HIST                    — Create history table

AS SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*

FROM   ALL_SOURCE WHERE 1=2;

CREATE OR REPLACE TRIGGER change_hist        — Store code in hist table

AFTER CREATE ON SCOTT.SCHEMA          — Change SCOTT to your schema name

DECLARE

BEGIN

IF ORA_DICT_OBJ_TYPE in (‘PROCEDURE’, ‘FUNCTION’,

‘PACKAGE’,   ‘PACKAGE BODY’,

‘TYPE’,      ‘TYPE BODY’)

THEN

— Store old code in SOURCE_HIST table

INSERT INTO SOURCE_HIST

SELECT sysdate, all_source.* FROM ALL_SOURCE

WHERE  TYPE = ORA_DICT_OBJ_TYPE  — DICTIONARY_OBJ_TYPE IN 8i

AND  NAME = ORA_DICT_OBJ_NAME; — DICTIONARY_OBJ_NAME IN 8i

END IF;

EXCEPTION

WHEN OTHERS THEN

raise_application_error(-20000, SQLERRM);

END;

/

show errors

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.

Q: How can I protect my PL/SQL source code?

Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin directory.

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!

The syntax is:

wrap iname=myscript.pls oname=xxxx.plb

Please note: there is no way to unwrap a *.plb binary file. You are supposed to backup and keep your *.pls source files after wrapping them.

Q: Can one 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 if you issue the SET SERVEROUTPUT ON; command. For example:

set serveroutput on

begin

dbms_output.put_line(‘Look Ma, I can print from PL/SQL!!!’);

end;

/

DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000

If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven’t cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.

Note that DBMS_OUTPUT doesn’t print blank or NULL lines. To overcome this problem, SET SERVEROUTPUT ON FORMAT WRAP; Look at this example with this option first disabled and then enabled:

SQL> SET SERVEROUTPUT ON

SQL> begin

dbms_output.put_line(‘The next line is blank’);

dbms_output.put_line();

dbms_output.put_line(‘The above line should be blank’);

end;

/

The next line is blank

The above line should be blank

SQL> SET SERVEROUTPUT ON FORMAT WRAP

SQL> begin

dbms_output.put_line(‘The next line is blank’);

dbms_output.put_line();

dbms_output.put_line(‘The above line should be blank’);

end;

/

The next line is blank

The above line should be blank

Q: Can one 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. Here is an example:

CONNECT / AS SYSDBA

CREATE OR REPLACE DIRECTORY mydir AS ‘/tmp';

GRANT read, write ON DIRECTORY mydir TO scott;

Provide user access to the UTL_FILE package (created by catproc.sql):

GRANT EXECUTE ON UTL_FILE TO scott;

Copy and paste these examples to get you started:

Write File

DECLARE

fHandler UTL_FILE.FILE_TYPE;

BEGIN

fHandler := UTL_FILE.FOPEN(‘MYDIR’, ‘myfile’, ‘w’);

UTL_FILE.PUTF(fHandler, ‘Look ma, Im writing to a file!!!\n’);

UTL_FILE.FCLOSE(fHandler);

EXCEPTION

WHEN utl_file.invalid_path THEN

raise_application_error(-20000, ‘Invalid path. Create directory or set UTL_FILE_DIR.’);

END;

/

Read File

DECLARE

fHandler UTL_FILE.FILE_TYPE;

buf      varchar2(4000);

BEGIN

fHandler := UTL_FILE.FOPEN(‘MYDIR’, ‘myfile’, ‘r’);

UTL_FILE.GET_LINE(fHandler, buf);

dbms_output.put_line(‘DATA FROM FILE: ‘||buf);

UTL_FILE.FCLOSE(fHandler);

EXCEPTION

WHEN utl_file.invalid_path THEN

raise_application_error(-20000, ‘Invalid path. Create directory or set UTL_FILE_DIR.’);

END;

/

NOTE: UTL_FILE was introduced with Oracle 7.3. Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.

Q: Can one 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). Examples:

begin

EXECUTE IMMEDIATE ‘CREATE TABLE X(A DATE)';

end;

begin execute Immediate ‘TRUNCATE TABLE emp'; end;

DECLARE

var VARCHAR2(100);

BEGIN

var := ‘CREATE TABLE temp1(col1 NUMBER(2))';

EXECUTE IMMEDIATE var;

END;

NOTE: The DDL statement in quotes should not be terminated with a semicolon.

Users running Oracle versions below Oracle 8i can look at the DBMS_SQL package.

 

 

Visit Us On FacebookVisit Us On Google PlusVisit Us On TwitterVisit Us On YoutubeVisit Us On LinkedinCheck Our Feed