Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Olaf.

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

Answered by: Chris Saxon - Last updated: July 08, 2020 - 6:43 am UTC

Category: SQL - Version: 12.2.0.1.0

Viewed 100+ 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 

and you rated our response

  (1 rating)

Reviews

no need for PL/SQL or WITH clause

July 08, 2020 - 1:10 am UTC

Reviewer: Kilson Araujo from Santos, SP Brazil

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

Followup  

July 08, 2020 - 6:43 am UTC

Good stuff.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.