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

> 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.

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 Received on Tue Jan 07 2003 - 11:29:25 CET