Re: Define Const for Date Format

From: <andrewst_at_onetel.com>
Date: 13 Jul 2005 04:46:54 -0700
Message-ID: <1121255214.691423.218630@g47g2000cwa.googlegroups.com>

Björn Wächter wrote:
> Hi,
>
> I'm using TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
> very often. So if I want to change the format I a have to
> change it very often. What is the best way to save the format
> at only one place?
> I tried:
>
> CREATE OR REPLACE PACKAGE userconst AS
>
> date_format CHAR(21) := 'YYYY-MM-DD HH24:MI:SS';
>
> END userconst;
>
> But when I try to use it I get an error:
>
> SELECT TO_CHAR(SYSDATE,userconst.date_format) FROM DUAL;
>
>
> ORA-06553: PLS-221: 'DATE_FORMAT' is not a procedure or is undefined

Yes, this is a restriction. You can get around it using a function like this:

SQL> CREATE OR REPLACE PACKAGE userconst AS

  2     k_date_format  CONSTANT CHAR(21) :=  'YYYY-MM-DD HH24:MI:SS';
  3     FUNCTION date_format RETURN VARCHAR2;

  4 END userconst;
  5 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY userconst AS

  2     FUNCTION date_format RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN k_date_format;
  5     END;

  6 END;
  7 /

Package body created.

SQL> SELECT TO_CHAR(SYSDATE,userconst.date_format) FROM DUAL;

TO_CHAR(SYSDATE,USERCONST.DATE_FORMAT)

---

2005-07-13 12:39:37

Of course, now you may as well dispense with the call to TO_CHAR as well:

SQL> CREATE OR REPLACE PACKAGE userpkg AS

  2     k_date_format CONSTANT CHAR(21) :=  'YYYY-MM-DD HH24:MI:SS';
  3     FUNCTION format_date (p_date IN DATE) RETURN VARCHAR2;

  4 END;
  5 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY userpkg AS

  2     FUNCTION format_date (p_date IN DATE) RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN TO_CHAR(p_date,k_date_format);
  5     END;

  6 END;
  7 /

Package body created.

SQL> SELECT userpkg.format_date(SYSDATE) FROM DUAL;

USERPKG.FORMAT_DATE(SYSDATE)

---

2005-07-13 12:42:28 Received on Wed Jul 13 2005 - 06:46:54 CDT