10 Replies Latest reply: Apr 7, 2013 3:21 AM by salute-Salem RSS

    set_block_property('emp',default_where,...) with variables

    salute-Salem
      db and dev 10g rel2 ,

      i am trying to learn about this built-in with variables and the equal(=) operator , i can not use it with variables , so show me please how to use it with number , charachter and date variables .
      1- working with number variables .
      - i have the block 'scott.emp' , and a button 'search' .
      - my variable 'v' holds the value 7788 , and i want to display the data based on this number of the 'empno' column .
      how to do it with set_block_property ?


      thanks in advance

      Edited by: newbi_egy on Apr 5, 2013 3:11 PM

      Edited by: newbi_egy on Apr 6, 2013 2:55 PM
        • 1. Re: set_block_property('emp',default_where,...) with variables
          indra budiantho
          SET_BLOCK_PROPERTY('block2', DEFAULT_WHERE, 'date = ' || to_date(:block.date,'dd/mm/rrrr')  ||' and emplno = '|| :block.emplno || ' and name= ''' || :block.name || '''');
          go_block('block2');
          EXECUTE_QUERY;
          • 2. Re: set_block_property('emp',default_where,...) with variables
            HamidHelal
            newbi_egy wrote:
            db and dev 10g rel2 ,

            i am trying to learn about this built-in with variables and the equal(=) operator , i can not use it with variables , so show me please how to use it with number , charachter and date variables .
            1- working with number variables .
            - i have the block 'scott.emp' with a text time ':t' and a button 'search' .
            - my variable 'v' holds the value 7788 , and i want to display the data based on this number of the 'empno' column .
            how to do it with set_block_property ?
            why you need of 'V' variable ? in case 1 your code should be
            SET_BLOCK_PROPERTY('emp', DEFAULT_WHERE, 'empno = '|| :t);
            go_block('emp');
            EXECUTE_QUERY;
            >
            2- the variable will hold 'SMITH' .
            3- the variable will hold '17/12/80'
            It's not a wise decision inputting number,date and char data in a single field. date may needs format mask. number doesn't need of single quote and var-char/char needs.

            check the indra budiantho example

            Hope this helps

            Hamid


            If someone's response is helpful or correct, please mark it accordingly.*
            • 3. Re: set_block_property('emp',default_where,...) with variables
              salute-Salem
              why you need of 'V' variable ? in case 1 your code should be
              that was a mistake , i am sorry , i edited my letters of the first post .

              let us talk only about using the variable when holding a number value . -- just this to understand .

              thanks
              • 4. Re: set_block_property('emp',default_where,...) with variables
                HamidHelal
                newbi_egy wrote:
                why you need of 'V' variable ? in case 1 your code should be
                that was a mistake , i am sorry , i edited my letters of the first post .

                let us talk only about using the variable when holding a number value . -- just this to understand .

                thanks
                For empno try in the button below code
                SET_BLOCK_PROPERTY('emp', DEFAULT_WHERE, 'empno = '|| :t);
                go_block('emp');
                EXECUTE_QUERY;
                • 5. Re: set_block_property('emp',default_where,...) with variables
                  salute-Salem
                  HamidHelal
                  let us talk only about using the variable when holding a number value .
                  the variable "v" := 7788 , how to use in set_block_property built-in , with the empno .

                  thanks
                  • 6. Re: set_block_property('emp',default_where,...) with variables
                    HamidHelal
                    newbi_egy wrote:
                    HamidHelal
                    let us talk only about using the variable when holding a number value .
                    the variable "v" := 7788 , how to use in set_block_property built-in , with the empno .

                    thanks
                    TRY
                    DECLARE
                         V NUMBER:=7788;
                    BEGIN
                         
                         SET_BLOCK_PROPERTY('emp', DEFAULT_WHERE, 'empno = '|| V);
                         go_block('emp');
                         EXECUTE_QUERY;
                    
                    END;
                    hope this helps


                    Hamid
                    • 7. Re: set_block_property('emp',default_where,...) with variables
                      salute-Salem
                      this is so correct HamidHelal , but what i want to ask about is ,

                      i know that the part after "default_where" of the set_block_property built-in should reside between two single quotes like so
                      set_block_property('emp',default_where,' ') ;
                      but your letters does not reside between these single quotes ' ' , what resides is just this ('empno=') , and if the whole sentence should reside between single quotes , it should be like so
                      ('''empno='||v'') as the two quotes at the beginning of the sentence and at the end are in fact one quote (one at the beginning and one at the end . -- these are which enclude the whole sentence which is ('empno=') .
                      should not it work like so ? -- sure it is not working , but i want to understand why is not it working ?

                      thanks a lot hamidhelal
                      • 8. Re: set_block_property('emp',default_where,...) with variables
                        HamidHelal
                        newbi_egy wrote:
                        this is so correct HamidHelal , but what i want to ask about is ,

                        i know that the part after "default_where" of the set_block_property built-in should reside between two single quotes like so
                        set_block_property('emp',default_where,' ') ;
                        but your letters does not reside between these single quotes ' ' , what resides is just this ('empno=') , and if the whole sentence should reside between single quotes , it should be like so
                        ('''empno='||v'') as the two quotes at the beginning of the sentence and at the end are in fact one quote (one at the beginning and one at the end . -- these are which enclude the whole sentence which is ('empno=') .
                        should not it work like so ? -- sure it is not working , but i want to understand why is not it working ?
                        Ok. working statement is
                        SET_BLOCK_PROPERTY('emp', DEFAULT_WHERE, 'empno = '|| V);
                        where 'empno = ' is fixed string. variable value of V is concatenate.
                        Following statement also work. and meets set_block_property('emp',default_where,' ') ;
                        SET_BLOCK_PROPERTY('emp', DEFAULT_WHERE, 'empno =7788 ');
                        What is the difference ?
                        Difference is, in the first statement we set the empno value dynamically where second one is fixed.

                        Hope it's clear..

                        Hamid
                        • 9. Re: set_block_property('emp',default_where,...) with variables
                          Andreas Weiden
                          i know that the part after "default_where" of the set_block_property built-in should reside between two single quotes.
                          Thats wrong. The third parameter should be a VARCHAR2, this might be an expression encloed by ', but must not be, e.g. when you concatenate variable.
                          • 10. Re: set_block_property('emp',default_where,...) with variables
                            salute-Salem
                            thanks a lot HAMIDHELAL , you did your best , but really i can not get it , the problem is not just with this situation, but i intended to ask about using the like operator and % , with variables , items and parameters , and these is a lot of things which i think i can not get it this way , but i found a way can simplify the whole issue for me , which is exporting the string to a text file with this code -- i am writing for the others who face the same problem .

                            declare 
                            c varchar2(90) := 's'
                            begin 
                                 x := text_io.fopen('d:\export.txt','w') ; 
                                 text_io.put_line(x,'ename like'||'''%'||c||'''');
                                 text_io.fclose(x) ; 
                                 end ; 
                            or use the whole string ('ename like'||'''%'||c||'''') as a parameter to dbms_output.put_line in an anonymous block in sql*plus .
                            then see how it is displayed . i think it can solve the situation .
                            thanks a lot again and again

                            Edited by: newbi_egy on Apr 7, 2013 1:20 AM