CODEWITHSUNDEEP
oracle-database
Dheeraj Yadav
Dheeraj Yadav

Reputation: 130

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

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;

Upvotes: 1

Views: 5843

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

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) + '"';

Upvotes: 2

Related Questions