Re: Define Const for Date Format

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 13 Jul 2005 09:48:38 -0700
Message-ID: <1121273318.623309.198410@g47g2000cwa.googlegroups.com>

I believe that you can only use such a constant that is declared inside of a package in such a way from within pl/sql. If you want to use it as your date format in sql, then you can create a function that returns it. Please see the demonstration below.

scott_at_ORA92> -- package with constant:
scott_at_ORA92> CREATE OR REPLACE PACKAGE userconst AS 2 date_format VARCHAR2(21) := 'YYYY-MM-DD HH24:MI:SS'; 3 END userconst;
4 /

Package created.

scott_at_ORA92> -- package.constant used in pl/sql:
scott_at_ORA92> SET SERVEROUTPUT ON
scott_at_ORA92> DECLARE

  2 v_date VARCHAR2(21);
  3 BEGIN
  4 SELECT TO_CHAR (SYSDATE, userconst.date_format)   5 INTO v_date
  6 FROM DUAL;
  7 DBMS_OUTPUT.PUT_LINE (v_date);
  8 END;
  9 /
2005-07-13 09:44:06

PL/SQL procedure successfully completed.

scott_at_ORA92> -- package with function:
scott_at_ORA92> CREATE OR REPLACE PACKAGE userconst AS 2 date_format VARCHAR2(21) := 'YYYY-MM-DD HH24:MI:SS'; 3 FUNCTION get_date_format RETURN VARCHAR2; 4 END userconst;
5 /

Package created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE PACKAGE BODY userconst AS   2 FUNCTION get_date_format RETURN VARCHAR2   3 IS
  4 BEGIN
  5 RETURN date_format;
  6 END get_date_format;
  7 END userconst;
  8 /

Package body created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> -- package.function used in sql: scott_at_ORA92> SELECT TO_CHAR (SYSDATE, userconst.get_date_format) FROM DUAL
2 /

TO_CHAR(SYSDATE,USERCONST.GET_DATE_FORMAT)

2005-07-13 09:44:07

scott_at_ORA92> Received on Wed Jul 13 2005 - 11:48:38 CDT