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.