CODEWITHSUNDEEP
oracle-databaseplsql
Karl Johnson
Karl Johnson

Reputation: 111

supply multiple values to substitution variable in PL/SQL

I created the following function for a generic query that is spooled to an xls file:

spool &filename..xls;

SELECT &variables
FROM &table_name
WHERE &conditional;

spool off;

ideally I want the user to be able to enter any amount of variables to the SELECT clause and different conditionals to the WHERE clause

like this

SELECT var1,var2,var3....
WHERE var1 = va OR var1 = otherval.....

Problem is that PL/SQL will cut off the input, for example if I put

define variables = var1,var2

it only takes this var1,

I can't really find anything relating on how this can be done in PL/SQL but obviously not the way I'm trying

Upvotes: 0

Views: 748

Answers (1)

AndyDan
AndyDan

Reputation: 759

If you don't define your variables (or define only one) for the SELECT clause, the user can enter a string of multiple comma-separated values. As long as they are all valid column names, and properly comma-separated, SQL*Plus will substitute the entire string into the query.

I wasn't spooling to a file, though. Doing set verify off before the spool command helps some, but you're still going to get prompts like

Enter value for variables:

Enter value for table_name:

Enter value for conditional:

for each variable. Probably better to do the "define" before starting to spool the results.

Upvotes: 1

Related Questions