Search code examples
oracle-databaseplsql

Is it possible to pass a dynamic number of bind arguments in open-for-using statements?

I have a table of translations that looks like this:

MODULE KEY LANG VALUE
A ONE EN One
A ONE DE Eins
A TWO EN Two

I want to create a script for this table that fetches one row for each different module key combination, with one column for each available language. This should look like this:

MODULE KEY EN DE
A ONE One Eins
A TWO Two NULL

I started with building a plsql script to execute dynamic sql to do this. This sql needs a bind argument for each language. The language values come from user input, so I dont want to concatenate them into the dynamic sql. However, at the moment I have to manually specify each language as a bind argument in the `open-for-using' clause:

open rc for varquery using ( 'EN', 'DE' );
dbms_sql.return_result(rc);

Is it possible to pass a dynamic list of bind arguments to this? A simple using (select distinct ...) does not work here.

Im not sure how to interprete the documentation for this. If I understand this correctly, it only talks about the type that any given bind argument might have.

Dynamic SQL supports all the SQL datatypes. For example, bind arguments can be collections, LOBs, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. For instance, bind arguments cannot be Booleans or index-by tables.

Solution 
  • open rc for varquery using ( 'EN', 'DE' );
dbms_sql.return_result(rc);

    Is it possible to pass a dynamic list of bind arguments to this? A simple using (select distinct ...) does not work here.

    No, the bind arguments cannot be dynamic so you will need to specify a fixed number of arguments.

    You could specify, for example, 10 bind arguments and then return the columns module, key and then 10 language columns. If the user passes in fewer than 10 languages fill the remaining ones with NULL values and if the user passes in more than 10 values ignore any excess.

    Alternatively, don't pivot the data and return three columns module, key and language and if you need to pivot the data then do that in the third-party application that is requesting the data.