OraFAQ Forum: Forms » problem in calling procedure from a form

Home » Developer & Programmer » Forms » problem in calling procedure from a form (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem in calling procedure from a form [message #409197]

Sat, 20 June 2009 04:47

emadnabil
Messages: 179
Registered: August 2007

Senior Member

Hii all

i have a strange problem
i created a procedure on the database and call it from the form

when i call this procedure from the database no problem happens and it execute succefully

when i execute it from the form (button pressing call the procedure) i got those errors

ORA-01858 :a non numeric charcter was found where a numeric was expected ora-02063: preceding line from sek_zh2 ora-06512 at "arask.last_zahraa2_sek", line 7
ora-06512: at "arask.last_zahraa2_sek", line 55

"sek_zh2": is a database link used in the procedure
"arask.last_zahraa2_sek": "last_zahraa2_sek" is the name of the procedure on the databse user "arask"

thanks

[Updated on: Sat, 20 June 2009 10:09] by Moderator

Report message to a moderator

Re: problem in calling procedure from a form [message #409217 is a reply to message #409197]

Sat, 20 June 2009 10:20

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Without having a look at your code, we can't help much.

By
Vamsi

Report message to a moderator

Re: problem in calling procedure from a form [message #409241 is a reply to message #409197]

Sun, 21 June 2009 01:13

emadnabil
Messages: 179
Registered: August 2007

Senior Member

Here is the is procedure

create or replace procedure LAST_ZAHRAA2_SEK(P_DATE_FROM DATE , P_DATE_TO DATE) is
s_date date := sysdate;
vWEIGHT_FLAG varchar2(1);
cursor headers is
SELECT H.*
 FROM HEADER@sek_zh2.us.oracle.com H,SEK_JTF_RS_SALESREPS J
WHERE
--and  h.AR_Interfaced is null
J.ENABLED_FLAG='Y'
AND J.CUSTOMER_ID IS NOT NULL
AND j.subinventory_code = H.subinventory_code
and H.costcenter_code = j.costcenter_code
and H.costcenter_code IN (1442)
--AND H.RETURNS_NUMBER IN (498,499)
and 'ZH2-'||'09-'||H.ACTIVITY_ID||'-'||h.returns_number
NOT IN (select t.trx_number from ra_customer_trx_all t)
AND RETURNS_DATE BETWEEN P_DATE_FROM AND P_DATE_TO
--AND H.returns_number ='6052'
--AND RETURNS_DATE >='01-JUN-2009'
;

cursor lines(header_id number) is
  select * from trx_lines@sek_zh2.us.oracle.com
  where trx_header_id = header_id;

--user_loged varchar2(240);
books NUMBER(10):=96;
--sales number(10);
prim_header number;
item number;
des varchar2(240);
price number;
stax number;
atax number;
uom varchar2(3);
line_no number:=0;
cust number(10);
prim_line number;
salesrep number;
tot_amount number;
div number;
COST_CEN VARCHAR2(25);
TTAX_AMOUNT  NUMBER;

begin

For r in headers loop
--message('loop header');
 BEGIN
 select NVL(set_of_books_id,96) into books from HR_OPERATING_UNITS h
  where
  h.organization_id=768
  and  NVL(inv_map,19) =r.ACTIVITY_ID;
 EXCEPTION WHEN OTHERS THEN NULL;
 END;
 --message ('One');
 --select NVL(SALESREP_ID,0) into sales from JTF_RS_SALESREPS where NVL(inv_map,0) = r.SALESPER_ID;
 --message ('Two');

 --select NVL(Customer_ID,0) into cust from jtf_rs_salesreps where salesper_id = r.SALESPER_ID;

select j.customer_id,j.salesper_id,j.costcenter_code into cust,salesrep,cost_cen
from SEK_jtf_rs_salesreps j,salespersons@sek_zh2.us.oracle.com s
where j.subinventory_code = s.subinventory_code
AND J.ENABLED_FLAG='Y'
AND S.ENABLED_FLAG='Y'
and s.costcenter_code = j.costcenter_code
and j.costcenter_code IN (1442)
and S.salesper_id = r.SALESPER_ID
;

 --message ('Three');
 tot_amount :=0;
 TtAX_amount :=0;
 select cust_trx_id_seq.nextval into prim_header from dual;
 insert into ra_customer_trx_all
 (customer_trx_id,
  last_update_date,
  last_updated_by,
  creation_date,
  created_by,
  trx_number,
  cust_trx_type_id,
  trx_date,
  set_of_books_id,
  batch_source_id,
  sold_to_customer_id,
  bill_to_customer_id,
  ship_to_customer_id,
  primary_salesrep_id,
  invoice_currency_code,
  created_from,
  org_id,
  exported_flag,
  gl_date,
  location,
  COMPLETE_FLAG,
  CUSTOMER_REFERENCE,
  percent_flag,
  upload_flag
--  COST_CEN,
 -- WAREHOUSE
  )
 values
 (prim_header,
  S_date,
  1027,
  S_date,
  1027,
 'ZH2-'||'09-'||R.ACTIVITY_ID||'-'||R.returns_number ,
  1,
  r.RETURNS_DATE,
  books,
  1,
  cust,
  cust,
  cust,
  salesrep,
  'EGP',
  'Inventory Offline Integration',
   768,
  'N',
   r.RETURNS_DATE,
  'Seclam',
  'Y',
   r.issue_number,
   'N',
   'Y'
--   COST_CEN,
 --  'ZH2'
   );
begin
   commit;
exception
	when others then
   null;
end;
 line_no:= 0;
for n in lines(r.Trx_Header_Id) loop
--message('loop lines');
  if nvl((n.p_qty-n.rp_qty),0)>0 then
   select NVL(inventory_item_id,0),NVL(DESCRIPTION,'NO ITEM'),NVL(retail_price_list,0),NVL(S_TAX,0),NVL(A_TAX,0),NVL(PRIMARY_UOM_CODE,'OOO'),NVl(uom_mul1,0)
          into item,des,price,stax,atax,uom,div
   from SEK_mtl_system_items where SEGMENT1 = n.item_code;
   --message ('Four');
  line_no:=line_no+1;
  select cust_trx_line_id_seq.nextval into prim_line from dual;
   insert into ra_customer_trx_lines_all
    (
     customer_trx_line_id,
     last_update_date,
     last_updated_by,
     creation_date,
     created_by,
     customer_trx_id,
     line_number,
     set_of_books_id,
     inventory_item_id,
     description,
     quantity_ordered,
     quantity_credited,
     quantity_invoiced,
     unit_standard_price,
     unit_selling_price,
     line_type,
     extended_amount,
     revenue_amount,
     uom_code,
     org_id,
     a_tax,
     s_tax
     )
     values
    (
     prim_line,
     S_date,
     1027,
     S_date,
     1027,
     prim_header,
     line_no,
     books,
     item,
     des,
     nvl((nvl(n.p_qty,0)-nvl(n.rp_qty,0))/div,0),
    nvl((nvl(n.p_qty,0)-nvl(n.rp_qty,0))/div,0),
     nvl((nvl(n.p_qty,0)-nvl(n.rp_qty,0))/div,0),
     NVL(N.price,0),
     0,
     'Incentive',
     0,
     0,
     uom,
     468,
     0,
     0
    );
  end if;

 if nvl((n.s_qty-n.rs_qty),0)>0 then
  select NVL(inventory_item_id,0),NVL(description,'NO ITEM'),NVL(retail_price_list,0),NVL(S_TAX,0),NVL(A_TAX,0),NVL(PRIMARY_UOM_CODE,'OOO'),NVl(uom_mul1,0)
          into item,des,price,stax,atax,uom,div
   from SEK_mtl_system_items where SEGMENT1 = n.item_code;
   --message ('Five');
   
   SELECT NVL(WEIGHT_FLAG,'N') INTO vWEIGHT_FLAG
   FROM ITEMS@sek_zh2.us.oracle.com
   WHERE ITEM_CODE =n.item_code;
   
  line_no:=line_no+1;
  insert into ra_customer_trx_lines_all
   (
    customer_trx_line_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    customer_trx_id,
    line_number,
    set_of_books_id,
    inventory_item_id,
    description,
    quantity_ordered,
    quantity_credited,
    quantity_invoiced,
    unit_standard_price,
    unit_selling_price,
    line_type,
    extended_amount,
    revenue_amount,
    uom_code,
    org_id,
    a_tax,
    s_tax
    )
    values
    (
    CUST_TRX_LINE_ID_SEQ.nextval,
    S_date,
    1027,
    S_date,
    1027,
    prim_header,
    line_no,
    books,
    item,
    des,
    DECODE(vWEIGHT_FLAG,'Y',nvl((nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0))/div,0),nvl((nvl(n.s_qty,0)-nvl(n.rs_qty,0))/div,0)),
    DECODE(vWEIGHT_FLAG,'Y',nvl((nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0))/div,0),nvl((nvl(n.s_qty,0)-nvl(n.rs_qty,0))/div,0)),
    DECODE(vWEIGHT_FLAG,'Y',nvl((nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0))/div,0),nvl((nvl(n.s_qty,0)-nvl(n.rs_qty,0))/div,0)),
    --nvl((n.s_qty-n.rs_qty)/div,0),
    --nvl((n.s_qty-n.rs_qty)/div,0),
    NVL(N.price,0),
    NVL(N.price,0),
    'Sales',
    nvl((atax * (DECODE(vWEIGHT_FLAG,'Y',(nvl(n.WEIGHT,0) - nvl(n.R_WEIGHT,0)),(nvl(n.s_qty,0) - nvl(n.rs_qty,0)))/div) * NVL(N.price,0) )/100+(NVL(N.price,0)*DECODE(vWEIGHT_FLAG,'Y',(nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0)),(nvl(n.s_qty,0)-nvl(n.rs_qty,0)))/div),0),
    nvl((atax * (DECODE(vWEIGHT_FLAG,'Y',(nvl(n.WEIGHT,0) - nvl(n.R_WEIGHT,0)),(nvl(n.s_qty,0) - nvl(n.rs_qty,0)))/div) * NVL(N.price,0) )/100+(NVL(N.price,0)*DECODE(vWEIGHT_FLAG,'Y',(nvl(n.WEIGHT,0)-nvl(n.R_WEIGHT,0)),(nvl(n.s_qty,0)-nvl(n.rs_qty,0)))/div),0),
   -- nvl((stax * ((n.s_qty - n.rs_qty)/div) * price )/100+(atax * ((n.s_qty - n.rs_qty)/div) * price )/100+(price*(n.s_qty-n.rs_qty)/div),0),
    uom,
    468,
    /*nvl(atax*((n.s_qty-n.rs_qty)/div),0),
    nvl(stax*((n.s_qty-n.rs_qty)/div),0)*/
    (0 * (DECODE(vWEIGHT_FLAG,'Y',(n.WEIGHT - n.R_WEIGHT),(n.s_qty - n.rs_qty))/div) * NVL(N.price,0) )/100,
    (((stax/(100+stax)) *  NVL(N.price,0) * (DECODE(vWEIGHT_FLAG,'Y',(n.WEIGHT - n.R_WEIGHT),(n.s_qty - n.rs_qty)))/div))
   );
    --tot_amount :=  tot_amount + nvl(((stax*(n.s_qty-n.rs_qty)/div)+(atax*(n.s_qty-n.rs_qty)/div)+(price*(n.s_qty-n.rs_qty)/div)),0);
    
    IF (vWEIGHT_FLAG = 'Y') THEN
    tot_amount :=  tot_amount + (atax * ((n.WEIGHT - n.R_WEIGHT)/div) * NVL(N.price,0) )/100 +  NVL(N.price,0) * ((n.WEIGHT - n.R_WEIGHT)/div);
    TTAX_AMOUNT := TTAX_AMOUNT + (((stax/(100+stax)) *  NVL(N.price,0) * NVL((n.WEIGHT - n.R_WEIGHT),0)/div));
    --NVL((stax * ((n.WEIGHT - n.R_WEIGHT)/div) * NVL(N.price,0) ),0)/100;
    ELSE
    tot_amount :=  tot_amount + (atax * ((n.s_qty - n.rs_qty)/div) * NVL(N.price,0) )/100 +  NVL(N.price,0) * ((n.s_qty - n.rs_qty)/div);
    TTAX_AMOUNT := TTAX_AMOUNT + (((stax/(100+stax)) *  NVL(N.price,0) * NVL((n.s_qty - n.rs_qty),0)/div));
--    TAX_AMOUNT := TAX_AMOUNT + NVL((stax * ((n.s_qty - n.rs_qty)/div) * NVL(N.price,0) ),0)/100;
    END IF;
    
  end if;

end loop;
--message('end loop lines');
line_no:=0;
	UPDATE trx_headers@sek_zh2.us.oracle.com  SET AR_Interfaced='Y' WHERE
	TRX_HEADER_ID=R.TRX_HEADER_ID;
  update ra_customer_trx_all set net_amount = tot_amount
	where customer_trx_id =	prim_header;
  update ra_customer_trx_all set TAX_AMOUNT = TTAX_AMOUNT
	where customer_trx_id =	prim_header;
  commit;

begin
	commit;
 exception
	when others then
   null;
end;


inv_discount_master_pro
('ZH2-'||'09-'||R.ACTIVITY_ID||'-'||R.returns_number,R.TRX_DATE,tot_amount);
inv_discount_pro
('ZH2-'||'09-'||R.ACTIVITY_ID||'-'||R.returns_number,R.TRX_DATE,tot_amount);

items('ZH2-'||'09-'||R.ACTIVITY_ID||'-'||R.returns_number,R.TRX_DATE);




END LOOP;
--message('end loop header');
--MESSAGE('Importing of new invoices is completed');


end LAST_ZAHRAA2_SEK;

and on the form
By pressing a button
it contain this code

lst_zahraa2_sek(:S_DATE1,:S_DATE2);

Report message to a moderator

Re: problem in calling procedure from a form [message #409244 is a reply to message #409241]

Sun, 21 June 2009 01:52

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What are :S_DATE1 and :S_DATE2 item datatypes? Procedure expect them to be DATE (not CHARACTERS that look like dates to you, but obviously not to Oracle). Also, check whether date formats in Forms (IAS?) fit the database date format (so that there wouldn't be something like DD-MON-YYYY and MM/DD/YYYY).

Oracle

ORA-01858: a non-numeric character was found where a numeric was expected

Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.

Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

Report message to a moderator

Re: problem in calling procedure from a form [message #409248 is a reply to message #409197]

Sun, 21 June 2009 02:38

emadnabil
Messages: 179
Registered: August 2007

Senior Member

Thanks for reply

:S_DATE1 and :S_DATE2 have datatype date
and they sent in this format DD-MM-YYYY

and please tell me what do you mean by
"check whether date formats in Forms (IAS)"
and how can i check for it
?

Report message to a moderator

Re: problem in calling procedure from a form [message #409251 is a reply to message #409197]

Sun, 21 June 2009 04:01

emadnabil
Messages: 179
Registered: August 2007

Senior Member

Forget the past

I got the problem where?

suppose this query

SELECT to_DATE(H.returns_Date,'dd-mm-yyyy') into Date_test
 FROM HEADER@sek_zh2.us.oracle.com H,SEK_JTF_RS_SALESREPS J
WHERE
--and  h.AR_Interfaced is null
J.ENABLED_FLAG='Y'
AND J.CUSTOMER_ID IS NOT NULL
AND j.subinventory_code = H.subinventory_code
and H.costcenter_code = j.costcenter_code
and H.costcenter_code IN (1442)
AND RETURNS_DATE BETWEEN '18-JUN-2009' AND '18-JUN-2009'
and returns_number ='3347'
;
message (Date_test);
message (Date_test);

this query run ok in the sql

while inside a form it give me this error

ORA-01858: a non-numeric character was found where a numeric was expected

thanks for help

Report message to a moderator

Re: problem in calling procedure from a form [message #409255 is a reply to message #409251]

Sun, 21 June 2009 04:47

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

alter session set nls_date_format='DDMMYY';

select * from scott.emp
where hiredate >= '01-JAN-2009';

Try this. This will give the same error.

Quote:

SELECT to_DATE(H.returns_Date,'dd-mm-yyyy') into Date_test

Why are you using to_date here? Isn't returns_date a date column? Is it a character?

Quote:

AND RETURNS_DATE BETWEEN '18-JUN-2009' AND '18-JUN-2009'

'18-JUN-2009' is a string. It isn't a date, unless you use to_date explicitly.

By
Vamsi

Report message to a moderator

Re: problem in calling procedure from a form [message #409256 is a reply to message #409197]

Sun, 21 June 2009 05:17

emadnabil
Messages: 179
Registered: August 2007

Senior Member

now i made more simple

SELECT 
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE
RETURNS_DATE BETWEEN TO_DATE('18-06-2009','DD-MM-RRRR') AND TO_DATE('18-06-2009','DD-MM-RRRR')
and returns_number ='3347'
;

and still have the probelm
note: header is a view not a table
if i select from the tables that work from the view it will work with no problem

Report message to a moderator

Re: problem in calling procedure from a form [message #409260 is a reply to message #409256]

Sun, 21 June 2009 05:33

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

describe the view.
What is the view text?
What is Date_test?

By
Vamsi

Report message to a moderator

Re: problem in calling procedure from a form [message #409263 is a reply to message #409197]

Sun, 21 June 2009 05:47

emadnabil
Messages: 179
Registered: August 2007

Senior Member

here is the view

CREATE OR REPLACE VIEW HEADER AS
SELECT DISTINCT H.RETURNS_NUMBER
,SUM(S_QTY) s_qty ,SUM(RS_QTY) rs_qty,SUM(P_QTY) p_qty,SUM(RP_QTY) rp_qty
,H.ISSUE_NUMBER,H.TRX_DATE,H.TRX_HEADER_ID,h.returns_date,H.ACTIVITY_ID,
H.SALESPER_ID,s.subinventory_code,s.costcenter_code
 FROM TRX_HEADERS  H,salespersons  s,trx_lines l
WHERE H.SALESPER_ID = S.SALESPER_ID
and l.trx_header_id=h.trx_header_id

and h.completed_flag = 'Y'
AND S.ENABLED_FLAG='Y'
and h.returns_date is not null
and S.costcenter_code IN ( 1442)
AND TRX_TYPE_ID=1
and h.returns_date between '01-JAN-2008' and sysdate-1
group by h.returns_number,H.ISSUE_NUMBER,H.TRX_DATE,H.TRX_HEADER_ID,h.returns_date,H.ACTIVITY_ID,H.SALESPER_ID,s.subinventory_code,s.costcenter_code

MINUS
SELECT DISTINCT H.RETURNS_NUMBER,
SUM(S_QTY) s_qty ,SUM(RS_QTY) rs_qty,SUM(P_QTY) p_qty,SUM(RP_QTY) rp_qty
 ,H.ISSUE_NUMBER,H.TRX_DATE,H.TRX_HEADER_ID,h.returns_date,H.ACTIVITY_ID,H.SALESPER_ID,s.subinventory_code,s.costcenter_code
 FROM TRX_HEADERS  H,salespersons s,trx_lines l
WHERE H.SALESPER_ID = S.SALESPER_ID
and l.trx_header_id=h.trx_header_id

and h.completed_flag = 'Y'
AND S.ENABLED_FLAG='Y'   

and h.returns_date is not null
and S.costcenter_code IN ( 1442)
AND TRX_TYPE_ID=1
and h.returns_date between '01-JAN-2008' and sysdate-1
having sum(s_qty)-sum(rs_qty)=0
and
sum(p_qty)-sum(rp_qty)=0
group by h.returns_number,H.ISSUE_NUMBER,H.TRX_DATE,H.TRX_HEADER_ID,h.returns_date,H.ACTIVITY_ID,H.SALESPER_ID,s.subinventory_code,s.costcenter_code

Report message to a moderator

Re: problem in calling procedure from a form [message #409264 is a reply to message #409263]

Sun, 21 June 2009 05:56

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

When you reply to any post, please read it fully and answer all the questions.

Quote:

describe the view. (desc header@sek_zh2.us.oracle.com)
What is Date_test?
Why are you using to_date here? Isn't returns_date a date column? Is it a character?

As you said

SELECT 
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE
RETURNS_DATE BETWEEN TO_DATE('18-06-2009','DD-MM-RRRR') AND TO_DATE('18-06-2009','DD-MM-RRRR')
and returns_number ='3347'

is also giving error.
I suspect your view itself is having issues.
Try the following.

SELECT 
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE returns_number ='3347'
AND rownum = 1;

SELECT 
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE rownum = 1

Your view is also having

Quote:

and h.returns_date between '01-JAN-2008' and sysdate-1

By
Vamsi

[Updated on: Sun, 21 June 2009 05:57]

Report message to a moderator

Re: problem in calling procedure from a form [message #409265 is a reply to message #409264]

Sun, 21 June 2009 06:11

emadnabil
Messages: 179
Registered: August 2007

Senior Member

for your question

describe the view. (desc header@sek_zh2.us.oracle.com)

SQL> desc header;
Name              Type          Nullable Default Comments 
----------------- ------------- -------- ------- -------- 
RETURNS_NUMBER    VARCHAR2(240) Y                         
S_QTY             NUMBER        Y                         
RS_QTY            NUMBER        Y                         
P_QTY             NUMBER        Y                         
RP_QTY            NUMBER        Y                         
ISSUE_NUMBER      VARCHAR2(240) Y                         
TRX_DATE          DATE          Y                         
TRX_HEADER_ID     NUMBER(10)    Y                         
RETURNS_DATE      DATE          Y                         
ACTIVITY_ID       NUMBER(10)    Y                         
SALESPER_ID       NUMBER(10)    Y                         
SUBINVENTORY_CODE VARCHAR2(10)  Y                         
COSTCENTER_CODE   VARCHAR2(4)   Y

What is Date_test?

it is a variable of datatype date that insert in it the value returned from the query

Why are you using to_date here? Isn't returns_date a date column? Is it a character?

it is just a test
abd returns_date is a date no character

and by try this

SELECT 
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE returns_number ='3347'
AND rownum = 1;

and this

SELECT 
H.returns_Date INTO Date_test
FROM header@sek_zh2.us.oracle.com H
WHERE rownum = 1

i got the same errors

Report message to a moderator

Re: problem in calling procedure from a form [message #409266 is a reply to message #409265]

Sun, 21 June 2009 06:20

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Great!!
Then your view is having the issue. As I said earlier

Quote:

and h.returns_date between '01-JAN-2008' and sysdate-1

Correct this and create the view again.

If you want to test the view from sql*plus, use

alter session set nls_date_format='DDMMYY';

and select from the view.

By
Vamsi

[Updated on: Sun, 21 June 2009 06:21]

Report message to a moderator

Re: problem in calling procedure from a form [message #409267 is a reply to message #409197]

Sun, 21 June 2009 06:55

emadnabil
Messages: 179
Registered: August 2007

Senior Member

thanks alot for help

yes the problem was in this

and h.returns_date between '01-JAN-2008' and sysdate-1

but now iam in conflict
why it run ok from the sql
and in form runtime it have a problem
is the problem in the nls_date_format?

anyway thanks alot for helping me

Report message to a moderator

Re: problem in calling procedure from a form [message #409438 is a reply to message #409267]

Mon, 22 June 2009 13:33

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Your code is dangerous. '01-JAN-2008' is a string, not a date. Saying that it runs correctly in SQL*Plus is pure luck, nothing more because Oracle was able to implicitly convert this string into a date (thanks to database date format).

Always, but always maintain correct datatypes. Oracle is stupid, it does what you tell it to do. If you don't pay attention, it will fool you.

This is how it should look like (more or less):

and h.returns_date between TO_DATE('01-JAN-2008', 'dd-mon-yyyy') and sysdate-1

Report message to a moderator

Previous Topic:	How to put item over image
Next Topic:	Auto clear image

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 10:24:58 CDT 2024