Swapping data of two rows without creating temp fields

dept (DeptName, DeptNo) with some data. I want to swap the all values of DeptName to DeptNo and DeptNo to DeptName.

Yes you can provide the DeptNAMe, DEPTNO are both VARCHAR2 or CHAR. But DEPTNO is of type NUMBER, you cannot SWAP using a single SQL statement.

Now, let us assume that the DeptNAME and DeptNo columns are of type VARCHAR2. However, DeptNO column is VARCHAR2(10) and DeptNAMe is VARCHAR2(100).

First of all DeptNo column needs to be modified to be 100.

i) ALTER TABLE DEPT MODIFY DEPTNO VARCHAR2(100);

ii)  update dept t1
      set deptname = (select deptno from dept2 t2 where t1.deptno = t2.deptno),
           deptno = (select deptname from dept2 t2 where t1.deptno =  t2.deptno);

Now, finally you want the deptname columns to be VARCHAR2(10)

iii) ALTER TABLE DEPT MODIFY DEPTNAME VARCHAR2(10).

If DEPTNO is a NUMBER column, you cannot alter the datatype unless the table is empty; in that case the whole swapping requirement would be moot.