Home » Developer & Programmer » Forms » problem in calling procedure from a form (10g)
problem in calling procedure from a form [message #409197] Sat, 20 June 2009 04:47 Go to next message
emadnabil
Messages: 163
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Without having a look at your code, we can't help much.

By
Vamsi
Re: problem in calling procedure from a form [message #409241 is a reply to message #409197] Sun, 21 June 2009 01:13 Go to previous messageGo to next message
emadnabil
Messages: 163
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);
Re: problem in calling procedure from a form [message #409244 is a reply to message #409241] Sun, 21 June 2009 01:52 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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.
Re: problem in calling procedure from a form [message #409248 is a reply to message #409197] Sun, 21 June 2009 02:38 Go to previous messageGo to next message
emadnabil
Messages: 163
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
?
Re: problem in calling procedure from a form [message #409251 is a reply to message #409197] Sun, 21 June 2009 04:01 Go to previous messageGo to next message
emadnabil
Messages: 163
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

Re: problem in calling procedure from a form [message #409255 is a reply to message #409251] Sun, 21 June 2009 04:47 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: problem in calling procedure from a form [message #409256 is a reply to message #409197] Sun, 21 June 2009 05:17 Go to previous messageGo to next message
emadnabil
Messages: 163
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


Re: problem in calling procedure from a form [message #409260 is a reply to message #409256] Sun, 21 June 2009 05:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
describe the view.
What is the view text?
What is Date_test?

By
Vamsi
Re: problem in calling procedure from a form [message #409263 is a reply to message #409197] Sun, 21 June 2009 05:47 Go to previous messageGo to next message
emadnabil
Messages: 163
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


Re: problem in calling procedure from a form [message #409264 is a reply to message #409263] Sun, 21 June 2009 05:56 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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 Go to previous messageGo to next message
emadnabil
Messages: 163
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

Re: problem in calling procedure from a form [message #409266 is a reply to message #409265] Sun, 21 June 2009 06:20 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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 Go to previous messageGo to next message
emadnabil
Messages: 163
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
Re: problem in calling procedure from a form [message #409438 is a reply to message #409267] Mon, 22 June 2009 13:33 Go to previous message
Littlefoot
Messages: 20896
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
Previous Topic: How to put item over image
Next Topic: Auto clear image
Goto Forum:
  


Current Time: Tue Dec 06 12:20:05 CST 2016

Total time taken to generate the page: 0.13539 seconds