Total Pageviews

October 8, 2015

10/08/2015 10:20:00 PM
In Oracle 12c, you can use:
truncate table <Parent> cascade;
• Must have defined the FK as ON DELETE CASCADE

Session Sequences:
Session Sequences – select seq1.nextval from dual;
– Session Seq: values visible only in the session
– Not persistent
SQL> create sequence sessseq session;
SQL> create sequence globseq global;
SQL> select globseq.nextval from dual;
3
SQL> select sessseq.nextval from dual;
1
Default values
SQL> create table emp_t1 (col1 number, col2 number default on null 0);
Table created.
SQL> desc emp_t1
Name Null? Type
----------------- -------- ------
COL1 NUMBER
COL2 NOT NULL NUMBER
SQL> insert into emp_t1 values (1, null);
SQL> insert into emp+t1 values (2,2);
SQL> select * from emp_t1;
COL1 COL2
---------- ----------
1     0
2     2

Identity Columnn
Now we can define sequnces while defining a table itself that concept is called Identity column


SQL> create table emp_t2(col1 number generated always as identity);
SQL> create table emP_t3 (col1 number generated always as identity (start with 1000
increment by 10));
SQL> insert into eml_t3 values (1);
insert into t6 values (1)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> create table emp_t4(col1 number, col2 number generated by default as identity);
SQL> insert into emp_t4 values (9,9);
SQL> insert into emp_t4 values (10,default);
SQL> insert into emp_t4 (col1) values (11);
 
Related Posts Plugin for WordPress, Blogger...