Re: Question about DATE conversion in Stored Procedure

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: 2000/04/14
Message-ID: <38F77410.CBC19C7B@0800-einwahl.de>#1/1

I would not mess with the NLS_DATE_FORMAT setting. I would use to_char () instead.

For finding out what has which type I can offer you a small package for free. Its usage looks like

SQL> select findtype.try (lpad ('xyz', 3)) from dual;

FINDTYPE.TRY(LPAD('XYZ',3))

---

VARCHAR2 SQL> select findtype.try ('x') from dual;

FINDTYPE.TRY('X')

---

VARCHAR2 SQL> select findtype.try (55) from dual;

FINDTYPE.TRY(55)

---

NUMBER You should extend it to all types that can occur in your environment (raw, etc.). The package is simple:

create or replace package findtype is

function try (x in varchar2) return varchar2;
function try (x in number) return varchar2;
function try (x in date) return varchar2;

end findtype;
/

create or replace package body findtype is

d_a32767 varchar2 (32767);
subtype t_a32767 is d_a32767%type;

function try (x in varchar2) return varchar2 is

        y t_a32767:= x || null;
begin

        return 'VARCHAR2';
exception

	when value_error then
	return null;

end try;

function try (x in number) return varchar2 is

        y number:= x + 0;
begin

        return 'NUMBER';
exception

	when value_error then
	return null;

end try;

function try (x in date) return varchar2 is

        y number:= sysdate - x;
begin

        return 'DATE';
exception

	when value_error then
	return null;

end try;

end findtype;
/

Martin

John Thomas wrote:
>
> I have a stored procedure which is using dynamic SQL to retrieve data from
> any table and output it in character format. Right now, DATE types are
> implicitly converted to VARCHAR2 using the NLS_DATE_FORMAT format. I need
> to format the dates differently. So, can I:
>
> a) Change the NLS_DATE_FORMAT setting within the scope of my stored
> procedure
>
> or
>
> b) detect that a column is of DATE type?
>
> This is PL/SQL stored procedure on Oracle 8, btw.
>
> Thanks,
>
> John
> --
> ***
> *** remove the vegetable from my e-mail address to send me mail
> ***
  Received on Fri Apr 14 2000 - 00:00:00 CDT