SQL> select table_name from user_constraints a where r_constraint_name in (select constraint_name from user_constraints b where b.table_name='ITEMS');
TABLE_NAME
------------------------------
SALES
SQL> drop table sales;
Table dropped.
SQL> drop table items;
Table dropped.
SQL> create table items (itemid number(4) primary key, item_name varchar2(20) not null,cost number(4,2),qty number );
Table created.
SQL> create table customer (cid number(3) primary key,cust_name varchar2(20) not null, cust_addr varchar2(25));
Table created.
SQL> create table sales (itemid number(4) constraint fkitems references items(itemid) deferrable initially deferred,cid number(3) constraint fkcust references customer(cid) deferrable initially deferred,qty number(4) constraint qty_chk (check qty>0),t_type char(1) constraint tp_chk check (t_type in ('P','S')),cost number(4,2) not null);
create table sales (itemid number(4) constraint fkitems references items(itemid) deferrable initially deferred,cid number(3) constraint fkcust references customer(cid) deferrable initially deferred,qty number(4) constraint qty_chk (check qty>0),t_type char(1) constraint tp_chk check (t_type in ('P','S')),cost number(4,2) not null)
*
ERROR at line 1:
ORA-02253: constraint specification not allowed here
SQL> ed
Wrote file afiedt.buf
1 create table sales (itemid number(4) constraint fkitems references items(itemid) deferrable initially deferred,
2 cid number(3) constraint fkcust references customer(cid) deferrable initially deferred,
3 qty number(4) constraint qty_chk (check qty>0),
4* t_type char(1) constraint tp_chk check (t_type in ('P','S')),cost number(4,2) not null)
SQL> /
qty number(4) constraint qty_chk (check qty>0),
*
ERROR at line 3:
ORA-02253: constraint specification not allowed here
SQL> ed
Wrote file afiedt.buf
1 create table sales (itemid number(4) constraint fkitems references items(itemid) deferrable initially deferred,
2 cid number(3) constraint fkcust references customer(cid) deferrable initially deferred,
3 qty number(4) constraint qty_chk check (qty>0),
4* t_type char(1) constraint tp_chk check (t_type in ('P','S')),cost number(4,2) not null)
SQL> /
Table created.
SQL> desc sales;
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEMID NUMBER(4)
CID NUMBER(3)
QTY NUMBER(4)
T_TYPE CHAR(1)
COST NOT NULL NUMBER(4,2)
SQL> desc user_indexes;
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
SQL> select index_name,index_type from user_indexes where table_name='SALES';
no rows selected
SQL> select index_name,index_type from user_indexes where table_name='ITEMS';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
SYS_C005457 NORMAL
SQL> select index_name,index_type,ityp_name from user_indexes where table_name='ITEMS';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ITYP_NAME
------------------------------
SYS_C005457 NORMAL
SQL> select index_name,index_type,degree from user_indexes where table_name='ITEMS';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
DEGREE
----------------------------------------
SYS_C005457 NORMAL
1
SQL> select index_name,index_type,degree,num_rows from user_indexes where table_name='ITEMS';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
DEGREE NUM_ROWS
---------------------------------------- ----------
SYS_C005457 NORMAL
1
SQL> select index_name,index_type,ityp_name from user_indexes where table_name='CUSTOMER';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ITYP_NAME
------------------------------
SYS_C005459 NORMAL
SQL> select index_name,index_type,ityp_name from user_indexes where table_name='SALES';
no rows selected
SQL> select index_name,index_type,ityp_name,uniqueness from user_indexes where table_name='SALES';
no rows selected
SQL> select index_name,index_type,ityp_name,uniqueness from user_indexes where table_name='ITEMS';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ITYP_NAME UNIQUENES
------------------------------ ---------
SYS_C005457 NORMAL
UNIQUE
SQL> desc sales
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEMID NUMBER(4)
CID NUMBER(3)
QTY NUMBER(4)
T_TYPE CHAR(1)
COST NOT NULL NUMBER(4,2)
SQL> create index sales_itemidx on sales(Item_ID);
create index sales_itemidx on sales(Item_ID)
*
ERROR at line 1:
ORA-00904: "ITEM_ID": invalid identifier
SQL> create index sales_itemidx on sales(ItemID);
Index created.
SQL> select index_name,index_type,ityp_name,uniqueness from user_indexes where table_name='SALES';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ITYP_NAME UNIQUENES
------------------------------ ---------
SALES_ITEMIDX NORMAL
NONUNIQUE
SQL> desc customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CID NOT NULL NUMBER(3)
CUST_NAME NOT NULL VARCHAR2(20)
CUST_ADDR VARCHAR2(25)
SQL> create unique index cust_nameidx on customer(cust_name);
Index created.
SQL> select index_name,index_type,ityp_name,uniqueness from user_indexes where table_name='CUSTOMER';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ITYP_NAME UNIQUENES
------------------------------ ---------
SYS_C005459 NORMAL
UNIQUE
CUST_NAMEIDX NORMAL
UNIQUE
SQL> desc sales
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEMID NUMBER(4)
CID NUMBER(3)
QTY NUMBER(4)
T_TYPE CHAR(1)
COST NOT NULL NUMBER(4,2)
SQL> create index qty_costidx on (T_type,qty);
create index qty_costidx on (T_type,qty)
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> create index qty_costidx on sales(T_type,qty);
Index created.
SQL> select index_name,index_type,ityp_name,uniqueness from user_indexes where table_name='SALES';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ITYP_NAME UNIQUENES
------------------------------ ---------
SALES_ITEMIDX NORMAL
NONUNIQUE
QTY_COSTIDX NORMAL
NONUNIQUE
SQL> desc user_indx_cols
ERROR:
ORA-04043: object user_indx_cols does not exist
SQL> desc user_index_cols
ERROR:
ORA-04043: object user_index_cols does not exist
SQL> desc user_col_index
ERROR:
ORA-04043: object user_col_index does not exist
SQL> select tname from tab where tname like '%COLS%';
no rows selected
SQL> conn / as sysdba
Connected.
SQL> select tname from tab where tname like '%COLS%';
TNAME
------------------------------
ALL_NESTED_TABLE_COLS
ALL_SEC_RELEVANT_COLS
ALL_TAB_COLS
ALL_TRIGGER_COLS
ALL_XML_TAB_COLS
ALL_XML_VIEW_COLS
DBA_MVIEW_LOG_FILTER_COLS
DBA_NESTED_TABLE_COLS
DBA_SEC_RELEVANT_COLS
DBA_TAB_COLS
DBA_TRIGGER_COLS
TNAME
------------------------------
DBA_XML_TAB_COLS
DBA_XML_VIEW_COLS
EXU9TAB_UNUSED_COLS
KU$_FIND_SGC_COLS_VIEW
KU$_FIND_SGI_COLS_VIEW
LOGMNR_INTERESTING_COLS
STREAMS$_DEST_OBJ_COLS
USER_NESTED_TABLE_COLS
USER_SEC_RELEVANT_COLS
USER_TAB_COLS
USER_TRIGGER_COLS
TNAME
------------------------------
USER_XML_TAB_COLS
USER_XML_VIEW_COLS
24 rows selected.
SQL> select tname from tab where tname like '%ind%';
TNAME
------------------------------
_utl$_gnp_ind
_utl$_gp_ind_parts
_utl$_lc_ind_subs
_utl$_lnc_ind_parts
SQL> select tname from tab where tname like 'IND%';
TNAME
------------------------------
IND$
INDARRAYTYPE$
INDCOMPART$
INDCOMPARTV$
INDEX_HISTOGRAM
INDEX_STATS
INDOP$
INDPART$
INDPARTV$
INDPART_PARAM$
INDSUBPART$
TNAME
------------------------------
INDSUBPARTV$
INDTYPES$
IND_ONLINE$
IND_STATS$
15 rows selected.
SQL> select tname from tab where tname like '%IND%';
TNAME
------------------------------
ALL_INDEXES
ALL_INDEXTYPES
ALL_INDEXTYPE_ARRAYTYPES
ALL_INDEXTYPE_COMMENTS
ALL_INDEXTYPE_OPERATORS
ALL_IND_COLUMNS
ALL_IND_EXPRESSIONS
ALL_IND_PARTITIONS
ALL_IND_STATISTICS
ALL_IND_SUBPARTITIONS
ALL_JOIN_IND_COLUMNS
TNAME
------------------------------
ALL_OPBINDINGS
ALL_PART_INDEXES
ALL_SCHEDULER_WINDOWS
ALL_SCHEDULER_WINDOW_DETAILS
ALL_SCHEDULER_WINDOW_GROUPS
ALL_SCHEDULER_WINDOW_LOG
ALL_SQLSET_BINDS
ALL_XML_INDEXES
DBA_ADVISOR_FINDINGS
DBA_HIST_SQLBIND
DBA_HIST_SQL_BIND_METADATA
TNAME
------------------------------
DBA_INDEXES
DBA_INDEXTYPES
DBA_INDEXTYPE_ARRAYTYPES
DBA_INDEXTYPE_COMMENTS
DBA_INDEXTYPE_OPERATORS
DBA_IND_COLUMNS
DBA_IND_EXPRESSIONS
DBA_IND_PARTITIONS
DBA_IND_STATISTICS
DBA_IND_SUBPARTITIONS
DBA_JOIN_IND_COLUMNS
TNAME
------------------------------
DBA_OPBINDINGS
DBA_PART_INDEXES
DBA_SCHEDULER_WINDOWS
DBA_SCHEDULER_WINDOW_DETAILS
DBA_SCHEDULER_WINDOW_GROUPS
DBA_SCHEDULER_WINDOW_LOG
DBA_SQLSET_BINDS
DBA_SQLTUNE_BINDS
DBA_XML_INDEXES
EXU10IND_BASE
EXU81IND
TNAME
------------------------------
EXU81INDC
EXU81INDI
EXU81INDIC
EXU81IND_BASE
EXU8IND
EXU8INDC
EXU8INDI
EXU8INDIC
EXU8INDU
EXU9IND
EXU9INDC
TNAME
------------------------------
EXU9INDI
EXU9INDIC
EXU9IND_BASE
GV_$INDEXED_FIXED_COLUMN
GV_$SQL_BIND_CAPTURE
GV_$SQL_BIND_DATA
GV_$SQL_BIND_METADATA
IND$
INDARRAYTYPE$
INDCOMPART$
INDCOMPARTV$
TNAME
------------------------------
INDEX_HISTOGRAM
INDEX_STATS
INDOP$
INDPART$
INDPARTV$
INDPART_PARAM$
INDSUBPART$
INDSUBPARTV$
INDTYPES$
IND_ONLINE$
IND_STATS$
TNAME
------------------------------
KU$_10_1_IND_STATS_VIEW
KU$_10_1_PIND_STATS_VIEW
KU$_10_1_SPIND_STATS_VIEW
KU$_FIND_HIDDEN_CONS_VIEW
KU$_FIND_SGC_COLS_VIEW
KU$_FIND_SGC_VIEW
KU$_FIND_SGI_COLS_VIEW
KU$_INDARRAYTYPE_VIEW
KU$_INDEXOP_VIEW
KU$_INDEXTYPE_VIEW
KU$_INDEX_COL_VIEW
TNAME
------------------------------
KU$_INDEX_VIEW
KU$_IND_CACHE_STATS_VIEW
KU$_IND_COL_VIEW
KU$_IND_COMPART_VIEW
KU$_IND_PARTOBJ_VIEW
KU$_IND_PART_COL_VIEW
KU$_IND_PART_VIEW
KU$_IND_STATS_VIEW
KU$_IND_SUBPART_COL_VIEW
KU$_IND_SUBPART_VIEW
KU$_IND_TS_VIEW
TNAME
------------------------------
KU$_LOBFRAGINDEX_VIEW
KU$_LOBINDEX_VIEW
KU$_OIDINDEX_VIEW
KU$_OPBINDING_VIEW
KU$_PIND_STATS_VIEW
KU$_SPIND_STATS_VIEW
KU$_SUBLOBFRAGINDEX_VIEW
KU$_TTS_IND_VIEW
LOADER_SKIP_UNUSABLE_INDEXES
LOGMNRG_IND$
LOGMNRG_INDCOMPART$
TNAME
------------------------------
LOGMNRG_INDPART$
LOGMNRG_INDSUBPART$
LOGMNRT_IND$
LOGMNRT_INDCOMPART$
LOGMNRT_INDPART$
LOGMNRT_INDSUBPART$
OPBINDING$
SCHEDULER$_WINDOW
SCHEDULER$_WINDOW_DETAILS
SCHEDULER$_WINDOW_GROUP
USER_ADVISOR_FINDINGS
TNAME
------------------------------
USER_INDEXES
USER_INDEXTYPES
USER_INDEXTYPE_ARRAYTYPES
USER_INDEXTYPE_COMMENTS
USER_INDEXTYPE_OPERATORS
USER_IND_COLUMNS
USER_IND_EXPRESSIONS
USER_IND_PARTITIONS
USER_IND_STATISTICS
USER_IND_SUBPARTITIONS
USER_JOIN_IND_COLUMNS
TNAME
------------------------------
USER_OPBINDINGS
USER_PART_INDEXES
USER_SQLSET_BINDS
USER_SQLTUNE_BINDS
USER_XML_INDEXES
UTL_ALL_IND_COMPS
V_$INDEXED_FIXED_COLUMN
V_$SQL_BIND_CAPTURE
V_$SQL_BIND_DATA
V_$SQL_BIND_METADATA
WRH$_SQL_BIND_METADATA
TNAME
------------------------------
WRI$_ADV_FINDINGS
WRI$_ADV_SQLT_BINDS
WRI$_OPTSTAT_IND_HISTORY
WRI$_SQLSET_BINDS
147 rows selected.
SQL> conn scott/lion
Connected.
SQL> desc user_ind_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
COLUMN_POSITION NUMBER
COLUMN_LENGTH NUMBER
CHAR_LENGTH NUMBER
DESCEND VARCHAR2(4)
SQL> select index_name,column_name,column_position from user_ind_columns;
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
PK_DEPT
DEPTNO
1
PK_EMP
EMPNO
1
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
SYS_C005400
AID
1
T1PK
EID
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
1
SYS_C005454
ID
1
BIN$u/R7a2iFSquoTbC0j8wH9g==$0
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
ITEM_ID
1
SYS_C005457
ITEMID
1
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
SYS_C005459
CID
1
SALES_ITEMIDX
ITEMID
1
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
CUST_NAMEIDX
CUST_NAME
1
QTY_COSTIDX
QTY
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
2
QTY_COSTIDX
T_TYPE
1
12 rows selected.
SQL> select index_name,column_name,column_position from user_ind_columns where table_name='SALES';
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
SALES_ITEMIDX
ITEMID
1
QTY_COSTIDX
T_TYPE
1
INDEX_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION
---------------
QTY_COSTIDX
QTY
2
SQL> desc items;
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEMID NOT NULL NUMBER(4)
ITEM_NAME NOT NULL VARCHAR2(20)
COST NUMBER(4,2)
QTY NUMBER
SQL> insert into items values(&id,&iname,&cost,&qty);
Enter value for id: 101
Enter value for iname: Lux
Enter value for cost: 15.50
Enter value for qty: 4
old 1: insert into items values(&id,&iname,&cost,&qty)
new 1: insert into items values(101,Lux,15.50,4)
insert into items values(101,Lux,15.50,4)
*
ERROR at line 1:
ORA-00984: column not allowed here
SQL> insert into items values(&id,&iname,&cost,&qty);
Enter value for id:
Enter value for iname:
Enter value for cost:
Enter value for qty:
old 1: insert into items values(&id,&iname,&cost,&qty)
new 1: insert into items values(,,,)
insert into items values(,,,)
*
ERROR at line 1:
ORA-00936: missing expression
SQL>
SQL> insert into items values(&id,'&iname',&cost,&qty);
Enter value for id: 101
Enter value for iname: Lux
Enter value for cost: 15.50
Enter value for qty: 33
old 1: insert into items values(&id,'&iname',&cost,&qty)
new 1: insert into items values(101,'Lux',15.50,33)
1 row created.
SQL> /
Enter value for id: 102
Enter value for iname: Surf
Enter value for cost: 40.00
Enter value for qty: 20
old 1: insert into items values(&id,'&iname',&cost,&qty)
new 1: insert into items values(102,'Surf',40.00,20)
1 row created.
SQL> /
Enter value for id: 106
Enter value for iname: Colgate
Enter value for cost: 16.50
Enter value for qty: 10
old 1: insert into items values(&id,'&iname',&cost,&qty)
new 1: insert into items values(106,'Colgate',16.50,10)
1 row created.
SQL> desc sales
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEMID NUMBER(4)
CID NUMBER(3)
QTY NUMBER(4)
T_TYPE CHAR(1)
COST NOT NULL NUMBER(4,2)
SQL> insert into sales values(&id,&cid,&qty,'&typ',&cost);
Enter value for id: 106
Enter value for cid: 200
Enter value for qty: 1
Enter value for typ: P
Enter value for cost: 17.00
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(106,200,1,'P',17.00)
1 row created.
SQL> /
Enter value for id: 102
Enter value for cid: 10
Enter value for qty: 2
Enter value for typ: S
Enter value for cost: 18.00
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(102,10,2,'S',18.00)
1 row created.
SQL> /
Enter value for id: 101
Enter value for cid: 20
Enter value for qty: 2
Enter value for typ: S
Enter value for cost: 20.50
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(101,20,2,'S',20.50)
1 row created.
SQL> desc customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CID NOT NULL NUMBER(3)
CUST_NAME NOT NULL VARCHAR2(20)
CUST_ADDR VARCHAR2(25)
SQL> insert into customer values(&cid,'&cnam','&cadr')
2 ;
Enter value for cid: 200
Enter value for cnam: Raman
Enter value for cadr: Delhi
old 1: insert into customer values(&cid,'&cnam','&cadr')
new 1: insert into customer values(200,'Raman','Delhi')
1 row created.
SQL> /
Enter value for cid: 50
Enter value for cnam: Shyam
Enter value for cadr: Bombay
old 1: insert into customer values(&cid,'&cnam','&cadr')
new 1: insert into customer values(50,'Shyam','Bombay')
1 row created.
SQL> /
Enter value for cid: 20
Enter value for cnam: Manoj
Enter value for cadr: Bangalore
old 1: insert into customer values(&cid,'&cnam','&cadr')
new 1: insert into customer values(20,'Manoj','Bangalore')
1 row created.
SQL> select * from customer;
CID CUST_NAME CUST_ADDR
---------- -------------------- -------------------------
200 Raman Delhi
50 Shyam Bombay
20 Manoj Bangalore
SQL> select * from sales;
ITEMID CID QTY T COST
---------- ---------- ---------- - ----------
106 200 1 P 17
102 10 2 S 18
101 20 2 S 20.5
SQL> insert into sales values(&id,&cid,&qty,'&typ',&cost);
Enter value for id: 106
Enter value for cid: 50
Enter value for qty: 1
Enter value for typ: S
Enter value for cost: 25
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(106,50,1,'S',25)
1 row created.
SQL> /
Enter value for id: 102
Enter value for cid: 20
Enter value for qty: 1
Enter value for typ: P
Enter value for cost: 18
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(102,20,1,'P',18)
1 row created.
SQL> select * from items
2 ;
ITEMID ITEM_NAME COST QTY
---------- -------------------- ---------- ----------
101 Lux 15.5 33
102 Surf 40 20
106 Colgate 16.5 10
SQL> commit
2 ;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FKCUST) violated - parent key not found
SQL> select * from sales;
no rows selected
SQL> select * from customer;
no rows selected
SQL> select * from items;
no rows selected
SQL> insert into sales values(&id,&cid,&qty,'&typ',&cost);
Enter value for id: 101
Enter value for cid: 10
Enter value for qty: 4
Enter value for typ: P
Enter value for cost: 10
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(101,10,4,'P',10)
1 row created.
SQL> /
Enter value for id: 50
Enter value for cid: 20
Enter value for qty: 5
Enter value for typ: S
Enter value for cost: 25
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(50,20,5,'S',25)
1 row created.
SQL> /
Enter value for id: 500
Enter value for cid: 30
Enter value for qty: 15
Enter value for typ: P
Enter value for cost: 10
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(500,30,15,'P',10)
1 row created.
SQL> /
Enter value for id: 101
Enter value for cid: 20
Enter value for qty: 5
Enter value for typ: S
Enter value for cost: 50
old 1: insert into sales values(&id,&cid,&qty,'&typ',&cost)
new 1: insert into sales values(101,20,5,'S',50)
1 row created.
SQL> desc items
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEMID NOT NULL NUMBER(4)
ITEM_NAME NOT NULL VARCHAR2(20)
COST NUMBER(4,2)
QTY NUMBER
SQL> insert into items values(&itmid,'&itmnam',&cst,&qty);
Enter value for itmid: 101
Enter value for itmnam: Lux
Enter value for cst: 13.50
Enter value for qty: 50
old 1: insert into items values(&itmid,'&itmnam',&cst,&qty)
new 1: insert into items values(101,'Lux',13.50,50)
1 row created.
SQL> /
Enter value for itmid: 50
Enter value for itmnam: Surf
Enter value for cst: 40
Enter value for qty: 10
old 1: insert into items values(&itmid,'&itmnam',&cst,&qty)
new 1: insert into items values(50,'Surf',40,10)
1 row created.
SQL> /
Enter value for itmid: 500
Enter value for itmnam: Colgate
Enter value for cst: 30
Enter value for qty: 5
old 1: insert into items values(&itmid,'&itmnam',&cst,&qty)
new 1: insert into items values(500,'Colgate',30,5)
1 row created.
SQL> desc customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CID NOT NULL NUMBER(3)
CUST_NAME NOT NULL VARCHAR2(20)
CUST_ADDR VARCHAR2(25)
SQL> insert into customer values(&cid,'&cnam','&cadr');
Enter value for cid: 20
Enter value for cnam: Raman
Enter value for cadr: Delhi
old 1: insert into customer values(&cid,'&cnam','&cadr')
new 1: insert into customer values(20,'Raman','Delhi')
1 row created.
SQL> /
Enter value for cid: 10
Enter value for cnam: Shyam
Enter value for cadr: Mumbai
old 1: insert into customer values(&cid,'&cnam','&cadr')
new 1: insert into customer values(10,'Shyam','Mumbai')
1 row created.
SQL> /
Enter value for cid: 30
Enter value for cnam: Manoj
Enter value for cadr: Bangalore
old 1: insert into customer values(&cid,'&cnam','&cadr')
new 1: insert into customer values(30,'Manoj','Bangalore')
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> select * from items;
ITEMID ITEM_NAME COST QTY
---------- -------------------- ---------- ----------
101 Lux 13.5 50
50 Surf 40 10
500 Colgate 30 5
SQL> select * from sales;
ITEMID CID QTY T COST
---------- ---------- ---------- - ----------
101 10 4 P 10
50 20 5 S 25
500 30 15 P 10
101 20 5 S 50
SQL> select * from customer;
CID CUST_NAME CUST_ADDR
---------- -------------------- -------------------------
20 Raman Delhi
10 Shyam Mumbai
30 Manoj Bangalore
SQL> insert into sales values(50,10,10,'P',25);
1 row created.
SQL> commit
2 ;
Commit complete.
SQL> host cls
SQL> select * from Items;
ITEMID ITEM_NAME COST QTY
---------- -------------------- ---------- ----------
101 Lux 13.5 50
50 Surf 40 10
500 Colgate 30 5
SQL> Select * from customer;
CID CUST_NAME CUST_ADDR
---------- -------------------- -------------------------
20 Raman Delhi
10 Shyam Mumbai
30 Manoj Bangalore
SQL> Select * from sales;
ITEMID CID QTY T COST
---------- ---------- ---------- - ----------
101 10 4 P 10
50 20 5 S 25
500 30 15 P 10
101 20 5 S 50
50 10 10 P 25
SQL> show plan
SP2-0158: unknown SHOW option "plan"
SQL> show
SQL> show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 9
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 1002000100
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool ON
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 10.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SCOTT"
verify ON
wrap : lines will be wrapped
SQL> set explain plan
SP2-0158: unknown SET option "explain"
SQL> set autotrace on explain
SQL> select count(*) from sales;
COUNT(*)
----------
5
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| SALES | 5 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> desc sales
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEMID NUMBER(4)
CID NUMBER(3)
QTY NUMBER(4)
T_TYPE CHAR(1)
COST NOT NULL NUMBER(4,2)
SQL> select count(itemid) from sales;
COUNT(ITEMID)
-------------
5
Execution Plan
----------------------------------------------------------
Plan hash value: 417427692
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:
00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
| 2 | INDEX FAST FULL SCAN| SALES_ITEMIDX | 5 | 65 | 2 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Note
-----
- dynamic sampling used for this statement
SQL> ed
Wrote file afiedt.buf
1 declare
2 x number ;
3 y number ;
4 z number ;
5 begin
6 select qty into x,cost into z from items where itemsid=&&itemid;
7 dbms_output.put_line('Qnty in Item Table'||x);
8 dbms_output.put_line('Cost of Item '||z);
9 update items set qty=qty-&salesqty where itemid=&&itemid;
10 savepoint s1;
11 select count(cid) into y from customer where cid=&cid;
12 if y=1
13 then
14 if x>0
15 then
16 insert into sales values (&&itemid,&&cid,&&salesqty,'S',&Cost);
17 commit;
18 else
19 rollback to savepoint s1;
20 end if;
21 else
22 dbms_output.put_line('Customer does not exist');
23 rollback to savepoint to s1;
24 end if;
25* end;
26 /
Enter value for itemid: 101
old 6: select qty into x,cost into z from items where itemsid=&&itemid;
new 6: select qty into x,cost into z from items where itemsid=101;
Enter value for salesqty:
User requested Interrupt or EOF detected.
SQL>
No comments:
Post a Comment