Sponsored

Describing 3 new useful Create Table command enhancements introduced in Oracle Database 12c. These new features were really required to improve the coding and performance.

1. Assign Default Values To Columns Using Sequence

You can now assign sequence number as default value to a particular column in Oracle 12c to generate unique value for every row. The following is the example given to generate serial number for every record inserted into EMP table:

Create Sequence Seq_Srl_No start with 1 increment by 1;

Create Table Emp (
  Emp_no Integer Constraint Pk_Emp_No Primary Key,
  Emp_Name Varchar2(100),
  Emp_Srl_No Integer Default Seq_Srl_No.nextval 
  );

2. Generate Automatically Default Unique Number Using IDENTITY

In Oracle 12c, we can generate unique number value for a column without using sequence object, by specifying Identity keyword in Create Table Command. The following is the example given for Emp table to generate unique number which starts with 10 and increment by 2 for every new record;

Create Table Emp (
  Emp_no Integer Constraint Pk_Emp_No Primary Key,
  Emp_Name Varchar2(100),
 Emp_Srl_No Integer GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 2));

3. VISIBLE And INVISIBLE Columns In Tables

You can define Invisible columns in a Table using Create Table command in Oracle database 12c. By default every column is visible when we create a table in Oracle, but now in Oracle 12c you can specify Invisible keyword to hide a column. In the below example, a Credentials table is created with the hidden password column:

CREATE TABLE Credentials (
username varchar2(30),    
password varchar2(30) INVISIBLE
 );
   
When we describe this table using DESCRIBE command to see its structure the following would be the result:

SQL> DESC CREDENTIALS
Name                          Null?    Type  
---------------------------------------- -------- ----------------------------
USERNAME                    VARCHAR2(30) 

The PASSWORD column is hidden from the above result, so if you want to see that column you must set the COLINVISIBLE to on:

SET COLINVISIBLE ON;

Now you would be able to see that hidden column using describe command:

SQL> DESC CREDENTIALS
Name                           Null?    Type
---------------------------------------- -------- ----------------------------
USERNAME                       VARCHAR2(30)
PASSWORD (INVISIBLE)                   VARCHAR2(30)

Note: You must specify that column specifically in DML statements to process it else it would be not considered and my through an error. The following example shows if we insert directly to CREDENTIALS Table without specifying this PASSWORD column:

SQL> INSERT INTO CREDENTIALS VALUES ('SCOTT', 'TIGER'); 

INSERT INTO CREDENTIALS VALUES ('SCOTT', 'TIGER')

* ERROR at line 1: 
ORA-00913: too many values 

However we can insert by specifying value only for USERNAME column because PASSWORD COLUMN is nullable:

SQL> INSERT INTO CREDENTIALS VALUES ('SCOTT'); 

1 row created.

So if we want to insert a value to password column also then we must specify in insert statement, check the following example:

SQL> INSERT INTO CREDENTIALS (USERNAME, PASSWORD) VALUES ('SCOTT', 'TIGER'); 

1 row created.
Oracle 12c SQL new features and enhancements


Providing an example form for creating hierarchical trees in Oracle Forms to control data block records. In this form whenever user selects any node in tree menu then corresponding record is displayed at right side.

This form is having two data blocks, EMP and Control, the control block is having Tree Item and populating that tree on PRE-FORM trigger with the following code:

ftree.populate_tree('CONTROL.EMP_TREE');
ftree.set_tree_property('CONTROL.EMP_TREE', FTREE.QUERY_TEXT,
   'SELECT decode(level, 1, 1, -1), level, INITCAP(ename), ''fxrun'',' ||
    'empno FROM   SCOTT_emp ' ||
    'START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr');

And whenever user select any node then the following code is written on When-Tree-Node-Selected trigger to fetch the record for the selected employee:

go_block('EMP');
set_block_property('EMP', DEFAULT_WHERE, 'EMPNO='||
   ftree.get_tree_node_property('CONTROL.EMP_TREE', :SYSTEM.TRIGGER_NODE,
      FTREE.NODE_VALUE));
execute_query;

The fxrun.ico icon and table scott_emp is used in this example and script of this table and icon file are available in source code.
Like us to get notifications for free source code in future, thanks.

In this tutorial you will learn how to create a local user in Oracle Database 12c and how to give admin rights to that user and how to import an user (schema) exported by Oracle 11g database into Oracle 12c database.

First I will give you just a brief introduction about Oracle 12c Architecture. Prior to Oracle 12c like Oracle 11g and Oracle 10g there is happen to be only one database root in which Oracle Admin users like Sys, system etc installed and some sample schema also if you choose to install and on that root you just create your users / schema to store application data. But In Oracle 12c it is different and there is now two roots one is the Oracle's default admin user root which is called CDB$ROOT and another is PDB root in which you create your users / schema same as you create in Oracle 11g or 10g. As CDB$ROOT is the admin root you can not create your application's local user in that root, you must create the users in PDB (pluggable database root) because if you will try to create a user in CDB$ROOT then you will get the following errors.

 SQL> create user hms identified by hms;
create user hms identified by hms
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Or if you try to create this way then you will receive this error:

SQL> grant connect, resource, dba to hms identified by hms;
grant connect, resource, dba to hms identified by hms
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

So I will tell you some important instructions on installing Oracle database 12c, which is when you install Oracle database 12c then you must choose the Pluggable database option while installing because you can create your application's user in PDB only or if you not selected PDB at the time of installation then I will tell you that how to create PDB database after installation.

To create a Pluggable database in Oracle 12c, click on start menu then choose Oracle12chome then Configuration and Migration Tools then Database Configuration Assistant then the following screen will appear:


Select Manage Pluggable Databases option as shown above and click on Next and the following screen will appear:


Select Create Pluggable Database and click on Next to continue...


Specify Sys user credentials then click on Next to continue...


Select Create a new Pluggable Database option then click on Next to continue..


Specify your Pluggable database name and user credentials then click on Next to continue.. I specified hms for my Hospital Management System database application to give you an example:


It will show you the summary of installation, just click on Finish to create Pluggable database and after completion the following screen will appear:


Now your Pluggable database creation is complete and now you need to connect it to do this you must specify its connection information in TNSNAMES.ORA file because this Pluggable database would be consider as a new Oracle database service.

Add the following in your tnsnames.ora file:

HMS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hms)
    )
  )

Replace hms, host and port information according to your database and machine information and then save.

Now this pluggable database must be start to connect to it, so log in with Sys user credentials to your database and give the following command:

SQL> Alter Pluggable Database hms open;

After that you can connect as following:

Type Connect and give the credentials:

SQL> conn

Enter user-name: hms/hms@hms
Connected.
SQL>

Now we have connected but our schema is empty because we don't have any objects in it, so we will import a database dump in this Oracle 12c Pluggable database which have been exported from Oracle 11g.

For this user must have proper privileges to do this task, so first we will give admin privileges to hms user to perform import task and for this you have to reconnect using Sys user then give the DBA privilege to hms as following:

SQL> conn
Enter user-name: sys/vinish@orcl as sysdba
Connected.
SQL> rem set session to hms to access this db from sys
SQL> alter session set container=hms;

Session altered.

SQL> grant dba to hms;

Grant succeeded.

SQL> connect
Enter user-name: hms/hms@hms
Connected.
SQL>

Now you run IMP command to import a database dump file (.dmp) into this hms user, to perform this task do the following:

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Vinish>IMP USERID=hms/hms@hms file=d:\vinish\hms14may.dmp full=y

Its done. Now you can check your user for the objects:

SQL> select * from tab;

You can now create users also through hms to access hms objects, the following is the example:

SQL> create user hmsuser1 identified by hmsuser1;

User created.

SQL> grant connect, resource to hmsuser1;

Grant succeeded.

In this tutorial you will learn to how to pass the parameters to SQL Script and use that parameters in SQL statements. The following examples shows how to pass the Character String, Numeric and Date type parameters to SQL script.
Passing parameters to sqlplus script in Oracle
Suppose we have an SQL script "MyScript.Sql" and the following statements are in it and we need to pass the JOB, MANAGER and HIREDATE parameters to get the results.

So we will call that script as following:

@ C:\Scripts\MyScript.sql SALESMAN 7698 31-MAR-2015

-- Where (1) JOB => SALESMAN, (2) MGR => 7698 AND (3) HIREDATE => 31-MAR-2015

Parameters can be read sequentially, for eg. if you want to access JOB parameter which is in first position, then you can reference it with &1 and second parameter with &2 and so on.

In the following statement we are getting the results on the behalf of first parameter JOB which is Character String type and String type parameters can be reference within single quotes, eg. '&1'

SELECT EMPNO,
       ENAME,
       MGR,
       SAL,
       COMM
  FROM SCOTT_EMP
 WHERE JOB = '&1'
/

In the following example, it will display the records for particular Manager by its second Numeric type parameter MGR, which can be access without quoted string as &2

SELECT EMPNO,
       ENAME,
       SAL,
       COMM
  FROM SCOTT_EMP
 WHERE MGR = &2
 /
The following example will show the records matching with first two parameters, one is String and another is Numeric.

SELECT EMPNO,
       ENAME,
       SAL,
       COMM
  FROM SCOTT_EMP
 WHERE JOB = '&1' AND MGR = &2
 /

The following example will display the records by matching third parameter, which is of Date type and Date type parameter also would be refer in quotes:

SELECT EMPNO, ENAME, HIREDATE
  FROM SCOTT_EMP
 WHERE HIREDATE = '&3'
 /

The following example shows how to use parameter in PL/SQL Block:

SET SERVEROUTPUT ON;

DECLARE
   v   VARCHAR2 (100);
BEGIN
   SELECT ENAME
     INTO V
     FROM SCOTT_EMP
    WHERE EMPNO = &2;

   DBMS_OUTPUT.put_line (v);
END;
/

Calling Script From Within A Script By Parameter

@ C:\Scripts\MyScript.sql Emp.sql

Contents of MyScript.sql

@ C:\Scripts\&1

-- It will run the Emp.sql script

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.