Home » SQL & PL/SQL » SQL & PL/SQL » Calling two variables in a script?
Calling two variables in a script? [message #214412] Tue, 16 January 2007 06:07 Go to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Hi all,

I have a quick question I hope ye can help me clarify it. I dont know whether this is the right section to put it into but hope ye can help.

For a project I am working on I have been asked to look into being able to call two variables from a sql* script

Original Script
e.g.
Select Round (to_number(value)/5,0)*5 from dual;

this script rounds a value to the nearest multiple of 5 but can you modify this script to pull in two variables from the database?

e.g.

Select Round (to_number(value1)/value2,0)*value2 from dual;

I hope ye can make sense of what I am trying to say. What I am trying to do is instead of going into the script and having to change it everytime you want to change the multiple e.g. 2,3,4 etc, is there a way that this value can be pulled from the database automatically.

Hope I have explained this enough, any advice that can be given would be really appreciated.



Re: Calling two variables in a script? [message #214425 is a reply to message #214412] Tue, 16 January 2007 07:28 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Do you mean something like this ?

SQL> create table t_value (value1 number, value2 number);

Table created.

SQL> insert into t_value values(11,5);

1 row created.

SQL> commit;

Commit complete.

SQL> var value1 number
SQL> var value2 number
SQL> exec select value1, value2 into :value1, :value2 from t_value;

PL/SQL procedure successfully completed.

SQL> select round(:value1/:value2,0)*:value2 from dual;

ROUND(:VALUE1/:VALUE2,0)*:VALUE2
--------------------------------
                              10

Rgds.
Re: Calling two variables in a script? [message #214447 is a reply to message #214412] Tue, 16 January 2007 08:31 Go to previous messageGo to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Yes exactly thank you very much.

What does : do?
Re: Calling two variables in a script? [message #214448 is a reply to message #214447] Tue, 16 January 2007 08:33 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
":" allows to call bind variable

See http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/ch6.htm#1007557

Rgds.





Re: Calling two variables in a script? [message #214452 is a reply to message #214412] Tue, 16 January 2007 08:53 Go to previous message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Thank you very much
Previous Topic: Which one is more effective?
Next Topic: Extracting a variable value
Goto Forum:
  


Current Time: Fri Dec 02 14:30:25 CST 2016

Total time taken to generate the page: 0.06990 seconds