Home » Developer & Programmer » Forms » Field column setting (Oracle Forms 10G R2)
Field column setting [message #597053] Mon, 30 September 2013 11:18 Go to next message
cam15
Messages: 4
Registered: September 2013
Location: Phoenix
Junior Member
Hello All,

I am currently working on a bug that came from a user. An oracle form is used for entering employee information and the backend table is employees. One for the field here is LAN ID. It was found that some users were using space bar either before or after entering the Lan id and this is causing issues in finding the employees in the system. A request is being submitted to not let any blanks or space to be entered in the field. In the table, this particular field/column datatype is varchar2 and the size is 8.

Could any of you please help me with this issue. How do I make no blanks or spaces to be entered in the field or otherwise, even if they put a space or blank, the field should only use the actual ID entered.. Is it possible?? Kindly advise.

thanks,

Nick
Re: Field column setting [message #597066 is a reply to message #597053] Mon, 30 September 2013 12:59 Go to previous messageGo to next message
joy_division
Messages: 4544
Registered: February 2005
Location: East Coast USA
Senior Member
Use the TRIM function in a forms trigger to set the value.
Re: Field column setting [message #597074 is a reply to message #597066] Mon, 30 September 2013 13:47 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I was thinking of two steps:
  • Update existing records in a table - trim leading and trailing spaces:
    update your_table set
      lan_id = trim(lan_id);
    
  • Use database trigger which will make sure that only trimmed values are stored into a table:
    SQL> create table test
      2    (lan_id varchar2(8));
    
    Table created.
    
    SQL> create or replace trigger trg_biu_lan
      2    before insert or update
      3    on test
      4    for each row
      5  begin
      6    :new.lan_id := trim(:new.lan_id);
      7  end;
      8  /
    
    Trigger created.
    
    SQL> insert all
      2    into test values ('123 ')
      3    into test values (' 456')
      4    into test values (' 678 ')
      5  select * from dual;
    
    3 rows created.
    
    SQL> select lan_id, length(lan_id) len from test;
    
    LAN_ID          LEN
    -------- ----------
    123               3
    456               3
    678               3
    
    SQL>
Re: Field column setting [message #597090 is a reply to message #597074] Mon, 30 September 2013 16:28 Go to previous messageGo to next message
DrabJay
Messages: 32
Registered: May 2013
Member
I would probably combine these two ideas as I along with others see trouble with triggers, particularly in the way they automagically change values.

Also the OP stated "no blanks or spaces to be entered in the field" not just at the beginning or end.

Amend the Form to either change the value entered in the field to remove all spaces or to fail validation if it does so. This is at the UI level so the user can see what has happened if you change the value.

Update the table to remove all spaces from the existing values in the lan id column.
UPDATE your_table
SET lan_id = regexp_replace(lan_id, '[[:space:]]*','');


Add a check constraint to the lan id column to ensure it does not contain spaces.
Re: Field column setting [message #597102 is a reply to message #597090] Tue, 01 October 2013 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Nice; I agree with what you said.
Re: Field column setting [message #597192 is a reply to message #597090] Tue, 01 October 2013 08:45 Go to previous messageGo to next message
joy_division
Messages: 4544
Registered: February 2005
Location: East Coast USA
Senior Member
DrabJay wrote on Mon, 30 September 2013 17:28

Update the table to remove all spaces from the existing values in the lan id column.
UPDATE your_table
SET lan_id = regexp_replace(lan_id, '[[:space:]]*','');



Good one DrabJay, but...the way I read it, users are not putting spaces in the middle of values, but that is only my interpretation.
However, your case would take care of all spaces, which surely looks better. Again though, what if spaces all allowed in the middle of values. OP didn't mention that.
Re: Field column setting [message #597210 is a reply to message #597192] Tue, 01 October 2013 10:42 Go to previous messageGo to next message
DrabJay
Messages: 32
Registered: May 2013
Member
I took my lead from the OPQuote:
to not let any blanks or space to be entered in the field

However, if the requirement is only to prevent them at the start and end of the field I would have also used your and Littlefoot's suggestion of the TRIM function for the update/check constraint.
Re: Field column setting [message #597237 is a reply to message #597210] Tue, 01 October 2013 20:01 Go to previous messageGo to next message
cam15
Messages: 4
Registered: September 2013
Location: Phoenix
Junior Member
I greatly appreciate your help and thank each one of you for your input.. As you rightly mentioned, the requirement initially was to take away all the spaces. Later the user requested only spaces created at the start or Last_name, and first_name should be removed. An employee may have more than one first name Juan Carlos and last name De La Rose. Also found out more details now. A LAN id is auto generated based on first and last name. So if there is a space in the first and last name at the start, when the save button is pressed, LAN ID and email is auto created. Also was told that if the user by mistake does enter a space at the start of either first or last name field, then the form should auto correct this error and eliminate the space from any field without giving any error to the user.

Extremely sorry for the confusion... Now we may have to make certain changes to get the now required results.

Thanks

Re: Field column setting [message #597250 is a reply to message #597237] Wed, 02 October 2013 02:42 Go to previous messageGo to next message
cookiemonster
Messages: 11177
Registered: September 2008
Location: Rainy Manchester
Senior Member
sounds like LF's solution is what you need.
Re: Field column setting [message #597369 is a reply to message #597250] Thu, 03 October 2013 17:55 Go to previous message
cam15
Messages: 4
Registered: September 2013
Location: Phoenix
Junior Member
Got it.. Done.. Used Ltrim and it did the magic.. Once again, thank you each one of you great people..

God Bless,
Previous Topic: Chatting Application
Next Topic: Error in Forms @ remote system
Goto Forum:
  


Current Time: Sun Nov 23 23:13:10 CST 2014

Total time taken to generate the page: 0.04593 seconds