Hi there,

Below is my SQL codes. It shows:

Error report -
ORA-01722: invalid number
ORA-06512: at line 37 --The line: insert tots4271_hw4_employee(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,

salary,commission_pct,manager_id,department_id)
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

Below is my sql codes:

CREATE TABLE ts4271_hw4_employee
( employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(20) NOT NULL,
phone_number VARCHAR2(20),
hire_date DATE NOTNULL,
job_id VARCHAR2(10) NOT NULL,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER,
department_id NUMBER(4),
CONSTRAINT ts4271_hw4_employee_pk PRIMARY KEY(employee_id)
);

DECLARE
TYPE varchar2_tt IS TABLE OF VARCHAR2(255) INDEX BYPLS_INTEGER;
rec_columns VARCHAR2_TT;
v_line_tx VARCHAR2(4000) :='employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id';
v_column_tx VARCHAR2(255) := '';
v_char_tx VARCHAR2(1);
BEGIN
--reset all variables
rec_columns.delete();
v_column_tx := null;
v_char_tx := null;
--loop through v_line_tx
for indx in 1..length(v_line_tx) loop
dbms_output.Put_line(indx);
--step1: get character at that position
v_char_tx := substr(v_line_tx, indx,1);
--step2: Evaluate the character
--step 2a: If a character is acomma, dosomething
if v_char_tx = ',' then
--Step 1a - add to rec_colimns
dbms_output.Put_line(v_column_tx ||':'||rec_columns.last);
rec_columns(rec_columns.count) :=v_column_tx;
--Step 1 --- cheal out variables
v_column_tx := null;
else
--Step 2: If character is NOT a comma, dosomething
v_column_tx :=v_column_tx||v_char_tx;
dbms_output.Put_line(v_column_tx);
end if;
end loop;
--to get the last column
rec_columns(rec_columns.count) := v_column_tx;
dbms_output.Put_line('------');

--Final Step--> insert into employee table
insert intots4271_hw4_employee(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)
values
(rec_columns(0), rec_columns(1), rec_columns(2),rec_columns(3), rec_columns(4), rec_columns(5),rec_columns(6),rec_columns(7), rec_columns(8), rec_columns(9),rec_columns(10));

--Print out each column
for indx in rec_columns.first..rec_columns.lastloop
dbms_output.Put_line(indx||':'||rec_columns(indx));
end loop;
END;

The error led to failure to insert the table, so can you plzhelp me fix it? Thank you.