7 Replies Latest reply: Apr 27, 2012 12:46 PM by berx RSS

    pass table name to report dynamically?

    berx
      I am searching for any method to pass any table name to a sql statement in a self-written report.

      a short example:
      I try to create a report which lets me select a column from a table - both (column and table) should be provided at time the report is executed.
      Of course something like
      select :column
      from :table
      does not work; error is ORA-01745.

      Does anyone knows a method to pass a variable string into the SQL before the cursor is created?
        • 1. Re: pass table name to report dynamically?
          Jim Smith
          YOu don't say what tool you are using, but the short answer is no.

          In oracle you can't use bind variable to replace syntax elements, only data.
          • 2. Re: pass table name to report dynamically?
            berx
            Sorry,
            the tool is SQL Developer - 3.1.07

            So it's more a question about clever tricks in SQL Developers Report code, not in oracles RDBMS-bind abilities.
            • 3. Re: pass table name to report dynamically?
              Jeff Smith Sqldev Pm-Oracle
              I think what you are looking for is something like this
              http://www.oracle.com/technetwork/articles/cunningham-sqldev-092226.html

              Let me know if not, we'll circle around and try again!
              • 4. Re: pass table name to report dynamically?
                berx
                Hi Jeff,

                Yes, that's the kind of report I am talking about.

                My big difference is, I don't want to query a well defined table, but any table.
                To be more precise, I'm trying to transfer some of my performance analyzing scripts into reports. In this particular case the statement is
                SELECT count(*), max( :column ) AS endpoint_value, endpoint_number  
                FROM(
                 SELECT val2, ntile( :buckets ) OVER (ORDER BY :column ) AS endpoint_number
                 FROM :table
                )
                 GROUP BY endpoint_number 
                 ORDER BY endpoint_number
                to see how the optimizers histogram might look like for a given table, column and bucket count.

                the original bucket.sql is similar to
                prompt table?   "&&table"
                prompt column?  "&&column"
                prompt buckets? "&&bucket"  
                
                SELECT count(*), max( &&column ) AS endpoint_value, endpoint_number  
                FROM(
                 SELECT  &&column, ntile( &&bucket ) OVER (ORDER BY &&column ) AS endpoint_number
                 FROM &&table
                )
                 GROUP BY endpoint_number 
                 ORDER BY endpoint_number  
                Any idea how to make a report out of this?

                Martin
                • 5. Re: pass table name to report dynamically?
                  Jeff Smith Sqldev Pm-Oracle
                  This works for me Martin -

                  SELECT count(*) TOTAL, max( &&column ) AS endpoint_value, endpoint_number
                  FROM(
                  SELECT &&column, ntile( &&bucket ) OVER (ORDER BY &&column ) AS endpoint_number
                  FROM &&table
                  )
                  GROUP BY endpoint_number
                  ORDER BY endpoint_number;



                  For some reason using the binds in the Worksheet as you wrote it, I was getting unexpected errors. Do you see this if you run your statement in the worksheet?
                  SEVERE     5231     7360605     oracle.dbtools.worksheet.commands.scriptrunner.task.ScriptRunnerTask     java.util.Formatter$FormatSpecifier.checkGeneral(Formatter.java:2862)
                  • 6. Re: pass table name to report dynamically?
                    Jeff Smith Sqldev Pm-Oracle
                    You can't use BINDS in a FROM apparently, although I'm struggling to find that explicitly in the DOCs, so you will need to use & or && in your report query for dynamic FROM clauses.
                    • 7. Re: pass table name to report dynamically?
                      berx
                      Jeff,
                      in your blogpost [http://www.thatjeffsmith.com/archive/2012/04/substitution-variables-html-reports-in-sql-developer/] the sentence
                      Set the ‘Style’ to ‘Script’ for your ... Report.
                      is the key to my question.
                      Thank you for the answer and this post!
                      Martin

                      Edited by: berx on Apr 27, 2012 7:45 PM