This discussion is archived
14 Replies Latest reply: Mar 4, 2013 5:44 AM by Christian Erlinger RSS

set_block_property(' ',default_where,' ') built-in

newbi_egy Explorer
Currently Being Moderated
db and dev 10g rel2 , xp
hi all,
if anyone could demonstrate this built-in to me , when using it with number and charachter or date text_fields ,
specially with (= and like) operators .
because i have a problem with using quotes with it . i do not know the roles for using quotes with it .

for in stance , i have the table emp , and two text items , one to execute the query on the block by the deptno column , and the other with date or charachter column like hiredate or ename , i think the two are the same .

if you could give me an example and demonstrate it please .
thanks in advance
  • 1. Re: set_block_property(' ',default_where,' ') built-in
    Christian Erlinger Guru
    Currently Being Moderated
    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
    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');
    as you can see no quote escaping no matter of the data type.

    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
    set_block_property('basetable_block', default_where, 'where num_col = '||num_val||' or char_col = '''||char_val||'''');
    it get's worse with dates:
    set_block_property('basetable_block', default_where, 'where date_col = to_date('''||to_char(date_val,'dd.mm.yyyy')||''',''dd.mm.yyyy'')');
    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.
     where num_col = '||num_val|| or char_col = '''||char_val||'''
    would be
    where num_col = 3 or char_col = 'ABCD'
    where date_col = to_date('''||to_char(date_val,'dd.mm.yyyy')||''',''dd.mm.yyyy'')
    would be
    where date_col = to_date('01.01.2013','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.

    cheers

    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
  • 2. Re: set_block_property(' ',default_where,' ') built-in
    newbi_egy Explorer
    Currently Being Moderated
    If in doubt with escaping simply output the string to e.g. a file,
    what is e.g. ?

    thanks a lot
  • 3. Re: set_block_property(' ',default_where,' ') built-in
    Christian Erlinger Guru
    Currently Being Moderated
    e.g. means for example

    http://en.wiktionary.org/wiki/e.g.

    cheers
  • 4. Re: set_block_property(' ',default_where,' ') built-in
    newbi_egy Explorer
    Currently Being Moderated
    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
  • 5. Re: set_block_property(' ',default_where,' ') built-in
    Christian Erlinger Guru
    Currently Being Moderated
    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
    set_block_property('block', default_where, 'char_column like '||vText||'%');
    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 ;)

    cheers
  • 6. Re: set_block_property(' ',default_where,' ') built-in
    newbi_egy Explorer
    Currently Being Moderated
    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 .
  • 7. Re: set_block_property(' ',default_where,' ') built-in
    HamidHelal Guru
    Currently Being Moderated
    semsem wrote:
    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 .
    Here is an example what i used
    DECLARE
         QT VARCHAR2(10) :='''';
    begin
         SET_BLOCK_PROPERTY('DA_CHECK',DEFAULT_WHERE,'DAC_name = '||QT||:PARAMETER.P_name||QT);
    
    end;
    Hope this helps

    Hamid
  • 8. Re: set_block_property(' ',default_where,' ') built-in
    Christian Erlinger Guru
    Currently Being Moderated
    Well, if the (interpreted) end result should look like
    where char_col = 'SOME_TEXT%':
    and the escape character for PL/SQL is ' the answer should be obvious. If it isn't you still have trial-and-error; like
    do
      set_block_property
      output get_block_property
      check sql
      exit when valid sql
    while true
    cheers
  • 9. Re: set_block_property(' ',default_where,' ') built-in
    newbi_egy Explorer
    Currently Being Moderated
    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 ,

    DECLARE
    v_dir VARCHAR2(250) := 'c:\';
    ft_tempfile TEXT_IO.FILE_TYPE;
    v varchar2(90):= 'SCO' ;
    begin

    ft_tempfile := TEXT_IO.FOPEN('c:\t.txt','w');
    TEXT_IO.PUT_LINE(ft_tempfile,'where ename like '%V%''');


    TEXT_IO.FCLOSE(ft_tempfile);
    end ;

    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
  • 10. Re: set_block_property(' ',default_where,' ') built-in
    HamidHelal Guru
    Currently Being Moderated
    Does my example works for you ?
  • 11. Re: set_block_property(' ',default_where,' ') built-in
    newbi_egy Explorer
    Currently Being Moderated
    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
  • 12. Re: set_block_property(' ',default_where,' ') built-in
    Christian Erlinger Guru
    Currently Being Moderated
    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
    where ename like "%V%"
    When defining a String this:
    String tmp = "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 ;) )

    now to switch back to PL/SQL you'd simply have to replace the " with ' and the \ with '.

    cheers

    Edited by: christian erlinger on 04.03.2013 05:09

    typo
  • 13. Re: set_block_property(' ',default_where,' ') built-in
    newbi_egy Explorer
    Currently Being Moderated
    can not i use q'[]?
  • 14. Re: set_block_property(' ',default_where,' ') built-in
    Christian Erlinger Guru
    Currently Being Moderated
    I don't know if forms PL/SQL is capable of using String Literals; You'd have to try. I guess it should work for forms 10gR2 as it basically uses the 10gR1 PL/SQL engine where String literals are available.

    See
    http://docs.oracle.com/cd/B14117_01/server.101/b10759/sql_elements003.htm#i42617

    cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points