9 Replies Latest reply: Jan 18, 2013 10:13 AM by HamidHelal RSS

    Plsql code - error

    Rdk
      Dear sir,

      I am using forms 6i and oracle 9i.

      I have 2 blocks

      Master Block_a

      invoice_no - database item
      invoice_date ( not a database column )
      Search button

      Details Block_b

      invoice_no - database item
      invoice_date - database item

      My master and details relation is invoice_no and invoice_date

      when button pressed trigger in search button is

      declare
      v_where_1 varchar2(10);
      v_where_2 varchar2(10);
      begin
      v_Where_1 := ' invoice = '||:block_a.invoive_no;
      v_where_2 := ' invoice_date = ' ||:block_a.invoive_date;
      set_block_property('block_b', default_where, v_where_1|| 'and' || v_where_2);
      go_block('block_b');----detailed block
      execute_query;
      end;

      after pressing search button it clear invoice_date from master block

      and displaying error

      Frm-40735 when button trigger raise unhandled exception ORA-06502

      Kindly give help me solve this problem and also suggest me if there is any other alternate method.

      Regards
      rdk
        • 1. Re: Plsql code - error
          François Degrelle
          Hello,

          Most of the times that errors raises when you try to store more characters than the declared variable can handle, or maybe you have wrong data in a string you want to convert to number.
          Check that out.

          Francois
          • 2. Re: Plsql code - error
            CraigB
            Francios is right. Take a look at your code:
            declare
            v_where_1 varchar2(10);
            v_where_2 varchar2(10);
            begin
            v_Where_1 := ' invoice = '||:block_a.invoive_no;
            v_where_2 := ' invoice_date = ' ||:block_a.invoive_date;
            Your problem is that your V_WHERE_? variables are too small for your value assignment right from the start. V_WHERE_1's value is 11 characters without the BLOCK_A.INVOIVE_NO and V_WHERE_2 is 16 characters without the BLOCK_A.INVOIVE_DATE.

            You need to size your variables bigger.

            Craig...
            • 3. Re: Plsql code - error
              HamidHelal
              Rdk wrote:
              Dear sir,

              I am using forms 6i and oracle 9i.

              I have 2 blocks

              Master Block_a

              invoice_no - database item
              invoice_date ( not a database column )
              Search button

              Details Block_b

              invoice_no - database item
              invoice_date - database item

              My master and details relation is invoice_no and invoice_date

              when button pressed trigger in search button is

              declare
              v_where_1 varchar2(10);
              v_where_2 varchar2(10);
              begin
              v_Where_1 := ' invoice = '||:block_a.invoive_no;
              v_where_2 := ' invoice_date = ' ||:block_a.invoive_date;
              set_block_property('block_b', default_where, v_where_1|| 'and' || v_where_2);
              go_block('block_b');----detailed block
              execute_query;
              end;

              after pressing search button it clear invoice_date from master block

              and displaying error

              Frm-40735 when button trigger raise unhandled exception ORA-06502

              Kindly give help me solve this problem and also suggest me if there is any other alternate method.
              rdk,
              try this
              declare
              v_where_1 varchar2(200);
              v_where_2 varchar2(200);
              QT VARCHAR2(10) :='''';
              begin
              v_Where_1 := ' invoice = '||QT||:block_a.invoive_no||QT; --- IF INVOICE_NO DATA TYPE IS CHAR/VARCHAR2 
              v_where_2 := ' invoice_date = ' ||QT||:block_a.invoive_date||QT;-- IF INVOICE_DATE DATA TYPE IS DATE
              
              go_block('block_b');----detailed block
              set_block_property('block_b', default_where, v_where_1|| ' and ' || v_where_2);
              execute_query;
              end;
              Hope this works....


              Hamid

              If someone's response is helpful or correct, please mark it accordingly.*
              • 4. Re: Plsql code - error
                Rdk
                Thanks for you reply

                I have checked both

                invoice_no - char (10)
                invoice_date - date (11)
                I have used format mask for invoice_date- DD-MM-YYYY

                Increased both variables to varchar2(16)

                v_where1 varchar2(16);
                v_where1 varchar2(16);

                Still i am getting same error

                regards
                rdk
                • 5. Re: Plsql code - error
                  HamidHelal
                  Did you test my code ?

                  Hamid
                  • 6. Re: Plsql code - error
                    Rdk
                    Yes Mr.hamidHelal

                    I tested your code master block record is displaying

                    but details block is not showing any record.

                    error-Frm-41009 function key not allowed

                    master block invoice_date is empty after retriving the data

                    regards
                    rdk
                    • 7. Re: Plsql code - error
                      HamidHelal
                      Give the Invoice_no and Invoice_date value and try. then let me know what you got.

                      Hamid
                      • 8. Re: Plsql code - error
                        Rdk
                        Thanks mr francois,craigb,Hamidhelal for your help

                        it is ok now

                        How restore my invoice_date (non-database item ) invoice_date from block_a after execute_query

                        regards
                        rdk
                        • 9. Re: Plsql code - error
                          HamidHelal
                          Rdk wrote:
                          Thanks mr francois,craigb,Hamidhelal for your help

                          it is ok now

                          How restore my invoice_date (non-database item ) invoice_date from block_a after execute_query
                          Hi, rdk

                          2 way.
                          1. After execute query re-assign the value in the item.
                          2. delete the relation between block_a and Block_b.


                          Hope this works...


                          Hamid



                          If someone's response is helpful or correct, please mark it accordingly.