Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:

8-33.

Here is the script that can be used to create the tables and types for the purchase order system:


/* Filename on web page: vaexprt.sql */
/*
   Drop existing objects
*/
DROP TABLE orders;
DROP TYPE order_detail_nt;
DROP TYPE order_detail_t;
DROP TYPE detail_t;
DROP TYPE tax_code_t;
DROP TYPE tax_code_detail_t;

/*
   Create a tax code object and table
*/
CREATE TYPE tax_code_detail_t AS OBJECT (
   tax_code VARCHAR2(10));
/

CREATE TYPE tax_code_t AS VARRAY(2) OF tax_code_detail_t;
/

/*
   Create a detail type and table
*/
CREATE TYPE detail_t AS OBJECT
   (detail_noNUMBER,
    detail_descVARCHAR2(30),
    detail_price      NUMBER)
/

CREATE TYPE order_detail_t AS VARRAY(10) OF detail_t;
/

CREATE TYPE order_detail_nt AS TABLE OF detail_t;
/

/*
   Create an orders table with a variable array column for
   details and tax codes
*/
CREATE TABLE orders
   (order_noNUMBER NOT NULL PRIMARY KEY USING INDEX,
    order_descVARCHAR2(30),
    order_detail        order_detail_t,
    tax_codes           tax_code_t)
/

Q:

8-34.

The variable array is used as the datatype of the column in the orders table while the nested table type is created in anticipation of future DML. It will be used in conjunction with the CAST function.

Q:

8-35.

For this solution, see the va_manage_orders.sql file on the book’s web page.

Q:

8-36.

For this solution, see the va_manage_orders.sql ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page