Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Substitution Variables & HTML Reports in SQL Developer

2 Mins Read

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:

Not sure this right, but that’s what you get when you offshore your development to me 😉

And here’s how I built it.

First the HTML stuff

Set the the style to ‘PLSQL DBMS Output’

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.

Substitution Variable Prompt

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 🙂

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

5 Comments

  1. Moris Reply

    Hello Jeff,
    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?
    Thank you

    • thatjeffsmith Post Author

      what type of report are you running? you need a way to slip a SET SCAN OFF into your report

    • Moris

      Thank you Jeff, that option appears to resolve the issue.
      My best regards,
      Moris

  2. David Donovan Reply

    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?

  3. Martin Berger Reply

    Hi Jeff,
    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!
    Martin

Write A Comment