Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ROWTYPE and "Too many values" error...

Wolf_22Mar 3 2011 — edited Mar 7 2011
I'm in the middle of trying to understand the inner workings of %ROWTYPE and how I can copy the structure of a table with it. I think I'm understanding the gist of it, but I continue to fail in pinpointing the actual values I try to insert with an example tutorial I'm using at the moment and was hoping someone could help me understand my problem better so that I can bridge this mental gap I seem to be slipping into...

That said, I have the following table: ct_employee
The columns of this table (and their schema settings) are as follows:
empno - NUMBER(4,0) 
ename - VARCHAR2(20)
job - VARCHAR2(20)
mgr - NUMBER(4,0)
hiredate - DATE
sal - NUMBER(7,2)
comm - NUMBER(7,2)
ct_department - NUMBER(2,0)
The SQL I'm using in all this is the following:
SET VERIFY OFF
DEFINE emp_num = 7369;
DECLARE
  emp_rec ct_retired_emps%ROWTYPE;
BEGIN
  SELECT *
  INTO emp_rec 
  FROM ct_employee
  WHERE empno = &emp_num;
  emp_rec.leavedate := SYSDATE;
  UPDATE ct_retired_emps SET ROW = emp_rec
  WHERE empno = &emp_num;
END;
As I hope you can tell from the above, I'm trying to create a variable (emp_rec) to store the structure of ct_employee where upon I then copy a record into emp_rec if and only if the empno column from ct_employee matches that of the emp_num "7369".

I'm using SQL*PLUS with 10g in all this (+a program I love, by the way; it's really easy to use and very informative+) and when I press the "Run Script" button, I receive the following Script Output:

Error report:
ORA-06550: line 6, column 3:
PL/SQL: ORA-00913: too many values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:>

What I translate from this is that there's either a column number mismatch or else there's some value being attempted to be inserted into the variable I created that isn't matching the structure of the variable.

Anyway, if someone around here could help me with this, you would make my day.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 4 2011
Added on Mar 3 2011
9 comments
2,346 views