Home » SQL & PL/SQL » SQL & PL/SQL » restrict user for input (Forms,6i,XP)
restrict user for input [message #353084] Sat, 11 October 2008 02:53 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

I have the following vin_upd table with the following structure.
PROD_CD                                  VARCHAR2(5)
TYPE                                     VARCHAR2(1)
FRM_VIN                                  VARCHAR2(17)
TO_VIN                                   VARCHAR2(17)
MODI_DT                                  DATE
GR_CD                                    VARCHAR2(3)
GR_SUB_CD                                VARCHAR2(4)
REM                                      VARCHAR2(50)

I have inserted the following data.

insert into vin_upd
(prod_cd,frm_vin,to_vin,gr_sub_cd)
values
('10','100','200','15a')

insert into vin_upd
(prod_cd,frm_vin,to_vin,gr_sub_cd)
values
('10','400','500','15a')


now i have the following values
frm_vin    to_vin   gr_sub_cd
100        200      15a
400        500      15a

now i want to restrict my user for not inputing the values between '100'(frm_vin) to (to_vin)'200' and '400' and '500' against '15a' (gr_sub_cd).

means if user input values between '100' to '200' or '400' to '500' against gr_sub_cd (15a) ,system stop him but if user want to input '300' to '399' against gr_sub_cd '15a' then system accept value.

I am very grateful to you if you could solve my problem.

Regards

Zuhair
Re: restrict user for input [message #353088 is a reply to message #353084] Sat, 11 October 2008 04:38 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How about a cursor FOR loop? Something like
for cur_r in (select frm_vin, to_vin 
              from vin_upd
              where gr_sub_cd = :block.gr_sub_cd 
             )
loop
  if :block.frm_vin < cur_r.frm_vin or
     :block.to_vin  > cur_r.to_vin
  then
     message('Invalid range');
     raise form_application_error;
  end if;
end loop;
Re: restrict user for input [message #353223 is a reply to message #353088] Mon, 13 October 2008 00:34 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear Littlefoot

Thanks for the reply,but problem is still the same,when i tried to input 300 into frm_vin field and 399 into to_vin field then system shows message 'invalid range' and As per my requirement system has to accept the above value.

I think i can achive my query through the following steps.

Firstly system store all values of frm_vin and to_vin against gr_sub_cd through cursor.

Secondly when i input value of frm_vin ,system compare frm_vin value from the cursor value by using between operator.

for instance
suppose i have the following values in my table
frm_vin   to_vin   gr_sub_cd
100       200      15a
400       500      15a


when i input 300 in frm_vin ,system goto the first record of vin_upd table and extract values between 100 to 200(101,102,103.....200) and compare 300(frm_vin) to the cursor values and if not found 300 values then goto the second record and so on and if not found 300 then simlpy accept input.

please help me to create the query of above scenario or give me some other suitable solution,i will be thanksful to you.
[EDITED by DJM: moved words out of 'code' sections]

[Updated on: Thu, 16 October 2008 00:51] by Moderator

Report message to a moderator

Re: restrict user for input [message #353524 is a reply to message #353084] Tue, 14 October 2008 02:00 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
waiting for your reply anxiously.
Re: restrict user for input [message #353994 is a reply to message #353524] Thu, 16 October 2008 00:52 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you tried writing and testing your code in SQL*Plus?

David

I am moving your problem to the SQL*Plus / PL/SQL forum.
Re: restrict user for input [message #354032 is a reply to message #353084] Thu, 16 October 2008 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We need more details about what you want:
With this data:
frm_vin    to_vin   gr_sub_cd
100        200      15a
400        500      15a
Which of the following ranges should be allowed:
From  To
50    90
50    100
50    110
50    200
20    210
100   210
110   210
200   210
210   220
Re: restrict user for input [message #354049 is a reply to message #353084] Thu, 16 October 2008 04:02 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
The following ranges are allowed
The following ranges are allowed.

From  To	status		
50    90	Allowed		
50    100	Not Allowed	
50    110	Not Allowed	
50    200	Not allowed
20    210	Not Allowed	
100   210	Not Allowed	
200   210	Not Allowed	
210   220	Allowed 




means if system found any of input number (frm_vin or to_vin) in vin_upd table then system stop input.
for example the following ranges are also acceptable
from  To
50    99
20    99
201   210
210  220




Re: restrict user for input [message #354053 is a reply to message #353084] Thu, 16 October 2008 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Then a piece of code like this should do:
for cur_r in (select frm_vin, to_vin 
              from vin_upd
              where gr_sub_cd = :block.gr_sub_cd 
             )
loop
  if :block.frm_vin between cur_r.frm_vin and cur_r.to_vin
  or :block.to_vin  between cur_r.frm_vin and cur_r.to_vin
  then
     message('Invalid range');
     raise form_application_error;
  end if;
end loop;
Re: restrict user for input [message #354203 is a reply to message #353084] Fri, 17 October 2008 00:05 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks you very much ,its working perfectly,you guys are really expert.
Previous Topic: Oracle Object type not working in two schemas
Next Topic: table data verification
Goto Forum:
  


Current Time: Sat Dec 03 15:51:21 CST 2016

Total time taken to generate the page: 0.10341 seconds