How to use TO_NUMBER function to update columns of a table Using oracle pl/sql

oracle-11gupdate

Hope you're doing well.
I have a table with this structure :

Table1(Cust_No    Varchar2(50), 
       First_Name Varchar2(50), 
       Last_Name  Varchar2(50), 
       Error_Code number(1), 
       Error_Desc Varchar2(50))

At first , data will be inserted into column Cust_No and I should
Update other columns of the table based on this column.The point is that all cust_No must have number format. For example Cust_No ='aa674' does not have a correct format.
I need to write a query to update the table and set
column First_Name='--' , columnLast_Name = '--',
column Error_Code=1 and column Error_Desc='Incorrect format'
for those Cust_No which has incorrect formats.
I wanted to use TO_NUMBER() function but it gives me invalid number error.
how may I solve this problem?
Thanks in advance

Best Answer

You may use UDF to detect if a value can be treated as a number. Something like

CREATE OR REPLACE FUNCTION check_for_number( check_value IN VARCHAR2 )
RETURN VARCHAR2 DETERMINISTIC
IS
    tmp NUMBER;
BEGIN
    IF check_value IS NULL THEN
        RETURN 'Z';
    ELSE
        tmp := TO_NUMBER( check_value );
        RETURN 'Y';
    END IF;
EXCEPTION
    WHEN value_error THEN
        RETURN 'N';
    WHEN OTHERS THEN
        RETURN '?';
END check_for_number;

with test as ( select 1 id, '123' val from dual union all
               select 2   , '123asd'  from dual union all
               select 3   , 'asd'     from dual union all
               select 4   , NULL      from dual           )
select id, val, check_for_number(val)
from test;

ID  VAL     CHECK_FOR_NUMBER(VAL)
1   123     Y
2   123asd  N
3   asd     N
4   -       Z

Related Solutions

How to execute this procedure in PL/SQL

You can do it with PIVOT operator, but it require hardcoding all the pivoting values.

select * from (select Persnbr, Userfieldcd, value from pivot_test) pivot (max(value) for Userfieldcd in ('Port', 'Vip1'));

Building the query dynamically as your example:

declare
    in_clause varchar2(256);
    sel_query varchar2(256);
    n number := 0;
begin
    for x in (select distinct userfieldcd from persuserfield)
    loop    
        if n <> 0 then 
                in_clause := in_clause || ', ';
        end if;
        in_clause := in_clause ||  '''' || x.userfieldcd || '''';
        n := 1;    
    end loop;
    sel_query := 'select * from (select Persnbr, userfieldcd, value from persuserfield) pivot (max(value) for userfieldcd in ('||in_clause||'));';
    dbms_output.put_line (sel_query);
end;
/

Postgresql – Most efficient way to do many UPDATES in Postgresql

If you don't want to interfere with other activity, the one UPDATE at a time in autocommit mode is very likely the best option. You should probably set synchronous_commit=off in that session (and only that session).

The indexes are going to slow you down, perhaps by a lot depending on your RAM and your IO system. But if the index is necessary for the other actions you don't want to interfere with, then there isn't anything you can do about it.

But since the fid is not yet correctly populated, the index on it is probably not actually useful to the concurrent processes you want avoid interfering with, as they haven't been changed yet to rely on that column being accurate. If that is the case, you can drop that index to gain speed, and build it in bulk later. The same probably applies to the foreign key constraint.

Once that index is gone, your updates can proceed via HOT (Heap Only Tuples) updates provided each block has enough free space. In that case, the updates will not have to do maintenance on the primary key index, either, saving that much more IO. To maximize the likelihood that this will work optimally, it is important that each UPDATE be its own transaction. That way one UPDATE can reuse space freed up by an earlier one.

Also, your WHERE clause should probably be like:

WHERE id=345 and fid is not null;

That way if the script gets interrupted, you can re-run it with minimal damage.

Since you seem to be running this on a test system already, then an EXPLAIN (ANALYZE,BUFFERS) of some of the updates would be helpful, especially with track_io_timing set to on.

Best Answer

Related Solutions

How to execute this procedure in PL/SQL

Postgresql – Most efficient way to do many UPDATES in Postgresql

Related Question