OraFAQ Forum: Forms » Search not working...

Home » Developer & Programmer » Forms » Search not working... (Form builder 6i and oracle 8i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Search not working... [message #327278]

Sun, 15 June 2008 22:53

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

Report message to a moderator

Re: Search not working... [message #327284 is a reply to message #327278]

Sun, 15 June 2008 23:27

Frank
Messages: 7901
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

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

Report message to a moderator

Re: Search not working... [message #327307 is a reply to message #327297]

Mon, 16 June 2008 00:25

Littlefoot
Messages: 21823
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)?

Report message to a moderator

Re: Search not working... [message #327308 is a reply to message #327307]

Mon, 16 June 2008 00:32

ashishkumar.88
Messages: 5
Registered: June 2008

Junior Member

It is for training purpose.
It is in my project ...

Report message to a moderator

Re: Search not working... [message #327309 is a reply to message #327297]

Mon, 16 June 2008 00:36

Frank
Messages: 7901
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

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

Report message to a moderator

Re: Search not working... [message #327318 is a reply to message #327314]

Mon, 16 June 2008 00:57

Frank
Messages: 7901
Registered: March 2000

Senior Member

What datatype (and length, especially) is pc_where_clause in the search block?

Report message to a moderator

Re: Search not working... [message #327320 is a reply to message #327318]

Mon, 16 June 2008 01:04

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

Report message to a moderator

Re: Search not working... [message #327340 is a reply to message #327320]

Mon, 16 June 2008 02:50

Frank
Messages: 7901
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

Report message to a moderator

Previous Topic:	simple query
Next Topic:	FRM-30180: No radio buttons in the radio group.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 23:34:39 CST 2025