Oracle “create table as” with null column value

When creating new tables with from a query output you may use "create table as" statement.

Sometimes you need to specify hardcoded null values for some columns. For example, in the following query we want null value for col_3:

create table NewTable as 
select 
   col_1, 
   col_2, 
   null col_3
from OldTable;

If you create an Oracle table using "create as" where one of your fields is null you will get the error:

ORA-01723: zero-length columns are not allowed

To fix this issue, you can cast the null value to a target data type and specify the size of the field where required. For example:

create table NewTable as 
select 
   col_1,
   col_2,
   cast(null as varchar2(100)) col_3,
   cast(null as date) col_4,
   cast(null as number) col_5
from OldTable;

User-defined exceptions in PL/SQL

If you are required to standardize the exception handling, not just to control the abnormal program flow but also to alter the program execution logic, you need to use user-defined exceptions. The user-defined exceptions are raised in the BEGIN..END section of the block using the RAISE statement.

With custom exceptions, Oracle allows users to specify names, associate error codes, and raise statements in line with the implementation logic.

There are three ways to declare user-defined exceptions.

• EXCEPTION type variable:

  • Declare the EXCEPTION type variable in the declaration section.
  • Raise it explicitly in the program body using the RAISE statement.
  • Handle it in the EXCEPTION section. No error code will be involved here.

• EXCEPTION variable:

  Declare the EXCEPTION variable and associate it with a standard error number using PRAGMA EXCEPTION_INIT.

• RAISE_APPLICATION_ERROR:

  Use the RAISE_APPLICATION_ERROR to declare a dedicated error number and error message.

Related Post(s):

• Exception handling in PL/SQL

Exception handling in PL/SQL

Exception happens when a program terminated abnomrally due to an unexpected flow during runtime. To suppress the abnormal termination with an alternative action we use exception handlers.

Exception handling is one of the important steps of database programming. Unhandled exceptions can result in unplanned application outages, impact business continuity, and frustrate end users.

There are two types of exceptions:

• system-defined:

  Implicitly raised by Oracle Database.

• user-defined:

  A user-defined exception is explicitly declared and raised within the program unit.

Let see an example of System-defined exception.

System-defined exceptions:

• These exceptions are defined and maintained implicitly by the Oracle Database and are defined in the Oracle STANDARD package.
• Whenever an exception occurs inside a program, the database picks up the appropriate one from an already defined list of exceptions.
• All system-defined exceptions are associated with a negative error code (except 1 to 100) and a short name. The short name is what we use while specifying the exception handlers.

For example, we have a table named EMP, with 100 employee records, with empployee ids as 1-100. In the following PL/SQL program, we include a SELECT statement to retreive the details of employee id 101. It raises NO_DATA_FOUND exception because a row with employee id 101 doesn't exist.

SET SERVEROUTPUT ON

DECLARE
	LOCAL_ENAME VARCHAR2 (100);
	LOCAL_SAL NUMBER;
	LOCAL_EMPID NUMBER := 101;

BEGIN
	SELECT ENAME, SAL
	INTO LOCAL_ENAME, LOCAL_SAL
	FROM EMP
	WHERE EMPNO = LOCAL_EMPID;
END;
/

You will get the output error message similar to this:

DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8

Let us rewrite the same PL/SQL block to include an EXCEPTION section and handle the NO_DATA_FOUND exception (note that we are using the short name (NO_DATA_FOUND) of exception we are expecting in our code block and want to handle):

SET SERVEROUTPUT ON

DECLARE
	LOCAL_ENAME VARCHAR2 (100);
	LOCAL_SAL NUMBER;
	LOCAL_EMPID NUMBER := 101;
BEGIN
	SELECT ENAME, SAL
	INTO LOCAL_ENAME, LOCAL_SAL
	FROM EMP
	WHERE EMPNO = LOCAL_EMPID;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE ('No record found with the employee id: '||LOCAL_EMPID);
END;
/

This time you will not get the default error message, but the flow will cath-up in the exception block and run our logic which will display a customized message like this:

No record found with the employee id: 101
PL/SQL procedure successfully completed.

Related Post(s):

• User-defined exceptions in PL/SQL

PL/SQL - Cursor FOR loop

In the last post we have seen how to loop through cursor records by using its attributes to manually check for the different stages of cursor OPEN, FETCH and CLOSE. In this post we will write more compact code with CURSOR FOR loop statement. A FOR loop opening a cursor directly is known as a CURSOR FOR loop.

Following program will print the Salary for each Employee in EMP table.

/*Enable the SERVEROUTPUT to display messages in output console*/
SET SERVEROUTPUT ON

DECLARE
/*Declare an explicit cursor to select employee information*/
 CURSOR C_EMP IS
 SELECT EMP_NAME, SAL
 FROM EMP;
BEGIN
/*FOR Loop uses the cursor C_EMP*/
FOR EMP IN C_EMP
LOOP

 /*Display message*/
 DBMS_OUTPUT.PUT_LINE(EMP.EMP_NAME||' Salary = '||EMP.SAL);

END LOOP;
END;
/

You will get the output similar to this:

Nasir Naveed Salary = 30000
Aurangzaib Khan Salary = 40000
Muhammad Anwar Salary = 50000
...
PL/SQL procedure successfully completed.

Note that, with the help of CURSOR FOR loop we have:

• No need to declare the block variables for holding the cursor columns. The CURSOR FOR loop index implicitly acts as a record of the cursor type.
• No need to explicitly open or close the cursor in the PL/SQL program.

Related Post(s):

• PL/SQL Cursor - Overview
• PL/SQL - Cursor Attributes and Loop

PL/SQL - Cursor Attributes and Loop

In the last post we have seen an overview of the Cursor in PL/SQL. In this post we will write a sample program to use Cursor which helps us to understand its execution cycle and query the information about different stages by using its attributes.

Lets say we have an EMP table.

create table EMP(  
  emp_no    number(4,0),  
  emp_name    varchar2(10),  
  hiredate date,  
  sal      number(7,2),  
  deptno   number(2,0)
)

This program uses the cursor attributes %ISOPEN, %NOTFOUND, and %ROWCOUNT to fetch the data from the EMP table and display it:

/*Enable the SERVEROUTPUT to display messages in output console*/
SET SERVEROUTPUT ON

DECLARE

	/*Declare a cursor to select employees data*/
	CURSOR C_EMP IS
	SELECT EMP_NO,EMP_NAME
	FROM EMP;

	LOCAL_EMP_NO EMP.EMP_NO%TYPE;
	LOCAL_EMP_NAME EMP.EMP_NAME%TYPE;
BEGIN

/*Check if the cursor is already open*/
IF NOT C_EMP%ISOPEN THEN
	DBMS_OUTPUT.PUT_LINE('Employee Data:');
END IF;

/*Open the cursor and iterate in a loop*/
OPEN C_EMP;
LOOP

 /*Fetch the cursor data into local variables*/
 FETCH C_EMP INTO LOCAL_EMP_NO, LOCAL_EMP_NAME;
 EXIT WHEN C_EMP%NOTFOUND;
	
 /*Display the employee information*/
 DBMS_OUTPUT.PUT_LINE(chr(10)||'Employee Info:'||C_EMP%ROWCOUNT);
 DBMS_OUTPUT.PUT_LINE('Employee No:'||LOCAL_EMP_NO);
 DBMS_OUTPUT.PUT_LINE('Employee Name:'||LOCAL_EMP_NAME);

END LOOP;

END;
/

You will get the output similar to this:

Employee Data:
Employee Info:1
Employee No:101
Employee Name:Muhammad Idrees

Employee Info:2
Employee No:102
Employee Name:Kamran Khan

Employee Info:3
Employee No:103
Employee Name:Ejaz Ahmed

….
PL/SQL procedure successfully completed.

Related Post(s):

• PL/SQL - Cursor Overview
• PL/SQL - Cursor FOR loop

PL/SQL - Cursor Overview

The SQL statements embedded within a PL/SQL block are executed as a cursor. A cursor is a private memory area, which is allocated in the session's User Global Area (UGA).

Cursors can be classified as implicit and explicit cursors.

Implicit cursor is created by Oracle itself for all the SQL statements in executable section of a PL/SQL block. In this case, the cursor lifecycle is maintained by the Oracle Database.

The execution cycle of Explicit cursor can be controlled by the user. Database developers can explicitly declare an implicit cursor under the DECLARE section along with a SELECT query.

The cursor execution cycle

A cursor goes through the following stages during execution.

• The OPEN stage - a context area in the session's User Global Area is allocated, SQL statements are parsed and bounded. In case of the SELECT query, the record pointer points to the first record in the result set.
• The FETCH stage - fetches the data from the query result set. The record pointer moves incrementally towrads next record in case of multi-record set on every fetch. Cursor will be in fetch stage until the last record is reached in the result set.
• The CLOSE stage - closes the cursor, flushes the context area, and releases the memory back to the UGA.

Note that, in the case of an implicit cursor, all the steps are carried out by the Oracle Database.

Cursor attributes

You can find information about the cursor stages execution by looking into Cursor attributes:

• %ROWCOUNT: Applicble for Select and DML statements. It denotes the number of rows fetched until the last fetch(in case of SELECT) or impacted by the last DML operation.
• %ISOPEN: If the cursor is still open it return Boolean TRUE, otherwise FALSE. For an implicit cursor, this attribute is always FALSE.
• %FOUND: If the fetch operation switches and points to a record, it returns Boolean TRUE, otherwise FALSE.
• %NOTFOUND: Opposite to %FOUND attribute. Returns TRUE when the cursor pointer switches but does not point to a record in the result set.

Only %ISOPEN is the attribute that is accessible outside the execution cylce. Remaining three attributes you could not access outside.

Related Post(s):

• PL/SQL - Cursor Attributes and Loop
• PL/SQL - Cursor FOR loop