9 Replies Latest reply: Apr 2, 2013 10:00 AM by BluShadow RSS

    PL/SQL - column value is updated as 0 instead of numeric value

    1000551
      Declare
      v_cnt varchar2(20);
      v_cnd varchar2(20);
      v_total varchar2(20);

      begin

      select count(emp_id) into v_cnt from emp1;
      select count(emp_id) into v_cnd from emp2;
      v_total:=v_cnt+v_cnd;

      dbms_output.put_line('before');
      dbms_output.put_line(v_total);

      update emp3 set total_emp=v_total where dept_no=40;
      commit;

      dbms_output.put_line('after');
      dbms_output.put_line(v_total);

      end;
      In the above program, value for the column total_emp is getting updated as 0 instead of numeric value. However, when I use dbms_output statement to print the value, for both the cases, before and after , I'm getting the numeric value for the variable v_total.

      v_total value is not getting updated to column value in table.

      table column definition for total_emp is varchar2(20).

      Also, I tried to hard code value for total_emp column in the above statement and it worked.

      So, the problem is variable value when used in the update statement it's not getting updated to column.

      Please help me.
        • 1. Re: PL/SQL - column value is updated as 0 instead of numeric value
          S10390
          change the v_total datatype to number and try.
          • 2. Re: PL/SQL - column value is updated as 0 instead of numeric value
            Ramin Hashimzadeh
            first explain please why you use varchar2 if the variables always number...
            Declare
            v_cnt varchar2(20);
            v_cnd varchar2(20);
            v_total varchar2(20);
            • 3. Re: PL/SQL - column value is updated as 0 instead of numeric value
              1000551
              Yeah.

              Now I changed to Number from Varchar2.

              But still the value is updated as 0.

              Could you please suggest me some other options?
              • 4. Re: PL/SQL - column value is updated as 0 instead of numeric value
                Ramin Hashimzadeh
                show
                SELECT * FROM all_triggers
                WHERE table_name = 'EMP3'
                • 5. Re: PL/SQL - column value is updated as 0 instead of numeric value
                  BluShadow
                  Post a reproducable test case for us...
                  SQL> create table emp1 as select * from emp;
                  
                  Table created.
                  
                  SQL> create table emp2 as select * from emp;
                  
                  Table created.
                  
                  SQL> create table emp3 as select * from emp;
                  
                  Table created.
                  
                  SQL> alter table emp3 add (total_emp varchar2(20));
                  
                  Table altered.
                  
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  Declare
                    2    v_cnt varchar2(20);
                    3    v_cnd varchar2(20);
                    4    v_total varchar2(20);
                    5  begin
                    6    select count(empno) into v_cnt from emp1;
                    7    select count(empno) into v_cnd from emp2;
                    8    v_total:=v_cnt+v_cnd;
                    9    dbms_output.put_line('before');
                   10    dbms_output.put_line(v_total);
                   11    update emp3 set total_emp=v_total where deptno=10;
                   12    commit;
                   13    dbms_output.put_line('after');
                   14    dbms_output.put_line(v_total);
                   15* end;
                  SQL> /
                  before
                  28
                  after
                  28
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> select * from emp3;
                  
                       EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO TOTAL_EMP
                  ---------- ---------- --------- ---------- -------------------- ---------- ---------- ---------- --------------------
                        7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
                        7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
                        7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30
                        7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
                        7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
                        7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
                        7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10 28
                        7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                    20
                        7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                    10 28
                        7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
                        7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                    20
                        7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                    30
                        7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                    20
                        7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                    10 28
                  
                  14 rows selected.
                  Works for me.. even with the incorrect datatypes.

                  You demonstrate exactly what you are doing.
                  • 6. Re: PL/SQL - column value is updated as 0 instead of numeric value
                    Ramin Hashimzadeh
                    In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a NUMBER.
                    • 7. Re: PL/SQL - column value is updated as 0 instead of numeric value
                      avish16
                      Apart from the above suggestions of changing varchar2 usage to number ,are you sure you have dept_no= 40 in emp3 table and your where condn is correct with regards to your data in table.
                      what is the o/p of following -

                      select total_emp from emp3 where dept_no=40;
                      • 8. Re: PL/SQL - column value is updated as 0 instead of numeric value
                        stratmo
                        DELETED

                        Edited by: stratmo on Apr 2, 2013 4:58 PM
                        • 9. Re: PL/SQL - column value is updated as 0 instead of numeric value
                          BluShadow
                          avish16 wrote:
                          are you sure you have dept_no= 40 in emp3 table
                          I thought that myself, but the fact the OP states that it's been updated to 0 would indicate that whatever he's updating has been updated with a value....

                          At least that's what it indicates... it could be that the OP has completely missed it. :D