8 Replies Latest reply on Feb 12, 2019 4:05 PM by Sven W.

    Dtaa Not Being Saved in Database Table

    W.Lionel

      Hello:

      I am running APEX 18.1 with ORDS 18.1 on database 11g (11.2.0.4.0).

       

      I am trying to update a table with data from another table but the update isn't happening.

      Here is the code:

       

      declare

       

      rate char (1000);

      decision varchar2 (2000);

       

      begin

      update table1 set col1 = :P7_RATING;

      select col1 into variable1 from table1;

      select col2 into variable2 from table1;

      update table2 set table2_col1 = variable1

      where table2_column2 = variable2;

       

      commit;

       

      end;

       

      Please point me in the direction to see why the update isn't happening.

        • 1. Re: Dtaa Not Being Saved in Database Table
          Sven W.

          What happend when you run this code directly on the database. Like by useing SQL Developer?

          Do you see any error message?

           

          Your code only has a chance to work, as long as there is only a single row in table1.

           

          Here is your logic just written differently.

          Maybe it helps you to see where the problem is.

           

          declare

           

            variable1 table1.col1%type;

            variable1 table2.col2%type;

           

          begin

            update table1

            set col1 = :P7_RATING;

           

            update table2 t2

            set t2.table2_col1 = :P7_RATING

            where t2.table2_column2 in (select t1.col2 from table1 t1);

           

          end;

           

          Only difference is this code will not raise an error if table1 has more than 1 row.

          However I believe the logic itself is not correctly solving your business requirement (which you didn't mention).

           

          One issue to mention is that you should never use the CHAR datatype. Always use VARCHAR2.

          There is a good chance that your code has issues because the comparison using CHAR is not how you expect it to work.

          • 2. Re: Dtaa Not Being Saved in Database Table
            fac586

            W.Lionel wrote:

             

            Hello:

            I am running APEX 18.1 with ORDS 18.1 on database 11g (11.2.0.4.0).

             

            I am trying to update a table with data from another table but the update isn't happening.

            Here is the code:

             

            declare

             

            rate char (1000);

            decision varchar2 (2000);

             

            begin

            update table1 set col1 = :P7_RATING;

            select col1 into variable1 from table1;

            select col2 into variable2 from table1;

            update table2 set table2_col1 = variable1

            where table2_column2 = variable2;

             

            commit;

             

            end;

             

            Please point me in the direction to see why the update isn't happening.

            Where is this code located?

             

            When/how is it executed?

             

            When/how is the value of P7_RATING set?

             

            The only way that the code posted could work is if TABLE1 contains exactly one row. Is this actually the case?

            • 3. Re: Dtaa Not Being Saved in Database Table
              W.Lionel

              Hello Sven W:

               

              :P7_RATING is in item from the APEX page and table1 already has col2 data. Table1 is a single row table. I use these two sets of data to update table2. I use the col2 data from table1 to identify the record in table2 (which has the column with unique data) then update that record with the variable1 (:P7_RATING).

              • 4. Re: Dtaa Not Being Saved in Database Table
                W.Lionel

                Hello fac586:

                 

                :P7_RATING is in item from the APEX page and table1 already has col2 data. Table1 is a single row table. I use these two sets of data to update table2. I use the col2 data from table1 to identify the record in table2 (which has the column with unique data) then update that record with the variable1 (:P7_RATING).

                 

                The code is run from the APEX page PL/SQL source submitting :P7_RATING.

                • 5. Re: Dtaa Not Being Saved in Database Table
                  Sven W.

                  And I asked, what happened when you run your code directly in the database.

                  SQL Developer will prompt you to input the value for the page item. Real code should be able run there (yours had syntax errors that probably came from simplifying it for the forum).

                   

                  Tell us what happend when you run it in SQL Developer. Or at least in the Apex SQL Workshop.

                  • 6. Re: Dtaa Not Being Saved in Database Table
                    W.Lionel

                    Hello Sven W:

                     

                    The reason why no data was being saved was that my select query was not returning any data. That was because the columns where I was selecting the data from were not satisfying the criteria due to the fact that the data had leading and trailing spaces. Once I TRIM the specific row column the data was selected and saved.

                     

                    Thanks

                    • 7. Re: Dtaa Not Being Saved in Database Table
                      W.Lionel

                      Hello fac586:

                       

                       

                      The reason why no data was being saved was that my select query was not returning any data. That was because the columns where I was selecting the data from were not satisfying the criteria due to the fact that the data had leading and trailing spaces. Once I TRIM the specific row column the data was selected and saved.

                       

                       

                      Thanks

                      • 8. Re: Dtaa Not Being Saved in Database Table
                        Sven W.

                        Good you found out what was causing the issue. As a very broad recommendation: you should never ever need to use the datatype CHAR. Always use VARCHAR2.

                        CHAR is one possible and likely reason, how the trailing spaces entered your system. Wouldn't explain the leading spaces thou.