SQL is used on a wide scale while PL/SQL is an exigent language that can incorporate easily with SQL commands. The greatest benefit of running PL/SQL is that the code handling happens natively within the Oracle Database.
In the earlier days, there have been talks and discussions on server side development while the consumer creates the PL/SQL queries to perform a task.
For more information join the SQL dba training in Pune
The server side development approach has many advantages. The database and the client . It cuts down on the code size and helps the code mobility because PL/SQL can run on all systems, wherever Oracle Database is reinforced.
Oracle Database 12c presents many language functions and improvements which are targeted on SQL to PL/SQL incorporation, code migration, and ANSI conformity. This area talks about the SQL and PL/SQL additional functions in Oracle database 12c.
Oracle Database 12c Release 1 presents identification content in the platforms in conformity with the American National Standard Institute (ANSI) SQL standard. A table line, noted as IDENTITY, instantly generate a step-by-step number value at the time of record development.
Before the release of Oracle 12c, designers had to create an additional series item in the schema and allocate its value to the line. The new feature makes easier rule writing and benefits the migration of a non-Oracle database to Oracle.
The following program states a brand line in the table T_ID_COL:
/*Create a table for demonstration purpose*/ CREATE TABLE t_id_col (id NUMBER GENERATED AS IDENTITY, name VARCHAR2(20)) /
The identification line meta-data can be queried from the vocabulary opinions USER_TAB_COLS and USER_TAB_IDENTITY_COLS. Observe that Oracle unquestioningly makes a string to produce the number principles for the line. However, Oracle allows the settings of the succession features of a brand line. The customized series settings shows up under IDENTITY_OPTIONS in USER_TAB_IDENTITY_COLS view:
/*Query identity column information 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 check the features of the previous series that Oracle has unquestioningly created. Note that the question uses REGEXP_SUBSTR to create the succession settings in several rows:
/*Check the sequence configuration from USER_TAB_IDENTITY_COLS view*/ SELECT table_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 information in the desk T_ID_COL, do not consist of the identification line as its value is instantly generated:
/*Insert test data 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 check the information in the desk. Note the identification line values:
/*Query the table*/
SELECT id, name FROM t_id_col /
The series designed under the includes for identification content is firmly along with the line. If a person tries to place a user-defined feedback for the identification line, the function brings different ORA-32795:
INSERT INTO t_id_col VALUES (7,’Steyn’); insert into t_id_col values (7,’Steyn’)
* ERROR at line 1: ORA-32795: cannot insert into a generated always identity column
Join the best Oracle training in Pune now!
Oracle Data source 12c allows designers to standard a line straight to a sequence‑generated value. The DEFAULT stipulation of a desk line can be allocated to SEQUENCE.CURRVAL or SEQUENCE.NEXTVAL. The function will be useful while moving non-Oracle information explanations to Oracle.
Thus you can join the database administrator training to make your career in this field.