utPLSQL/documentation/src/utassert.html at master · pascal8/utPLSQL · GitHub

History

1399 lines (1241 loc) · 46.2 KB

Raw

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<head>

</head>

<body>

<h1> utAssert Package</h1>

<p>This package contains the following procedures and functions: </p>

<tr>

<td><a href="#utassert.this">utAssert.this</a></td>

<td><a href="#utassert.this">Generic "Assert This" Procedure</a></td>

</tr>

<tr>

<td>

<a href="#utassert.isnull">utAssert.isnull</a>

<a href="#utassert.isnull">utAssert.isnotnull</a>

</td>

<td><a href="#utassert.isnull">Check for NULL and NOT NULL values</a></td>

</tr>

<tr>

<td><a href="#utassert.eq">utAssert.eq</a></td>

<td><a href="#utassert.eq">Check Equality of Scalar Values</a></td>

</tr>

<tr>

<td><a href="#utassert.eqtable">utAssert.eqtable</a></td>

<td><a href="#utassert.eqtable">Check Equality of Database Tables</a></td>

</tr>

<tr>

<td><a href="#utassert.eqtabcount">utAssert.eqtabcount</a></td>

<td><a href="#utassert.eqtabcount">Check Equality of Table Counts</a></td>

</tr>

<tr>

<td><a href="#eqquery">utAssert.eqquery</a></td>

<td><a href="#eqquery">Check Equality of Queries</a></td>

</tr>

<tr>

<td><a href="#eqqueryvalue">utAssert.eqqueryvalue</a></td>

<td><a href="#eqqueryvalue">Check Equality of Query against single value</a></td>

</tr>

<tr>

<td><a href="#utassert.eqfile">utAssert.eqfile</a></td>

<td><a href="#utassert.eqfile">Check Equality of Files</a></td>

</tr>

<tr>

<td><a href="#utassert.eqpipe">utAssert.eqpipe</a></td>

<td><a href="#utassert.eqpipe">Check Equality of Database Pipes</a></td>

</tr>

<tr>

<td>

<a href="#utassert.eqcoll">utAssert.eqcoll</a>

<a href="#utassert.eqcoll">utAssert.eqcollapi</a>

</td>

<td><a href="#utassert.eqcoll">Check Equality of Collections</a></td>

</tr>

<tr>

<td><a href="#throws">utAssert.throws</a></td>

<td><a href="#throws">Check a procedure or function throws an exception</a></td>

</tr>

<tr>

<td>

<a href="#previous">utAssert.previous_passed

utAssert.previous_failed</a>

</td>

<td>

<a href="#previous">Check if the previous assertion

passed or failed</a>

</td>

</tr>

<tr>

<td><a href="#eqoutput">utAssert.eqoutput</a></td>

<td><a href="#eqoutput">Check Equality of DBMS_OUTPUT Collections</a></td>

</tr>

<tr>

<td>

<a href="#object">utAssert.objexists</a>

<a href="#object">utAssert.objnotexists</a>

</td>

<td><a href="#object">Check for existence of database objects</a></td>

</tr>

<tr>

<td><a href="#eq_refc_query">utAssert.eq_refc_query</a></td>

<td><a href="#eq_refc_query">Check Equality of RefCursor and Query</a></td>

</tr>

<tr>

<td><a href="#eq_refc_table">utAssert.eq_refc_table</a></td>

<td><a href="#eq_refc_table">Check Equality of RefCursor and Database Table</a></td>

</tr>

</table>

<p>

The utAssert package provides a set of assertion routines ("assert that

the following condition is true") that you will use to register the outcome

of a test case. You must call a utAssert assertion program after (or containing)

a test case so that the results of that test can be recorded and then reported.

See <a href="buildpack.html">Build Test Packages</a> for many examples and

more details on this process. Here is a very simple example, though, to give

you an idea of the code you would write:

</p>

<pre>

PROCEDURE ut_BETWNSTR IS

BEGIN

utAssert.eq (

'Typical valid usage',

BETWNSTR(

STRING_IN => 'abcdefg',

START_IN => 3,

END_IN => 5

'cde'

);

END;

</pre>

<p>

utAssert offers a wide (and ever expanding) set of assertion programs that

allow you to efficiently (a) test the outcome of your unit test and (b) report

the results of that test to utPLSQL. You should review <a href="#utassert-common">

Common Assertion Parameters and Behavior</a> before using any specific assertion

program. It is also possible to <a href="#utassert-BYOA">build your own assertion

routine.</a> Note: all utAssert assertions are defined in the ut_assertion

table, as well as actually coded in the utAssert package.

</p>

<h2> <a name="utassert-common"></a>Common Assertion Parameters and Behavior</h2>

<p>

Each type of assertion routine accepts different kinds of data, but there

are lots of similarities between the assertions, as well. Here is an explanation

of the common assertion parameters:

</p>

<tr>

A message to be displayed if the assertion

fails. This is the first argument and is mandatory, because the tests need

to be self documenting.

</td>

</tr>

<tr>

<td valign="top"> check_this_in </td>

The value to be checked.. If a Boolean expression,

this will usually include the invocation of the method being tested, resulting

in a single line of code for the entire test case.

</td>

</tr>

<tr>

<td valign="top"> against_this_in </td>

For assert_eq, the assertion routine will

check the check_this_in value against the against_this_in value. This parameter

should be the certifiably correct value.

</td>

</tr>

<tr>

TRUE if a NULL value should be interpreted

as a successful test, FALSE if NULL indicates failure.

</td>

</tr>

<tr>

<td valign="top"> raise_exc_in </td>

TRUE if it is OK for the assertion routine

to allow an exception to be propagated out unhandled.

</td>

</tr>

</table>

<h2> <a name="utassert.this"></a>Generic "Assert This" Assertion Procedure</h2>

<p>

This most generic assertion program simply says "assert this" and passes

a Boolean expression. It is used by all the other assertion routines, which

<i>construct</i> a Boolean expression from their specific values and logic.

</p>

<pre>

PROCEDURE utAssert.this (

msg_in IN VARCHAR2,

check_this_in IN BOOLEAN,

null_ok_in IN BOOLEAN := FALSE,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

Use utAssert.this when you have a Boolean expression that you want to check,

as in:

</p>

<pre>

BEGIN

...

utAssert.this (

'Boolean function result',

is_valid_account (my_account)

);

</pre>

<p>

You can also use this assertion to register a failure, most usually in

an exception section, as in:

</p>

<pre>

EXCEPTION

WHEN OTHERS

THEN

utAssert.this (

SQLERRM,

FALSE

);

</pre>

<p>

Generally, you should avoid utAssert.this and instead use a specialized

assertion routine, documented below. Most of the assertions give you the

ability check for equality (of scalars, such as strings, or more complex

data structures like tables, pipes and files): does the data generated by

my code match the expected value(s)?

</p>

<h2> <a name="utassert.isnull"></a>Check for NULL and NOT NULL Values</h2>

<p>

You can check to see if a value is NULL or is NOT NULL with the following

assertions:

</p>

<pre>

PROCEDURE utAssert.isnotnull (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2,

null_ok_in IN BOOLEAN := FALSE,

raise_exc_in IN BOOLEAN := FALSE

);

PROCEDURE utAssert.isnull (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2,

null_ok_in IN BOOLEAN := FALSE,

raise_exc_in IN BOOLEAN := FALSE

);

PROCEDURE utAssert.isnotnull (

msg_in IN VARCHAR2,

check_this_in IN BOOLEAN,

null_ok_in IN BOOLEAN := FALSE,

raise_exc_in IN BOOLEAN := FALSE

);

PROCEDURE utAssert.isnull (

msg_in IN VARCHAR2,

check_this_in IN BOOLEAN,

null_ok_in IN BOOLEAN := FALSE,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

Use these assertions when you simply want to check if a scalar expression

(string, date, number and Boolean are supported) is NULL or NOT NULL, as

in:

</p>

<pre>

BEGIN

...

utAssert.isNULL (

'Should be nothing left',

TRANSLATE (digits_in_string, 'A1234567890', 'A')

);

</pre>

<h2> <a name="utassert.eq"></a>Check Equality of Scalar Values</h2>

<p>

If you need to compare two dates or two strings or two numbers or two Booleans,

use the utAssert.eq assertion program.

</p>

<p>Here is the header for the scalar equality check assertion:</p>

<pre>

PROCEDURE utAssert.eq (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2|BOOLEAN|DATE|NUMBER,

against_this_in IN VARCHAR2|BOOLEAN|DATE|NUMBER,

null_ok_in IN BOOLEAN := FALSE,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

If the two values are equal, your code gets a green light. Otherwise, utAssert

writes the test results to the utResult package, resulting in a red light

for the test. If NULL values are considered value for this test, pass TRUE

for null_ok_in. If you want the assertion to raise an exception on failure

and stop the test from proceeding, pass TRUE for raise_exc_in. Here is an

example of using the utAssert.eq program:

</p>

<pre>

PROCEDURE ut_emp_dept_lookuprowcount

l_rowcount1 PLS_INTEGER;

l_rowcount2 PLS_INTEGER;

BEGIN

-- Run baseline code.

SELECT COUNT (*)

INTO l_rowcount1

FROM employee

WHERE department_id = 30;

-- Compare to program call:

l_rowcount2 := te_employee.emp_dept_lookuprowcount (30);

-- Test results

utassert.eq (

'Successful EMP_DEPT_LOOKUPROWCOUNT',

l_rowcount2,

l_rowcount1

);

END;

</pre>

<h2> <a name="utassert.eqtable"></a>Check Equality of DatabaseTables</h2>

<p>

If your test performs DML operations (update, insert or delete), you will

need to check your results in a database table. You could do this by querying

the results into local variables and then calling utAssert.eq to check those

values against your expected data. That can be a very laborious process,

so utAssert offers the eqtable and equerry assertion routines to streamline

the process. Both these procedures use the MINUS SQL operator to essentially

"subtract" the contents of one table (query) from the other. If anything

is left, then the two tables (queries) are not the same and the test is given

a red light. As you can probably see, the structure of the two tables (queries)

must be identical for this assertion to work properly. The utAssert.eqtable

allows you to compare the contents of your data table (changed by your code)

against another table, which you can preset with the data you expect to see

after the test. Here is the header for eqtable:

</p>

<pre>

PROCEDURE utAssert.eqtable (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2,

against_this_in IN VARCHAR2,

check_where_in IN VARCHAR2 := NULL,

against_where_in IN VARCHAR2 := NULL,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

where check_this_in and against_this_in are the names of tables or views.

You can supply an optional WHERE clause to restrict the rows you wish to

compare. Here is an example that calls eqTable twice, to test two different

conditions.

</p>

<pre>

PROCEDURE ut_del1

fdbk PLS_INTEGER;

BEGIN

/* Delete that finds now rows. */

EXECUTE IMMEDIATE '

DELETE FROM ut_DEL1

WHERE employee_id = -1

te_employee.del (-1, rowcount_out => fdbk);

-- Test results

utassert.eqtable ('Delete rows', 'EMPLOYEE', 'ut_DEL1');

/* Successful delete */

EXECUTE IMMEDIATE '

DELETE FROM ut_DEL1

WHERE employee_id between 7800 and 7899

FOR rec IN (SELECT *

FROM employee

WHERE employee_id BETWEEN 7800 AND 7899)

LOOP

te_employee.del (

rec.employee_id,

rowcount_out => fdbk

);

END LOOP;

-- Test results

utassert.eqtable ('Delete rows', 'EMPLOYEE', 'ut_DEL1');

ROLLBACK;

EXCEPTION

WHEN OTHERS

THEN

utassert.this (

'DEL1 exception ' || SQLERRM,

SQLCODE = 0

);

END;

</pre>

<h2> <a name="utassert.eqtabcount"></a>Check Equality of Table Counts</h2>

<p>

If your tests simply produce the right number of rows in a table but not

a fixed set of values, you will not be able to use <a href="#utassert.eqtable">

utAssert.eqtable</a> above. However, utAssert.eqtabcount allows you to simply

test that the numbers of rows are equal. The declaration of the procedure

is as follows:

</p>

<pre>

PROCEDURE utAssert.eqtabcount (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2,

against_this_in IN VARCHAR2,

check_where_in IN VARCHAR2 := NULL,

against_where_in IN VARCHAR2 := NULL,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

where check_this_in and against_this_in are the names of tables or views.

As in utAssert.eqtable, you can supply an optional WHERE clause to restrict

the rows you wish to compare. The following test will compare the number

of rows in the CD_COLLECTION and UT_TEST_5_1 tables where the given condition

holds:

</p>

<pre>

utassert.eqtabcount('Test 5.1: Insert new rows',

'CD_COLLECTION',

'UT_TEST_5_1',

'ARTIST = ''The Fall''',

'ARTIST = ''The Fall''');

</pre>

<h2> <a name="eqquery"></a>Asserting Query Equality</h2>

<p>

The utAssert.eqquery allows you to compare the data returned by two queries

(strings that are contained in the check_this_in and against_this_in parameters).

In this case, you specify the full SELECT statements for each query as the

parameters. By using equery, you may be able to avoid constructing a separate

table with preset data.

</p>

<pre>

PROCEDURE utAssert.eqquery (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2,

against_this_in IN VARCHAR2,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

If you want the assertion to raise an exception on failure and stop the

test from proceeding, pass TRUE for raise_exc_in. Here is an example of

using eqQuery:

</p>

<pre>

PROCEDURE ut_upd1

BEGIN

/* Update 3 columns by ID */

EXECUTE IMMEDIATE '

UPDATE ut_UPD1 SET

FIRST_NAME = ''SILLY'',

HIRE_DATE = trunc (SYSDATE+100),

COMMISSION = 5000

WHERE

EMPLOYEE_ID = 7600

te_employee.upd (

7600,

first_name_in => 'SILLY',

commission_in => 5000,

hire_date_in => TRUNC (SYSDATE + 100),

rowcount_out => fdbk

);

-- Test results (audit fields are different so do a query)

utassert.eqquery (

'Update three columns',

'select first_name, commission, hire_date from EMPLOYEE',

'select first_name, commission, hire_date from ut_upd1'

);

ROLLBACK;

END;

</pre>

<h2> <a name="eqqueryvalue"></a>Check Query Equality against a Single Value</h2>

<p>

Often we will wish to test the result of a query against a single value rather

than another query as in <a href="#eqquery">utAssert.eqquery</a> above.

It is possible to get around this problem by using a trivial query of the

form:

</p>

<pre>

SELECT <i>fixed_value</i>

FROM DUAL;

</pre>

<p>

Unfortunately, if the query returns multiple values or the wrong value we

will only be told that the test has failed with no details. This is where

utAssert.eqqueryvalue comes to the rescue. The procedure is declared as

follows:

</p>

<pre>

PROCEDURE utAssert.eqqueryvalue (

msg_in IN VARCHAR2,

check_query_in IN VARCHAR2,

against_value_in IN VARCHAR2|NUMBER|DATE,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

Where check_query_in is the query in question and against_value_in is the

value to check it against. If the query returns more than one value, the

resulting error message will tell you this. Similarly, if the query returns

the wrong value, the message will state the expected and obtained values.

The following call compares the maximum value found in a table against a

given number value:

</p>

<pre>

utAssert.eqqueryvalue('Maximum value test',

'SELECT MAX(MEMORY)

FROM COMPUTERS

WHERE OS IN (''Linux'', ''Unix'')',

256);

</pre>

<p>

Obviously this should only return a single value, but if it returns something

other than 256, we'll know about it.

</p>

<h2> <a name="utassert.eqfile"></a>Check Equality of Files</h2>

<p>

Many programs generate output to operating system files; alternatively,

you might write data to a file simply to test results. Use the eqfile assertion

for either of these scenarios. This procedure uses PL/SQL's UTL_FILE package

to compare the contents of two different files. Note: If you have not used

UTL_FILE in the past, you must <a href="admin.html#UTL_FILE">configure</a>

it before it can be used -- by utPLSQL or by your own code. UTL_FILE must

be allowed accss to either or both of the directories you specify (this involves

setting the utl_file_dir database parameter).

</p>

<pre>

PROCEDURE utAssert.eqfile (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2,

check_this_dir_in IN VARCHAR2,

against_this_in IN VARCHAR2,

against_this_dir_in IN VARCHAR2 := NULL,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

If you want the assertion to raise an exception on failure and stop the

test from proceeding, pass TRUE for raise_exc_in. You must specify the directory

containing the "check this" file; if you do not specify a directory for the

"against this" file, the "check this" directory will be used. Here is an

example of using eqFile (see ut_DEPARTMENT2file.pkg in the Examples directory

for the full implementation):

</p>

<pre>

PROCEDURE ut_DEPARTMENT2FILE IS

BEGIN

DEPARTMENT2FILE (

LOC => 'c:\temp',

FILE => 'department.dat',

DELIM => '***'

);

utAssert.eqfile (

'Test of DEPARTMENT2FILE',

'department.dat',

'c:\temp',

'department.tst',

'c:\temp'

);

END ut_DEPARTMENT2FILE;

</pre>

<h2> <a name="utassert.eqpipe"></a>Check Equality of Database Pipes</h2>

<p>

Database pipes offer a handy mechanism for passing data between different

sessions connected to the RDBMS. It is important to know that pipes are being

filled properly; use the eqpipe to check this condition. With the eqpipe

procedure, you compare the contents of two different pipes.

</p>

<pre>

PROCEDURE utAssert.eqpipe (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2,

against_this_in IN VARCHAR2,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>

If you want the assertion to raise an exception on failure and stop the

test from proceeding, pass TRUE for raise_exc_in. To check the contents

of a pipe based on the execution of code, you will need to populate a pipe

against which to test equality. The employee_pipe.pkg file in the Examples

directory contains a demonstration of the kind of code you might write to

do this. This package contains all of the unit test code within the same

package. Here is my unit test program, which relies on the utAssert.eqpipe

program:

</p>

<pre>

PROCEDURE ut_fillpipe IS

stat PLS_INTEGER;

BEGIN

emptypipe ('emps');

emptypipe ('emps2');

fillpipe ('emps');

/* Direct filling of pipe. */

FOR rec IN (SELECT *

FROM employee)

LOOP

DBMS_PIPE.RESET_BUFFER;

DBMS_PIPE.PACK_MESSAGE (rec.EMPLOYEE_ID);

DBMS_PIPE.PACK_MESSAGE (rec.LAST_NAME);

DBMS_PIPE.PACK_MESSAGE (rec.FIRST_NAME);

DBMS_PIPE.PACK_MESSAGE (rec.MIDDLE_INITIAL);

DBMS_PIPE.PACK_MESSAGE (rec.JOB_ID);

DBMS_PIPE.PACK_MESSAGE (rec.MANAGER_ID);

DBMS_PIPE.PACK_MESSAGE (rec.HIRE_DATE);

DBMS_PIPE.PACK_MESSAGE (rec.SALARY);

DBMS_PIPE.PACK_MESSAGE (rec.COMMISSION);

DBMS_PIPE.PACK_MESSAGE (rec.DEPARTMENT_ID);

DBMS_PIPE.PACK_MESSAGE (rec.CHANGED_BY);

DBMS_PIPE.PACK_MESSAGE (rec.CHANGED_ON);

stat := DBMS_PIPE.SEND_MESSAGE ('emps2', 0);

END LOOP;

<b> /* Compare the two */

utassert.eqpipe (

'Two employee pipes', 'emps', 'emps2');

</b>

END ut_fillpipe;

</pre>

<p>

Since I have stored my unit test logic with my source code package, I would

run my test as follows:

</p>

<pre>

SQL> exec utplsql.test ('employee_pipe', samepackage_in=>TRUE)

FAILURE: "employee_pipe"

fillpipe: Pipes equal? Compared "emps" against "emps2"

</pre>

<h2> <a name="utassert.eqcoll"></a>Check Equality of Collections</h2>

<p>

Collections are as close as you come to arrays in PL/SQL. They are very

useful for managing lists of information, but can be difficult to debug and

maintain. With the eqcoll and eqcollAPI procedures, you can compare the

contents of two different arrays. Use the eqColl procedure when you want

to compare two collections that are defined in the specification of a package.

Use the eqCollAPI procedure when you want to compare two collections that

are defined in the body of a package, with programs defined in the specification

(an API) to access and manipulate the collections. The collection equality

check headers are:

</p>

<pre>

/* Direct access to collections */

PROCEDURE utAssert.eqcoll (

msg_in IN VARCHAR2,

check_this_in IN VARCHAR2, /* pkg1.coll */

against_this_in IN VARCHAR2, /* pkg2.coll */

eqfunc_in IN VARCHAR2 := NULL,

check_startrow_in IN PLS_INTEGER := NULL,

check_endrow_in IN PLS_INTEGER := NULL,

against_startrow_in IN PLS_INTEGER := NULL,

against_endrow_in IN PLS_INTEGER := NULL,

match_rownum_in IN BOOLEAN := FALSE,

null_ok_in IN BOOLEAN := TRUE,

raise_exc_in IN BOOLEAN := FALSE

);

/* API based access to collections */

PROCEDURE utAssert.eqcollapi (

msg_in IN VARCHAR2,

check_this_pkg_in IN VARCHAR2,

against_this_pkg_in IN VARCHAR2,

eqfunc_in IN VARCHAR2 := NULL,

countfunc_in IN VARCHAR2 := 'COUNT',

firstrowfunc_in IN VARCHAR2 := 'FIRST',

lastrowfunc_in IN VARCHAR2 := 'LAST',

nextrowfunc_in IN VARCHAR2 := 'NEXT',

getvalfunc_in IN VARCHAR2 := 'NTHVAL',

check_startrow_in IN PLS_INTEGER := NULL,

check_endrow_in IN PLS_INTEGER := NULL,

against_startrow_in IN PLS_INTEGER := NULL,

against_endrow_in IN PLS_INTEGER := NULL,

match_rownum_in IN BOOLEAN := FALSE,

null_ok_in IN BOOLEAN := TRUE,

raise_exc_in IN BOOLEAN := FALSE

);

</pre>

<p>where the eqcoll-specific parameters are as follows:</p>

<tr>

<td><b>Parameter</b></td>

<td><b>Description</b></td>

</tr>

<tr>

<td valign="top"> The message to be displayed if the test failes </td>

</tr>

<tr>

<td valign="top"> check_this_in </td>

The name of the collection to be checked.

Format: package.collection. In other words, the collection must be defined

in a package specification. Use eqCollAPI (and check_this_pkg_in) if you

want to hide the declaration of your collection in your package body (recommended).

</td>

</tr>

<tr>

<td valign="top"> against_this_in </td>

The name of the collection to be checked

against. Format: package.collection. In other words, the collection must

be defined in a package specification. Use eqCollAPI (and check_this_pkg_in)

if you want to hide the declaration of your collection in your package body

(recommended).

</td>

</tr>

</table>

<p>and the eqcollAPI-specific parameters are as follows: </p>

<tr>

<td><b>Parameter</b></td>

<td><b>Description</b></td>

</tr>

<tr>

<td valign="top"> The message to be displayed if the test failes</td>

</tr>

<tr>

<td valign="top"> check_this_pkg_in </td>

The name of the package that contains the

collection to be checked.

</td>

</tr>

<tr>

<td valign="top"> against_this_pkg_in </td>

The name of the package that contains the

collection to be checked against.

</td>

</tr>

<tr>

<td valign="top"> countfunc_in </td>

The name of the function in the package that

returns the number of rows defined in the collection.

</td>

</tr>

<tr>

<td valign="top"> firstrowfunc_in </td>

The name of the function in the package that

returns the first defined row in the collection.

</td>

</tr>

<tr>

<td valign="top"> lastrowfunc_in </td>

The name of the function in the package that

returns the last defined row in the collection.

</td>

</tr>

<tr>

<td valign="top"> nextrowfunc_in </td>

The name of the function in the package that

returns the next defined row in the collection from the specified row.

</td>

</tr>

<tr>

<td valign="top"> getvalfunc_in </td>

The name of the function in the package that

returns the contents of the specified row.

</td>

</tr>

</table>

<p>The parameters common to both eqColl and eqCollAPI are as follows </p>

<tr>

<td><b>Parameter</b></td>

<td><b>Description</b></td>

</tr>

<tr>

<td valign="top"> eqfunc_in </td>

The function used to determine if the contents

of each row of the two collections are the same. If you pass NULL for this

argument, then a standard equality check will be used. This is fine for scalar

values, but will not work, for example, with tables of records.

</td>

</tr>

<tr>

<td valign="top"> check_startrow_in </td>

The starting row in the check collection

for comparison. If NULL, then first row is used.

</td>

</tr>

<tr>

<td valign="top"> check_endrow_in </td>

The ending row in the check collection for

comparison. If NULL, then last row is used.

</td>

</tr>

<tr>

<td valign="top"> against_startrow_in </td>

The starting row in the against collection

for comparison. If NULL, then first row is used.

</td>

</tr>

<tr>

<td valign="top"> against_endrow_in </td>

The ending row in the against collection

for comparison. If NULL, then last row is used.

</td>

</tr>

<tr>

<td valign="top"> match_rownum_in </td>

Pass TRUE if you want to make sure that the

same row numbers are used in each collection. If FALSE, then the row numbers

can be different, but the contents of each corresponding row must be the same.

</td>

</tr>

<tr>

Pass TRUE if the assertion routine should

consider two NULL collections to be equal.

</td>

</tr>

<tr>

<td valign="top"> raise_exc_in </td>

If you want the assertion to raise an exception

on failure and stop the test from proceeding, pass TRUE for raise_exc_in.

</td>

</tr>

</table>

<p>

Here is an example of a script that uses utAssert.eqColl (taken from filepath1.pkg

in the Examples directory):

</p>

<pre>

PROCEDURE ut_setpath

BEGIN

/* Populate base collection */

ut_dirs.DELETE;

ut_dirs.EXTEND(2);

ut_dirs(1) := 'c:\temp';

ut_dirs(2) := 'e:\demo';

/* Call setpath to do the work */

setpath ('c:\temp;e:\demo');

utAssert.eqColl (

'Valid double entry',

'fileio.dirs',

'fileio.ut_dirs'

);

END;

</pre>

<h2> <a name="throws"></a>Checking a Procedure or Function throws an exception</h2>

<p>

Sometimes we design a procedure or function to throw an exception under certain

circumstances. This is something we'd like to be able to test for. Obviously

this is not particularly easy due to the way exceptions propagate through

the call stack. If we simply call the procedure in our test code, the exception

will have no chance of being caught within the utAssert package! Therefore,

we need to pass the tested call in to the package as a string. The procedure

utAssert.throws allows us to do this:

</p>

<pre>

PROCEDURE throws (

msg_in VARCHAR2,

check_call_in IN VARCHAR2,

against_exc_in IN VARCHAR2|NUMBER

);

</pre>

<p>

Where check_call_in is the call to be made, complete with parameters and

terminating semicolon. The argument against_exc_in is the exception we expect

to be thrown. This can be specified either as a named exception, or a SQLCODE

value.

</p>

<p>The following example shows both usages:</p>

<pre>

/* Test the Except Function */

PROCEDURE ut_except

BEGIN

/* Call the procedure with a negative number */

/* We expect a NO_DATA_FOUND exception */

utAssert.throws('Negative Number',

'Except(-1);',

'NO_DATA_FOUND'

);

/* Call the procedure with zero and a string */

/* over 2 in length - We expect a SQLCODE of -1 */

utAssert.throws('Zero and String',

'Except(0, ''Hello'');',

-1

);

END;

</pre>

<p>

Note how we have to quote the string parameters to the call and terminate

the string with a semicolon.

</p>

<h2><a name="previous"></a>Check if the Previous Assertion Passed or Failed</h2>

<p>

Sometimes, a procedure may have a large number of effects that need to be

tested. For example, it might insert and update data in a series of

tables. To test all of these changes, it will be necessary to make

a series of calls to utAssert. This can have the effect that if the

procedure is not behaving as expected, then the user is presented with a

screenful of errors. To avoid this and just present them with a single

error, the functions previous_passed and previous_failed can be used.

These return a BOOLEAN argument giving the success or failure of the previously

called assertion.

</p>

<p>The following example gives a demonstration:</p>

<pre>

/* Test the BookTrips Procedure */

View remainder of file in raw view

utPLSQL/documentation/src/utassert.html at master · pascal8/utPLSQL · GitHub

Expand file tree

Breadcrumbs

utassert.html

Latest commit

History

Breadcrumbs

utassert.html

File metadata and controls