Introduction of Dictonary Object

Dictionary Object stores data key, item pairs. A Dictionary object stores the items in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.

Adavntages of using it in QTP:

1. can be used as Global variable declaration. so that any test can access the values from it in the run time.

2. You can store and retrive any number of run time values in to dictonary.

3. It is one of the Parameterization techique we can use in QTP

Disadvantages:

we can not specify the values in the desingn time like Datatable , Action parameters, environment variable.

So it is useful only in Run time , not design time

Methods:

Add Method

Adds a key and item pair to a Dictionary

object. object.Add (key, item)

Arguments

object Required. Always the name of a Dictionary object.

key Required. The key associated with the item being added.item Required.

The item associated with the key being added.

Remarks

An error occurs if the key already exists.

The following example illustrates the use of the Add method.

Dim d ‘ Create a variable.

Set d = CreateObject(“Scripting.Dictionary”)

d.Add “a”, “Athens” ‘ Add some keys and items.

d.Add “b”, “Belgrade”

Items Method

Returns an array containing all the items in a Dictionary object.

object.Items( )
Remarks The object is always the name of a Dictionary object.The following code illustrates use of the Items method:

Set d = CreateObject(“Scripting.Dictionary”)
d.Add “a”, “Athens” ‘ Add some keys and items.
d.Add “b”, “Belgrade”
a = d.Items ‘ Get the items.
For i = 0 To d.Count -1 ‘ Iterate the array.
s = s & a(i) & “” ‘ Create return string.
Next
Msgbox s
Exists Method

Returns true if a specified key exists in the Dictionary object, false if it does not.

object.Exists(key)

Arguments

object Required. Always the name of a Dictionary object.

key Required. Key value being searched for in the Dictionary object.

Remarks

The following example illustrates the use of the Exists method.

Set d = CreateObject(“Scripting.Dictionary”)
d.Add “a”, “Athens” ‘ Add some keys and items.
d.Add “b”, “Belgrade”
If d.Exists(“c”) Then
Msgbox “Specified key exists.”
Else
Msgbox “Specified key doesn’t exist.”
End If
Keys Method

Returns an array containing all existing keys in a Dictionary object.

object.Keys( )

Remarks

The object is always the name of a Dictionary object.

The following code illustrates use of the Keys method:

Dim a, d, i ‘ Create some variables.
Set d = CreateObject(“Scripting.Dictionary”)
d.Add “a”, “Athens” ‘ Add some keys and items.
d.Add “b”, “Belgrade”
a = d.Keys ‘ Get the keys.
For i = 0 To d.Count -1 ‘ Iterate the array.
s = s & a(i) & “” ‘ Return results.
Next
Msgbox s
Remove Method

Removes a key, item pair from a Dictionary object.

object.Remove(key)

Arguments

object Required. Always the name of a Dictionary object.

key Required. Key associated with the key, item pair you want to remove from the Dictionary object.

Remarks

An error occurs if the specified key, item pair does not exist.

The following code illustrates use of the Remove method:

Dim a, d ‘ Create some variables.
Set d = CreateObject(“Scripting.Dictionary”)
d.Add “a”, “Athens” ‘ Add some keys and items.
d.Add “b”, “Belgrade”
d.Add “c”, “Cairo” …
d.Remove(“b”) ‘ Remove second pair.
RemoveAll Method

The RemoveAll method removes all key, item pairs from a Dictionary object.

object.RemoveAll( )

Dim a, d, i ‘ Create some variables.
Set d = CreateObject(“Scripting.Dictionary”)
d.Add “a”, “Athens” ‘ Add some keys and items.
d.Add “b”, “Belgrade”
d.Add “c”, “Cairo” …
a = d.RemoveAll ‘ Clear the dictionary.

Excel Sorting(Ascending , Descending) By Rows and Columns

Excel Sorting By Row:
Const xlAscending = 1
Const xlNo = 2
Const xlSortRows = 2

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and Settingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1).activate

Set objRange = objExcel.ActiveCell.EntireRow
objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows
set objExcel=nothing

Excel Sorting By Colum :
Const xlAscending = 1′represents the sorting type 1 for Ascending 2 for Desc
Const xlYes = 1

Set objExcel = CreateObject(“Excel.Application”)’Create the excel object
objExcel.Visible = True’Make excel visible
Set objWorkbook = _
objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\Docs1.xls”)’Open the

document

Set objWorksheet = objWorkbook.Worksheets(1)’select the sheet based on the index .. 1,2 ,3 …
Set objRange = objWorksheet.UsedRange’which select the range of the cells has some data other than blank
Set objRange2 = objExcel.Range(“A1″)’ select the column to sort

objRange.Sort objRange2, xlAscending, , , , , , xlYes
set objExcel=nothing

Introduction of PL/SQL language Elements

PL/SQL offers various languageconstructs such as variable assignments, control structures (loops, if-then-else), procedureand function calls, etc. However, PL/SQL does not allow commands of the SQL data definition language such as the create table statement. For this, PL/SQL provides special packages.

PL/SQL uses a modified select statement that requires each selected tuple to be assigned to a record (or a list of variables). There are several alternatives in PL/SQL to a assign a value to a variable. The most simple way to assign a value to a variable is

declare
counter integer := 0;
. . .
begin
counter := counter + 1;

Values to assign to a variable can also be retrieved from the database using a select statement
select into from where ;

It is important to ensure that the select statement retrieves at most one tuple ! Otherwise
it is not possible to assign the attribute values to the specified list of variables and a runtime
error occurs. If the select statement retrieves more than one tuple, a cursor must be used instead. Furthermore, the data types of the specified variables must match those of the retrieved attribute values. For most data types, PL/SQL performs an automatic type conversion (e.g., from integer to real).

Instead of a list of single variables, a record can be given after the keyword into. Also in this case, the select statement must retrieve at most one tuple !

declare
employee rec EMP%ROWTYPE;
max sal EMP.SAL%TYPE;
begin
select EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO
into employee rec
from EMP where EMPNO = 5698;
select max(SAL) into max sal from EMP;
. . .
end;

PL/SQL provides while-loops, two types of for-loops, and continuous loops. Latter ones are used in combination with cursors. All types of loops are used to execute a sequence of statements multiple times. The specification of loops occurs in the same way as known from imperative programming languages such as C or Pascal.

A while-loop has the pattern
[<< >>]
while loop
;
end loop [] ;

A loop can be named. Naming a loop is useful whenever loops are nested and inner loops are
completed unconditionally using the exit ; statement.

Whereas the number of iterations through a while loop is unknown until the loop completes,
the number of iterations through the for loop can be specified using two integers.

[<< >>]
for in [reverse] .. loop

end loop [] ;

The loop counter is declared implicitly. The scope of the loop counter is only the
for loop. It overrides the scope of any variable having the same name outside the loop. Inside
the for loop, can be referenced like a constant. may appear in expressions,
but one cannot assign a value to . Using the keyword reverse causes the iteration to
proceed downwards from the higher bound to the lower bound.
Processing Cursors: Before a cursor can be used, it must be opened using the open statement
open [()] ;

The associated select statement then is processed and the cursor references the first selected
tuple. Selected tuples then can be processed one tuple at a time using the fetch command
fetch into ;

The fetch command assigns the selected attribute values of the current tuple to the list of
variables. After the fetch command, the cursor advances to the next tuple in the result set.
Note that the variables in the list must have the same data types as the selected values. After
all tuples have been processed, the close command is used to disable the cursor.

close ;

The example below illustrates how a cursor is used together with a continuous loop:

declare
cursor emp cur is select from EMP;
emp rec EMP%ROWTYPE;
emp sal EMP.SAL%TYPE;
begin
open emp cur;
loop
fetch emp cur into emp rec;
exit when emp cur%NOTFOUND;
emp sal := emp rec.sal;

end loop;
close emp cur;
. . .
end;

Each loop can be completed unconditionally using the exit clause:
exit [] [when ]

Using exit without a block label causes the completion of the loop that contains the exit statement.

A condition can be a simple comparison of values. In most cases, however, the condition
refers to a cursor. In the example above, %NOTFOUND is a predicate that evaluates to false if the
most recent fetch command has read a tuple. The value of %NOTFOUND is null
before the first tuple is fetched. The predicate evaluates to true if the most recent fetch failed
to return a tuple, and false otherwise. %FOUND is the logical opposite of %NOTFOUND.
Cursor for loops can be used to simplify the usage of a cursor:

[<< >>]

for in [()] loop

end loop [];

A record suitable to store a tuple fetched by the cursor is implicitly declared. Furthermore,
this loop implicitly performs a fetch at each iteration as well as an open before the loop is
entered and a close after the loop is left. If at an iteration no tuple has been fetched, the loop
is automatically terminated without an exit.

It is even possible to specify a query instead of in a for loop:

for in () loop

end loop;

That is, a cursor needs not be specified before the loop is entered, but is defined in the select
statement.
Example:

for sal rec in (select SAL + COMM total from EMP) loop
. . . ;
end loop;

total is an alias for the expression computed in the select statement. Thus, at each iteration
only one tuple is fetched. The record sal rec, which is implicitly defined, then contains only
one entry which can be accessed using sal rec.total. Aliases, of course, are not necessary if
only attributes are selected, that is, if the select statement contains no arithmetic operators
or aggregate functions.

For conditional control, PL/SQL offers if-then-else constructs of the pattern
if then
[elsif ] then
. . .
[else] end if ;

Starting with the first condition, if a condition yields true, its corresponding sequence of statements
is executed, otherwise control is passed to the next condition. Thus the behavior of this type of PL/SQL statement is analogous to if-then-else statements in imperative programming languages.

Except data definition language commands such as create table, all types of SQL statements
can be used in PL/SQL blocks, in particular delete, insert, update, and commit. Note that in PL/SQL only select statements of the type select into are allowed, i.e., selected attribute values can only be assigned to variables (unless the select statement is used in a subquery). The usage of select statements as in SQL leads to a syntax error. If update or delete statements are used in combination with a cursor, these commands can be restricted to currently fetched tuple. In these cases the clause where current of is added as shown in the following example.

Example: The following PL/SQL block performs the following modifications: All employees having ’KING’ as their manager get a 5% salary increase.

declare
manager EMP.MGR%TYPE;
cursor emp cur (mgr no number) is
select SAL from EMP
where MGR = mgr no
for update of SAL;
begin
select EMPNO into manager from EMP
where ENAME = ’KING’;
for emp rec in emp cur(manager) loop
update EMP set SAL = emp rec.sal 1.05
where current of emp cur;
end loop;
commit;
end;

Introduction of Exception Handling in PL/SQL

A PL/SQL block may contain statements that specify exception handling routines. Each error or warning during the execution of a PL/SQL block raises an exception. One can distinguish between two types of exceptions:

• system defined exceptions
• user defined exceptions (which must be declared by the user in the declaration part of a block
where the exception is used/implemented)

System defined exceptions are always automatically raised whenever corresponding errors or
warnings occur. User defined exceptions, in contrast, must be raised explicitly in a sequence of statements using raise . After the keyword exception at the end of a block, user defined exception handling routines are implemented. An implementation has the pattern

when then ;

The most common errors that can occur during the execution of PL/SQL programs are handled by system defined exceptions. The table below lists some of these exceptions with their names and a short description.

Exception name Number Remark
CURSOR ALREADY OPEN ORA-06511 – You have tried to open a cursor which is already open
INVALID CURSOR ORA-01001 Invalid cursor operation such as fetching from a closed cursor
NO DATA FOUND ORA-01403 A select . . . into or fetch statement returned no tuple
TOO MANY ROWS ORA-01422 A select . . . into statement returned more than one tuple
ZERO DIVIDE ORA-01476 You have tried to divide a number by 0

Example:
declare
emp sal EMP.SAL%TYPE;
emp no EMP.EMPNO%TYPE;
too high sal exception;
begin
select EMPNO, SAL into emp no, emp sal
from EMP where ENAME = ’KING’;
if emp sal * 1.05 > 4000 then raise too high sal
else update EMP set SQL . . .
end if ;
exception
when NO DATA FOUND – – no tuple selected
then rollback;
when too high sal then insert into high sal emps values(emp no);
commit;
end;

After the keyword when a list of exception names connected with or can be specified. The last
when clause in the exception part may contain the exception name others. This introduces the default exception handling routine, for example, a rollback.

If a PL/SQL program is executed from the SQL*Plus shell, exception handling routines may
contain statements that display error or warning messages on the screen. For this, the procedure
raise application error can be used. This procedure has two parameters and . is a negative integer defined by the user and must range
between -20000 and -20999. is a string with a length up to 2048 characters.
The concatenation operator “||” can be used to concatenate single strings to one string. In order
to display numeric variables, these variables must be converted to strings using the function
to char. If the procedure raise application error is called from a PL/SQL block, processing the PL/SQL block terminates and all database modifications are undone, that is, an implicit rollback is performed in addition to displaying the error message.

Example:
if emp sal * 1.05 > 4000
then raise application error(-20010, ’Salary increase for employee with Id ’
|| to char(Emp no) || ’ is too high’);

Introduction of Procedures and Functions in PL/SQL

PL/SQL provides sophisticated language constructs to program procedures and functions as stand-alone PL/SQL blocks. They can be called from other PL/SQL blocks, other procedures
and functions. The syntax for a procedure definition is

create [or replace] procedure [()] is

begin

[exception
]
end [];

A function can be specified in an analogous way

create [or replace] function [()]
return is
. . .
The optional clause or replace re-creates the procedure/function. A procedure can be deleted using the command drop procedure (drop function ). In contrast to anonymous PL/SQL blocks, the clause declare may not be used in procedure/ function definitions.

Valid parameters include all data types. However, for char, varchar2, and number no length and scale, respectively, can be specified. For example, the parameter number(6) results in a compile error and must be replaced by number. Instead of explicit data types, implicit types of the form %TYPE and %ROWTYPE can be used even if constrained declarations are referenced.

A parameter is specified as follows:
[IN | OUT | IN OUT] [{ := | DEFAULT} ]

The optional clauses IN, OUT, and IN OUT specify the way in which the parameter is used. The default mode for a parameter is IN. IN means that the parameter can be referenced inside the procedure body, but it cannot be changed. OUT means that a value can be assigned to the parameter in the body, but the parameter’s value cannot be referenced. IN OUT allows both assigning values to the parameter and referencing the parameter. Typically, it is sufficient to use the default mode for parameters.

Example: The subsequent procedure is used to increase the salary of all employees who work
in the department given by the procedure’s parameter. The percentage of the salary increase
is given by a parameter, too.

create procedure raise salary(dno number, percentage number DEFAULT 0.5) is
cursor emp cur (dept no number) is
select SAL from EMP where DEPTNO = dept no
for update of SAL;
empsal number(8);
begin
open emp cur(dno); – - Here dno is assigned to dept no
loop
fetch emp cur into empsal;
exit when emp cur%NOTFOUND;
update EMP set SAL = empsal ((100 + percentage)/100)
where current of emp cur;
end loop;
close emp cur;
commit;
end raise salary;

This procedure can be called from the SQL*Plus shell using the command

execute raise salary(10, 3);

If the procedure is called only with the parameter 10, the default value 0.5 is assumed as specified in the list of parameters in the procedure definition. If a procedure is called from a PL/SQL block, the keyword execute is omitted. Functions have the same structure as procedures. The only difference is that a function returns a value whose data type (unconstrained) must be specified.

Example:
create function get dept salary(dno number) return number is
all sal number;
begin
all sal := 0;
for emp sal in (select SAL from EMP where DEPTNO = dno
and SAL is not null) loop

all sal := all sal + emp sal.sal;
end loop;
return all sal;
end get dept salary;

In order to call a function from the SQL*Plus shell, it is necessary to first define a variable to which the return value can be assigned. In SQL*Plus a variable can be defined using the command variable ;, for example, variable salary
number. The above function then can be called using the command execute :salary :=
get dept salary(20); Note that the colon “:” must be put in front of the variable.