Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Why is this not working

Re: Why is this not working

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 29 Oct 2002 18:23:12 +0300
Message-ID: <apm99j$ppl$1@babylon.agtel.net>


Hmm... Looks like Transact-SQL blindly converted to PL/SQL to me. :) As Jim correctly pointed out, DATEs in Oracle are not strings, and the default format for string representation of a date varies accorting to NLS settings of the database and of the session, so it's not always 'MMDDYYYY' as in SQL Server. But if you will use date as date in your code, be sure to replace all TO_DATE() calls with TO_CHAR() calls, too, so that they look like this:

TO_NUMBER(TO_CHAR(date_var,'YYYY'))

otherwise your code either will not compile or will not work as you expect.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Johnny" <kdankwah_at_aol.com> wrote in message news:732d872a.0210261021.5988b063_at_posting.google.com...

> I am trying to do an insert into a table called time. Could someone
> please tell me if this will do an insert. Also how will I do the
> execute on this procedure. The arguments are startdate and enddate.
> Help.
>
> CREATE OR REPLACE PROCEDURE TIME_PROC
> ( startdate DATE :='01011996',
> enddate DATE :='12311998')
> AS
> incdate DATE;
> recno INTEGER;
> BEGIN
> incdate := startdate;
> recno := 1;
> WHILE incdate <= enddate LOOP
> INSERT INTO time
> (Time_key,
> Year_key,
> Year_id,
> Year_desc,
> Quarter_key,
> Quarter_id,
> Quarter_desc,
> Month_key,
> Month_id,
> Month_desc,
> Date_id,
> Date_desc)
> VALUES( recno,
> recno,
> TO_NUMBER( TO_DATE( incdate, 'YYYY' ) ),
> TO_NUMBER( TO_DATE( incdate, 'YYYY' ) ),
> TO_NUMBER( TO_DATE( incdate, 'Q1' ) ),
> TO_NUMBER( TO_DATE( incdate, 'Q1'||'-'||'YYYY' ) ),
> TO_NUMBER(TO_DATE( incdate, 'Q1'||'-'||'YYYY' ) ),
> TO_NUMBER( TO_DATE( incdate, 'MM' ) ),
> TO_NUMBER( TO_DATE( incdate, 'MM'||'-'||'YY' ) ),
> TO_NUMBER( TO_DATE( incdate, 'MM'||'-'||'YY' ) ),
> TO_DATE( incdate, 'MMDDYYYY' ) ,
> TO_DATE( incdate, 'MMDDYYYY' ) );
> recno := recno + 1;
> incdate := incdate + 1;
> END LOOP;
> COMMIT;
> END time_proc;
> /
>
> Execute time_proc TO_DATE(01012002,09302002)
>
> /
>
> Its not doing the insert.
>
>
> Thanks,
Received on Tue Oct 29 2002 - 09:23:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US