PL/SQL DataType: Block to insert data to a table using character type variable

PL/SQL DataType: Exercise-2 with Solution

Write a PL/SQL block to insert data to a table using character type variable.

Sample Solution:

PL/SQL Code:

DROP TABLE test;
CREATE TABLE test (col1 CHAR(5));
 
DECLARE
  var1 VARCHAR2(5 CHAR) := 'abc ';
BEGIN
  INSERT INTO test(col1) VALUES(var1);
END;
/

Sample Output:

PL/SQL procedure successfully completed.

or

Flowchart:

DROP TABLE test;
CREATE TABLE test (col1 CHAR(5 CHAR));
 
DECLARE
  var1 VARCHAR2(5 CHAR) := 'abc ';
BEGIN
  INSERT INTO test(col1) VALUES(var1);
END;
/

Sample Output:

PL/SQL procedure successfully completed.

Flowchart:

if you write the block like this, error will occure, because the length of the column is 5 and the length of the variable declared is 6 and its value is 7 characters long -

DROP TABLE test;
CREATE TABLE test (col1 CHAR(5 CHAR));
 
DECLARE
  var1 VARCHAR2(6 CHAR) := 'abc   ';
BEGIN
  INSERT INTO test(col1) VALUES(var1);
END;
/

Sample Output:

DECLARE
*
ERROR at line 1:
ORA-12899: value too large for column "HR"."TEST"."COL1" (actual: 6, maximum:
5)
ORA-06512: at line 4

Flowchart:

Try to insert the data using TRIM() to eleminate the trailling blank spaces from the value of the variable var1 -

DROP TABLE test;
CREATE TABLE test (col1 CHAR(5 CHAR));
 
DECLARE
  var1 VARCHAR2(5 CHAR);
BEGIN
  var1:=RTRIM('abc    ');
  INSERT INTO test(col1) VALUES(var1);
END;
/

Sample Output:

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to learn how to declare a character type variable.
Next:  Write a PL/SQL block to differenciate between CHAR and VARCHAR2 datatype..