Hi,

Following EXAMPLE proceudre calls TO_CHAR function which is given below.

CREATE PROCEDURE EXAMPLE()
LANGUAGE SQL
BEGIN ATOMIC
DECLARE VAR VARCHAR(254) DEFUALT ' ';
SET VAR = TO_CHAR(TIMESTAMP('20020202182727'),'DDMMYYHHMMSS' )
END
@

TO_CHAR function:

CREATE FUNCTION TO_CHAR(D TIMESTAMP, FMT VARCHAR(50))
.........
......
..........
END
@

On compiling the EXAMPLE procedure, following error was thrown,

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.TO_CHAR" is incorrect. LINE NUMBER=6. SQLSTATE=42815.

Can anyone give a solution for this?.

Thanks,
Kamesh.

A function by name SYSIBM.TO_CHAR is provided as part of the db2 installation .. Looks like, SYSIBM comes first in your function path ...
VALUES(FUNCTION PATH)

If you want DB2 to take your functions before SYSIBM, then use

SET PATH = SATHYARAM, SYSIBM

But, I would suggest that you change the name of the function you create to avoid any confusions ...

HTH

Sathyaram

Hi Sathya,

You spotted exactly. Thanks for your immediate solution. I have changed function name and it worked. But, other solution - setting PATH as USERNAME(I guess) had no effect.

TA
Kamesh.