Home » Developer & Programmer » Forms » FORM BUILDER
FORM BUILDER [message #673027] Fri, 02 November 2018 09:05 Go to next message
Farhan ud din
Messages: 32
Registered: October 2018
Location: malakand
Member

aNY ONE PLEASE GUIDE THAT WHY THESE TOO MUCH SINGLE QUOTES ARE USED HERE THIS WORKS FINE BUT ILLUSTRATE ME THAT WHY WE USE IT

DECLARE

A VARCHAR2(200);

BEGIN

IF :SEARCH.CHO =1 THEN
A:= 'ENAME=''' || :SEARCH.TXT||'''';


END IF;
SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,A);
GO_BLOCK('EMP');
EXECUTE_QUERY;

END;



BESIDE FROM THIS THE FOLLOWING CODE ERROR =>UNABLE TO PERFORM QUERY

DECLARE


A VARCHAR2(200);

BEGIN

IF :SEARCH.CHO =1 THEN
A:= 'ENAME=' || :SEARCH.TXT;


END IF;
SET_BLOCK_PROPERTY('EMP',DEFAULT_WHERE,A);
GO_BLOCK('EMP');
EXECUTE_QUERY;

END;
  • Attachment: 1.png
    (Size: 72.86KB, Downloaded 295 times)
Re: FORM BUILDER [message #673030 is a reply to message #673027] Fri, 02 November 2018 09:37 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't post in upper case - it's considered shouting on all internet forums.

You're trying to create part of a where clause.
Have a look at this:
SQL> DECLARE
  2  
  3  l_sql_string VARCHAR2(32767);
  4  
  5  l_value VARCHAR2(1) := 'X';
  6  
  7  BEGIN
  8  
  9    --without extra quotes
 10    l_sql_string := 'select * from dual where dummy = '||l_value;
 11    dbms_output.put_line('1st select is '||l_sql_string);
 12  
 13    --with extra quotes
 14    l_sql_string := 'select * from dual where dummy = '''||l_value||'''';
 15    dbms_output.put_line('2nd select is '||l_sql_string);
 16  
 17  END;
 18  /

1st select is select * from dual where dummy = X
2nd select is select * from dual where dummy = 'X'

PL/SQL procedure successfully completed

Only the 2nd select is a valid SQL statement. In the first oracle will assume X is a column name and throw an error because there's no such column.
To get quotes into a variable you need to concatenate extra quotes, otherwise oracle just thinks you're terminating the current string.

However, for your forms code there's another way:
A := 'ENAME = :SEARCH.TXT';

Which is to say you can put the form item name itself in the where clause (rather than the current value of the form item) and it'll work just fine.
In fact that is the method you should use here. It'll avoid hard-parsing every time the form item value changes and avoid filling up the SGA with lots of variants of what is really the same select.
Re: FORM BUILDER [message #673053 is a reply to message #673030] Sat, 03 November 2018 13:57 Go to previous message
Farhan ud din
Messages: 32
Registered: October 2018
Location: malakand
Member

thanks alot nice job
Previous Topic: Form Builder
Next Topic: Oracle form Builder
Goto Forum:
  


Current Time: Tue May 18 02:07:56 CDT 2021