Create_ObjectType_EmpT1.htm
Objectives:
  • Creating simple object
  • difference between Run-Script, Run-Statement
  • Format the output / width of a column: using column format command in SQL * Plus  terminal application.
  • Intro : Importance of Packages
Script

create or replace
TYPE empOBJ AS OBJECT
(
fname VARCHAR(20),
lname VARCHAR(30),
address VARCHAR(100)
);

 

CREATE TABLE empT1
(
eid NUMBER(2) NOT NULL,
info scott.empOBJ
);
Insert some rows

INSERT INTO empT1
VALUES (10,empOBJ( 'Anita','Das', 'Address 1'));
INSERT INTO empT1
VALUES (11, empOBJ('Frank','Delon', 'Address 2'));
INSERT INTO empT1
VALUES (12, empOBJ('Juno','Rastogi', 'Address 3'));
INSERT INTO empT1
VALUES (13, empOBJ('Sunil','Pandey', 'Address 4'));
COMMIT;
Execute a query from SQL Developer, and choose Run-Script option ( NOT RUN STATEMENT)

If you choose to run as a statement, you may see a screen similar to the one show below.
Oracle SQL*Plus, runs as Query as a script than statement.

Reformat the column "info"
Viewing with PHP query application

SELECT e.eid eid, e.info.fname fname, e.info.lname lname,e.info.address address FROM empt1 e