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.
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.
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.
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.
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.
That is how we can initialize a record datatype variable using data from selected columns of a table.
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!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.