Prompt for determining which list to use in SQL Developer?

Question

I have a group of users who run scripts frequently. I write these scripts for them and usually prompt them for a variable. I am familiar with that. However, I've wrote a script recently and made multiple iterations of it with only one thing changed: a list (EXISTS IN (x, y, z) in script 1, EXISTS IN (a,b,c) in script 2). Is there a way I can prompt my user to select which list they'd like to run, so I can simplify their scripts?

Code, simplified for privacy:

SELECT firstname, lastname
FROM tablename
WHERE somecolumn = &CodeNumber
    AND EXISTS (
        SELECT 1
        FROM table2 
        WHERE anothercolumn = somecolumn
        AND anotherCode IN ('A','B','C') 
        )

Right now, that script has a prompt for Code Number but I'd also like a prompt for what list "anotherCode" will be (either (a,b,c) or (x,y,z) for example, though there are many more than that in reality)

I thought about making a prompt that says something like this:

accept runOption char format A1 prompt 'Type 1 for (a,b,c) or 2 for (x,y,z) '

but even once I've got the run option, I don't know how to "translate that" to the list. Thoughts? TYIA.

SQL Developer version 22.2.0.173 ; Oracle 19c

GMB · Accepted Answer · 2022-11-09 22:48:58Z

3

If you don't mind repeating the parameter in the code, you can use boolean logic:

where 
    ( 
           ( &runOption = 1 and somecolumn in ('a', 'b', 'c') )
        or ( &runOption = 2 and somecolumn in ('d', 'e', 'f') )
    ) 
    and exists (
        ...
    )

That might prompt twice. You could use a bind variable instead, to have SQLDeveloper prompt only once. Or we can work around with case expressions:

where 
    1 = case &runOption
        when 1 then case when somecolumn in ('a', 'b', 'c') then 1 end
        when 2 then case when somecolumn in ('d', 'e', 'f') then 1 end
    end 
    and exists (
        ...
    )

answered Nov 9, 2022 at 22:48

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Littlefoot Over a year ago

Just a remark: as of & usage with substitution variables: if you put two consecutive && signs, then you'll be prompted to enter the value only once. However, if you run the same query again, it'll re-use the same value you've entered previously. If you want to "reset" it, then you'll have to undefine runoption

Lucy Taylor Over a year ago

Wonderful, this solution appears to be working, I've got to get someone else to confirm the data is the correct set pulled. The only thing is ... used to, the results would fall in the Query tab where it could be right-click and exported, but this is putting it out in Script Output. I think the issue is because I'm now having to use f5 instead of f9 due to the fact I'm having to use undefine runOption; and then accept runOption (...); then my actual query. Otherwise, I can't use the case because the actual nonsimplified query contains multiple usages of this list/runOption.

Collectives™ on Stack Overflow

Prompt for determining which list to use in SQL Developer?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related