1

We’ve got a page that displays a list of records, and the user is allowed to check the desired lines, and then get a report printed of those selected records. I’m trying to define the Oracle Report to have a single parameter which would be a comma-delimited list of the IDs to be printed. However it is not working. The SQL is something like this: 

Select * from CEP_TABLE where Table_ID in (:P_IDLIST)

If I define the parameter to be numeric, I get an “invalid parameter input” error when trying to give it 654,655 – it doesn’t like having the comma.

If I define the parameter to be character, it accepts the parameter but then the database gives an “invalid number” error. It appears it is substituting the bind variable with the entire parameter in quotes – e.g.

Select * from CEP_TABLE where Table_ID in (‘654,655’)

But I want it without the quotes:

Select * from CEP_TABLE where Table_ID in (654,655)

I can get the report to work if I define multiple parameters – e.g. Table_ID in (:P1,:P2,:P3,:P4) but they could have a hundred items they want to include so it seems crazy to define 100 parameters – and really… I don’t want any limit.

Has anyone tackled this problem before? – it seems like it would be pretty common. I could have the page write out the selected ids to some temporary table and then define the query to join to that table, but that also seems excessive.

Radagast81
  • 2,921
  • 1
  • 7
  • 21
tanayals
  • 37
  • 1
  • 3
  • 1
    You have to convert you're string into a table to use it in sql. Look here how this can be done: https://stackoverflow.com/questions/3142665/how-to-convert-csv-to-table-in-oracle – Radagast81 Oct 02 '18 at 11:27
  • Is it possible for Reports to turn the list of ticked IDs into a collection of numbers (e.g. `sys.odcinumberlist`) rather than a string where they are concatenated together? – Alex Poole Oct 02 '18 at 11:33
  • 1
    Oracle Reports only allows for basic datatypes to be used as input parameters, so to pass a csv string is absolutely fine. The only other way is the use of a temporary table as described... – Radagast81 Oct 02 '18 at 11:37

2 Answers2

1

There's a hero that comes to rescue, and his name is lexical parameter.

You said that your query looks like this:

select * 
from CEP_TABLE 
where Table_ID in (:P_IDLIST)

Report already contains parameter named p_idlist. Now create another one, manually; let's call it lex_idlist. Its datatype should be character, its length somewhat larger than p_idlist parameter's (for example, if p_idlist is character(50), make lex_idlist character(70)).

Then rewrite query as follows:

select *
from cep_table
&lex_idlist

Go to After Parameter Form trigger which should look like this:

function AfterPForm return boolean is
begin
  :lex_idlist := 'where table_id in (' || :p_idlist || ')';
  return (TRUE);
end;

That's all - compile the report and run it. Enter some values into p_idlist parameter (such as 654,655). The trigger will dynamically create the WHERE clause, store it into the lexical parameter which will then "act" as if it was a real WHERE clause.

Read more about lexical parameters in online Reports Help system.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

This is a very common and non-trivial question.

You need to split a single input string into its component tokens. Since I don't have your table, I illustrate how it's done on the emp table in the standard scott schema; here is how you can get the rows for the list of department numbers given in a single comma-separated string, such as '10,30':

select * from scott.emp
where  deptno in 
    (select to_number(regexp_substr(:p_deptno, '\d+', 1, level)) from dual
       connect by level <= regexp_count(:p_deptno, '\d+')
    )
;

In fairness, there are also methods that don't split the string, and instead play silly games with string comparisons; but those will be quite ineffective - for example they would not allow you to use an index you may have on table_id (or on deptno in my example).