OraFAQ Forum: Forms » FORM BUILDER

Home » Developer & Programmer » Forms » FORM BUILDER

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

FORM BUILDER [message #673027]

Fri, 02 November 2018 09:05

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 633 times)

Report message to a moderator

Re: FORM BUILDER [message #673030 is a reply to message #673027]

Fri, 02 November 2018 09:37

cookiemonster
Messages: 13904
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.

Report message to a moderator

Re: FORM BUILDER [message #673053 is a reply to message #673030]

Sat, 03 November 2018 13:57

Farhan ud din
Messages: 32
Registered: October 2018
Location: malakand

Member

thanks alot nice job

Report message to a moderator

Previous Topic:	Form Builder
Next Topic:	Oracle form Builder

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 29 08:56:20 CDT 2023