|Re: FORM BUILDER [message #673030 is a reply to message #673027]
||Fri, 02 November 2018 09:37
Registered: September 2008
Location: Rainy Manchester
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:
3 l_sql_string VARCHAR2(32767);
5 l_value VARCHAR2(1) := 'X';
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);
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);
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.