1 2 Previous Next 17 Replies Latest reply: May 3, 2013 11:26 PM by Tubby RSS

    Can Oracle convert literals to bind variables?

    Thomas Morgan
      Good morning everyone.

      I have a procedure similar to this in our database:
          FUNCTION Get_Tag_Element_Id_For_Set(Element_Attr_Id_Table_In   in    ATTRIBUTE_TABLE_ID_TAB) RETURN NUMBER IS
            v_Select_Statement                    VARCHAR2(32767);
            v_Element_Id                             ELEMENT.Element_Id%TYPE;
          BEGIN
            FOR I IN 1..Element_Attr_Id_Table_In.COUNT LOOP
              IF v_Select_Statement IS NULL THEN
                v_Select_Statement := 'SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Element_Attribute_Id = ' || Element_Attr_Id_Tab_In(i);
              ELSE
                v_Select_Statement := v_Select_Statement || ' intersect ' ||
                                      'SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Element_Attribute_Id = ' || Element_Attr_Id_Table_In(i);
              END IF;
            END LOOP;
            --
            EXECUTE IMMEDIATE v_Select_Statement INTO v_Element_id;
            --
            RETURN v_Element_Id;
          END;
      What this does is to create a query similar to this:

      SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 1 intersect
      SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 2 intersect
      SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 3 intersect
      SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 4 intersect
      SELECT distinct Element_Id FROM ELEMENT_ATTRIBUTE_MANY_2_MANY WHERE Tag_Element_Attribute_Id = 5;


      I am using Dynamic SQL because the number of intersect pieces can vary from 1 to 15 and in the future it can grow.

      The problem that I have is that because the literals are part of the string, the query is being hard parsed every single time. In our production environment this means 100s of thousands of times per day which according to our DBA has added 15% plus to our average load. I know that I can come up with a rewrite to avoid this, but I was wondering if there is a parameter I can set at the session or at the database level where the database itself can convert this kind of query into one with bind variables.

      Thanks,

      Tom

      Edited by: Thomas Morgan on May 3, 2013 8:21 PM
        • 1. Re: Can Oracle convert literals to bind variables?
          Tubby
          Thomas Morgan wrote:
          The problem that I have is that because the literals are part of the string, the query is being hard parsed every single time. In our production environment this means 100s of thousands of times per day which according to our DBA has added 15% plus to our average load. I know that I can come up with a rewrite to avoid this, but I was wondering if there is a parameter I can set at the session or at the database level where the database itself can convert this kind of query into one with bind variables.
          There is a parameter you could set, but the consequences of doing so would not be justified by a single piece of poorly written code that you already know how to fix :)

          If you had an entire application built like this and you needed it to survive until the root problem could be addressed you'd look at using that parameter, but it's only a band-aid ... not a fix.

          Cheers,
          • 2. Re: Can Oracle convert literals to bind variables?
            Tubby
            On another note, this query seems quite strange.

            If you'd care to post some sample data (create table and some sample data to populate it) and explain the results you need I'm quite confident one of the forum members can aid you in creating a more "friendly" query.

            Cheers,
            • 3. Re: Can Oracle convert literals to bind variables?
              Thomas Morgan
              I do not know yet how to fix but there are a couple of ideas in my head which I am executing now.

              Thanks for your reply. Just out of curiosity though and in case I cannot come up with a fix quickly, what is such parameter?
              • 4. Re: Can Oracle convert literals to bind variables?
                Tubby
                Thomas Morgan wrote:
                I do not know yet how to fix but there are a couple of ideas in my head which I am executing now.

                Thanks for your reply. Just out of curiosity though and in case I cannot come up with a fix quickly, what is such parameter?
                https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5180609822543

                The most important takeaway from this post
                "
                in short, cursor sharing is a crutch that MIGHT be useful in some cases to help a poorly written program survive for the period of time the developers are hard at work CORRECTING their bug and putting bind variable support into their application
                "

                As previously mentioned, you really don't want to do this.

                Spend 10 minutes putting together a sample data set for us (create table and insert statements) and you'll probably have dozens of replies on a more efficient query that doesn't need any dynamic SQL (people like puzzles).

                Cheers,
                • 5. Re: Can Oracle convert literals to bind variables?
                  Thomas Morgan
                  I'll put together DDL and data set as soon as I can.

                  BTW, do you what tag I use to ensure my code maintains format (identation)? I do not see it in the markup to the right.

                  I will also post a solution if I find one.

                  Thanks,

                  Thomas
                  • 6. Re: Can Oracle convert literals to bind variables?
                    Tubby
                    Thomas Morgan wrote:
                    I'll put together DDL and data set as soon as I can.
                    Cool!
                    Thomas Morgan wrote:
                    BTW, do you what tag I use to ensure my code maintains format (identation)? I do not see it in the markup to the right.
                    https://wikis.oracle.com/display/Forums/Forums+FAQ

                    I use the
                     tags (surround the block you want with a begin/end tag). 
                    
                    
                    Thomas Morgan wrote:
                    I will also post a solution if I find one.
                    Sounds good. Cheers,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                    • 7. Re: Can Oracle convert literals to bind variables?
                      Thomas Morgan
                      Good old DBMS_SQL to the rescue. It allows you to bind a variable number of variables into your statement.

                      Here is what the solution looks like (My apologies if I changed table name(s)):
                          FUNCTION Get_Element_Id_For_Set(Element_Attr_Id_Tab_In   in    ELEMENT_ATTRIBUTE_ID_TAB) RETURN NUMBER IS
                            v_Statement                           VARCHAR2(32767);
                            v_CursorID                            INTEGER;
                            v_Dummy                               INTEGER;
                            v_Element_Id                             ELEMENT.Element_Id%TYPE;
                            v_Bind_Variable_Index                 PLS_INTEGER := 0;
                          BEGIN
                            FOR I IN 1..Tag_Element_Attr_Id_Tab_In.COUNT LOOP
                              IF v_Statement IS NULL THEN
                                v_Statement := 'SELECT distinct Element_Id FROM ELEMENT_ELEMENT_ATTR_XREF WHERE Element_Attribute_Id = :B' || I;
                              ELSE
                                v_Statement := v_Statement || ' intersect ' ||
                                              'SELECT distinct Element_Id FROM ELEMENT_ELEMENT_ATTR_XREF WHERE Element_Attribute_Id = :B' || I;
                              END IF;
                            END LOOP;
                            --
                            v_Statement := 'BEGIN ' || 'SELECT Element_Id INTO :Element_Id FROM(' || v_Statement || '); END;';
                            --
                            v_CursorID := DBMS_SQL.OPEN_CURSOR;
                            --
                            DBMS_SQL.PARSE(v_CursorID, v_Statement, DBMS_SQL.V7);
                            -- 
                            DBMS_SQL.BIND_VARIABLE(v_CursorID, ':Element_Id', v_Element_Id);
                            --
                            FOR I IN 1..Element_Attr_Id_Tab_In.COUNT LOOP
                              DBMS_SQL.BIND_VARIABLE(v_CursorID, ':B' || I, Element_Attr_Id_Tab_In(i));
                            END LOOP;
                            --
                            v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
                            DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':Element_Id', v_Element_Id);
                            DBMS_SQL.CLOSE_CURSOR(v_CursorID);
                            --
                            RETURN v_Element_Id;
                          END;
                      Edited by: Thomas Morgan on May 3, 2013 8:21 PM
                      • 8. Re: Can Oracle convert literals to bind variables?
                        Thomas Morgan
                        See last posting for solution.
                        • 9. Re: Can Oracle convert literals to bind variables?
                          Tubby
                          Thomas Morgan wrote:
                          Good old DBMS_SQL to the rescue. It allows you to bind a variable number of variables into your statement.
                          Glad you found a way to kick in the bind variables.

                          The SQL you are producing still looks much more complex than need be, but impossible to tell without knowing exactly what you're doing :)

                          Cheers,
                          • 10. Re: Can Oracle convert literals to bind variables?
                            APC
                            Thomas Morgan wrote:
                            FYI {noformat}
                            {noformat} is markup but not XML.  Consequently the closing tag does not have a slash.  That's why your code sample is not formatted correctly. 
                            
                            Cheers, APC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                            • 11. Re: Can Oracle convert literals to bind variables?
                              Thomas Morgan
                              Basically I have an XML document.

                              The document has ELEMENTS.

                              ELEMENTS can have attributes.

                              There is a many to many relationship between ELEMENT and ATTRIBUTES so I have a XREF table to represent this relationship.

                              When I receive a new document, I need to determine if a group of ATTRIBUTES already exist together in the XREF table so I have to find their common ELEMENT_ID or NULL in which case I would create a new ELEMENT and associate the new set of attributes to the new ELEMENT.

                              Since the number of attributes can vary from 1 to around 15 for now, I had to dynamically build the query that find the common ID(intersect) if it exists.

                              Here is some DDL and inserts:

                              CREATE TABLE ELEMENT(ELEMENT_ID NUMBER NOT NULL);

                              CREATE TABLE ELEMENT_ELEMENT_ATTRIBUTE_XREF(Element_Id NUMBER NOT NULL, Element_Attribute_Id NUMBER NOT NULL);

                              ALTER TABLE ELEMENT_ELEMENT_ATTRIBUTE_XREF ADD CONSTRAINT ELEMENT_ATTR_MANY_2_MANY_PK PRIMARY KEY(Element_Id, Element_Attribute_Id);

                              ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_PK PRIMARY KEY(Element_Id);

                              INSERT INTO ELEMENT VALUES(1);
                              INSERT INTO ELEMENT VALUES(2);
                              INSERT INTO ELEMENT VALUES(3);
                              INSERT INTO ELEMENT VALUES(4);

                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(1, 1);
                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(1, 2);

                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(2, 1);

                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 1);
                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 3);
                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 4);
                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 5);

                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(4, 6);
                              INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(4, 7);

                              So for instance if I passed an array (Attribute ID Tab) containing ELEMENT_ATTRIBUTE_ID list 6 and 7 I would get 4 as the answer.

                              Hope this helps,

                              Thanks,

                              Tom
                              • 12. Re: Can Oracle convert literals to bind variables?
                                Thomas Morgan
                                Thanks APC, I'll make a note for future.

                                Any possibility it could be added to the Markup displayed to the right?

                                Thanks,

                                Thomas
                                • 13. Re: Can Oracle convert literals to bind variables?
                                  Tubby
                                  Thomas Morgan wrote:
                                  Basically I have an XML document.

                                  The document has ELEMENTS.

                                  ELEMENTS can have attributes.

                                  There is a many to many relationship between ELEMENT and ATTRIBUTES so I have a XREF table to represent this relationship.

                                  When I receive a new document, I need to determine if a group of ATTRIBUTES already exist together in the XREF table so I have to find their common ELEMENT_ID or NULL in which case I would create a new ELEMENT and associate the new set of attributes to the new ELEMENT.

                                  Since the number of attributes can vary from 1 to around 15 for now, I had to dynamically build the query that find the common ID(intersect) if it exists.

                                  Here is some DDL and inserts:

                                  CREATE TABLE ELEMENT(ELEMENT_ID NUMBER NOT NULL);

                                  CREATE TABLE ELEMENT_ELEMENT_ATTRIBUTE_XREF(Element_Id NUMBER NOT NULL, Element_Attribute_Id NUMBER NOT NULL);

                                  ALTER TABLE ELEMENT_ELEMENT_ATTRIBUTE_XREF ADD CONSTRAINT ELEMENT_ATTR_MANY_2_MANY_PK PRIMARY KEY(Element_Id, Element_Attribute_Id);

                                  ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_PK PRIMARY KEY(Element_Id);

                                  INSERT INTO ELEMENT VALUES(1);
                                  INSERT INTO ELEMENT VALUES(2);
                                  INSERT INTO ELEMENT VALUES(3);
                                  INSERT INTO ELEMENT VALUES(4);

                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(1, 1);
                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(1, 2);

                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(2, 1);

                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 1);
                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 3);
                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 4);
                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(3, 5);

                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(4, 6);
                                  INSERT INTO ELEMENT_ELEMENT_ATTRIBUTE_XREF VALUES(4, 7);

                                  So for instance if I passed an array (Attribute ID Tab) containing ELEMENT_ATTRIBUTE_ID list 6 and 7 I would get 4 as the answer.

                                  Hope this helps,

                                  Thanks,

                                  Tom
                                  Awesome, thanks!

                                  I'm not sure this works 100% for what you need, but I thought I'd give it a shot. I've used a built in array (sys.odcinumberlist) that you should have access to on your installation (assuming you have a relatively recent version of Oracle).

                                  I left out the part where you would loop through the array you are currently passing in to your procedure to build up this new array that you can use in a SQL statement.

                                  Cheers,
                                  create or replace procedure maybe_this
                                  (
                                     in_list           sys.odcinumberlist
                                  )
                                  is
                                     l_commonality     number;
                                  begin
                                  
                                     begin
                                        select
                                           distinct
                                              xref.element_id
                                        into
                                           l_commonality
                                        from
                                              table(cast(in_list as sys.odcinumberlist))  list
                                          ,  element_element_attribute_xref   xref
                                        where
                                           xref.element_attribute_id = list.column_value;
                                   
                                        dbms_output.put_line('match on element_id = ' || l_commonality);
                                   
                                     exception
                                        when too_many_rows or no_data_found
                                        then
                                           dbms_output.put_line('no match');
                                     end; 
                                  
                                  end;
                                  /
                                   
                                  ME_TUBBZ?exec maybe_this(sys.odcinumberlist(1,2));
                                  no match
                                   
                                  PL/SQL procedure successfully completed.
                                   
                                  Elapsed: 00:00:00.01
                                  ME_TUBBZ?exec maybe_this(sys.odcinumberlist(6,7));
                                  match on element_id = 4
                                   
                                  PL/SQL procedure successfully completed.
                                   
                                  Elapsed: 00:00:00.01
                                  • 14. Re: Can Oracle convert literals to bind variables?
                                    Thomas Morgan
                                    Thanks Tubby,

                                    I'll give it a try when I comeback to the office on Monday.

                                    Thomas
                                    1 2 Previous Next