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 :)