ORA-01722: invalid number ORA-06512: at line 17 01722. 00000 - "invalid number"

Question

I am getting this type of error while coding:

ORA-01722: invalid number ORA-06512: at line 17 01722. 00000 - "invalid number"

declare
dname varchar(15);
mname varchar(20);
sal number(20);
mid number(20);
row_worker worker%rowtype;
cursor work_cur(manid number) is select w.* from worker w ,manager m where w.manager_id = manid and w.manager_id=m.manager_id;
begin
dname:='&dname';
for x in(select m.department,m.name,m.salary,m.manager_id into dname,mname,sal,mid from manager m, worker w where m.department=dname and m.manager_id=w.manager_id)
loop
DBMS_OUTPUT.PUT_LINE('Department Name:'||dname||'       Manager Name:'||mname||'        Salary:'||sal);
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Worker_id    Name     Specialized_in     Salary       Join_date       Dept_name');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------');
open work_cur(mid);
fetch work_cur into row_worker;
--exit when work_cur%notfound;
DBMS_OUTPUT.PUT_LINE(''||row_worker.worker_id||'    '||row_worker.name||'   '||row_worker.specialized_in||'     '||row_worker.salary||'     '||row_worker.join_date);
end loop;
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------------------');
--DBMS_OUTPUT.PUT_LINE(1/n)
close work_cur;
end;

I expect the output of records that match the manager id of both the tables and also match the name of department with manager table given by user

Boneist Boneist · Accepted Answer · 2019-04-24T14:59:52

Your cursor for loop doesn't look right - you don't select into variables in the cursor - you use the record implicitly declared in the for loop, like this:

DECLARE
  dname      VARCHAR(15);
  mname      VARCHAR(20);
  sal        NUMBER(20);
  mid        NUMBER(20);
  row_worker worker%ROWTYPE;
  CURSOR work_cur(manid NUMBER) IS
    SELECT w.*
    FROM   worker  w,
           manager m
    WHERE  w.manager_id = manid
    AND    w.manager_id = m.manager_id;
BEGIN
  dname := '&dname';
  FOR x IN (SELECT m.department,
                   m.name,
                   m.salary,
                   m.manager_id
            FROM   manager m,
                   worker  w
            WHERE  m.department = dname
            AND    m.manager_id = w.manager_id)
  LOOP
    dbms_output.put_line('Department Name:' || x.department || '       Manager Name:' || x.name || '        Salary:' || x.salary);
    dbms_output.put_line('------------------------------------------------------------------------------');
    dbms_output.put_line('Worker_id    Name     Specialized_in     Salary       Join_date       Dept_name');
    dbms_output.put_line('------------------------------------------------------------------------------');

    OPEN work_cur(x.manager_id);
    FETCH work_cur
      INTO row_worker;
    --exit when work_cur%notfound;
    dbms_output.put_line('' || row_worker.worker_id || '    ' || row_worker.name || '   ' || row_worker.specialized_in || '     ' ||
                         row_worker.salary || '     ' || row_worker.join_date);
  END LOOP;
  dbms_output.put_line('------------------------------------------------------------------------------');
  --DBMS_OUTPUT.PUT_LINE(1/n)
  CLOSE work_cur;
END;
/

N.B. I've formatted your code so that it's easier to read (although the particular format I've used isn't the be all and end all of formats!).

ORA-01722: invalid number ORA-06512: at line 17 01722. 00000 - "invalid number"

1 Answers