Question:  what happens when you print out the SQL command?  Does it look like what you'd expect?

Try breaking it up into this first:

my $sql = "
    select * from (
    select * from qatest
    where ser_app_name
    like upper('%$mainsearch%')
    or att_data
    like upper('%$mainsearch%')
    or mrserver
    like upper('%$mainsearch%')
    or owner_user
    like upper('%$mainsearch%')
    or lan_serv
    like upper('%$mainsearch%')
    or s_contact
    like upper('%$mainsearch%')
    or s_provider
    like upper('%$mainsearch%')
    or prod_deliv
    like upper('%$mainsearch%')
    order by ser_app_name ASC
    ) where rownum < 31
";
print "Debug>  sql is:\n", "-" x 70, $sql, "\n", "-" x 70, "\n";
my $st = $dbh->prepare($sql);
$st->execute();
[download]

Maybe that will expose something that shows you how to fix it.

If not, perhaps you can try sending the value displayed for $sql to the database by hand.

Update:  By the way, once you've got the problem solved, you might consider shortening it a bit:

my $like_string = "like upper('%$mainsearch%') ";
my @fields = qw(
    att_data mrserver owner_user lan_serv s_contact s_provider prod_de
+liv
);
my $like_clause = "where ser_app_name $like_string";
map { $like_clause .= " or $_ like $like_string" } @fields;


# The simplified sql statement ....
my $sql = "
    select * from (
        select * from qatest $like_clause order by ser_app_name ASC
    ) where rownum < 31
";
[download]

I wonder if you issue might be that you are UPPER'ing the search parameter but not the field. So if the field holds "eric" and you search for "e" it isn't going to work because "eric" is not like "%E%". Below is your code formated differently, to each his own, but i find this much easier to read. I also added a call to $dbh->quote() so that users can't shove scary bad stuff into your query.

my %incoming = &read_input;

# Read information into associated
# array %incoming.
my $mainsearch = $incoming{'yoursearch'}; 
# Fetch the text from the array.
my $maintable = $incoming{'yourtable'}; 
# Fetch the text from the array.

#Start DBI
my $dbh = DBI->connect("dbi:xxxxxxxxxxx", "xxxxxxx", "xxxxxxxx");
#quote the search in case the user tries to hide something nasty in th
+ere
$mainsearch = $dbh->quote($mainsearch);
my $st = $dbh->prepare("
    select * from (
        select * from qatest
            where 
                  UPPER(ser_app_name) like upper('%$mainsearch%')
               or UPPER(att_data)     like upper('%$mainsearch%')
               or UPPER(mrserver)     like upper('%$mainsearch%')
               or UPPER(owner_user)   like upper('%$mainsearch%')
               or UPPER(lan_serv)     like upper('%$mainsearch%')
               or UPPER(s_contact)    like upper('%$mainsearch%')
               or UPPER(s_provider)   like upper('%$mainsearch%')
               or UPPER(prod_deliv)   like upper('%$mainsearch%')
        order by ser_app_name ASC
    ) where rownum < 31");
$st->execute();
[download]

For line breaks, you can use <p> tags or <br/> tags. Or, as Corion mentioned, you can check out the Writeup Formatting Tips.

A few notes on your code:

• Don't use & when calling subroutines (unless you know that's what you should be using)
• Your variable problem may go away if you try using placeholders in prepare (and then executing with bind values), although what you have looks okay. Have you tried manually running the query? If so, does it return the correct results?
• %incoming is a hash, not an array ;)
• The single quotes aren't necessary when accessing a hash element.

Sorry that I don't have anything too specific. You may want to check out the Tutorials section, there are a few DBI helpers in there.

$dbh->trace(3)
[download]

$st->trace(3)
[download]

While not inevitably necessary, using '%${mainsearch}%' instead of '%$mainsearch%' will make your code more readable in this special context. Otherwise, it looks at first glance as if $mainsearch was a hash reference being dereferenced.

Otherwise, what geekphilosopher said.

If I run the SQL statement without the perl formatting (duh) on the database but replace '$mainsearch' with something like CAT...the statement DOES work correctly, and grabs rows with a value of 'CAT" from the database. But the real problem comes when I change it back and in perl when the user runs the code...It "stops" (not HAULTS) but just doesnt finish the rest of the 'or' statements. (if that makes sense) So basically, in my terms $mainsearch is loosing its value cause its no longer checking the condition on att_data and etc... I'm still fairly clueless to what I've done wrong. In response to using % on read_input, apparently the snippit of code I got to read input from the previous page (form) is suppost to be that way. *shrugs* It works :)