1 2 Previous Next 16 Replies Latest reply: Jan 4, 2013 11:12 AM by 668136 RSS

    Dynamic SQL execute immediate

    668136
      Hi all,

      We have one table where we keep the query that is going to be executed and the using bind variables.

      But im facing some problems when i try to use it in execute immediate.

      supose

      function validate is
      varaux varchar2(200);
      begin
      for r1 in (select query,
      using_bind
      from table_1) loop
      execute immediate r1.query into varaux using r1.using_bind;
      return varaux;
      end loop;
      return null;
      end;

      I know that's not the way to do it, but can you give me directions in how to...

      Thanks
      Bruna

      Edited by: Bruna on 4/Jan/2013 3:07
        • 1. Re: Dynamic SQL execute immediate
          Keith Jamieson
          Your example does not require execute imemdiate and a bind variable.

          Here instead you should use a parameterised cursor.
          Also validate is not a legal name for a function.
          CREATE OR REPLACE 
          FUNCTION VALIDATE_IT(p_value in varchar2)
          return varchar2
          is
          VARAUX VARCHAR2(200);
          CURSOR R1(p_value in varchar2)
          IS 
          Select * from dual where dummy = p_value;
          BEGIN
          OPEN R1(p_value);
          LOOP
          FETCH R1 INTO VARAUX;
          EXIT WHEN R1%NOTFOUND;
          end loop;
          RETURN VARAUX;
          end validate_it; 
          • 2. Re: Dynamic SQL execute immediate
            Bawer
            Bruna wrote:
            I know that's not the way to do it, but can you give me directions in how to...
            than check doc:
            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm
            • 3. Re: Dynamic SQL execute immediate
              Karthick_Arp
              We have one table where we keep the query that is going to be executed and the using bind variables.
              I would suggest you read

              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1943344500346351703
              • 4. Re: Dynamic SQL execute immediate
                6363
                Bruna wrote:

                We have one table where we keep the query that is going to be executed and the using bind variables.
                Repeat this until you truly understand and believe it.

                Tables are for data, views and stored procedures are for executable code.

                {message:id=10590811}

                Queries are executable code and should not be stored in tables.
                • 5. Re: Dynamic SQL execute immediate
                  668136
                  3360 wrote:
                  Bruna wrote:

                  We have one table where we keep the query that is going to be executed and the using bind variables.
                  Repeat this until you truly understand and believe it.

                  Tables are for data, views and stored procedures are for executable code.

                  {message:id=10590811}

                  Queries are executable code and should not be stored in tables.
                  You should get out of that "box"
                  • 6. Re: Dynamic SQL execute immediate
                    6363
                    Bruna wrote:
                    3360 wrote:
                    Bruna wrote:

                    We have one table where we keep the query that is going to be executed and the using bind variables.
                    Repeat this until you truly understand and believe it.

                    Tables are for data, views and stored procedures are for executable code.

                    {message:id=10590811}

                    Queries are executable code and should not be stored in tables.
                    You should get out of that "box"
                    There is a right way to do things and a wrong way. You are doing it the wrong way.

                    Or do you think this is a brilliant, genius idea that no one else has ever thought of? If so you should read the links posted, it has been thought of many times by people who didn't know any better what a mess they were making.
                    • 7. Re: Dynamic SQL execute immediate
                      668136
                      3360 wrote:
                      Bruna wrote:
                      3360 wrote:
                      Bruna wrote:

                      We have one table where we keep the query that is going to be executed and the using bind variables.
                      Repeat this until you truly understand and believe it.

                      Tables are for data, views and stored procedures are for executable code.

                      {message:id=10590811}

                      Queries are executable code and should not be stored in tables.
                      You should get out of that "box"
                      There is a right way to do things and a wrong way. You are doing it the wrong way.

                      Or do you think this is a brilliant, genius idea that no one else has ever thought of? If so you should read the links posted, it has been thought of many times by people who didn't know any better what a mess they were making.
                      thank you very much for your reply
                      • 8. Re: Dynamic SQL execute immediate
                        BluShadow
                        Bruna wrote:
                        3360 wrote:
                        Bruna wrote:

                        We have one table where we keep the query that is going to be executed and the using bind variables.
                        Repeat this until you truly understand and believe it.

                        Tables are for data, views and stored procedures are for executable code.

                        {message:id=10590811}

                        Queries are executable code and should not be stored in tables.
                        You should get out of that "box"
                        You should learn to understand the fundamentals of good software engineering, especially in terms of databases.

                        Storing queries as data in tables is very poor.
                        • 9. Re: Dynamic SQL execute immediate
                          668136
                          BluShadow wrote:
                          Bruna wrote:
                          3360 wrote:
                          Bruna wrote:

                          We have one table where we keep the query that is going to be executed and the using bind variables.
                          Repeat this until you truly understand and believe it.

                          Tables are for data, views and stored procedures are for executable code.

                          {message:id=10590811}

                          Queries are executable code and should not be stored in tables.
                          You should get out of that "box"
                          You should learn to understand the fundamentals of good software engineering, especially in terms of databases.

                          Storing queries as data in tables is very poor.
                          again, thank you very much for your reply

                          Edited by: Bruna on 4/Jan/2013 6:07
                          • 10. Re: Dynamic SQL execute immediate
                            668136
                            "Queries are executable code and should not be stored in tables."
                            "Storing queries as data in tables is very poor."

                            "Repeat this until you truly understand and believe it."
                            "*Tables are for data, views and stored procedures are for executable code.*"
                            "*Tables are for data, views and stored procedures are for executable code.*"
                            "*Tables are for data, views and stored procedures are for executable code.*"


                            Oh my God

                            Look bad software engineering...

                            select series_query
                            from APEX_APPLICATION_PAGE_FLASH_S

                            Regards
                            • 11. Re: Dynamic SQL execute immediate
                              BluShadow
                              Bruna wrote:
                              "Queries are executable code and should not be stored in tables."
                              "Storing queries as data in tables is very poor."

                              "Repeat this until you truly understand and believe it."
                              "*Tables are for data, views and stored procedures are for executable code.*"
                              "*Tables are for data, views and stored procedures are for executable code.*"
                              "*Tables are for data, views and stored procedures are for executable code.*"


                              Oh my God

                              Look bad software engineering...

                              select series_query
                              from APEX_APPLICATION_PAGE_FLASH_S

                              Regards
                              Don't be an arse. There are always exceptions to the rule, and I doubt you are developing a development system like Application Express.
                              • 12. Re: Dynamic SQL execute immediate
                                668136
                                BluShadow wrote:
                                Bruna wrote:
                                "Queries are executable code and should not be stored in tables."
                                "Storing queries as data in tables is very poor."

                                "Repeat this until you truly understand and believe it."
                                "*Tables are for data, views and stored procedures are for executable code.*"
                                "*Tables are for data, views and stored procedures are for executable code.*"
                                "*Tables are for data, views and stored procedures are for executable code.*"


                                Oh my God

                                Look bad software engineering...

                                select series_query
                                from APEX_APPLICATION_PAGE_FLASH_S

                                Regards
                                Don't be an arse. There are always exceptions to the rule, and I doubt you are developing a development system like Application Express.
                                Ok i understand you are from Oracle so you can... humiliate

                                Cant i use BLOB, CLOB?
                                Cant i use Dynamic SQL?
                                Isnt Oracle the best tool to do the job?

                                Since i put this thread i was called stupid, ignorant, arse...

                                But its ok because you are from ORACLE.

                                Thanks for all the help,

                                Kisses
                                • 13. Re: Dynamic SQL execute immediate
                                  6363
                                  Bruna wrote:

                                  Since i put this thread i was called stupid, ignorant, arse...
                                  You were not called at least 2 out of 3, and the third is questionable.

                                  Although you seem to be trying very hard to prove that you are all of the above with very little help from anyone.
                                  • 14. Re: Dynamic SQL execute immediate
                                    668136
                                    3360 wrote:
                                    Bruna wrote:

                                    Since i put this thread i was called stupid, ignorant, arse...
                                    You were not called at least 2 out of 3, and the third is questionable.

                                    Although you seem to be trying very hard to prove that you are all of the above with very little help from anyone.
                                    i'm not trying to prove anything... again humiliation... you are very good on that, congratulations. but in the rest... pufff

                                    if im not getting any help thats because there are more simple questions that gives points.

                                    i know this is not a simple question, thats why i asked for help

                                    but leave it, its almost done :)

                                    All the best
                                    1 2 Previous Next