Oracle Apps PL-SQL Interview Questions – Set 4
Oracle Apps PL-SQL Interview Questions – Set 4
Q: Numeric variables
variable_name NUMBER(P[,S]) := VALUE;
To define a numeric variable, the programmer appends the variable type NUMBER to the name definition. To specify the (optional) precision(P) and the (optional) scale (S), one can further append these in round brackets, separated by a comma. (“Precision” in this context refers to the number of digits which the variable can hold, “scale” refers to the number of digits which can follow the decimal point.)
A selection of other datatypes for numeric variables would include: binary_float, binary_double, dec, decimal, double precision, float, integer, int, numeric, real, smallint, binary_integer
Q: Character variables
variable_name varchar2(L) := ‘Text';
To define a character variable, the programmer normally appends the variable type VARCHAR2 to the name definition. There follows in brackets the maximum number of characters which the variable can store.
Other datatypes for character variables include:
varchar, char, long, raw, long raw, nchar, nchar2, clob, blob, bfile
Q: Date variables
variable_name date := ’01-Jan-2005′;
Oracle provides a number of data types that can store dates (DATE, DATETIME, TIMESTAMP etc.), however DATE is most commonly used.
Programmers define date variables by appending the datatype code “DATE” to a variable name. The TO_DATE function can be used to convert strings to date values. The function converts the first quoted string into a date, using as a definition the second quoted string, for example:
TO_DATE (’31-Dec-2004′,’dd-mon-yyyy’, ‘NLS_DATE_LANGUAGE = American’)
To convert the dates to strings one uses the function TO_CHAR (date_string, format_string).
PL/SQL also supports the use of ANSI date and interval literals. The following clause gives an 18-month range:
WHERE dateField BETWEEN DATE ‘2004-12-31′ – INTERVAL ‘1-6′ YEAR TO MONTH
AND DATE ‘2004-12-31′
Q: Datatypes for specific columns
This syntax defines a variable of the type of the referenced column on the referenced tables.
Programmers specify user-defined datatypes with the syntax:
type data_type is record (field_1 type_1 :=xyz, field_2 type_2 :=xyz, …, field_n type_n :=xyz);
TYPE t_address IS RECORD (
SELECT name, street, street_number, postcode INTO v_address FROM address WHERE ROWNUM = 1;
This sample program defines its own datatype, called t_address, which contains the fields name, street, street_number and postcode.
so according the example we are able to copy the data from database to the fields in program. Using this datatype the programmer has defined a variable called v_address and loaded it with data from the ADDRESS table.
Programmers can address individual attributes in such a structure by means of the dot-notation, thus: “v_address.street := ‘High Street';”
Q: Conditional Statements
The following code segment shows the IF-THEN-ELSIF construct. The ELSIF and ELSE parts are optional so it is possible to create simpler IF-THEN or, IF-THEN-ELSE constructs.
IF x = 1 THEN
ELSIF x = 2 THEN
ELSIF x = 3 THEN
ELSIF x = 4 THEN
ELSIF x = 5 THEN
The CASE statement simplifies some large IF-THEN-ELSE structures.
WHEN x = 1 THEN sequence_of_statements_1;
WHEN x = 2 THEN sequence_of_statements_2;
WHEN x = 3 THEN sequence_of_statements_3;
WHEN x = 4 THEN sequence_of_statements_4;
WHEN x = 5 THEN sequence_of_statements_5;
CASE statement can be used with predefined selector:
WHEN 1 THEN sequence_of_statements_1;
WHEN 2 THEN sequence_of_statements_2;
WHEN 3 THEN sequence_of_statements_3;
WHEN 4 THEN sequence_of_statements_4;
WHEN 5 THEN sequence_of_statements_5;
Q: Array handling
PL/SQL refers to arrays as “collections”. The language offers three types of collections:
Index-by tables (associative arrays)
Varrays (variable-size arrays)
Programmers must specify an upper limit for varrays, but need not for index-by tables or for nested tables. The language includes several collection methods used to manipulate collection elements: for example FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE, etc. Index-by tables can be used to simulate associative arrays, as in this example of a memo function for Ackermann’s function in PL/SQL.
As a procedural language by definition, PL/SQL provides several iteration constructs, including basic LOOP statements, WHILE loops, FOR loops, and Cursor FOR loops.
Q: LOOP statements
EXIT parent_loop WHEN <condition>; — Terminates both loops
EXIT WHEN <condition>; — Returns control to parent_loop
EXIT WHEN <condition>;
END LOOP parent_loop;
Loops can be terminated by using the EXIT keyword, or by raising an exception.
Q: FOR loops
Q: Cursor FOR loops
FOR RecordIndex IN (SELECT person_code FROM people_table)
Cursor-for loops automatically open a cursor, read in their data and close the cursor again
As an alternative, the PL/SQL programmer can pre-define the cursor’s SELECT-statement in advance in order (for example) to allow re-use or to make the code more understandable (especially useful in the case of long or complex queries).
CURSOR cursor_person IS
SELECT person_code FROM people_table;
FOR RecordIndex IN cursor_person
The concept of the person_code within the FOR-loop gets expressed with dot-notation (“.”):
/*N.B. for loop variables in pl/sql are new declarations, with scope only inside the loop */
FOR var IN 0 … 10 LOOP
IF (var IS NULL) THEN
DBMS_OUTPUT.put_line(‘var is null’);
DBMS_OUTPUT.put_line(‘var is not null’);
var is null
Q: Similar languages
PL/SQL functions analogously to the embedded procedural languages associated with other relational databases. Sybase ASE and Microsoft SQL Server have Transact-SQL, PostgreSQL has PL/pgSQL (which tries to emulate PL/SQL to an extent), and IBM DB2 includes SQL Procedural Language, which conforms to the ISO SQL’s SQL/PSM standard.
The designers of PL/SQL modelled its syntax on that of Ada. Both Ada and PL/SQL have Pascal as a common ancestor, and so PL/SQL also resembles Pascal in numerous aspects. The structure of a PL/SQL package closely resembles the basic Pascal program structure or a Borland Delphi unit. Programmers can define global data-types, constants and static variables, public and private, in a PL/SQL package.
PL/SQL also allows for the definition of classes and instantiating these as objects in PL/SQL code. This resembles usages in object-oriented programming languages like Object Pascal, C++ and Java. PL/SQL refers to a class as an “Advanced Data Type” (ADT) or “User Defined Type”(UDT), and defines it as an Oracle SQL data-type as opposed to a PL/SQL user-defined type, allowing its use in both the Oracle SQL Engine and the Oracle PL/SQL engine. The constructor and methods of an Advanced Data Type are written in PL/SQL. The resulting Advanced Data Type can operate as an object class in PL/SQL. Such objects can also persist as column values in Oracle database tables.
PL/SQL does not resemble Transact-SQL, despite superficial similarities. Porting code from one to the other usually involves non-trivial work, not only due to the differences in the feature sets of the two languages, but also due to the very significant differences in the way Oracle and SQL Server deal with concurrency and locking.
The Fyracle project aims to enable the execution of PL/SQL code in the open-source Firebird database.