Re: date problem in migrating Oracle Forms&Reports 4 to Oracle Forms&Reports 6

From: Frank <fvanbortel_at_netscape.net>
Date: Tue, 07 Jan 2003 20:47:21 +0100
Message-ID: <3E1B2EC9.6030009_at_netscape.net>

Christodoulou Demetris wrote:
>>PROCEDURE OU550_S12(p_start_date1 IN date,
>> p_last_date1 IN date,
>> P_RC OUT NUMBER) IS
>>CURSOR c_acco IS
>> SELECT acco_trans_number,
>> entry_date_time,
>> payment_method,
>> fee_paid,
>> adjustment_amount,
>> refund_amount,
>> transaction_type,
>> operator_id,
>> receipt_no,
>> operator_id,
>> terminal_id
>> FROM accounts_transactions
>> WHERE entry_date_time BETWEEN p_start_date AND p_last_date
>> ORDER BY receipt_no ASC;
>>r_acco c_acco%rowtype;
>> p_start_date := to_date('01/01/1997','DD/MM/YYYY');
>> p_last_date := to_date('31/12/2002','DD/MM/YYYY');
>>
>>for r_acco in c_acco loop
>>dbms_output.putline('Found: '||to_char(r_acco.acco_trans_number)||
>> ' entered: '||to_char(r_acco.entry_date_time,'DDMMYYYY HH:MI');
>> END LOOP;
>>
>>Next, are the date fileds in the table really date fileds?
>>Could you post the relevant partion or all of the table description?
>>
>>Frank

> 
> 
> 
> Hi Frank
> I tried to execute the above code but since this is a stored procedure
> the "dbms_output.putline" does not work.
> 

Oops - typo. SHould have been put_line (you could have spotted this; describe dbms_output)
Also, you need to enable the session to read the output: SQL> set serveroutput on

> The above code when defined inside a program unit (ie client side as
> you said) and executed from there it retrieves the expected results.
> Also this takes place also when executed from the sql plus
> environment.!!
> 
> However, at the server side (ie stored procedure) i get no rows.
> 
> You can find the table definition below:
> 
> SQL> desc accounts_transactions
>  Name                            Null?    Type
>  ------------------------------- -------- ----
>  ACCO_TRANS_NUMBER               NOT NULL NUMBER(9)
>  ENTRY_DATE_TIME                 NOT NULL DATE
>  OPERATOR_ID                     NOT NULL VARCHAR2(8)
>  TERMINAL_ID                     NOT NULL NUMBER(4)
>  FEE_PAID                                 NUMBER(6,2)
>  PAYMENT_METHOD                           NUMBER(1)
>  TIMESTAMP                       NOT NULL DATE
>  TRANSACTION_TYPE                NOT NULL NUMBER(3)
>  ADJUSTMENT_AMOUNT                        NUMBER(7,2)
>  BUDGET_CODE                              NUMBER(2)
>  RECONCILED                      NOT NULL NUMBER(1)
>  REFUND_AMOUNT                            NUMBER(7,2)
>  RECEIPT_NO                               NUMBER(9)
>  DOCUMENT_TYPE                            NUMBER(1)
>  SERIAL_NO                                NUMBER(9)
> 
> 

>>Next, are the date fileds in the table really date fileds?
>>Could you post the relevant partion or all of the table description?

> 
> 
> 
> Thanks again for your help!!!
> I think that stored procedures hate me :)
> 
> Demetris

guess you need the format:
- in the database default (ie DD/MON/RR) or
- tell the procedure what format to expect.

That would be:
- p_start_date := to_date('01/JAN/97','DD/MON/RR');

    p_last_date := to_date('31/DEC/02','DD/MON/RR'); or
- WHERE to_char(entry_date_time,'DD/MM/YYYY')

   BETWEEN p_start_date AND p_last_date

Your client does report the correct output, as I suspect it has DD/MON/YYYY as default format - SQL*Net will do the transformations to/from DD/MON/YYYY and DD/MON/RR formats.

Frank Received on Tue Jan 07 2003 - 20:47:21 CET