Hi all.
I'm having this really simple PL/SQL anonymous block that returns the current time in seconds. I get an error when I run it...
So, here's my (rather short) code:
DECLARE
x_seconds_systime CLOB;
BEGIN
SELECT to_clob((to_char(systimestamp, 'HH24')*3600)+(to_char(systimestamp, 'MI')*60))
INTO x_seconds_systime
FROM dual;
DBMS_OUTPUT.PUT_LINE(x_seconds_systime);
:P24_TIME := x_seconds_systime;
END;
If I remove the ":P24_TIME := x_seconds_systime;" part, then it works like a charm. But I can't just remove this, because I need to put the resulting value in this item (I use this code on APEX).
So, if I run it as it is now (and you can test it yourself), then it gives the following error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I assume that the to_char has something to do with this. Probably because the number it has to hold exceeds the limit of the max value a char can have. So,
how do I work around this?
Any help is really appreciated, I'm stuck on this since yesterday morning and I still haven't found it. Thanks.