1 2 Previous Next 29 Replies Latest reply: Sep 13, 2013 1:35 PM by TSharma-Oracle Go to original post RSS
      • 15. Re: why my data doesnt update?
        EdStevens

        Why don't you just demonstrate the supposed problem?

        It took me all of 2 minutes to do this, and most of that was a setup that you won't need to do:

         

        SQL> create table mytest (ob_max number(3));

         

        Table created.

         

        SQL> insert into mytest values (12);

         

        1 row created.

         

        SQL> commit;

         

        Commit complete.

         

        SQL> select * from mytest;

         

            OB_MAX

        ----------

                12


        Really, just go to sqlplus, insert the data you claim can't be inserted.  copy the entire session and paste it back here.


         

        ============================================================================

        BTW, it would be really helpful if you would go to your profile and give yourself a recognizable name.  It doesn't have to be your real name, just something that looks like a real name.  Who says my name is really Ed Stevens?  But at least when people see that on a message they have a recognizable identity.  Unlike the system generated name of 'ed0f625b-6857-4956-9b66-da280b7cf3a2', which is like going to the pub with a bag over your head.

        ============================================================================

        • 16. Re: why my data doesnt update?
          natpidgeon

          the data is saved in p_pm_property , inf_p_pmPproperty is a trigger

          • 17. Re: why my data doesnt update?
            Sven W.

            2065f302-50bf-4d55-8423-ffcb24d3d429 wrote:

             

            the data is saved in p_pm_property , inf_p_pmPproperty is a trigger

            Sorry to say so, but that is not true.

             

            The name of the trigger is: INF_P_PM_PROPERTY_TGR.

            This trigger insert into a table (might also be a view or a synonym) INF_P_PM_PROPERTY.

            • 18. Re: why my data doesnt update?
              natpidgeon

              OK so i try to insert a new value here it is

              insert into p_pm_property (property_id, overbook_max_percent)

              values ('testvalue', '123');

              and got this error

              ora:12899: value too large for column 'premier'. 'p_pm_property'. "property_id"

              <actual:9, maximun:6>

              • 19. Re: why my data doesnt update?
                Sven W.

                And now read the error message and understand it...

                I give you a hint

                 

                ora:12899: value too large for column 'premier'. 'p_pm_property'. "property_id"

                <actual:9, maximun:6>


                Unfortunatly, this would not fully explain, why you get no error message in you original problem.

                It might be, that there is some additional layer, that hides the oracle exception from you. That is why it is so important, to tell about missing bits and to test it using sql*plus.

                 

                Do you get another error message when using a fitting property_id?

                Like

                 

                insert into p_pm_property (property_id, overbook_max_percent)
                values ('tst123', '123');
                

                 

                I still expect an error message coming from the trigger.

                • 20. Re: why my data doesnt update?
                  natpidgeon

                  AWWW I did a mistake when i test it

                  insert into p_pm_property (property_id, overbook_max_percent)

                  values ('tes', '123');

                  and got this error

                  ora-06502 pl/sql:numeric or value error: number precision too large

                  ora-06512: at "premier.inf_p_pm_property _tgr" line 99

                  ora-04088: error during execution of trigger 'premier.inf_p_pm_propery_tgr'

                  • 21. Re: why my data doesnt update?
                    Sven W.

                    Good! Now we are talking.

                    Here is the reason. The important parts of the trigger are those:

                     

                    CREATE OR REPLACE TRIGGER PREMIER.INF_P_PM_PROPERTY_TGR BEFORE DELETE OR INSERT OR UPDATE ON PREMIER.P_PM_PROPERTY

                    REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

                    DECLARE

                       v_inf_rec  inf_p_pm_property%ROWTYPE;

                       vUser        p_personnel.username%type := sec_pkg.get_username;

                    BEGIN

                    ...

                        IF INSERTING OR UPDATING THEN

                    ...

                            v_inf_rec.overbook_max_percent := :NEW.overbook_max_percent;

                    ...

                     

                       INSERT INTO inf_P_PM_PROPERTY VALUES v_inf_rec;

                    END;

                    /

                     

                    You need to increase the column overbook_max_percent in table inf_p_pm_property from 2 to 3.

                     

                    The datatype of the variable v_inf_rec is inherited from the target table (inf..) and it is number(2) for the overbook_max_percent column.

                    At the moment where you try to fit the 123 into the number(2) variable, you get the "number precision too large" error message

                    • 22. Re: why my data doesnt update?
                      natpidgeon

                      and here is the correct trigger

                       

                      DROP TRIGGER PREMIER.INF_P_PM_PROPERTY_TGR;

                       

                       

                       

                       

                          INSERT INTO inf_P_PM_PROPERTY VALUES v_inf_rec;

                       

                       

                      END;

                      /

                      • 23. Re: why my data doesnt update?
                        natpidgeon

                        i see what you mean but i did that part right?

                        in the table script when i alter the number (2) to number (3)

                        here

                         

                          OVERBOOK_MAX_PERCENT         NUMBER(3)        DEFAULT 25,

                        • 24. Re: why my data doesnt update?
                          Sven W.

                          Yes you did that right on table p_pm_property.

                          But I repeat it for the 5th time or so. You also have a table inf_p_pm_property.

                          • 25. Re: why my data doesnt update?
                            natpidgeon

                            but inf_p_pm_property is NOT a table is a trigger.

                            • 26. Re: why my data doesnt update?
                              TSharma-Oracle

                              You changed the column in p_pm_property table BUT you need to change the same column in inf_P_PM_PROPERTY table as well.

                              • 27. Re: why my data doesnt update?
                                Sven W.

                                2065f302-50bf-4d55-8423-ffcb24d3d429 wrote:

                                but inf_p_pm_property is NOT a table is a trigger.

                                Proove it!

                                 

                                select object_name, object_type
                                from user_objects
                                where upper(object_name) like '%P_PM_PROPERTY%';
                                
                                

                                This will show all tables, triggers and other stuff that you have with names similiar to p_pm_property.

                                 

                                Post the output here.

                                • 28. Re: why my data doesnt update?
                                  TSharma-Oracle

                                  Run this: Run this: Run this: and post the output

                                  alter table inf_P_PM_PROPERTY modify OVERBOOK_MAX_PERCENT  number(3);

                                  • 29. Re: why my data doesnt update?
                                    natpidgeon

                                    thanks guys for help, i really appreciate, i need to learn how to better approach a problem , i would have not think out using sqlplus

                                    1 2 Previous Next