as you can see no quote escaping no matter of the data type.
set_block_property('basetable_block', default_where, 'where num_col = :blk.num_item or char_col = :blk.char_item or date_col = :blk.date_item');
it get's worse with dates:
set_block_property('basetable_block', default_where, 'where num_col = '||num_val||' or char_col = '''||char_val||'''');
To demonstrate how escaping actually works we'll translate the where clauses the way they get sent to the database; assuming num_val has a value of 3, char_val has a value 'ABCD' and date_val has a value of 01.01.2013.
set_block_property('basetable_block', default_where, 'where date_col = to_date('''||to_char(date_val,'dd.mm.yyyy')||''',''dd.mm.yyyy'')');
where num_col = '||num_val|| or char_col = '''||char_val||'''
where num_col = 3 or char_col = 'ABCD'
where date_col = to_date('''||to_char(date_val,'dd.mm.yyyy')||''',''dd.mm.yyyy'')
If in doubt with escaping simply output the string to e.g. a file, and when it contains valid SQL you did everything right with the escaping.
where date_col = to_date('01.01.2013','dd.mm.yyyy')
how to output the string to a file ?Search google/the forum/the documentation on how to write textfiles with forms. as a tip you might look after text_io
by the way i got the first example well , the = with items example , but it really conflicts with me with the rest of issues , and we still did not talk about like operator , and i still feel so confused , that's why i want to know the rule for this,'Like' works the same way as '=' as far as escaping is concerned. You have to add the wildcards of course (concattenate a '%' to your searchstring for example).
By invoking my brain-inbuilt Dynamic SQL compiler I can tell you at one glance that this will fail miserably (even if I hadn't written it in the first place); a good exercise would be if you could tell me why it is wrong and how it can be made right ;)
set_block_property('block', default_where, 'char_column like '||vText||'%');
semsem wrote:Here is an example what i useda good exercise would be if you could tell me why it is wrong ,i do can , because the variable is a char variable , so i believe that it needs more quotes , but the problem is
i do not know how many quotes it needs .
Hope this helps
DECLARE QT VARCHAR2(10) :=''''; begin SET_BLOCK_PROPERTY('DA_CHECK',DEFAULT_WHERE,'DAC_name = '||QT||:PARAMETER.P_name||QT); end;
and the escape character for PL/SQL is ' the answer should be obvious. If it isn't you still have trial-and-error; like
where char_col = 'SOME_TEXT%':
do set_block_property output get_block_property check sql exit when valid sql while true
Does my example works for you ?i did not , but in your example hamid , i think we do not need these 4 '''', because you use parameters , and params the same as items , differs from variables , and the issue with '=' not like the issue with 'like operator , and in the two cases differs if the case is number or character , that is why i need a rule ?
TEXT_IO.PUT_LINE(ft_tempfile,'where ename like '%V%''');That can't work, where do you escape the first quote?
When defining a String this:
where ename like "%V%"
would produce compile errors as you have a " right inside your string, so you'd have to escape your string. It should look like
String tmp = "where ename like "%V%"";
(the syntax highlighting of jive is actually in java, so for the samples above you should see where the error is ;) )
String tmp = "where ename like \"%V%\"";