Hello Tom,
I have a question about inline functions.
The following statement runs without problems (sorry for the German names of the functions and variables). The function is quite simple and subtracts 12 months from a reference date sttag until it is smaller than a comparison day btag.
with
function zeitpunkt (sttag in timestamp, btag in timestamp) return timestamp is
v_zeitpunkt timestamp;
begin
v_zeitpunkt := sttag;
while v_zeitpunkt > btag loop
v_zeitpunkt := add_months_ts(v_zeitpunkt,-12);
end loop;
return v_zeitpunkt;
end;
select to_timestamp('01.03.2020','DD.MM.YYYY') stichtag,
zeitpunkt(to_timestamp('01.03.2020','DD.MM.YYYY'), to_timestamp('11.07.1961','DD.MM.YYYY'))
from dual
; STICHTAG ZEITPUNKT(TO_TIMESTAMP('01.03
----------------------------- -----------------------------
01.03.2020 00:00:00,000000000 01.03.1961 00:00:00,000000000
But if I put a CREATE TABLE (or INSERT INTO) around this statement, I get several error messages e.g. ORA-00933 or ORA-06550. Is it just a syntax error or is it semantically impossible to create a table this way?
create table z_tariferstattungen_flexi as
with
function zeitpunkt (sttag in timestamp, btag in timestamp) return timestamp is
v_zeitpunkt timestamp;
begin
v_zeitpunkt := sttag;
while v_zeitpunkt > btag loop
v_zeitpunkt := add_months_ts(v_zeitpunkt,-12);
end loop;
return v_zeitpunkt;
end;
select to_timestamp('01.03.2020','DD.MM.YYYY') stichtag,
zeitpunkt(to_timestamp('01.03.2020','DD.MM.YYYY'), to_timestamp('11.07.1961','DD.MM.YYYY'))
from dual
; Fehlerbericht -
ORA-00933: SQL-Befehl wurde nicht korrekt beendet
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
many greetings
Olaf
If you use inline PL/SQL functions in anything other than a top-level SELECT statement, you need the WITH_PLSQL hint.
For example:
create table z_tariferstattungen_flexi as
select /*+ WITH_PLSQL */* from (
with
function zeitpunkt (sttag in timestamp, btag in timestamp) return timestamp is
v_zeitpunkt timestamp;
begin
v_zeitpunkt := sttag;
while v_zeitpunkt > btag loop
v_zeitpunkt := add_months(v_zeitpunkt,-12);
end loop;
return v_zeitpunkt;
end;
select to_timestamp('01.03.2020','DD.MM.YYYY') stichtag,
zeitpunkt(to_timestamp('01.03.2020','DD.MM.YYYY'), to_timestamp('11.07.1961','DD.MM.YYYY'))
from dual
);
/
insert /*+ WITH_PLSQL */into z_tariferstattungen_flexi
with
function zeitpunkt (sttag in timestamp, btag in timestamp) return timestamp is
v_zeitpunkt timestamp;
begin
v_zeitpunkt := sttag;
while v_zeitpunkt > btag loop
v_zeitpunkt := add_months(v_zeitpunkt,-12);
end loop;
return v_zeitpunkt;
end;
select to_timestamp('01.03.2020','DD.MM.YYYY') stichtag,
zeitpunkt(to_timestamp('01.03.2020','DD.MM.YYYY'), to_timestamp('11.07.1961','DD.MM.YYYY'))
from dual ;
/
select * from z_tariferstattungen_flexi;
STICHTAG ZEITPUNKT(TO_TIMESTAMP('01.03.2020','DD.MM.YYYY'),TO_TIMESTAMP('11.07.1961','DD.MM.YYYY'))
01-MAR-2020 00.00.00.000000000 01-MAR-1961 00.00.00.000000000
01-MAR-2020 00.00.00.000000000 01-MAR-1961 00.00.00.000000000