12 Replies Latest reply on Jul 19, 2013 6:13 AM by 864019

    How can I execute Dynamic SQL statement in Forms?

    864019

      Hi All,

       

      I have to execute dynamic SQL statement from Forms

       

      Below statement I have to execute

       

      "EXECUTE IMMEDIATE v_stmt INTO v_return;".

       

      Googled for the same got results saying, Better use Database function or procedures to execute these Dynamic Statements but We want to execute in forms only.

       

      Can any one help me..

       

      Thanks,

      Madhu

        • 1. Re: How can I execute Dynamic SQL statement in Forms?
          François Degrelle

          Hello,

           

          EXECUTE IMMEDIATE is a database side statement only. You cannot use it directly inside Forms.

          You can create a stored function that takes the statement in parameter then return the result. If it is select only, you can also use a Forms dynamic group created with create_group_from_query() built-in.

           

          FRancois

          • 2. Re: How can I execute Dynamic SQL statement in Forms?
            864019

            Thanks for the reply.

             

            As per our requirement we are refrain to use Database procedures or functions. So we have to write in forms only.

             

            I need to execute dynamic SQL in form???

             

            Thanks,

            Madhu

            • 3. Re: How can I execute Dynamic SQL statement in Forms?
              Christian Erlinger

              So you payed big bucks for your oracle database license and yet you refuse to use it to it's fullest? A very strange requirement IMO. Anyway; as Francois already said have a look at create_group_from_query


              cheers

              • 4. Re: How can I execute Dynamic SQL statement in Forms?
                François Degrelle

                You can also use the EXEC_SQL Forms internal package, even though it is not easy to drive.

                 

                Francois

                • 5. Re: How can I execute Dynamic SQL statement in Forms?
                  dare

                  Try FORMS_DDL('your statement here');

                  • 6. Re: How can I execute Dynamic SQL statement in Forms?
                    Andreas Weiden

                    Hm,

                     

                    As far as i know, FORMS_DDL doesn't allow to return a value into a variable.

                    • 7. Re: How can I execute Dynamic SQL statement in Forms?
                      dare

                      You're right, I didn't pay enough attention on exact statement in question...

                      • 8. Re: How can I execute Dynamic SQL statement in Forms?
                        864019

                        Hi christian,

                         

                        I will give a brief why we want it from Forms. Even Our existing application works on database packages. Now we are giving our application to other third party. In order to protect our source code We thought to wrap the source code exists in database packages but there are lot of tools out there which will unwrap.

                         

                        Now we are planning to use Forms pll to store all the backend packages and just give executable like (plx,fmx). No code on Database.

                         

                        We are facing problem with this Execute immediate issue. We have few dynamic statements need to be executed. How can we achieve this??

                         

                        Thanks for the support,

                        Madhu

                        • 9. Re: How can I execute Dynamic SQL statement in Forms?
                          Christian Erlinger

                          So in short you are trading code obfuscation for maintainability and the ability to share code between tools? If from somewhere else you need a procedure already implemented in database PL/SQL (and now ported to forms) this would mean you'd need to implement it in every other tool. In times where you might want to integrate your forms with $other_technology and putting stuff on the database is the first step to share functionality you just go the opposite way? And all that because someone is afraid that somebody might steal your source code? I am sorry to be blunt, but this is just plain stupid.

                          Leaving aside that some things like Analytic Functions, Bulk processing or execute immediate are not even available in forms your software consists of how many LOC? How long does it take to bring a new developer up to speed with your source code? Imagine how long that would take for a developer who doesn't have coleagues who know their way around.

                          And just so you know: I work for a ISV selling a closed-source product as well. We have 200+ customers all over the planet. We are well aware that wrapped packages can be reverse engineered. The premise is: stored procedures can be reused in every tool we have, if it makes sense to put stuff on the database by all means do it. If someone would want to reverse engineer our software I'd wish him good luck as some parts are implemented in such a hilarious complicated way I have troubles understanding them (and quite frankly I refuse to understand certain parts, but that's another story). I do work for almost 10 years for that ISV.

                           

                          In any case the possible solutions have already been mentioned: you have exec_sql, create_group_from_query and forms_ddl to execute dynamic SQL in forms whereas forms_ddl is a one way street and most certainly not the thing you need or want. Take a look at the documentation for the other 2 things.

                           

                          cheers

                          • 10. Re: How can I execute Dynamic SQL statement in Forms?
                            Christian Erlinger

                            Oh and by the way: If you are really are afraid that someone would want to steal your source code: how do you protect your code from your own developers? I tell you what: if I'd really want to steal your source code I'd either wait until you offer a job as a developer, apply for it and if I get the job I have full access to your code. If I don't get your code I'd simply bribe one of your developers to get me the code. This is much easier, much more efficient and less time consuming then reverse engineering and most certainly also more likely. So in essence you'd fortify the front door so heavy it even will give you troubles yourself when entering it while you leave the back door open wide with a big sign saying "please enter here" pointing at it as well.

                             

                            cheers

                            • 11. Re: How can I execute Dynamic SQL statement in Forms?
                              InoL

                              I agree with Christian that it is not a good idea to move code from the database to the form. You are now facing one problem, but there could be more. The pl/sql engine of Forms is always (a few) versions behind the database version. There are many constructs in database sql and pl/sql that cannot be used in Forms. and there are many SYS database packages that cannot be used in Forms (utl_file, utl_http to name some examples).

                               

                              Depending on your database version, it is not always possible to unwrap (as far as I know anyway). If code protection is your worry, you can also use PFCLObfuscate

                              • 12. Re: How can I execute Dynamic SQL statement in Forms?
                                864019

                                Thanks.. I will discuss about this with our team.