Oracle TruncDate UDF - cannot compile truncdate2

Extensibility
Enthusiast

Oracle TruncDate UDF - cannot compile truncdate2

We have had the Oracle UDFs installed for a number of years.  But we had an issue with our 15.10 database upgrade and SYSLIB had to be dropped and recreated.  This meant we had to reinstall all of the Oracle UDFs.  The only problem we had is with the truncdate2 UDF (the TruncDate UDF installed truncdate1 and truncdate2 UDFs).  Compiling truncdate2 resulted in:

   Warning: 5603 Errors encountered in compiling UDF/XSP/UDM/UDT/JAR.

   truncdate2.c:95: warning: incompatible implicit declaration of built-in function sprintf

(10 instances of this error).

I googled this error and found that #include "< stdio.h>" needed to be included in truncdate2.c.  I added this and am now getting another error:

   Warning: 5603 Errors encountered in compiling UDF/XSP/UDM/UDT/JAR.

   truncdate2.c: 251: error: lvalue required as left operand of assignment

I believe the problem is in line 251 (line 250 if you have not added "#include <stdio.h>"):

   (int)*result = (int) ( (y-1900) * 10000 + m * 100 + d );

which is in the else statement.  I don't know C and cannot tell what is wrong with the statement.  Does anyone have an idea about this?  Or maybe has encountered this issue?

Tags (2)
9 REPLIES
Junior Supporter

Re: Oracle TruncDate UDF - cannot compile truncdate2

Hi

A shoot in the dark but...

It seems that you upgraded the gcc compiler (with the platform upgrade?). gcc 4 compilers are more strict with some programming rules like the ones that are giving you trouble. The original UDF was probably compiled with an older gcc compiler that allowed such programming, but the new one doesn't allow them.

You might amend the original C code to adhere to the gcc 4 compiler rules...

HTH.

Cheers.

Carlos.

Senior Apprentice

Re: Oracle TruncDate UDF - cannot compile truncdate2

Why do you install the Oracle UDFs? Most of them are built-in since TD14.10 and can be found in TD_SYSFNLIB. The functions in TD_SYSFNLIB are more efficient and fully supported. 

I could understand why you need truncts (because that stupid built-in version never returns TIMESTAMPs), but truncdate should return exactly the same.

Of course you should double check if old & new are implemented exactly the same (e.g. there are some differences in the returned VARCHAR sizes). If they are actually used with a qualified name you can easily use a SQL UDF to redirect them to TD_SYSFNLIB.

Enthusiast

Re: Oracle TruncDate UDF - cannot compile truncdate2

Carlos, I think you are correct about the compiler.  The last time we compiled these was back in 2008 and 2009.  I am learning more about C right now.  I think I have identified the problem but am not sure how to fix it.

Dieter, we are using these as their usage is already present in our data warehouse.  Some of the functionality within the Oracle functions is not present within the built-in functions in TD_SYSFNLIB.  Such as date rounding: SEL TRUNC(DATE),'MM';.  This does not work with the built-in TRUNC function.

Senior Apprentice

Re: Oracle TruncDate UDF - cannot compile truncdate2

Hi Cliff,

ROUNDing and TRUNCating DATEs works as expected, but the builtin never return a TIMESTAMP only DATE:

SELECT td_sysfnlib.TRUNC(DATE,'MM');

2016-03-01

SELECT td_sysfnlib.TRUNC(CURRENT_TIMESTAMP(2),'HH');

2016-03-23

Maybe try an Enhancement Request to get them work on Timestamps, too.

Enthusiast

Re: Oracle TruncDate UDF - cannot compile truncdate2

Final update:  after I dropped all of the other trunc* functions, the SEL TRUNC(DATE),'MM'; query does run successfully.  Based on the documentation for the Oracle trunc UDF, the "MM" parameter is supposed to take the month and round up if it is the 16th or later.  However, it isn't doing that - it rounds down.  Same thing with all of the other date functions that are supposed to round up.

And Dieter is correct that the built-in trunc function does not return timestamp or timezone values correctly.

Senior Apprentice

Re: Oracle TruncDate UDF - cannot compile truncdate2

Hi Cliff, 

this is a bug in the documentation (seems to be for ROUND).

As the name implies TRUNC truncates while ROUND rounds :-)

SELECT td_sysfnlib.ROUND(DATE,'MM');

2016-04-01
Enthusiast

Re: Oracle TruncDate UDF - cannot compile truncdate2

Thanks.  I will pursue the timestamp issue with GSC.

Enthusiast

Re: Oracle TruncDate UDF - cannot compile truncdate2

Final, final update:  I also found the ISO year functionality does not seem to be working correctly in the "Oracle" UDF (at least according to what I have learned about ISO year).  The built-in trunc function does seem to return the correct value.

Enthusiast

Re: Oracle TruncDate UDF - cannot compile truncdate2

For the timestamp issue, DR 181569 has been created for an RFC.  No info yet on when this will be fixed.