Struggling to write Oracle Sql Query using Substitution Variables

Question

I'm trying to take some of our more complicated queries and make them as user friendly as possible. For various reasons, we can't/would prefer not to bake them into the schema. Thus, I'm trying to write a version of a query we have to run regularly to simplify entering some of the parameters. My current option is to find-replace the pertinent values each time I have to run it, which is less than ideal.

I'd like to leverage TOAD's support of substitution variables (where if one of these is present, it prompts the user to enter the intended values then runs the whole query).

I've attempted to set up a test query to familiarize myself with the process, but it's not behaving as expected.

DECLARE
   &&v_value   VARCHAR2 (200);
   v_result   VARCHAR2 (200);
BEGIN

select &v_value into v_result from dual;
dbms_output.PUT_LINE('result :'|| to_char(v_result));
END;

The result is

result :

Which is of course less than ideal. My expectation is for it to be

result : some_value

Where 'some_value' was the text I entered into the TOAD prompt window.

The reason I need this to work is so that we can treat this as a procedure and pass it the values it needs once and use it throughout the series of queries we have to run. I'm not very familiar or adept with building these kids of queries, so any help or guidance would be appreciated.

I was planning on running it via TOAD's SQL editor window.

The actual procedure I need to run is actually a series of updates and inserts; I used a select as a test case to ensure I understood how to use them.

"so that we can treat this as a procedure and pass it the values it needs once and use it throughout the series of queries we have to run" - could you please edit your question to expand on this please? Also, how are these queries going to be run? Via some sort of front end program or via a SQL script? Knowing both of these things would help us to be able to steer you in the right direction. — Boneist
– Boneist, Commented Aug 17, 2015 at 14:20

Chris Hep · Accepted Answer · 2015-08-17 14:29:17Z

2

It looks like you're having a problem with assigning a value to the parameter. I would use the following syntax as it both avoids doing an unnecessary context switch and allows you to do exception handling on the value passed.

DECLARE
   v_value  VARCHAR2(200);
   v_result VARCHAR2(200);
BEGIN
   v_value := '&input';
   v_result := v_value;
   dbms_output.put_line('result : ' || v_result);
END;
/

answered Aug 17, 2015 at 14:29

Chris Hep

1,1518 silver badges13 bronze badges

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

3 Comments

Boneist Over a year ago

You could just do: BEGIN dbms_output.PUT_LINE('result :'|| to_char('&v_value')); END; - there's no need to assign it to a variable at all. However, I fail to see how the OP thinks running a procedure like this would help with assigning values to their subsequent queries!

Chris Hep Over a year ago

@Boneist I agree that that would be simpler. However, I was aiming to fix the OP's code with as change as possible. I'm sure the actual use case is more involved, so keeping it close it more likely to help.

Marisa Over a year ago

Thank you, this was the gap closer I needed to make sense of what I was trying to do.

Boneist · Accepted Answer · 2015-08-18 11:14:15Z

0

Given that you're running your series of select statements in Toad - presumably as a script - I fail to see why you need a procedure at all.

You could just use the parameter in your queries like so (making sure to run as a script):

set verify on;

select '&&v_val' value from dual;

select * from dual
where  dummy = '&&v_val';

old: select '&&v_val' value from dual
new: select 'X' value from dual

VALUE
-----
X    
1 row selected.


old: select * from dual
where  dummy = '&&v_val'
new: select * from dual
where  dummy = 'X'

DUMMY
-----
X    
1 row selected.

(I deliberately set the verify to be on so that you could see the results of what happens when running the above two statements as a script; you might prefer to switch it off.)

answered Aug 18, 2015 at 11:14

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

2 Comments

Marisa Over a year ago

The actual query involves a series of updates and inserts; the select was just a proof of concept to ensure I understood how to refer to the values and how to use them.

Boneist Over a year ago

same prinicple applies; you still won't need a stored procedure to do the setting of the values. I mean, you can, but you don't have to. Also, in Toad, you could just use a bind variable eg. select :p_val from dual and then the parameter box that appears would allow you to select the correct datatype (ie. so you don't need to put single quotes around a string value)

Collectives™ on Stack Overflow

Struggling to write Oracle Sql Query using Substitution Variables

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related