In the previous tutorial we saw, how to initialize a record variable by fetching data from all the columns of a table using SELECT-INTO statement. But what if we want to fetch the data from selected columns of a table? Can we still use the same process for initializing the record variable?

The answer is No, we cannot use the same process shown in the previous tutorial for initializing a record datatype variable by fetching data from select columns of a table. The execution of the following example where we are using the data from one column “First Name” of the “Employees” table for initializing the record datatype variable “v_emp” will return an error.

SET SERVEROUTPUT ON;
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT first_name INTO v_emp FROM employees
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_emp.first_name);
END;
/ 

That error is “PL/SQL: ORA-00913: too many values” So what does this error means? Why is it saying “too many values” even when we are fetching only one data.  I know sometimes PL/SQL error can be deceptive and confusing.

table based record datatype variable by manish sharma

In order to find out the solution of this problem and understand this error we have to recall what we learnt in the previous tutorial. There we learnt that Database Records are composite data structures made up of multiple fields which share same name, datatype and data width as that of the columns of the table over which it is created. And the data which was fetched gets stored into these fields.

In case of initializing a record variable by fetching data from all the columns of a table, we don’t have to do anything, oracle engine does all the dirty work in the background for you. It first fetches the data from the table and then stores that data into the corresponding field into the record which has the same data type and data width, and for that matter also shares the same name.

table based record datatype variable in oracle database by manish sharma

But when it comes to initializing a record datatype variable by fetching the data from selected columns we have to do all this work manually. We have to specify the name of the column from which we want to fetch the data and then we have to specify the name of the field where we want to store that data.

In the above example we did specify the column name of the table whose data we want to fetch but we didn’t specify the name of the record’s field where we want to store the fetched data. That confuses the compiler on where to store the data which ultimately causes the error.

Reference Book for SQL Expert 1z0-047 Affiliate link
OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

How to Solve “PL/SQL ORA-00913: Too Many Values” Error?

The PL/SQL ORA-00913 error can easily be solved by specifying the record’s field name where you want the fetched data to be stored. But here you have to be a bit careful. You have to make sure that the field’s datatype and data width must match with the datatype and data width of the column whose data you have fetched.

Now the question here is that how to specify the record data type field’s name? We can specify the name of the field of the record variable using Dot (.) notation where we first write the name of the already declared record variable followed by a dot (.) and the name of the field. And luckily both the columns of the table and fields of the record share the same name.

So now that we have learnt why we are having PL/SQL ORA-00913 error on initializing the record data type variable using data from select columns. Let’s modify the above example accordingly and try to resolve it.

Example 1: Initialize the Record Datatype variable using data from One Column.

SET SERVEROUTPUT ON;
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT first_name INTO v_emp.first_name FROM employees
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE (v_emp.first_name);
END;
/

 

Example 2: Initialize the Record Datatype variable using data from Multiple Columns.

SET SERVEROUTPUT ON;
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT first_name,
    hire_date
  INTO v_emp.first_name,
    v_emp.hire_date
  FROM employees
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE (v_emp.first_name);
  DBMS_OUTPUT.PUT_LINE (v_emp.hire_date);
END;
/

That is how we can initialize a record datatype variable using data from selected columns of a table.

Question: Can we assign values of one record datatype variable to another record datatype variable?
Try it yourself and send your answers to me on my Twitter or Facebook.

Hope you enjoyed reading. Send your feedbacks on my Twitter and Facebook. Also share this blog and tag me as I am giving away RebellionRider’s merchandise to randomly selected winners. Thanks & have a great day!

1 COMMENT