CREATE OR REPLACE FUNCTION problem

Prev: Hollywood NetBook™ Webshop
Next: spool command in sql plus

From: Chepurnykh on 30 Jun 2010 06:59

---

Hello,

I need a function that converts date_to_unixtime.
I found a good select example in the post #4 by Mark Schrijver:
http://jehiah.cz/archive/oracle-date-to-unix-timestamp

I modified it a little and tried to make a function out of this
select. There is an error in the function, I can not figure it out.
Please help.

<pre>
CREATE OR REPLACE FUNCTION CURRENT_UNIXTIME_UTC_SEC RETURN number IS
xt number;
BEGIN
SELECT d * (24*60*60) + h * (60*60) + (m * 60) + s as unix_time
FROM (
SELECT to_number(rtrim(substr(dt, 2, instr(dt, ' ')-1))) d,
to_number(substr(dt, instr(dt, ' ')+1, 2)) h,
to_number(substr(dt, instr(dt, ':')+1, 2)) m,
to_number(substr(dt, instr(dt, '.')-2, 2)) s,
dt
FROM ( SELECT to_char(dt) dt
FROM (
SELECT (
sys_extract_utc(current_timestamp) -
TO_TIMESTAMP('01.01.1970','DD.MM.YYYY')) dt FROM dual
))) INTO xt;
RETURN xt;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20777,'An error encountered - '||
SQLCODE||' ERROR: '||SQLERRM);
END;
</pre>

Thank you!

From: gazzag on 30 Jun 2010 07:26

---

On 30 June, 11:59, Chepurnykh <chepurn...(a)gmail.com> wrote:
> Hello,
>
> I need a function that converts date_to_unixtime.
> I found a good select example in the post #4 by Mark Schrijver:http://jehiah.cz/archive/oracle-date-to-unix-timestamp
>
> I modified it a little and tried to make a function out of this
> select. There is an error in the function, I can not figure it out.
> Please help.
>
> <pre>
> CREATE OR REPLACE FUNCTION CURRENT_UNIXTIME_UTC_SEC RETURN number IS
>    xt number;
> BEGIN
>   SELECT d * (24*60*60) + h * (60*60) + (m * 60) + s  as unix_time
> FROM (
> SELECT to_number(rtrim(substr(dt, 2, instr(dt, ' ')-1))) d,
>     to_number(substr(dt, instr(dt, ' ')+1, 2)) h,
>     to_number(substr(dt, instr(dt, ':')+1, 2)) m,
>     to_number(substr(dt, instr(dt, '.')-2, 2)) s,
>     dt
> FROM ( SELECT to_char(dt) dt
> FROM (
> SELECT (
>     sys_extract_utc(current_timestamp) -
> TO_TIMESTAMP('01.01.1970','DD.MM.YYYY')) dt FROM dual
>     ))) INTO xt;
> RETURN xt;
> EXCEPTION
> WHEN OTHERS THEN
>       raise_application_error(-20777,'An error encountered - '||
> SQLCODE||' ERROR: '||SQLERRM);
> END;
> </pre>
>
> Thank you!

What error are you receiving?

HTH
-g

From: Chepurnykh on 30 Jun 2010 07:33

---

Thank you :-)
Just fixed it. INTO was in a wrong place.

CREATE OR REPLACE FUNCTION CURRENT_UNIXTIME_UTC_SEC RETURN number IS
xt number;
BEGIN
SELECT (d * (24*60*60) + h * (60*60) + (m * 60) + s) as unix_time
into xt
FROM (
SELECT to_number(rtrim(substr(dt, 2, instr(dt, ' ')-1))) d,
to_number(substr(dt, instr(dt, ' ')+1, 2)) h,
to_number(substr(dt, instr(dt, ':')+1, 2)) m,
to_number(substr(dt, instr(dt, '.')-2, 2)) s,
dt
FROM ( SELECT to_char(dt) dt
FROM (
SELECT (
sys_extract_utc(current_timestamp) -
TO_TIMESTAMP('01.01.1970','DD.MM.YYYY')) dt FROM dual
)));
RETURN xt;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20777,'An error encountered - '||
SQLCODE||' ERROR: '||SQLERRM);
END;


On 30 июн, 15:26, gazzag <gar...(a)jamms.org> wrote:
> What error are you receiving?
>
> HTH
> -g

 | 
Pages: 1
Prev: Hollywood NetBook™ Webshop
Next: spool command in sql plus