Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Stephen.

Asked: January 09, 2017 - 8:40 pm UTC

Last updated: January 11, 2017 - 6:09 am UTC

Version: Oracle 12.10.0.03

Viewed 1000+ times

You Asked

Trying to use an operator to create an "overloaded" PL/SQL function.

The goal of the function is to accept 3 different formats (Timestamp with timezone, Timestamp, and Date) and return just a timestamp withzone.

I am getting different errors such as

Error at line 29
ORA-00907: missing right parenthesis

Any help in resolving this error or another method of creating an "overloaded" function would be appreciated. 

CREATE OR REPLACE FUNCTION MSPDS.TO_MSPDS_TIME_TZ (v_time TIMESTAMP WITH TIME ZONE)
    RETURN TIMESTAMP WITH TIME ZONE
IS
BEGIN
    RETURN v_time;
END;



CREATE OR REPLACE FUNCTION MSPDS.TO_MSPDS_TIME_T (v_time TIMESTAMP)
    RETURN TIMESTAMP WITH TIME ZONE
IS
BEGIN
    RETURN FROM_TZ (v_time, 'US/Pacific');
END;



CREATE OR REPLACE FUNCTION MSPDS.TO_MSPDS_TIME_D (v_time DATE)
    RETURN TIMESTAMP WITH TIME ZONE
IS
BEGIN
    RETURN FROM_TZ (v_time, 'US/Pacific');
END;


CREATE OR REPLACE OPERATOR TO_MSPDS_TIME BINDING
    (TIMESTAMP WITH TIME ZONE)
        RETURN TIMESTAMP WITH TIME ZONE
        USING TO_MSPDS_TIME_TZ,
    (TIMESTAMP)
        RETURN TIMESTAMP WITH TIME ZONE
        USING TO_MSPDS_TIME_T,
    (DATE)
        RETURN TIMESTAMP WITH TIME ZONE
        USING TO_MSPDS_TIME_D;


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/eesuz09xia0usq6nj6elffunu

and Connor said...

Looks like its getting confused by the spaces in the data type names. Hell...I know *I* always do :-)

See if this works for you

SQL> CREATE OR REPLACE FUNCTION TO_MSPDS_TIME_TZ (v_time TIMESTAMP WITH TIME ZONE)
  2      RETURN TIMESTAMP WITH TIME ZONE
  3  IS
  4  BEGIN
  5      RETURN v_time;
  6  END;
  7  /

Function created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION TO_MSPDS_TIME_T (v_time TIMESTAMP)
  2      RETURN TIMESTAMP WITH TIME ZONE
  3  IS
  4  BEGIN
  5      RETURN FROM_TZ (v_time, 'US/Pacific');
  6  END;
  7  /

Function created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION TO_MSPDS_TIME_D (v_time DATE)
  2      RETURN TIMESTAMP WITH TIME ZONE
  3  IS
  4  BEGIN
  5      RETURN FROM_TZ (v_time, 'US/Pacific');
  6  END;
  7  /

Function created.

SQL>
SQL>
SQL> CREATE OR REPLACE OPERATOR TO_MSPDS_TIME BINDING
  2      (STANDARD."TIMESTAMP WITH TIME ZONE")
  3          RETURN STANDARD."TIMESTAMP WITH TIME ZONE"
  4          USING TO_MSPDS_TIME_TZ,
  5      (TIMESTAMP)
  6          RETURN STANDARD."TIMESTAMP WITH TIME ZONE"
  7          USING TO_MSPDS_TIME_T,
  8      (DATE)
  9          RETURN STANDARD."TIMESTAMP WITH TIME ZONE"
 10          USING TO_MSPDS_TIME_D;

Operator created.


Rating

  (1 rating)

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

Comments

A reader, January 10, 2017 - 7:13 pm UTC

Thanks! that worked great
Connor McDonald
January 11, 2017 - 6:09 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library