10 Replies Latest reply on Dec 13, 2017 7:01 PM by rp0428

    SqlDeveloper Reports, Bind variable in IN condition and multiple values

    Regis Levesque

      Hi

       

      I created a User Defined Reports base on the Data Dictionnary Reports - Column Comments.

       

      I made a change in the where clause to use a IN condition to get only the table needed. (SqlQuery below)

      When using the IN condition directly, i.e  IN ('tablename1','tablename2'), it work perfectly.

       

      Now, I would like to use a bind variable.

      Using simply tablename1 work great.

      Unfortunately trying to use more that one values i.e. tablename1, tablename2, I can't achieved it.

      After spending on hour googling, I didn't find an answer.

       

      Then hopefully,  someone will be able to find an answer.

       

      Thank you

       

      SELECT c.owner     "Owner",

            c.table_name  "Table_Name",

            c.column_name "Column_Name",

            c.comments    "Comments",

            c.owner        sdev_link_owner,

            c.table_name   sdev_link_name,

            t.table_type   sdev_link_type

      FROM sys.all_col_comments c,

            sys.all_tab_comments t

      WHERE c.owner = user

        AND c.owner = t.owner

        AND c.table_name = t.table_name

        AND (:TABLE_NAME_LIST IS NULL OR

             INSTR(UPPER(c.table_name),UPPER(:TABLE_NAME_LIST)) > 0)

        AND (:COLUMN_NAME IS NULL OR

             INSTR(UPPER(c.column_name),UPPER(:COLUMN_NAME)) > 0)

        AND SUBSTR(c.table_name,1,4) != 'BIN$'

        AND SUBSTR(c.table_name,1,3) != 'DR$'

      AND UPPER(T.TABLE_NAME) IN (UPPER(:TABLE_NAME_LIST))

      ORDER BY c.owner, c.table_name, c.column_name

        • 1. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values

          Wrong forum - this question has NOTHING to do with Sql Developer which is what this forum is for.

           

          Please move the thread to the SQL and PL/SQL forum.

           

          I made a change in the where clause to use a IN condition to get only the table needed. (SqlQuery below)

          When using the IN condition directly, i.e IN ('tablename1','tablename2'), it work perfectly.

           

          Now, I would like to use a bind variable.

          Not possible - variable is SINGULAR - meaning ONE value.

           

          You would need to use dynamic sql to do what you want.

           

          If you need more help than that move the thread to the proper forum.

          • 2. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values
            Gary Graham-Oracle

            Since SQL Developer uses JDBC, the issue is probably (unless more modern JDBC driver versions fix this quirk) related to...

            Due to other bugs in the JDBC driver that I believe are still there, it's also best not to repeat the same bind variable name in the query to have the most predictable behavior. If semantically you need the same value to be bound several times, it's safest to just assign each bind variable a unique name, then bind the same value to all of the bind variable slot-numbers that need to have the same value.

            which is taken from the following blog post:  Clarifying Some Bind Variable Confusion

            • 3. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values
              Gary Graham-Oracle

              Looking at your code, I initially only noted the repeated use of the bind variable :TABLE_NAME_LIST, so that is the subject of my prior post and applies to running that SQL over JDBC.

               

              Upon further inspection, there is another issue...

              1. Need to pass last use of UPPER(T.TABLE_NAME) IN (UPPER(:TABLE_NAME_LIST) as an argument to INSTR( ... > 0) as you did in the prior instance, or else eliminate it.

              • 4. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values

                1. Need to pass last use of UPPER(T.TABLE_NAME) IN (UPPER(:TABLE_NAME_LIST) as an argument to INSTR( ... > 0) as you did in the prior instance, or else eliminate it.

                A bind variable is ONE VALUE - you can't pass pass a list.

                 

                If 'table_name_list' contains "tableA, tableB, tableC" then it will be treated as a string.

                 

                It won't be treated the same as coding ' IN ('TableA', 'tableB', 'tableC')' which contains THREE strings separated by commas.

                • 5. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values
                  Gary Graham-Oracle

                  If 'table_name_list' contains "tableA, tableB, tableC" then it will be treated as a string.

                  True -- no dispute there.  And this is exactly what we could do in a user-defined report in SQL Developer if the report creator wants to check for a variable number of tables within the IN-list, or perhaps a fixed number of tables but not bother with a distinct bind variable for each one.

                   

                  For example, create a report called TestMultipleBinds with the following SQL...

                  SELECT
                    c.owner     "Owner",
                    c.table_name  "Table_Name",
                    c.column_name "Column_Name",
                    c.comments    "Comments",
                    c.owner        sdev_link_owner,
                    c.table_name   sdev_link_name,
                    t.table_type   sdev_link_type
                  FROM sys.all_col_comments c,
                       sys.all_tab_comments t
                  WHERE c.owner = user
                    AND c.owner = t.owner
                    AND c.table_name = t.table_name
                    AND (:TABLE_NAME_LIST1 IS NULL OR
                         INSTR(UPPER(:TABLE_NAME_LIST2), UPPER(c.table_name)) > 0)
                    AND (:COLUMN_NAME1 IS NULL OR
                         INSTR(UPPER(:COLUMN_NAME2), UPPER(c.column_name)) > 0)
                    AND SUBSTR(c.table_name,1,4) != 'BIN$'
                    AND SUBSTR(c.table_name,1,3) != 'DR$'
                  ORDER BY c.owner, c.table_name, c.column_name
                  

                  Note: I use each bind variable name only once and had to reverse the argument order in the OP's INSTR calls so the logic is correct.

                   

                  Next, when running the report in SQL Developer, we get prompted for the bind variables.   I used the HR schema, and set

                  COLUMN_NAME1 and 2 to: DEPARTMENT_ID

                  TABLE_NAME_LIST1 and 2 to: EMPLOYEES, DEPARTMENTS

                  So that the report definition shows the bind variables like this...

                  UserDefinedTestMultipleBindsValues.jpg

                  And the report's query result grid is...

                  UserDefinedTestMultipleBindsResult.jpg

                  Fortunately, in this case, we both get to be right.

                  • 6. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values
                    Regis Levesque

                    rp0428, sorry, but your wrong.

                     

                    Reports is part of sqldeveloper. Si I'm in the good forum.

                    I don't understand your intervention.

                    • 7. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values
                      Regis Levesque

                      Hi Gary

                       

                      I just took a copy of the  Columns Comments Report  found under Dictionary report, Table, Comment.

                      (Note that I'm using SqlDeveloper 17.3.1)

                       

                      Then, I just replace the original :TABLE_NAME by TABLE_NAME_LIST

                       

                      Original code is

                       

                      select c.owner       "Owner",

                      c.table_name  "Table_Name",

                      c.column_name "Column_Name",

                      c.comments    "Comments",

                      c.owner        sdev_link_owner,

                      c.table_name   sdev_link_name,

                      t.table_type   sdev_link_type

                      from sys.all_col_comments c,

                      sys.all_tab_comments t

                      where c.owner = user

                      and c.owner = t.owner

                      and c.table_name = t.table_name

                      and (:TABLE_NAME is null or

                                   instr(upper(c.table_name),upper(:TABLE_NAME)) > 0)

                      and (:COLUMN_NAME is null or

                                   instr(upper(c.column_name),upper(:COLUMN_NAME)) > 0)

                      and substr(c.table_name,1,4) != 'BIN$'

                      and substr(c.table_name,1,3) != 'DR$'

                      order by c.owner, c.table_name, c.column_name

                      • 8. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values
                        Regis Levesque

                        Gary this is the final approach I did.

                         

                        Unfortunately, you will have to create a bind variable for each tables

                         

                        But at least, it's easy to adapt with the number of tables you would need.

                         

                        Thank for your support

                        • 9. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values
                          Gary Graham-Oracle

                          Glad you found a solution that works for you.  This was an interesting and helpful distraction for me since I was able to correct my mistaken belief that repeating a bind variable name might be problematic when using JDBC.  That was an issue with much older JDBC drivers and I imagine hasn't been a problem for quite a long while.

                          • 10. Re: SqlDeveloper Reports, Bind variable in IN condition and multiple values

                            Reports is part of sqldeveloper. Si I'm in the good forum.

                            I don't understand your intervention.

                            Your question is about trying to use a single string that has embedded commas as if it were multiple strings.

                            AND UPPER(T.TABLE_NAME) IN (UPPER(:TABLE_NAME_LIST))

                            That won't work - you need to use dynamic sql to construct a query and concatenate that single string to the other text of the query to form a single string containing the entire query.

                             

                            Then you can use EXECUTE IMMEDIATE to execute the query. That query can use normal bind variables.

                             

                            Using dynamic sql, which is often the wrong thing to do, is a sql and pl/sql topic.