Home » Developer & Programmer » Forms » Search not working... (Form builder 6i and oracle 8i)
Search not working... [message #327278] Sun, 15 June 2008 22:53 Go to next message
ashishkumar.88
Messages: 5
Registered: June 2008
Junior Member
Hello all,

I have made a search form in my main form (using form builder 6i)
in which it has to search for records based on the values filled in for the various columns.

Now the problem is that it works when i use the first two parameters
ie sysno and drcd both indivisually and together,
or when i use the second and the third column parameters drcd and dept_code
but other than that it does not work if i enter value in any other combination of column parameters,
i get the error
"trigger raised unhandled exception ora-06502"

The search works fine if i enter values in any of the column parameters indivisually.
I have checked using the debug tool but the query comes out correct.

I require the form to search values irrespective of how many column parameters are given and in whatever combination.

DECLARE
  where_clause varchar2 (3000);
  

BEGIN

 where_clause := null;


 if not (:SEARCH.SYSNO is null or :SEARCH.SYSNO = ' ')  then 
    if where_clause is null then
      where_clause := ' SYSNO = ' ||:SEARCH.SYSNO ;
    else
      where_clause := where_clause || ' and SYSNO = ' || :SEARCH.SYSNO ;
    end if;
 end if; 
 

if not (:SEARCH.DRCD is null or :SEARCH.DRCD = ' ')  then 
    if where_clause is null then
      where_clause := ' DRCD = ' || :SEARCH.DRCD;
    else
      where_clause := where_clause || ' and DRCD = ' || :SEARCH.DRCD;
    end if;
 end if; 


if not (:SEARCH.DEPT_CODE is null or :SEARCH.DEPT_CODE = ' ')  then 
    if where_clause is null then
      where_clause := ' DEPT_CODE = ' || :SEARCH.DEPT_CODE;
    else
      where_clause := where_clause || ' and DEPT_CODE = ' || :SEARCH.DEPT_CODE;
    end if;
end if; 


if not (:SEARCH.SYS_LABEL is null or :SEARCH.SYS_LABEL = ' ')  then 
    if where_clause is null then
      where_clause := ' SYS_LABEL like ''%' || :SEARCH.SYS_LABEL || '%''';
    else
      where_clause := where_clause || ' and  SYS_LABEL like ''%' || :SEARCH.SYS_LABEL || '%''' ;
    end if;
 end if; 





if not (:SEARCH.SYSTP   is null or :SEARCH.SYSTP   = ' ')  then 
    if where_clause is null then
      where_clause := ' SYSTP like ''%' || :SEARCH.SYSTP   || '%''';
    else
      where_clause := where_clause || ' and  SYSTP like ''%' || :SEARCH.SYSTP   || '%''' ;
    end if;
 end if; 



if not (:SEARCH.DISP   is null or :SEARCH.DISP   = ' ')  then 
    if where_clause is null then
      where_clause := ' DISP like ''%' || :SEARCH.DISP   || '%''';
    else
      where_clause := where_clause || ' and  DISP like ''%' || :SEARCH.DISP   || '%''' ;
    end if;
 end if; 



if not (:SEARCH.VEND   is null or :SEARCH.VEND   = ' ')  then 
    if where_clause is null then
      where_clause := ' VEND like ''%' || :SEARCH.VEND   || '%''';
    else
      where_clause := where_clause || ' and  VEND like ''%' || :SEARCH.VEND   || '%''' ;
    end if;
end if; 



if not (:SEARCH.FLOOR   is null or :SEARCH.FLOOR   = ' ')  then 
    if where_clause is null then
      where_clause := ' FLOOR like ''%' || :SEARCH.FLOOR   || '%''';
    else
      where_clause := where_clause || ' and  FLOOR like ''%' || :SEARCH.FLOOR   || '%''' ;
    end if;
end if; 

if not (:SEARCH.DEPTT   is null or :SEARCH.DEPTT   = ' ')  then 
    if where_clause is null then
      where_clause := ' DEPTT like ''%' || :SEARCH.DEPTT   || '%''';
    else
      where_clause := where_clause || ' and  DEPTT like ''%' || :SEARCH.DEPTT   || '%''' ;
    end if;
end if; 



if not (:SEARCH.COORD   is null or :SEARCH.COORD   = ' ')  then 
    if where_clause is null then
      where_clause := ' COORD like ''%' || :SEARCH.COORD   || '%''';
    else
      where_clause := where_clause || ' and  COORD like ''%' || :SEARCH.COORD   || '%''' ;
    end if;
end if; 



:SEARCH.PC_WHERE_CLAUSE :=WHERE_CLAUSE; --this where clause is executed 
GO_BLOCK('pcmay2008');
CLEAR_BLOCK(NO_COMMIT);
EXECUTE_QUERY;
END;



This is the code i have written for the retrieve records button (the first three columns are of type number rest are varchar2,
also i have over 30 columns but for convinience sake i am putting only the first few in the code here)

please do advice
Re: Search not working... [message #327284 is a reply to message #327278] Sun, 15 June 2008 23:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You forgot the quotes around your values.
[Edit: I see you have them around your other fields]

There are some issues with the way you do this:
first and foremost: this enables sql injection (google that, if you don't know what it is)
second: if you start by initializing where_clause to 1=1, you can omit all the "if where_clause is null" parts.
third: If you want to use Oracle Forms, teach your users to use that. Show them how to use query-by-example. Trust them to be able to use wildcards.


If you want to know the error, display your final where-clause first.

[Updated on: Sun, 15 June 2008 23:30]

Report message to a moderator

Re: Search not working... [message #327297 is a reply to message #327278] Mon, 16 June 2008 00:05 Go to previous messageGo to next message
ashishkumar.88
Messages: 5
Registered: June 2008
Junior Member
Value of where clause when i enter the first
two parameters

  WHERE_CLAUSE(CHAR)= SYSNO = 1191 and DRCD = 4 


THIS GIVES CORRECT RESULTS

When i give first three parameters then
  WHERE_CLAUSE(CHAR)= SYSNO = 1191 and DRCD = 4 and DEPT_CODE = 3040 


This does not give result, rather unhandled exception error


when i give 2nd and 4th parameter

  WHERE_CLAUSE(CHAR)= DRCD = 4 and  SYS_LABEL like '%T%'


still gives error



Re: Search not working... [message #327307 is a reply to message #327297] Mon, 16 June 2008 00:25 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'll never understand why (some) people are trying to reinvent the wheel.

Forms perform search since ice age; version 3.0 did it great, and all subsequent versions do it perfectly well. So, what reason made you create your own "search form" (besides training purposes)?
Re: Search not working... [message #327308 is a reply to message #327307] Mon, 16 June 2008 00:32 Go to previous messageGo to next message
ashishkumar.88
Messages: 5
Registered: June 2008
Junior Member
It is for training purpose.
It is in my project ...
Re: Search not working... [message #327309 is a reply to message #327297] Mon, 16 June 2008 00:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ashishkumar.88 wrote on Mon, 16 June 2008 07:05
still gives error

Debug.
Find out which line throws the error, then find out error is thrown
("Unhandled exception" by itself is not the error. The error you are after is the exception that is unhandled)

[Updated on: Mon, 16 June 2008 00:36]

Report message to a moderator

Re: Search not working... [message #327314 is a reply to message #327278] Mon, 16 June 2008 00:45 Go to previous messageGo to next message
ashishkumar.88
Messages: 5
Registered: June 2008
Junior Member
Well no error till line
:SEARCH.PC_WHERE_CLAUSE :=WHERE_CLAUSE;


after this line the procedude executes and
displays message in the bar at the bottom.
So i m guesing the error occurs when execute_query is encountered.
But i can find nothing wrong with the query Confused

Re: Search not working... [message #327318 is a reply to message #327314] Mon, 16 June 2008 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What datatype (and length, especially) is pc_where_clause in the search block?
Re: Search not working... [message #327320 is a reply to message #327318] Mon, 16 June 2008 01:04 Go to previous messageGo to next message
ashishkumar.88
Messages: 5
Registered: June 2008
Junior Member
OOh man stupid mistake Razz
had the max value of PC_WHERE_CLAUSE set to 30

problem solved now Smile

thanks Frank

thanks everyone who replied and helped

Re: Search not working... [message #327340 is a reply to message #327320] Mon, 16 June 2008 02:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I hope you learned something from this: when tackling a problem go step by step. First determine the real error (read beyond the "Unhandled exception"), then find out where exactly the error occurs.
Finally think what could go wrong at that location.

This will solve about 90% of all your "mystical" runtime errors
Previous Topic: simple query
Next Topic: FRM-30180: No radio buttons in the radio group.
Goto Forum:
  


Current Time: Sun Dec 04 04:52:57 CST 2016

Total time taken to generate the page: 0.07382 seconds