4 Replies Latest reply: May 7, 2014 8:41 PM by Doolius RSS

    Update statement based on item values

    Doolius

      I am trying to create a page where a user can enter/select values and click submit, and a table is updated with the selected values.

      I have 6 items on my page:

      TABLE_NAME (popup LOV -4 different table names-)

      COLUMN1 (select list based on the table_name value -displays the column names based on what table is selected-)

      VALUE1 (textbox)

      COLUMN2 (select list based on the table_name value -displays the column names based on what table is selected-)

      OPERATOR (operators such as: =, !=, in, not in, >,<,etc)

      VALUE2(textbox)

      When the user clicks "update" I want the following script to run:

      UPDATE :TABLE_NAME
      SET :COLUMN1 := :VALUE1
      WHERE :COLUMN2 :OPERATOR :VALUE2
      

      I created a DA with the above code but it doesn't compile.

      Is there a way to do this?

       

      APEX: 4.0.2

      THEME: SAND

      SERVER: Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server

       

      Thanks,

      Steven

        • 1. Re: Update statement based on item values
          TexasApexDeveloper

          You could build an update string and then run it via a dynamic action and execute immediate...

           

          Elephants wear tu-tus so they can hide in pine trees. Did you ever see an elephant in a pine tree? No? Well then, you know it works.

           

          Thank you,

           

          Tony Miller
          LuvMuffin Software
          Ruckersville, VA

          • 2. Re: Update statement based on item values
            fac586

            Doolius wrote:

             

            I am trying to create a page where a user can enter/select values and click submit, and a table is updated with the selected values.

            I have 6 items on my page:

            TABLE_NAME (popup LOV -4 different table names-)

            COLUMN1 (select list based on the table_name value -displays the column names based on what table is selected-)

            VALUE1 (textbox)

            COLUMN2 (select list based on the table_name value -displays the column names based on what table is selected-)

            OPERATOR (operators such as: =, !=, in, not in, >,<,etc)

            VALUE2(textbox)

            When the user clicks "update" I want the following script to run:

            1. UPDATE :TABLE_NAME 
            2. SET :COLUMN1 := :VALUE1 
            3. WHERE :COLUMN2 :OPERATOR :VALUE2 

            I created a DA with the above code but it doesn't compile.

            Is there a way to do this?

             

            APEX: 4.0.2

            THEME: SAND

            SERVER: Oracle-Application-Server-10g/10.1.3.1.0 Oracle-HTTP-Server

            Eeek! This is the express service to SQL Injection Central!

             

            What validation do you have in place to prevent this gaping hole into your database being exploited?

             

            Strongly recommend that you reconsider this approach as it is a security nightmare.

            • 3. Re: Update statement based on item values
              Mike Kutz

              From a database standpoint, what you want to do is a horrible idea.  You can try asking over in General Database Discussions to see what they think.

              You're better off teaching people how to write proper UPDATE statements.

               

              actual information

              Things like TABLE NAME, COLUMN NAME, and OPERATOR can not be 'bind' variables.

              (again, this is a database thing, not an apex thing)

               

              If you still want to do that, what you desire is called "dynamic sql".

              You'll need to use DBMS_ASSERT to ensure Bobby Tables doesn't screw you over (ie SQL Injection)

              Then, you'll want to use DBMS_SQL so that you can 'parse' the actual SQL.. (not just 'BIND' it)

               

              Again, you're better off asking people in the General Database Discussions forum as what you want to do is a database thing, not an APEX thing.

               

              MK

               

              ps - for your own safety, I'm not telling you how to do it.

              • 4. Re: Update statement based on item values
                Doolius

                Thanks for the info.

                 

                As far as the SQL Injection goes, I know this is a bad way to do it.

                I was hoping that because most likely the only person who would be using/able to access this page would be the Project Manager, so it's less likely that he will want to/have the knowledge to screw up the database because it would screw up the whole project.

                 

                I think I'll be better off making this page like the other update pages that have javascript validation before the table is updated. I just thought this might be a quick/easy way for the PM to update the records he wants.

                 

                Steven