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.