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 🙂
A question – I need to select records based on a specific name. the name includes an imbedded ampersand. I am not looking to substitute or display a prompt, so I frame the values with a single quote (‘). Should I be using a different control character to avoid a substitution and have the string treated as a search element?
what type of report are you running? you need a way to slip a SET SCAN OFF into your report
Thank you Jeff, that option appears to resolve the issue.
My best regards,
I have a bind variable question with SQL Developer…maybe just slightly OT.
If I have:
select * from emp where deptno = 10;
and then I do something like this:
variable deptno number
exec :deptno := 10
select * from emp where deptno = :deptno;
If I use F5 it will run, but I’d rather have it in the F9 grid format. If I run it with F9, it will just run the query and a popup will prompt me to enter the deptno value at run time.
Is there a way to get the grid to read this before hand?
thank you for this great answer to my simple question.
At the moment you I saw the answer, it was easy – just never thought I should click on this ‘Style’ button for any report.
Just to do proper references, the statement is based on Chris Antogninis “Troubleshooting Oracle Performance” – page 125.
And yes, you are welcome for the question, the picture, the reference. You are doing a great job promoting SQL*Developer!