Oracle Apps PL-SQL Interview Questions – Set 3

Oracle Apps PL-SQL Interview Questions – Set 3

Q: Is there a PL/SQL Engine in SQL*Plus?

No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.

Q: Is there a limit on the size of a PL/SQL block?

Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:

SQL> select * from dba_object_size where name = ‘procedure_name';

Q: What are the PL/SQL compiler limits for block, record, subquery and label nesting?

The following limits apply:

Level of Block Nesting: 255

Level of Record Nesting: 32

Level of Subquery Nesting: 254

Level of Label Nesting: 98

Q: Can one COMMIT/ ROLLBACK from within a trigger?

A commit inside a trigger would defeat the basic definition of an atomic transaction (see ACID). Trigger logic is by definition an extension of the original DML operation. Changes made within triggers should thus be committed or rolled back as part of the transaction in which they execute. For this reason, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers). Here is an example of what will happen when they do:

SQL> CREATE TABLE tab1 (col1 NUMBER);

Table created.

SQL> CREATE TABLE log (timestamp DATE, operation VARCHAR2(2000));

Table created.

SQL> CREATE TRIGGER tab1_trig

AFTER insert ON tab1

BEGIN

INSERT INTO log VALUES (SYSDATE, ‘Insert on TAB1′);

COMMIT;

END;

/

Trigger created.

SQL> INSERT INTO tab1 VALUES (1);

INSERT INTO tab1 VALUES (1)

*

ERROR at line 1:

ORA-04092: cannot COMMIT in a trigger

ORA-06512: at “SCOTT.TAB1_TRIG”, line 3

ORA-04088: error during execution of trigger ‘SCOTT.TAB1_TRIG’

Autonomous transactions:

As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.

Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements. Example:

SQL> CREATE OR REPLACE TRIGGER tab1_trig

AFTER insert ON tab1

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO log VALUES (SYSDATE, ‘Insert on TAB1′);

COMMIT; — only allowed in autonomous triggers

END;

/

Trigger created.

SQL> INSERT INTO tab1 VALUES (1);

1 row created.

Note that with the above example will insert and commit log entries – even if the main transaction is rolled-back!

Retrieved from “http://www.orafaq.com/wiki/PL/SQL_FAQ”

You might have got a good idea about PL-SQL already. In that case, maybe you wouldn’t need to refer the below sections. This is another reference from Wikipedia about PL-SQL.

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s proprietary procedural extension to the SQL database language, used in the Oracle database. Some other SQL database management systems offer similar extensions to the SQL language. PL/SQL’s syntax strongly resembles that of Ada, and just like some Ada compilers of the 1980s, the PL/SQL runtime system uses Diana as intermediate representation.

The key strength of PL/SQL is its tight integration with the Oracle database.

PL/SQL is one of three languages embedded in the Oracle Database, the other two being SQL and Java.

Contents

1 Functionality

2 Basic code structure

2.1 Functions

2.2 Procedures

2.3 Anonymous Blocks

2.4 Packages

2.5 Numeric variables

2.6 Character variables

2.7 Date variables

2.8 Datatypes for specific columns

3 Conditional Statements

4 Array handling

5 Looping

5.1 LOOP statements

5.2 FOR loops

5.3 Cursor FOR loops

5.3.1 Example

6 Similar languages

7 See also

8 References

9 External links

Q: Functionality

 

PL/SQL supports variables, conditions, loops, arrays (in somewhat unusual way) and exceptions. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. PL/SQL, however, as a Turing-complete procedural language that fills in these gaps, allows Oracle database developers to interface with the underlying relational database in an imperative manner. SQL statements can make explicit in-line calls to PL/SQL functions, or can cause PL/SQL triggers to fire upon pre-defined Data Manipulation Language (DML) events.

PL/SQL stored procedures (functions, procedures, packages, and triggers) performing DML will get compiled into an Oracle database: to this extent, their SQL code can undergo syntax-checking. Programmers working in an Oracle database environment can construct PL/SQL blocks of functionality to serve as procedures, functions; or they can write in-line segments of PL/SQL within SQL*Plus scripts.

While programmers can readily incorporate SQL DML statements into PL/SQL (as cursor definitions, for example, or using the SELECT … INTO syntax), Data Definition Language (DDL) statements such as CREATE TABLE/DROP INDEX etc. require the use of “Dynamic SQL”. Earlier versions of Oracle Database required the use of a complex built-in DBMS_SQL package for Dynamic SQL where the system needed to explicitly parse and execute an SQL statement. Later versions have included an EXECUTE IMMEDIATE syntax called “Native Dynamic SQL” which considerably simplifies matters. Any use of DDL in an Oracle database will result in an implicit COMMIT. Programmers can also use Dynamic SQL to execute DML where they do not know the exact content of the statement in advance.

PL/SQL offers several pre-defined packages for specific purposes. Such PL/SQL packages include:

DBMS_OUTPUT – for output operations to non-database destinations

DBMS_JOB – for running specific procedures/functions at a particular time (i.e. scheduling)

DBMS_XPLAN – for formatting Explain Plan output

DBMS_SESSION – provides access to SQL ALTER SESSION and SET ROLE statements, and other session information.

DBMS_METADATA – for extracting meta data from the data dictionary (such as DDL statements)

DBMS_EPG – for managing the built-in webserver (Embedded PL/SQL Gateway) in the database

UTL_FILE – for reading and writing files on disk

UTL_HTTP – for making requests to web servers from the database

UTL_SMTP – for sending mail from the database (via an SMTP server)

and many more – Oracle Corporation customarily adds more packages and/or extends package functionality with each successive release of Oracle Database.

Q: Basic code structure

Anonymous blocks are the basis of standalone PL/SQL scripts, and have the following structure:

<<label>>

DECLARE

TYPE / item / FUNCTION / PROCEDURE declarations

BEGIN

Statements

EXCEPTION

EXCEPTION handlers

END label;

The <<label>> and the DECLARE and EXCEPTION sections are optional.

Exceptions, errors which arise during the execution of the code, have one of two types:

Predefined exceptions

User-defined exceptions.

User-defined exceptions are always raised explicitly by the programmers, using the RAISE or RAISE_APPLICATION_ERROR commands, in any situation where they have determined that it is impossible for normal execution to continue. RAISE command has the syntax:

RAISE <exception name>;

Oracle Corporation has pre-defined several exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, etc. Each exception has a SQL Error Number and SQL Error Message associated with it. Programmers can access these by using the SQLCODE and SQLERRM functions.

The DECLARE section defines and (optionally) initialises variables. If not initialised specifically, they default to NULL.

For example:

DECLARE

number1 NUMBER(2);

number2 NUMBER(2)    := 17;             — value default

text1   VARCHAR2(12) := ‘Hello world';

text2   DATE         := SYSDATE;        — current date and time

BEGIN

SELECT street_number

INTO number1

FROM address

WHERE name = ‘BILLA';

END;

The symbol := functions as an assignment operator to store a value in a variable.

The major datatypes in PL/SQL include NUMBER, INTEGER, CHAR, VARCHAR2, DATE, TIMESTAMP, TEXT etc.

Q: Functions

Functions in PL/SQL are a collection of SQL and PL/SQL statements that perform a task and should return a value to the calling environment.

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type

<IS|AS>

[declaration block]

BEGIN

<PL/SQL block WITH RETURN statement>

[EXCEPTION

EXCEPTION block]

END;

Q: Procedures

Procedures are the same as Functions, in that they are also used to perform some task with the difference being that procedures cannot be used in a SQL statement and although they can have multiple out parameters they do not return a value. This is not alway’s true for when an NVL function is used.

Q: Anonymous Blocks

Anonymous PL/SQL blocks can be embedded in an Oracle Precompiler or OCI program. At run time, the program, lacking a local PL/SQL engine, sends these blocks to the Oracle server, where they are compiled and executed. Likewise, interactive tools such as SQL*Plus and Enterprise Manager, lacking a local PL/SQL engine, must send anonymous blocks to Oracle.

Q: Packages

Packages are groups of conceptually linked Functions, Procedures,Variable,Constants & Cursors etc. The use of packages promotes re-use of code. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.

 

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