Join the DBA training courses now for learning the SQL New features

DBA training courses over here teaches about SQL New features.

SQL is the most generally used data accessibility language while PL/SQL is an exigent language that can incorporate easily with SQL instructions. The most important advantage of operating PL/SQL is that the code handling happens natively within the Oracle Databases. Previously, there have been controversy and conversations on server part development while the consumer produces the PL/SQL workouts to carry out an activity. The server part development strategy has many advantages. It decreases the system circular visits between the consumer and the database. It decreases the rule dimension and helps the rule mobility because PL/SQL can run on all systems, wherever Oracle Databases is reinforced.

Oracle Databases 12c presents many terminology functions and improvements which are targeted on SQL to PL/SQL incorporation, rule migration, and ANSI conformity. It talks about the SQL and PL/SQL new functions in Oracle database 12c.

Oracle Databases 12c Release 1 presents identification content in the platforms in conformity with the American National Standard Institute (ANSI) SQL standard. A table column, noticeable as IDENTITY, instantly produce an step-by-step number value at the duration of history development.

Before the discharge of Oracle 12c, designers had to make an extra series item in the schema and allocate its value to the range. The new function makes easier rule composing and advantages the migration of a non-Oracle database to Oracle.

The following program states an identification range in the desk T_ID_COL:

/*Create a desk for business presentation purpose*/

CREATE TABLE t_id_col

(id NUMBER GENERATED AS IDENTITY,

name VARCHAR2(20))

/

The identification range meta-data can be queried from the vocabulary opinions USER_TAB_COLS and USER_TAB_IDENTITY_COLS. Observe that Oracle unquestioningly makes a series to create the amount principles for the range. However, Oracle allows the settings of the succession popular functions of an identification range. The customized series settings is detailed under IDENTITY_OPTIONS in USER_TAB_IDENTITY_COLS view:

/*Query identification range details in USER_TAB_COLS*/

SELECT column_name, data_default, user_generated, identity_column

FROM user_tab_cols

WHERE table_name=’T_ID_COL’

/

COLUMN_NAME DATA_DEFAULT USE IDE

————– —————————— — —

ID “SCOTT”.”ISEQ$$_93001″.nextval YES YES

NAME YES NO

Let us examine the options of the previous series that Oracle has unquestioningly designed. Observe that the question uses REGEXP_SUBSTR to create the succession settings in several rows:

/*Check the succession settings from USER_TAB_IDENTITY_COLS view*/

SELECTtable_name,column_name,generation_type,REGEXP_SUBSTR(identity_options,’[^,]+’, 1, LEVEL) identity_options

FROM user_tab_identity_cols

WHERE table_name = ‘T_ID_COL’

CONNECT BY REGEXP_SUBSTR(identity_options,’[^,]+’,1,level)

IS NOT NULL

/

TABLE_NAME COLUMN_NAME GENERATION IDENTITY_OPTIONS

———- ———————- ———————————

T_ID_COL ID ALWAYS START WITH: 1

T_ID_COL ID ALWAYS INCREMENT BY: 1

T_ID_COL ID ALWAYS MAX_VALUE: 9999999999999999999999999999

T_ID_COL ID ALWAYS MIN_VALUE: 1

T_ID_COL ID ALWAYS CYCLE_FLAG: N

T_ID_COL ID ALWAYS CACHE_SIZE: 20

T_ID_COL ID ALWAYS ORDER_FLAG: N

7 rows selected

While placing details in the desk T_ID_COL, do not consist of the identification range as its value is instantly generated:

/*Insert analyze details in the table*/

BEGIN

INSERT INTO t_id_col (name) VALUES (‘Allen’);

INSERT INTO t_id_col (name) VALUES (‘Matthew’);

INSERT INTO t_id_col (name) VALUES (‘Peter’);

COMMIT;

END;

/

Let us examine the details in the desk. Observe the identification range values:

/*Query the table*/

SELECT id, name FROM t_id_col

/

ID NAME

—– ——————–

1 Allen

2 Matthew

3 Peter

The series designed under the includes for identification content is firmly combined with the range. If a person tries to place a user-defined feedback for the identification range, the function brings an exemption ORA-32795:

INSERT INTO t_id_col VALUES (7,’Steyn’);

insert into t_id_col principles (7,’Steyn’)

*

ERROR at range 1:

ORA-32795: cannot place into a generated always identification column

Oracle Databases 12c allows designers to plain a range straight to a sequence‑generated value. The DEFAULT stipulation of a desk range can be sent to SEQUENCE.CURRVAL or SEQUENCE.NEXTVAL. The function will be useful while moving non-Oracle details explanations to Oracle.

Thus our Oracle Certification course is more than enough for you to make your Oracle career in this field.

Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>