Oracle PL/SQL: numeric or value error: character to number conversion error

Question

i am having following pl/sql block i am getting error of PL/SQL: numeric or value error: character to number conversion error but i dont find any such conversion problem please help

Oracle Pl/Sql Block

declare
Menu_Item_ID  integer :=80;
SelectedColumns  varchar2(800):='';


    IsBlank Number(2);
    V_Column_Name varchar2(150);
    V_Column_Name_Value Varchar2(200);
    V_Url_Column_Name varchar2(100);
SelectedColumnsTemp varchar(8000):=SelectedColumns;
cursor c1 is
Select  Column_Name,Column_Name_Value,Url_Column_Name
    From    COM_MST_REPORT_DEFAULT_COLUMN
    Where   Link_ID = Menu_Item_ID;
Begin

    if SelectedColumnsTemp = ''
  then
            IsBlank := 1;
    Else
            IsBlank := 0;
    End if;
FOR DefaultColumns in c1
LOOP
            V_Column_Name := '';
            V_Column_Name_Value := '';
            V_Url_Column_Name := '';    
    V_Column_Name := '"' + TO_CHAR(DefaultColumns.Column_Name) + '"';
    V_Column_Name_Value := TO_CHAR(DefaultColumns.Column_Name_Value);
    V_Url_Column_Name := TO_CHAR(DefaultColumns.Url_Column_Name);
            if(nvl(V_Url_Column_Name,'') <> '')
                then
                    V_Column_Name_Value  := V_Url_Column_Name  + ' as ' + V_Column_Name; 
            Else
                    V_Column_Name_Value :=  V_Column_Name_Value  + ' as ' + V_Column_Name; 
                End if;
            if IsBlank = 0
                then
                    SelectedColumnsTemp := replace(SelectedColumnsTemp,V_Column_Name,V_Column_Name_Value);
            Else
                            SelectedColumnsTemp := SelectedColumnsTemp + V_Column_Name_Value +  ',';
                End if;

END LOOP;
    DBMS_OUTPUT.PUT_LINE(SelectedColumnsTemp);
End;

What are the types of all the columns and variables? Could it be you are trying to join text and numbers somewhere? — shree.pat18
– shree.pat18, Commented Jul 16, 2014 at 10:32

Wernfried Domscheit · Accepted Answer · 2014-07-16 13:08:46Z

2

In Oracle strings are concatenated by || (or little-known by CONCAT()) not +

e.g.

V_Column_Name := '"' || TO_CHAR(DefaultColumns.Column_Name) || '"';

instead of

V_Column_Name := '"' + TO_CHAR(DefaultColumns.Column_Name) + '"';

edited Jul 16, 2014 at 13:08

answered Jul 16, 2014 at 11:00

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

"In Oracle string are concatenated by ||" - actually that should be "In SQL strings are concatenated by ||". This is not Oracle specific. The SQL standard defined that operator nearly 30 years ago.

Collectives™ on Stack Overflow

Oracle PL/SQL: numeric or value error: character to number conversion error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related