DB Development

//1st of all create a directory to store ur external table

CREATE DIRECORY EMP_DIR AS '/FLAT_FILES' ;

// now write the following line of code to create an

external table at that directory

CREATE TABLE OLDEMP(ENO NUMBER,ENAME CHAR(20),DOB DATE,)

ORGANIZATION EXTERNAL

( TYPE ORACLE_LOADER

DEFAULT DIRECTORY EMP_DIR

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

BADFILE 'BAD_EMP')

LOGFILE 'LOG_EMP'

FIELDS TERMINATED BY ','

(ENO CHAR , ENAME CHAR DOB CHAR DATE_FORMAT

DATE MASK "DD-MON-YYYY"

)

)

LOCATION ('EMP1.TXT')

)

PARALLEL 5

REJECT LIMIT 200;

On its face, the relational database management system would appear to be a very poor tool for representing and manipulating trees. This chapter is designed to accomplish the following things:

show you that a row in an SQL database can be thought of as an object

show you that a pointer from one object to another can be represented by storing an integer key in a regular database column

demonstrate the Oracle tree extensions (CONNECT BY … PRIOR)

show you how to work around the limitations of CONNECT BY with PL/SQL

The canonical example of trees in Oracle is the org chart.

create table corporate_slaves (

slave_id integer primary key,

supervisor_id references corporate_slaves,

name varchar(100)

);

insert into corporate_slaves values (1, NULL, ‘Big Boss Man’);

insert into corporate_slaves values (2, 1, ‘VP Marketing’);

insert into corporate_slaves values (3, 1, ‘VP Sales’);

insert into corporate_slaves values (4, 3, ‘Joe Sales Guy’);

insert into corporate_slaves values (5, 4, ‘Bill Sales Assistant’);

insert into corporate_slaves values (6, 1, ‘VP Engineering’);

insert into corporate_slaves values (7, 6, ‘Jane Nerd’);

insert into corporate_slaves values (8, 6, ‘Bob Nerd’);