utPLSQL/documentation/src/fourstep.html at version2 · utPLSQL/utPLSQL

History

629 lines (532 loc) · 18.5 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

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

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

<head>

</head>

<body>

<h1>The Four Step Program to Using utPLSQL</h1>

<h3><a href="fourstep.html">Step 1. Install utPLSQL.</a></h3>

<h3><a href="#step2">Step 2. Choose a program to test and identify the test cases.</a></h3>

<h3><a href="#step3">Step 3. Build a test package.</a></h3>

<h3><a href="#schemas">A note on which schemas to use</a></h3>

<h3>

<a href="#Fromhere">Where to go from here</a>

</h3>

<h2><a name="step1"></a>Step 1. Install (and Upgrade) utPLSQL.</h2>

Note: if you have already installed a previous version of

utPLSQL, you will use these same steps to perform your install. The

installation procedure does not remove any objects, such as tables,

prior to installation. If you wish to install a fresh copy of utPLSQL, and not upgrade

over the existing installation, please follow the steps below for removing

utPLSQL.

Connect via SQL*Plus to the session that will own the

utPLSQL components. If you do not already have a schema defined, then you must

create it. The utPLSQL schema must have the authority to:

<ul>

<li>Create a session.</li>

<li>Create tables, views, packages and sequences.</li>

</ul>

If you like, you can install utPLSQL into the SYSTEM schema, which will avoid the need to create

a new user. However, you may prefer to keep everything in a separate place.

The following is an example script submitted by Bill Pribyl, which creates a user "UTP" with sufficient privileges

to install utPLSQL. Obviously it is only an example and will need to be changed for your environment:

<pre>

connect system/manager

create user utp identified by utp default tablespace

users temporary tablespace temp;

grant create session, create table, create procedure,

create sequence, create view, create public synonym,

drop public synonym to utp;

alter user utp quota unlimited on users;

</pre>

Note If the schema in question does not have the ability to create

and drop public synonyms or execute privilege on DBMS_PIPE, you may get

error messages when installing. However, utPLSQL will still function

correctly.

Once you have connected to the schema, run the ut_i_do.sql

file with the parameter "install" to install all utPLSQL objects. You should ensure that

the working directory of your SQL*Plus

session is the directory holding the utPLSQL files, then issue this as follows:

<pre>

SQL> @ut_i_do install

</pre>

This file will create all tables, packages and other objects needed.

Note that the installation script

creates some files dynamically using the SPOOL command. For this reason, it is

necessary that you have write permission in the directory.

To check the installation of utPLSQL, examine the ut_i_install.log file.

<h3><a name="Upgrade"></a>Removing utPLSQL</h3>

To de-install the product, run the ut_i_do.sql

script again, but with the parameter "uninstall", as in:

<pre>

SQL> @ut_i_do uninstall

</pre>

<h2><a name="step2"></a>Step 2. Choose a program to test and identify the test cases.</h2>

You may want to test a single stand-alone procedure or

function, or a set of programs in a package. Pick the program and then come up

with the set of different cases you want to test. This data will determine what

kind of and how many tests you run for your program.

Suppose, for example, that I have created a stand alone function called

betwnStr (a variation on SUBSTR that returns a sub-string based on a starting

and ending location) that is stored in betwnstr.sf <a href="#footnote">(1)</a>:

<pre>

CREATE OR REPLACE FUNCTION betwnStr (

string_in IN VARCHAR2,

start_in IN INTEGER,

end_in IN INTEGER

)

RETURN VARCHAR2

BEGIN

RETURN (

SUBSTR (

string_in,

start_in,

end_in - start_in + 1

)

);

END;

</pre>

To test this function, I will want to pass in a variety of

inputs, as shown in this table:

<tr>

Start

</td>

End

</td>

Result

</td>

</tr>

<tr>

NULL

</td>

NOT NULL

</td>

NULL

</td>

</tr>

<tr>

NOT NULL

</td>

NULL

</td>

NULL

</td>

</tr>

<tr>

NULL

</td>

NULL

</td>

NULL

</td>

</tr>

<tr>

3 (positive number)

</td>

1 (smaller positive number)

</td>

NULL

</td>

</tr>

<tr>

3 (positive number)

</td>

100 (larger than length of string)

</td>

Remainder of string from 3

</td>

</tr>

</table>

So now I know what I want to test and how I want to test it.

<h2><a name="step3"></a>Step 3. Build a test package.</h2>

utPLSQL offers an easy, automated way to run your tests. To

work automatically, though, you have to follow some rules so that utPLSQL can

find and execute your test code. Here are the rules:

The test code must be placed inside a test package.

The test package specification should

be stored in a file named ut_<program>.pks and the body must be stored in

a file named ut_<program>.pkb (by following this naming convention,

utPLSQL can be set to automatically recompile your test package before each

test).

The test package must contain a <a href="howto.html#Setup">setup procedure</a> called ut_setup and a <a href="howto.html#Teardown">teardown procedure</a> called ut_teardown, neither of

which take any arguments.

The test package should have a separate procedure for each program to be

tested in this package.

Now, you should know that there are a number of bells and

whistles in utPLSQL that allow you to change many default values (such as the

prefixes used for the setup, teardown and test procedures) and behavior of the

utPLSQL packages. While you are "Getting Started", however, we will

rely completely on the defaults and get you up and testing ASAP.

So if I am going to test the stand-alone procedure, betwnstr, my test

package specification, saved in ut_betwnstr.pks<a href="#footnote">(1)</a>,

will look like this:

<pre>

CREATE OR REPLACE PACKAGE ut_betwnstr

PROCEDURE ut_setup;

PROCEDURE ut_teardown;

PROCEDURE ut_betwnstr;

END ut_betwnstr;

</pre>

Now let's build the package body, saved in ut_betwnstr.pkb<a href="#footnote">(1)</a>. In this very simple

case, I don't have to set up any data structures and I do not, therefore, have

to tear anything down. My teardown procedure can be empty (but it must

be present). So I have:

<pre>

CREATE OR REPLACE PACKAGE BODY ut_betwnstr

PROCEDURE ut_setup IS

BEGIN

NULL;

END;

PROCEDURE ut_teardown

BEGIN

NULL;

END;

</pre>

Time to build the unit test procedure. To do this, I need to

go back to my grid of test cases and translate those sets of data inputs and

results into calls to programs in the <a href="utassert.html">utAssert</a>

package.

utAssert offers a number of "assertion routines" that test the

values or expression you pass to them and then record the results in utPLSQL.

You can, with utAssert, test for equality between two strings or files or

tables or collections. You can test to see if an expression evaluates to NULL.

I can use both of these types of assertions (equality and IS NULL) for my test

cases, which I repeat below:

<tr>

Start

</td>

End

</td>

Result

</td>

</tr>

<tr>

NULL

</td>

NOT NULL

</td>

NULL

</td>

</tr>

<tr>

NOT NULL

</td>

NULL

</td>

NULL

</td>

</tr>

<tr>

NULL

</td>

NULL

</td>

NULL

</td>

</tr>

<tr>

3 (positive number)

</td>

1 (smaller positive number)

</td>

NULL

</td>

</tr>

<tr>

3 (positive number)

</td>

100 (larger than length of string)

</td>

Remainder of string

</td>

</tr>

</table>

Here's how it works: for each test case, I provide a string description of

the case, then the expression I want to evaluate. Let's start with

"typical valid usage". I pass a string "abcdefg", a start

location of 3 and end location of 5, and betwnstr should return

"cde". I express that in my unit test procedure as follows:

<pre>

PROCEDURE ut_betwnstr IS

BEGIN

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

'Typical valid usage',

BETWNSTR(

STRING_IN => 'abcdefg',

START_IN => 3,

END_IN => 5

'cde'

);

</pre>

Notice that I call utAssert.eq because I want to compare the

value returned by betwnstr with the string "cde". They should

be equal.

I can now write another call to a utAssert program for each of my cases. In

this very next example, I call utAssert.isnull, because I am expecting betwnstr

to return a NULL value.

<pre>

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

'NULL start',

BETWNSTR(

STRING_IN => 'abcdefg',

START_IN => NULL,

END_IN => 5

)

);

utAssert.isnull (

'NULL end',

BETWNSTR(

STRING_IN => 'abcdefg',

START_IN => 2,

END_IN => NULL

)

);

utAssert.isnull (

'End smaller than start',

BETWNSTR(

STRING_IN => 'abcdefg',

START_IN => 5,

END_IN => 2

)

);

utAssert.eq (

'End larger than string length',

BETWNSTR(

STRING_IN => 'abcdefg',

START_IN => 3,

END_IN => 200

'cdefg'

);

END ut_BETWNSTR;

END ut_betwnstr;

</pre>

I have now created my unit test program for the betwnstr

function. I will compile both these files to make sure there are no compile

errors:

<pre>

SQL> @ut_betwnstr.pks

Package created.

SQL> @ut_betwnstr.pkb

Package body created.

</pre>

Note: when you run your test, utPLSQL will by default

attempt to recompile your test package to ensure that the latest changes are

incorporated into the test. It is still worth doing an initial compile to make

sure you built your test properly. You will also need to make sure that <a href="admin.html#UTL_FILE">UTL_FILE</a> is installed and configured so that your

test package files can be read and compiled by utPLSQL.

So with the test package in place and compiling, now let's

see how we go about running the test.

You've built your code, you've built your test package,

you've compiled that test package. Now it's time to run the test. Start up

SQL*Plus and connect to the schema owning the code you want to test.

Then run your test package within the utPLSQL testing framework by calling <a href="utplsql.html#utplsql.test">utPLSQL.test</a>:

<pre>

SQL> exec utplsql.test ('betwnstr', recompile_in => FALSE)

</pre>

That second parameter in the call to utplsql.test,

"recompile_in => FALSE", tells utPLSQL that you have already

compiled your test package. You can also have utPLSQL <a href="utconfig.html#Autocompile">automatically recompile</a> your test package

each time you run a test.

If the test does not find any errors (which means that the assertion

programs did not detect any conflicts), you will see this output:

<pre>

SQL> exec utplsql.test ('betwnstr', recompile_in => FALSE)

SUCCESS: "betwnstr"

</pre>

If the test detected a failure, you will see output along these lines:

<pre>

SQL> exec utplsql.test ('betwnstr', recompile_in => FALSE)

FAILURE: "betwnstr"

BETWNSTR: IS NULL: NULL start

BETWNSTR: End larger than string length; expected "cdeg", got "cdefg"

</pre>

As you can see, utPLSQL tells you the description of the

test case that failed, and also shows you as much as it can about what caused

the failure.

You have now successfully installed utPLSQL, written a test package and run

your test!

<h3><a name="Recompile"></a>Automatic Recompilation of Test Package</h3>

utPLSQL will, by default, attempt to recompile your test package code

(which must be put in two files <name>.pks for the package specification

and <name>.pkb for the package body). This of course assumes that the files

are situated on the same machine as your database. If this is not the case, you can

turn off this functionality by calling <a href="utconfig.html#autocompile">utConfig.autocompile</a>

as follows:

<pre>

utConfig.autocompile(false);

</pre>

If you do wish to use this functionality, utPLSQL needs

the UTL_FILE package provided by Oracle to read the source code files and then

compile the code found in those files. Before using UTL_FILE you must <a href="admin.html#UTL_FILE">configure</a> it for use from within PL/SQL.

Once you have confirmed that UTL_FILE works in your database instance, you

must tell utPLSQL where the test package is located by calling utPLSQL.setdir.

If you do not do this, then utPLSQL will not be able to recompile your test

package before each run, and instead will display an error message.

Call the <a href="utconfig.html#Setdir">utConfig.setdir</a> program to tell

utPLSQL the location of your source code. Suppose that I stored all my code in e:\utplsql\testall. Then I would make this

call in SQL*Plus:

<pre>

SQL> exec utplsql.setdir ('e:\utplsql\testall')

</pre>

<h2><a name="schemas"></a>A note on which schemas to use</h2>

In <a href="#step1">step 1</a>, above, we described which user should own the objects which make up the utPLSQL framework.

However, there has often been confusion about which schema should contain the test packages and which schema to connect as

when running the tests. There are many ways to do it, but the simplest is as follows:

<ul>

<li>It doesn't matter which schema owns utPLSQL itself, so long as other users have access to it.</li>

<li>The test packages should go in the same schema as the code that is being tested.</li>

<li>You should connect as the user who owns the test packages (and hence the tested code) when running the tests</li>

</ul>

<h2><a name="Fromhere"></a>Where to go from here</h2>

If you proceeded through all four steps, you should now have

used utPLSQL successfully to test a very simple function (betwnstr) or your own

functionality. This will undoubtedly leave you very excited about using utPLSQL

to handle much more complex code and elaborate testing requirements.

To find out more about the different features and

functionality available in utPLSQL, visit the <a href="userguide.html">User

Guide</a>.

To read through a more thorough presentation of how to build

test packages in utPLSQL, visit <a href="buildpack.html">How to

Build Test Packages</a>.

To see a wide array of examples of building test cases and

different kinds of test packages, visit the <a href="examples.html">Examples</a>

document.

<hr />

<h3><a name="footnote">Footnotes</a></h3>

1. This file is to be found in the Examples directory of the utPLSQL distribution.

</body>

</html>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

fourstep.html

Latest commit

History

fourstep.html

File metadata and controls