Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Olaf.

Asked: July 06, 2020 - 8:07 am UTC

Last updated: July 08, 2020 - 6:43 am UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

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

and we said...

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 

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

no need for PL/SQL or WITH clause

Kilson Araujo, July 08, 2020 - 1:10 am UTC

Although it's nice to know about this WITH_PLSQL hint, it seems this problem doesn't need a PL/SQL nor a while loop within a function.

This can be solved using delivered sql functions:

SELECT
to_timestamp('01.03.2020', 'DD.MM.YYYY') stichtag,
add_months(to_timestamp('01.03.2020', 'DD.MM.YYYY'),
least(ceil(months_between(to_timestamp('01.03.2020', 'DD.MM.YYYY'),
to_timestamp('11.07.1961', 'DD.MM.YYYY')) / 12) * - 12, 0)) zeitpunkt
FROM
dual

STICHTAG ZEITPUNKT
------------------------------- -------------------
01-MAR-20 12.00.00.000000000 AM 1961-03-01 00:00:00

The least() function is meant to add 0 months in case btag is greater than sttag.
Connor McDonald
July 08, 2020 - 6:43 am UTC

Good stuff.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.