Martin asks –
I am searching for any method to pass any table name to a sql statement in a self-written report.
My initial response was to quote Lewis’ article in Oracle Magazine where he discusses bind variables, Making the Most of Oracle Developer Reports.
So binds work, but what about SQL*Plus Substitution Variables? You’ve seen these before:
& – Input value and use once
&& – Input value and use for entire session.
The answer is, I THINK, ‘Yes.’ Let’s investigate.
Here’s the report I was able to generate with a bit of tinkering:
And here’s how I built it.
First the HTML stuff
Lewis talk about the HTML reporting in his previously cited article.
And now the tricky Substitution Stuff
Set the ‘Style’ to ‘Script’ for your Child Report.
Then insert your SQL*Plus’ish script – note that we support most but not all SQL*Plus commands. For a full list, check out the SQL Developer Help topic on the subject.
SET linesize 100 COLUMN endpoint_value format 999,999 COLUMN endpoint_number format 999,999 COLUMN TOTAL format 999,999 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; SELECT 'Column: &&column' FROM dual; SELECT 'Table: &&table' FROM dual; SELECT 'Buckets: &&bucket' FROM dual;
Then save the report, and let’s run it.
SQL Developer will prompt me for the value of the COLUMN, TABLE, and number of buckets for the analytic call in the ntile function.
It prompts once and then uses the input values throughout the script as it’s ran.
Thanks Martin for the question!
I’m not a total jerk, I did ask Martin for permission to post this, and I expect we’ll continue the discussion below 🙂