This content has been marked as final. Show 14 replies
If you are using forms items/parameters you can actually use them in the default where like bind variables and not care about quotes at all. Let's say you have a block with one number, one date and one char item (all non-basetable). You'd simply could
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 a little bit harder when you are using PL/SQL variables as you cannot bind them like you can with items or parameters
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')
EDIT: this is actually pen-and-paper writing of code; I didn't compile anything of the above so it might be that some of the ' are missing, but I guess you should get the point.
Edited by: christian erlinger on 27.02.2013 05:25
how to output the string to a file ?
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,
or a way to help me, like the "output file" which i do not know what it means till now .
thanks a lot
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).
All in all it is the same thing as dynamic SQL; when using bind variables (with parameters or items) you don't have to escape at all; otherwise you'll have to take care of that yourself. In the end the (dynamic) Query you are building must be valid SQL, that's why I suggested you to output your query to somewhere so you can check it for errors (if any). A text file is nice as you'd simply have to copy it over to SQL*Plus etc. and execute it to see if it fails. You could of course also show your where clause in an alert, but you'd have to typewrite it instead of copy/paste it...
You can of course play the PL/SQL interpreter yourself by removing all the single ' and filling in the variables if you like; a simple example would be
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 used
a 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;
i appreciate your efforts , but , all your answers suppose that the forms builder compiles but there is no right result ,
but in this case for example , it does not even compile because of the quotes problem ,
i tried to output it to a file ,
v_dir VARCHAR2(250) := 'c:\';
v varchar2(90):= 'SCO' ;
ft_tempfile := TEXT_IO.FOPEN('c:\t.txt','w');
TEXT_IO.PUT_LINE(ft_tempfile,'where ename like '%V%''');
and i tried before an easy way , like assignning the statement to a varchar2 variable , and also i faced a compiling problem , that is why i want to know the role of escaping .
thanks a lot
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 ?
i need to figure it out myself when dealing with any case .
thanks a lot
TEXT_IO.PUT_LINE(ft_tempfile,'where ename like '%V%''');That can't work, where do you escape the first quote?
It might be confusing as ' is the escape character as well as the defining character for strings. Let's say we are in java where the escape character is \ and Strings are defined with ".
Suppose we have the String
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%\"";
now to switch back to PL/SQL you'd simply have to replace the " with ' and the \ with '.
Edited by: christian erlinger on 04.03.2013 05:09