0

I heve a function (use Dbms_sql.Varchar2s) that part of it is below:

str_work(Nvl(str_work.Last, 0) + 1) :=  ' Select ';
str_work(Nvl(str_work.Last, 0) + 1) :=  '  TIP.ID_ITEM,';
str_work(Nvl(str_work.Last, 0) + 1) :=  '  CPVW.ID_FAB,';
str_work(Nvl(str_work.Last, 0) + 1) := '  TO_CHAR(CPVW.QTD, ''9999999'') as QTD';
str_work(Nvl(str_work.Last, 0) + 1) :=  ' from ';
str_work(Nvl(str_work.Last, 0) + 1) :=  '  CAD_PILAR_VW CPVW';

When I execute it everithing is OK but when I change the line from

str_work(Nvl(str_work.Last, 0) + 1) := '  TO_CHAR(CPVW.QTD, ''9999999'') as QTD';

to

str_work(Nvl(str_work.Last, 0) + 1) := '  TO_CHAR(CPVW.QTD, ''99999.99'') as QTD';

I got error invalid number

A table have only 3 values to CPVW.QTD : 100, 200, 300 and the type is NUMBER

I try a lot of "9999999" combination with comma, decimal point and all cases I receive the same error. INVALID NUMBER.

The function only works if the TO_CHAR is ''9999999'' without comma or decimal point.

Can you help me?

user2364211
  • 21
  • 1
  • 2
  • Please show the exact line of code raising the exception. – Jens Krogsboell Nov 09 '14 at 10:36
  • 1
    Are you sure that the problem is with your to_char conversation? Maybe you're treating the converted QTD somewhere else and this raises an exception? Try to use: TO_CHAR(CPVW.QTD, ''99999D99'') – Multisync Nov 09 '14 at 11:19
  • Why are you using `Dbms_sql.Varchar2s` rather than a big text variable? – APC Nov 09 '14 at 15:42
  • I don't understand what are you doing. Your query does not seem to be dynamic. So, use a cursor or an EXECUTE IMMEDIATE statement with bind variables. – Cyryl1972 Nov 11 '14 at 08:53

0 Answers0