We help IT Professionals succeed at work.

CompariingValues_inplsql

sam15
sam15 asked
on
Does mod plsql add extra space to the data submitted from HTML form.
 
  I noticed today that even when i do not change any data and hit submit I always get the "Data
Changed" message.
 
  After I added TRIM and NVL (for nulls), it stoppped.
 
 
  Is this how you would compare data inputed via form against record in database.
  I know if a column already has "abc123" and another is NULL, this will not yield correct result.
 
 
 
  open c_org(l_orgcode);
   fetch c_org into l_rec;
   
     
   if  TRIM(NVL(p_name,'abc123')) = TRIM(NVL(l_rec.name,'abc123')) and
       TRIM(NVL(p_addr1,'abc123')) = TRIM(NVL(l_rec.addr1,'abc123'))  and
       TRIM(NVL(p_addr2,'abc123')) = TRIM(NVL(l_rec.addr2,'abc123')) and
       TRIM(NVL(p_city,'abc123')) = TRIM(NVL(l_rec.city,'abc123')) and
       TRIM(NVL(p_state,'abc123')) = TRIM(NVL(l_rec.state,'abc123')) and
       TRIM(NVL(p_zip,'abc123')) = TRIM(NVL(p_zip,'abc123'))  
    then
   
      htp.p('No change in data');
     
    else
      update...
     
      htp.p('Data changed);
     
    end if;


Comment
Watch Question

Top Expert 2011
Commented:
"Does mod plsql add extra space to the data submitted from HTML form."
- no i believe it did not. can you check your html form code if it add &nbsp in any one of the input form?



"Is this how you would compare data inputed via form against record in database.
  I know if a column already has "abc123" and another is NULL, this will not yield correct result."

- is there any particular reason why you want to have default value? you just compare and trim the extra spaces instead as follows:

TRIM(p_name) = TRIM(l_rec.name)

Top Expert 2011

Commented:
- check also in the form input tag, if its value is sets with spaces such as:

<input type="text" name="p_name" value=" "> or <input type='text' name='p_name' value=' '>

Author

Commented:
I used NVL because if both fields are null they are the SAME but the comparison fails. so i wanted to use some common value in case of NULL values.
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>Is this how you would compare data inputed via form against record in database

I would need more information about the form and the process before I would say how I would do it.

Since the code snippet you posted doesn't have a loop, I assume you are selecting a single row from the database for comparison.  If so, I probably wouldn't use a cursor and just select some value from the database and compare.

something like:
select count(*) into row_count from table where column_value=form_value and ...;

if row_count = 0 then
    --do something
else
    --do something else
end if;

Author

Commented:
I can still use explicit cursor for one record fetch. I do not need a loop. I did it this way because there is a lot of variables. Doing it implicit like you I would need to declare 50 variables.
WIth cursor i can declare a variable based on cursor row type.

The parameters are like this

procedure handle_form
( p_name    in varchar2  default null,
p_addr1    in    varchar2  default null,
p_addr2    in   varchar2   default null,
p_city       in   varchar2   default null )

then you have the above code comaprig input values to record value in DB.

I think there is a standard way for using NVL for NULLcomparisons but i cant find it. RIght now I used 'abc123' but if DB value was null and someone inputed abc123' it would return true even though they are different values.
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Not sure why my approach would need 50 variables.  I'm thinking a single select with 50 different items in the where clause.

Same if structure you have but I moved them into the select.


As far as you NVL issue, you just need something that cannot be entered in the form or at least has a very slim chance. Like chr(0)

Try: ... nvl(p_name,chr(0)) ...

Author

Commented:
you are doing a row count. why?

I need to do

select col1,col2, col50 into l_col1,l_col2,l_col3,...l_col50
  from T1, T2, T3 where.

and then

if (p_col1 = l_col1 AND p_col2 = l_col2 and .....
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Same thing but without the variables and explicit cursor open/fetch.

You have:
Select col1, col2 into l_col1, l_col2 from tab1 where...

if p_col1=l_col1 and ...


I suggest:
Select count(*) from tab1
Where ...
And col1=p_col1 and ...;

If you get a 0 from my count that is the same as a 'false' as your if statement.

Tell you what: provide a simple test case with your way and I'll tweak it and show mine.

A simple two column table, some sample data and a quick procedure should work.

Author

Commented:
oh so you are propsing a different way using COUNT that does not require selecting the table row into variables and comparing it into the original parameters getting passed?

It is pretty smart actually the way you did it although I have never seen it in text books or websites. Most seem to di it the same way I wrote it.

I guess also your way woukld handle NULLS since if the table column is null and parameter is null the WHERE caluse returns TRUE. right?

My only concern is if MOD_PLSQL is passing extra spaces in the data. IT might be safer to do a TRIM on the parameters.
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Pretty sure it isn't a unique way but yes, no need to fetch into a lot of variables when you don't need to.

You basicaly just want to see if a 'row' exists.  That is a 'count'.

You will still need the nvl calls since null cannot '=' null.  Feel free to keep the trim in there unless a 'space' is valid in the form and you want to preserve them.

Author

Commented:
so basically it would look like this

select count(*) from ORG where name=NVL(TRIM(p_name,'123') and city=NVL(TRIM(p_city,'123')
etc....

there must be some better than '123' because if one column has 123 and parameter passed is null it will return true. I cant remember what is a common practice using the NVL value.
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>there must be some better than '123'

I covered that in http:#a37086572

Just use a value that will never be passed in as a parameter (or so likely to never be passed in).

The chr(0) would be hard to pass in.  If the field has 'edits' for something like all numbers then 'abc' would work since it cannot be passed in.  Is there a max allowed number of characters?  If say 3, make the compare string 4 long, '----'.

There are many ways around this.

Author

Commented:
each column in the table i check for NULL is different. It would be cumbersome to check the length for each field abs base the NVL n that.

CHr(0) = 0 (ascii equivalent).

i think if a user enters a ZERO (0) and table has 0 then it wont be correct.
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>enters a ZERO

a '0' is NOT the same as chr(0).  a '0' is chr(48)

>>It would be cumbersome to check the length for each field abs base the NVL n that.


pick the 'max' for ALL columns unless is is like a varcahr2(4000).


This does raise an interesting point:  The columns for NVL need to be the same data type so if on of the fields is a 'date', then you will need a different check for that.

Author

Commented:
I was thinking about your count method to compare each field.

It would work but in case there is a table of 100,000 it would take a while (slow) to compare all records instead of selecting one record (by primary key) to a cursor and then doing comparison unless it uses the primary key index only to find the record first.

do you agree.
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>do you agree.

Not really.  You have a select to 'fetch' that one row currently.  I'm just saying add the checks to the where clause you already have.  You will still either get the 1 row or 0 rows.

A picture is worth 1000 words.  Back in http:#a37086644 I asked for you to post a simple test using your approach.  I'll then convert it to my suggestion so you can see it, run it, test it.

A single table with a few rows showing 'success' and 'failure' will do.  It doesn't have to be complicated to do a proof of concept.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.